Re: [GENERAL] User Privileges using dblink

[DeJuan Jackson]

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:[EMAIL PROTECTED]
Gesendet: Dienstag, 22. Juni 2004 16:05
An: Kreißl, Karsten
Cc: [EMAIL PROTECTED]
Betreff: Re: [GENERAL] User Privileges using dblink 

=?iso-8859-1?Q?=22Krei=DFl=2C_Karsten=22?= <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly
 


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly