Is this a security risk?

[Adam Witney <awitney@xxxxxxxxxx>]

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




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general