SECURITY DEFINER not being propagated... - Mailing list 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




--
Sean Chittenden