Обсуждение: Using dblink to connect as non-superuser

I try to use dblink_connect while logged into the database as the
superuser and it returns OK, but if I disconnect and use the same
command as follows, it give and error, password is required. Even if I
try to use the superuser login in the connect string below while
connected as a non-superuser, I get the error...

select dblink_connect('dbname=egroupware user=myuser password=mypass');

Does this mean I have to be connected to the db as the superuser? I
assume not since reading the docs and having the ability to use user and
password in my connection string. I can try the same command as above
with only the dbname while logged in as the superuser and works. All
works fine with superuser, but not any non-superuser. I am trying to
develop a trigger pl/pgsql on this 8.3 server, but the connections to
the db will be from a non-superuser from a web site connection. What am
I doing wrong?

My non-superuser owns both db's. The error hints that the target
server's authentication method must be changed. I have tried to setup
localhost to trust in pg_hba.conf, still no help.

--
Robert

Robert Fitzpatrick wrote:
> I try to use dblink_connect while logged into the database as the
> superuser and it returns OK, but if I disconnect and use the same
> command as follows, it give and error, password is required. Even if I
> try to use the superuser login in the connect string below while
> connected as a non-superuser, I get the error...
>
> select dblink_connect('dbname=egroupware user=myuser password=mypass');
>
> Does this mean I have to be connected to the db as the superuser? I
> assume not since reading the docs and having the ability to use user and
> password in my connection string.

Did you install it as postgres and not follow up with a GRANT to PUBLIC?

Anyway a very old memory and a quick look at the dblink.sql indicates
that may be needed.


Rod
--
> I can try the same command as above
> with only the dbname while logged in as the superuser and works. All
> works fine with superuser, but not any non-superuser. I am trying to
> develop a trigger pl/pgsql on this 8.3 server, but the connections to
> the db will be from a non-superuser from a web site connection. What am
> I doing wrong?
>
> My non-superuser owns both db's. The error hints that the target
> server's authentication method must be changed. I have tried to setup
> localhost to trust in pg_hba.conf, still no help.
>

Robert Fitzpatrick <lists@webtent.net> writes:
> I try to use dblink_connect while logged into the database as the
> superuser and it returns OK, but if I disconnect and use the same
> command as follows, it give and error, password is required. Even if I
> try to use the superuser login in the connect string below while
> connected as a non-superuser, I get the error...

This probably means that the remote database is not in fact prompting
for a password on this connection.  For security reasons dblink wants
non-superuser callers to supply a password that actually gets used
--- otherwise they are effectively able to masquerade as the local
superuser to the remote database.

            regards, tom lane