Обсуждение: Is this a security risk?

I would like to provide a limited view of my database to some users,
so i thought of creating a second database (I can control access by IP
address through pg_hba.conf) with some views that queried the first
database using dblink.

The problem is that dblink requires non-superusers to provide a
password, but i would like to use the authentication from the first
database connection in the second dblink connection.

I can do this with the example below, but i was wondering is this a
really bad idea or does it create a security hole?

Example code:

CREATE DATABASE test1;
CREATE DATABASE test2;

\c test1
CREATE TABLE test (id int);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);

\c test2
CREATE OR REPLACE FUNCTION my_func() RETURNS SETOF record
     AS $$
  DECLARE
     _username text;
     _query text;
     _row record;
     old_path text;

  BEGIN

     old_path := pg_catalog.current_setting('search_path');
     PERFORM pg_catalog.set_config('search_path', 'public, pg_temp',
true);

     SELECT INTO _username session_user;

     _query := 'SELECT * FROM dblink(''dbname=test1'', ''SET SESSION
AUTHORIZATION ' || _username || ';';
     _query := _query || ' SELECT * FROM test'') ';
     _query := _query || '  AS t1(id int);';

     FOR _row IN EXECUTE _query LOOP
      RETURN NEXT _row;
     END LOOP;

     PERFORM pg_catalog.set_config('search_path', old_path, true);

  END;
$$
     LANGUAGE plpgsql SECURITY DEFINER;


SELECT * FROM my_func() AS (id int);


thanks for any help

adam

Adam Witney wrote:
> I would like to provide a limited view of my database to some users,
> so i thought of creating a second database (I can control access by IP
> address through pg_hba.conf) with some views that queried the first
> database using dblink.

In my opinion dblink is not the right tool for that.
It will require a user account on the "secret" database through which
dblink accesses it. You'd have to restrict permissions for that user
if you want to keep the thing secure.

So why not access the "secret" database directly with that user and
get rid of the added difficulty of dblink?

You can rely on the permission system. Just grant the user the appropriate
privileges on the necessary objects, and if you need the user to see
only part of the data in a table, create a view for that.

Yours,
Laurenz Albe

On 17 Dec 2008, at 07:48, Albe Laurenz wrote:

> Adam Witney wrote:
>> I would like to provide a limited view of my database to some users,
>> so i thought of creating a second database (I can control access by
>> IP
>> address through pg_hba.conf) with some views that queried the first
>> database using dblink.
>
> In my opinion dblink is not the right tool for that.
> It will require a user account on the "secret" database through which
> dblink accesses it. You'd have to restrict permissions for that user
> if you want to keep the thing secure.
>
> So why not access the "secret" database directly with that user and
> get rid of the added difficulty of dblink?
>
> You can rely on the permission system. Just grant the user the
> appropriate
> privileges on the necessary objects, and if you need the user to see
> only part of the data in a table, create a view for that.

thanks for your reply,

The user already has permissions within the 'secret' database, but
normally they interact with it through a web interface only. I was
worried that the user could get in and mess around with other things,
such as the sequences which are used to populate primary keys.

Also ideally I only wanted to create a read only access to certain
parts of the database, I couldn't think of any other way to do it...
are there any more standard ways of doing this?

thanks again

adam

Adam Witney wrote:
>>> I would like to provide a limited view of my database to some users,
>>> so i thought of creating a second database (I can control access by
>>> IP
>>> address through pg_hba.conf) with some views that queried the first
>>> database using dblink.
>>
>> In my opinion dblink is not the right tool for that.
>> It will require a user account on the "secret" database through which
>> dblink accesses it. You'd have to restrict permissions for that user
>> if you want to keep the thing secure.
>>
>> So why not access the "secret" database directly with that user and
>> get rid of the added difficulty of dblink?
>>
>> You can rely on the permission system. Just grant the user the
>> appropriate
>> privileges on the necessary objects, and if you need the user to see
>> only part of the data in a table, create a view for that.
>
> thanks for your reply,
>
> The user already has permissions within the 'secret' database, but
> normally they interact with it through a web interface only. I was
> worried that the user could get in and mess around with other things,
> such as the sequences which are used to populate primary keys.
>
> Also ideally I only wanted to create a read only access to certain
> parts of the database, I couldn't think of any other way to do it...
> are there any more standard ways of doing this?

Yes.

You grant read access with GRANT SELECT ON table/view TO user.

It's no less secure than accessing a database as that user via dblink.

Yours,
Laurenz Albe

On 17 Dec 2008, at 14:44, Albe Laurenz wrote:

> Adam Witney wrote:
>>>> I would like to provide a limited view of my database to some
>>>> users,
>>>> so i thought of creating a second database (I can control access by
>>>> IP
>>>> address through pg_hba.conf) with some views that queried the first
>>>> database using dblink.
>>>
>>> In my opinion dblink is not the right tool for that.
>>> It will require a user account on the "secret" database through
>>> which
>>> dblink accesses it. You'd have to restrict permissions for that user
>>> if you want to keep the thing secure.
>>>
>>> So why not access the "secret" database directly with that user and
>>> get rid of the added difficulty of dblink?
>>>
>>> You can rely on the permission system. Just grant the user the
>>> appropriate
>>> privileges on the necessary objects, and if you need the user to see
>>> only part of the data in a table, create a view for that.
>>
>> thanks for your reply,
>>
>> The user already has permissions within the 'secret' database, but
>> normally they interact with it through a web interface only. I was
>> worried that the user could get in and mess around with other things,
>> such as the sequences which are used to populate primary keys.
>>
>> Also ideally I only wanted to create a read only access to certain
>> parts of the database, I couldn't think of any other way to do it...
>> are there any more standard ways of doing this?
>
> Yes.
>
> You grant read access with GRANT SELECT ON table/view TO user.
>
> It's no less secure than accessing a database as that user via dblink.

thanks again for your email.

The problem is that the user account already has SELECT/UPDATE/INSERT/
DELETE access on the views, as they need it when accessing the
database through the web interface. What i wanted to do is provide a
read only access to only some views (this is for a programmatic
querying API). By using the second database i could restrict access to
this side of it using IP address filtering in the pg_hba.conf file.

thanks again

adam