Re: User Privileges using dblink

Hello Tom,

Ok, we have changed our authentication to password. Sorry, my mistake.

But, under this conditions we must specify username and password (without encryption!) in the view definition.
Every user can read this information using pgadmin or other tools. It's very simple !
In our environment the remote DB knows the same users as our local DB. So we are always searching for a solution, without publishing username and password.
Our background is a migration from INFORMIX DB to PostgreSQL. Using INFORMIX there is a rather simple solution for this problem, called Synonyms.

Regards
Karsten

-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Gesendet: Dienstag, 22. Juni 2004 16:05
An: Kreißl, Karsten
Cc: pgsql-general(at)postgresql(dot)org
Betreff: Re: [GENERAL] User Privileges using dblink

=?iso-8859-1?Q?=22Krei=DFl=2C_Karsten=22?= <KREISSL(at)his(dot)de> writes:
> The second problem with dblink is a security hole.

> create view myinst as select * from dblink('dbname=sva4_int1','select .... from inst') as (.......);

This is not a security hole in dblink, it is a security hole in your
pg_hba.conf setup. Don't use trust authentication.

> This problem could also be resolved, if dblink uses the current login
> information.

That seems completely impractical. In the first place, it's not a
reasonable default (there's no good reason to assume that the remote
DB has the same users as the local), and in the second place dblink
cannot get at the user's password. (We *would* have a security hole
if it could.)

regards, tom lane