## 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
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