User Privileges using dblink

[Kreißl, Karsten]

Hello,

we want use dblink to connect several databases in a client/server environment. Connection from local users to the remote databases should be possible only for privileged users. We tried a solution with dblink, embedding this command in a view, like this:

create view inst as select * from dblink('host=po llux port=5432 dbname=cob_int user=his_int password=#integ ration#', .....

This solution is insecure, because login and password is readable for everyone.

We tried to call dblink without username and login, but it fails,i.e.

create view inst as select * from dblink('host=po llux port=5432 dbname=cob_int' , .....

What we are searching for, is a solution which uses the current login information (user and password).
The second problem with dblink is a security hole. If you have a table without any grants for the current user, this user can create a view to circumvent the table privileges, i.e..

Current user is svawork (not a superuser!). Current database is sva4_int1. Table inst has privileges only for a user sva. If user svawork tried to read from inst it fails. This is ok.
If svawork create a view like:

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

The view connect not to a remote database. It uses the local database.
You can read the data from table inst without any restrictions! (Select * from myinst ...)
This problem could also be resolved, if dblink uses the current login information.

Any solutions welcome.

Karsten


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

=?iso-8859-1?Q?=22Krei=DFl =2C_Karsten=22? = <KR*****@his.de > writes:

The second problem with dblink is a security hole. create view myinst as select * from dblink('dbname= sva4_int1','sel ect .... 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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match