Skip site navigation (1) Skip section navigation (2)

SECURITY DEFINER not being propagated...

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: SECURITY DEFINER not being propagated...
Date: 2004-04-27 23:18:42
Message-ID: 3947104A-98A1-11D8-A29C-000A95C705DC@chittenden.org (view raw or flat)
Thread:
Lists: pgsql-patches
This one's simple enough to reproduce (see SQL script below), but, 
there are some comments in src/backend/catalog/namespace.c that seem 
questionable and incorrect:


## BEGIN ##
         /*
          * First, do permission check to see if we are authorized to 
make temp
          * tables.      We use a nonstandard error message here since
          * "databasename: permission denied" might be a tad cryptic.
          *
          * Note we apply the check to the session user, not the 
currently active
          * userid, since we are not going to change our minds about 
temp table
          * availability during the session.
          */
## END ##


Suppose the following example:

*) PUBLIC has TEMP privs revoked from the test database.
*) DBAs have TEMP privs on the test database.
*) A script is written by the DBA with SECURITY DEFINER that CREATEs a 
TEMP TABLE, populates it, and REVOKEs all privs but SELECT from the 
session user.

With the current logic, it's impossible to achieve this without 
granting temp table privs to all users.  The attached patch changes 
things from GetSessionUserID() to GetUserId().  Comments?  I think the 
reasoning and rationale in the comments hinders a DBAs ability to 
secure a database.


-- BEGIN EXAMPLE SQL SCRIPT
REVOKE ALL PRIVILEGES ON DATABASE test FROM PUBLIC CASCADE;
GRANT CREATE,TEMPORARY ON DATABASE test TO dba;
GRANT USAGE ON SCHEMA public TO PUBLIC;

CREATE OR REPLACE FUNCTION create_tmptbl()
         RETURNS BOOL
         AS 'BEGIN
         PERFORM TRUE FROM pg_catalog.pg_class c WHERE c.relkind = 
''r''::CHAR(1) AND c.relname = ''tmptbl''::TEXT;
         IF NOT FOUND THEN
                 EXECUTE ''CREATE LOCAL TEMP TABLE tmptbl (
                         i INT
                 ) WITHOUT OIDS ON COMMIT DELETE ROWS;'';
         END IF;

         RETURN TRUE;
END;' LANGUAGE 'plpgsql';
REVOKE ALL PRIVILEGES ON FUNCTION create_tmptbl() FROM PUBLIC CASCADE;

CREATE OR REPLACE FUNCTION setuid_wrapper()
         RETURNS BOOL
         AS 'BEGIN
         RETURN create_tmptbl();
END;' LANGUAGE 'plpgsql'
         SECURITY DEFINER;
REVOKE ALL PRIVILEGES ON FUNCTION setuid_wrapper() FROM PUBLIC CASCADE;
GRANT EXECUTE ON FUNCTION setuid_wrapper() TO PUBLIC;
-- END SCRIPT

test=# \c test usr
You are now connected to database "test" as user "usr".
test=> SELECT setuid_wrapper();
ERROR:  permission denied to create temporary tables in database "test"
CONTEXT:  SQL query "CREATE LOCAL TEMP TABLE tmptbl (
                         i INT
                 ) WITHOUT OIDS ON COMMIT DELETE ROWS;"
PL/pgSQL function "create_tmptbl" line 4 at execute statement
PL/pgSQL function "setuid_wrapper" line 2 at return



Attachment: patch.txt
Description: text/plain (942 bytes)

Responses

pgsql-patches by date

Next:From: Bruce MomjianDate: 2004-04-28 03:44:20
Subject: Re: Basic subtransaction facility
Previous:From: David FetterDate: 2004-04-27 21:12:29
Subject: Dollar quoting docs, round 1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group