Re: User Privileges using dblink

I've never used dblink, but why don't you store your connection strings
in a table.

CREATE connections (id SERIAL PRIMARY KEY, conn_str text NOT NULL);
The create a function as SECURITY DEFINER that takes id as a parameter
and returns the conn_str
CREATE OR REPLACE FUNCTION get_connection(INT) RETURNS TEXT STABLE
RETURNS NULL ON NULL INPUT SECURITY DEFINER AS 'SELECT conn_str FROM
connections WHERE id = $1';
You can revoke read from that table by everyone besides the user
defining the function, then create the view as: SELECT * FROM
dblink(get_connection(5) ...);

Haven't tried it, but I hope it leads you down the right path.

Kreißl, Karsten wrote:

>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>