Thread: User Privileges using dblink

Hello,

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

create view inst as select * from dblink('host=pollux port=5432 dbname=cob_int user=his_int password=#integration#',
.....

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=pollux 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
usercan 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','select .... 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

=?iso-8859-1?Q?=22Krei=DFl=2C_Karsten=22?= <KREISSL@his.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

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,
withoutpublishing 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@sss.pgh.pa.us]
Gesendet: Dienstag, 22. Juni 2004 16:05
An: Kreißl, Karsten
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] User Privileges using dblink


=?iso-8859-1?Q?=22Krei=DFl=2C_Karsten=22?= <KREISSL@his.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

On Wed, Jun 23, 2004 at 09:03:37AM +0200, "Kreißl, Karsten" wrote:

> 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.

Personally, I think it's a very bad idea to use a different database for
this kind of data exchange.  I'd go with using different schemas.
There's a lot more fine grained control on access privileges, and users
won't be able to see any password; also, the performance should be much
better.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El número de instalaciones de UNIX se ha elevado a 10,
y se espera que este número aumente" (UPM, 1972)

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,
withoutpublishing username and password. 
>Our background is a migration from INFORMIX DB to PostgreSQL. Using INFORMIX there is a rather simple solution for
thisproblem, called Synonyms. 
>
>Regards
>    Karsten
>
>-----Ursprüngliche Nachricht-----
>Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>Gesendet: Dienstag, 22. Juni 2004 16:05
>An: Kreißl, Karsten
>Cc: pgsql-general@postgresql.org
>Betreff: Re: [GENERAL] User Privileges using dblink
>
>
>=?iso-8859-1?Q?=22Krei=DFl=2C_Karsten=22?= <KREISSL@his.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@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>