Обсуждение: BUG #5583: dblink connection error

The following bug has been logged online:

Bug reference:      5583
Logged by:          Piergiorgio Buongiovanni
Email address:      piergiorgio.buongiovanni@netspa.it
PostgreSQL version: 8.4.3
Operating system:   x86_64-redhat-linux-gnu
Description:        dblink connection error
Details:

We are trying to use dblink and following instructions on your manual pages
we are trying to execute the following command with user sa on a db named
netdw:

SELECT * FROM dblink(
'dbname=lportal',
'SELECT b.name FROM users_roles a LEFT OUTER JOIN role_ b ON b.roleid =
a.roleid WHERE a.userid = 13902 ORDER BY 1')
AS roles (Role varchar)

The above command works and returns a list of roles as expected. If now we
change the user and execute the following command with user netdw_owner on
the same db named netdw:

SELECT * FROM dblink(
'dbname=lportal user=sa password=xxx',
'SELECT b.name FROM users_roles a LEFT OUTER JOIN role_ b ON b.roleid =
a.roleid WHERE a.userid = 13902 ORDER BY 1')
AS roles (Role varchar)

we obtain the following error:

ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a
password.
HINT:  Target server's authentication method must be changed.


********** Errore **********

ERROR: password is required
Stato SQL: 2F003
Dettaglio: Non-superuser cannot connect if the server does not request a
password.
Suggerimento: Target server's authentication method must be changed.

Could you explain how to solve this problem?
Regards.

On 07/30/2010 07:04 AM, Piergiorgio Buongiovanni wrote:
> we obtain the following error:
>=20
> ERROR:  password is required
> DETAIL:  Non-superuser cannot connect if the server does not request a
> password.
> HINT:  Target server's authentication method must be changed.
>=20
>=20
> ********** Errore **********
>=20
> ERROR: password is required
> Stato SQL: 2F003
> Dettaglio: Non-superuser cannot connect if the server does not request a
> password.
> Suggerimento: Target server's authentication method must be changed.
>=20
> Could you explain how to solve this problem?


This is *not* a bug -- please post to the correct list next time.

> HINT:  Target server's authentication method must be changed.

Please see:
  http://www.postgresql.org/docs/8.4/interactive/auth-pg-hba-conf.html

Specifically, as suggested by the hint:
  auth-method

Also read:
  http://www.postgresql.org/docs/8.4/interactive/contrib-dblink-connect.html

-and-


http://www.postgresql.org/docs/8.4/interactive/contrib-dblink-connect-u.html

HTH,

Joe


--=20
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support