[PATCHES] SECURITY DEFINER not being propagated...

[Sean Chittenden]

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:

Advertising







*) 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

Index: namespace.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/catalog/namespace.c,v
retrieving revision 1.63
diff -u -r1.63 namespace.c
--- namespace.c 13 Feb 2004 01:08:20 -0000      1.63
+++ namespace.c 27 Apr 2004 23:14:36 -0000
@@ -1639,12 +1639,8 @@
         * 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.
         */
-       if (pg_database_aclcheck(MyDatabaseId, GetSessionUserId(),
+       if (pg_database_aclcheck(MyDatabaseId, GetUserId(),
                                                         ACL_CREATE_TEMP) != 
ACLCHECK_OK)
                ereport(ERROR,
                                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),

--
Sean Chittenden---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly