Greetings, The following patch implements individual privileges for TRUNCATE, VACUUM and ANALYZE. Includes documentation and regression test updates. Resolves TODO item 'Add a separate TRUNCATE permission'. Created off of current (2005/01/03) CVS TIP.
At least the 'no one interested has written a patch' argument is gone now, fire away with other comments/concerns. :) Thanks, Stephen
Index: doc/src/sgml/func.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.301 diff -c -r1.301 func.sgml *** doc/src/sgml/func.sgml 28 Dec 2005 01:29:58 -0000 1.301 --- doc/src/sgml/func.sgml 4 Jan 2006 03:38:01 -0000 *************** *** 8961,8968 **** The desired access privilege type is specified by a text string, which must evaluate to one of the values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>, ! <literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>, or ! <literal>TRIGGER</literal>. (Case of the string is not significant, however.) An example is: <programlisting> SELECT has_table_privilege('myschema.mytable', 'select'); --- 8961,8969 ---- The desired access privilege type is specified by a text string, which must evaluate to one of the values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>, ! <literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>, ! <literal>TRIGGER</literal>, <literal>TRUNCATE</literal>, <literal>VACUUM</literal>, or ! <literal>ANALYZE</literal>. (Case of the string is not significant, however.) An example is: <programlisting> SELECT has_table_privilege('myschema.mytable', 'select'); Index: doc/src/sgml/information_schema.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/information_schema.sgml,v retrieving revision 1.23 diff -c -r1.23 information_schema.sgml *** doc/src/sgml/information_schema.sgml 8 Dec 2005 20:48:10 -0000 1.23 --- doc/src/sgml/information_schema.sgml 4 Jan 2006 03:38:01 -0000 *************** *** 2395,2401 **** Type of the privilege: <literal>SELECT</literal>, <literal>DELETE</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>REFERENCES</literal>, ! <literal>RULE</literal>, or <literal>TRIGGER</literal> </entry> </row> --- 2395,2403 ---- Type of the privilege: <literal>SELECT</literal>, <literal>DELETE</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>REFERENCES</literal>, ! <literal>RULE</literal>, <literal>TRIGGER</literal>, ! <literal>TRUNCATE</literal>, <literal>VACUUM</literal>, or ! <literal>ANALYZE</literal>. </entry> </row> *************** *** 3643,3649 **** Type of the privilege: <literal>SELECT</literal>, <literal>DELETE</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>REFERENCES</literal>, ! <literal>RULE</literal>, or <literal>TRIGGER</literal> </entry> </row> --- 3645,3653 ---- Type of the privilege: <literal>SELECT</literal>, <literal>DELETE</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>REFERENCES</literal>, ! <literal>RULE</literal>, <literal>TRIGGER</literal>, ! <literal>TRUNCATE</literal>, <literal>VACUUM</literal>, or ! <literal>ANALYZE</literal>. </entry> </row> Index: doc/src/sgml/user-manag.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/user-manag.sgml,v retrieving revision 1.33 diff -c -r1.33 user-manag.sgml *** doc/src/sgml/user-manag.sgml 20 Oct 2005 19:18:00 -0000 1.33 --- doc/src/sgml/user-manag.sgml 4 Jan 2006 03:38:01 -0000 *************** *** 296,301 **** --- 296,302 ---- There are several different kinds of privilege: <literal>SELECT</>, <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>, + <literal>TRUNCATE</>, <literal>VACUUM</>, <literal>ANALYZE</>, <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>, and <literal>USAGE</>. For more information on the different types of privileges supported by Index: doc/src/sgml/ref/grant.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v retrieving revision 1.50 diff -c -r1.50 grant.sgml *** doc/src/sgml/ref/grant.sgml 20 Oct 2005 19:18:01 -0000 1.50 --- doc/src/sgml/ref/grant.sgml 4 Jan 2006 03:38:02 -0000 *************** *** 20,26 **** <refsynopsisdiv> <synopsis> ! GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] --- 20,26 ---- <refsynopsisdiv> <synopsis> ! GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER | TRUNCATE | VACUUM | ANALYZE } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] *************** *** 205,210 **** --- 205,243 ---- </varlistentry> <varlistentry> + <term>TRUNCATE</term> + <listitem> + <para> + Allows the truncation of the specified table. NOTE: This will NOT execute + triggers defined on the table. Additionally, this requires locking the table + exclusivly because it does not follow normal MVCC rules. See the <xref + linkend="sql-truncate" endterm="sql-truncate-title"> statement. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>VACUUM</term> + <listitem> + <para> + Allows the vacuuming of the specified table. Note: This implies ANALYZE + rights. See the <xref linkend="sql-vacuum" endterm="sql-vacuum-title"> + statement. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>ANALYZE</term> + <listitem> + <para> + Allows the analyzing of the specified table. See the <xref + linkend="sql-analyze" endterm="sql-analyze-title"> statement. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term>CREATE</term> <listitem> <para> *************** *** 402,408 **** U -- USAGE C -- CREATE T -- TEMPORARY ! arwdRxt -- ALL PRIVILEGES (for tables) * -- grant option for preceding privilege /yyyy -- user who granted this privilege --- 435,444 ---- U -- USAGE C -- CREATE T -- TEMPORARY ! e -- TRUNCATE ! V -- VACUUM ! A -- ANALYZE ! arwdRxteVA -- ALL PRIVILEGES (for tables) * -- grant option for preceding privilege /yyyy -- user who granted this privilege Index: doc/src/sgml/ref/revoke.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v retrieving revision 1.35 diff -c -r1.35 revoke.sgml *** doc/src/sgml/ref/revoke.sgml 20 Oct 2005 19:18:01 -0000 1.35 --- doc/src/sgml/ref/revoke.sgml 4 Jan 2006 03:38:02 -0000 *************** *** 21,27 **** <refsynopsisdiv> <synopsis> REVOKE [ GRANT OPTION FOR ] ! { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] --- 21,27 ---- <refsynopsisdiv> <synopsis> REVOKE [ GRANT OPTION FOR ] ! { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER | TRUNCATE | VACUUM | ANALYZE } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] Index: src/backend/catalog/aclchk.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/catalog/aclchk.c,v retrieving revision 1.123 diff -c -r1.123 aclchk.c *** src/backend/catalog/aclchk.c 1 Dec 2005 02:03:00 -0000 1.123 --- src/backend/catalog/aclchk.c 4 Jan 2006 03:38:02 -0000 *************** *** 1286,1291 **** --- 1286,1297 ---- return ACL_CREATE_TEMP; if (strcmp(privname, "temp") == 0) return ACL_CREATE_TEMP; + if (strcmp(privname, "truncate") == 0) + return ACL_TRUNCATE; + if (strcmp(privname, "vacuum") == 0) + return ACL_VACUUM; + if (strcmp(privname, "analyze") == 0) + return ACL_ANALYZE; ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("unrecognized privilege type \"%s\"", privname))); *************** *** 1319,1324 **** --- 1325,1336 ---- return "CREATE"; case ACL_CREATE_TEMP: return "TEMP"; + case ACL_TRUNCATE: + return "TRUNCATE"; + case ACL_VACUUM: + return "VACUUM"; + case ACL_ANALYZE: + return "ANALYZE"; default: elog(ERROR, "unrecognized privilege: %d", (int) privilege); } *************** *** 1502,1508 **** * protected in this way. Assume the view rules can take care of * themselves. */ ! if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE)) && IsSystemClass(classForm) && classForm->relkind != RELKIND_VIEW && !has_rolcatupdate(roleid) && --- 1514,1520 ---- * protected in this way. Assume the view rules can take care of * themselves. */ ! if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE)) && IsSystemClass(classForm) && classForm->relkind != RELKIND_VIEW && !has_rolcatupdate(roleid) && *************** *** 1511,1517 **** #ifdef ACLDEBUG elog(DEBUG2, "permission denied for system catalog update"); #endif ! mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_DELETE); } /* --- 1523,1529 ---- #ifdef ACLDEBUG elog(DEBUG2, "permission denied for system catalog update"); #endif ! mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE); } /* Index: src/backend/commands/analyze.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/commands/analyze.c,v retrieving revision 1.90 diff -c -r1.90 analyze.c *** src/backend/commands/analyze.c 22 Nov 2005 18:17:08 -0000 1.90 --- src/backend/commands/analyze.c 4 Jan 2006 03:38:02 -0000 *************** *** 112,117 **** --- 112,118 ---- double totalrows, totaldeadrows; HeapTuple *rows; + AclResult aclresult; if (vacstmt->verbose) elevel = INFO; *************** *** 146,158 **** */ onerel = relation_open(relid, AccessShareLock); ! if (!(pg_class_ownercheck(RelationGetRelid(onerel), GetUserId()) || (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared))) { /* No need for a WARNING if we already complained during VACUUM */ if (!vacstmt->vacuum) ereport(WARNING, ! (errmsg("skipping \"%s\" --- only table or database owner can analyze it", RelationGetRelationName(onerel)))); relation_close(onerel, AccessShareLock); return; --- 147,160 ---- */ onerel = relation_open(relid, AccessShareLock); ! aclresult = pg_class_aclcheck(RelationGetRelid(onerel), GetUserId(), ACL_ANALYZE); ! if (!((aclresult == ACLCHECK_OK) || (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared))) { /* No need for a WARNING if we already complained during VACUUM */ if (!vacstmt->vacuum) ereport(WARNING, ! (errmsg("skipping \"%s\" --- only user with ANALYZE privilege or database owner can analyze it", RelationGetRelationName(onerel)))); relation_close(onerel, AccessShareLock); return; Index: src/backend/commands/tablecmds.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.176 diff -c -r1.176 tablecmds.c *** src/backend/commands/tablecmds.c 22 Nov 2005 18:17:09 -0000 1.176 --- src/backend/commands/tablecmds.c 4 Jan 2006 03:38:02 -0000 *************** *** 544,549 **** --- 544,550 ---- { RangeVar *rv = lfirst(cell); Relation rel; + AclResult aclresult; /* Grab exclusive lock in preparation for truncate */ rel = heap_openrv(rv, AccessExclusiveLock); *************** *** 556,564 **** RelationGetRelationName(rel)))); /* Permissions checks */ ! if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId())) ! aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, ! RelationGetRelationName(rel)); if (!allowSystemTableMods && IsSystemRelation(rel)) ereport(ERROR, --- 557,565 ---- RelationGetRelationName(rel)))); /* Permissions checks */ ! aclresult = pg_class_aclcheck(RelationGetRelid(rel), GetUserId(), ACL_TRUNCATE); ! if (aclresult != ACLCHECK_OK) ! aclcheck_error(aclresult, ACL_KIND_CLASS, RelationGetRelationName(rel)); if (!allowSystemTableMods && IsSystemRelation(rel)) ereport(ERROR, Index: src/backend/commands/vacuum.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuum.c,v retrieving revision 1.319 diff -c -r1.319 vacuum.c *** src/backend/commands/vacuum.c 22 Nov 2005 18:17:09 -0000 1.319 --- src/backend/commands/vacuum.c 4 Jan 2006 03:38:02 -0000 *************** *** 948,953 **** --- 948,954 ---- LockRelId onerelid; Oid toast_relid; bool result; + AclResult aclresult; /* Begin a transaction for vacuuming this relation */ StartTransactionCommand(); *************** *** 989,1008 **** /* * Open the class, get an appropriate lock on it, and check permissions. * ! * We allow the user to vacuum a table if he is superuser, the table ! * owner, or the database owner (but in the latter case, only if it's not ! * a shared relation). pg_class_ownercheck includes the superuser case. * * Note we choose to treat permissions failure as a WARNING and keep * trying to vacuum the rest of the DB --- is this appropriate? */ onerel = relation_open(relid, lmode); ! if (!(pg_class_ownercheck(RelationGetRelid(onerel), GetUserId()) || (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared))) { ereport(WARNING, ! (errmsg("skipping \"%s\" --- only table or database owner can vacuum it", RelationGetRelationName(onerel)))); relation_close(onerel, lmode); StrategyHintVacuum(false); --- 990,1011 ---- /* * Open the class, get an appropriate lock on it, and check permissions. * ! * We allow the user to vacuum a table if he is superuser, has the ! * VACUUM permission on the table, or the database owner (but in the ! * latter case, only if it's not a shared relation). ! * pg_class_aclcheck includes the superuser case. * * Note we choose to treat permissions failure as a WARNING and keep * trying to vacuum the rest of the DB --- is this appropriate? */ onerel = relation_open(relid, lmode); ! aclresult = pg_class_aclcheck(RelationGetRelid(onerel), GetUserId(), ACL_VACUUM); ! if (!((aclresult == ACLCHECK_OK) || (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared))) { ereport(WARNING, ! (errmsg("skipping \"%s\" --- only user with VACUUM privilege or database owner can vacuum it", RelationGetRelationName(onerel)))); relation_close(onerel, lmode); StrategyHintVacuum(false); Index: src/backend/parser/gram.y =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v retrieving revision 2.521 diff -c -r2.521 gram.y *** src/backend/parser/gram.y 29 Dec 2005 04:53:18 -0000 2.521 --- src/backend/parser/gram.y 4 Jan 2006 03:38:02 -0000 *************** *** 3300,3305 **** --- 3300,3306 ---- privilege: SELECT { $$ = pstrdup($1); } | REFERENCES { $$ = pstrdup($1); } | CREATE { $$ = pstrdup($1); } + | ANALYZE { $$ = pstrdup($1); } | ColId { $$ = $1; } ; Index: src/backend/utils/adt/acl.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/acl.c,v retrieving revision 1.129 diff -c -r1.129 acl.c *** src/backend/utils/adt/acl.c 18 Nov 2005 02:38:23 -0000 1.129 --- src/backend/utils/adt/acl.c 4 Jan 2006 03:38:02 -0000 *************** *** 287,292 **** --- 287,301 ---- case ACL_CREATE_TEMP_CHR: read = ACL_CREATE_TEMP; break; + case ACL_TRUNCATE_CHR: + read = ACL_TRUNCATE; + break; + case ACL_VACUUM_CHR: + read = ACL_VACUUM; + break; + case ACL_ANALYZE_CHR: + read = ACL_ANALYZE; + break; default: ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), *************** *** 1334,1339 **** --- 1343,1354 ---- return ACL_CREATE_TEMP; if (pg_strcasecmp(priv_type, "TEMPORARY") == 0) return ACL_CREATE_TEMP; + if (pg_strcasecmp(priv_type, "TRUNCATE") == 0) + return ACL_TRUNCATE; + if (pg_strcasecmp(priv_type, "VACUUM") == 0) + return ACL_VACUUM; + if (pg_strcasecmp(priv_type, "ANALYZE") == 0) + return ACL_ANALYZE; ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), *************** *** 1559,1564 **** --- 1574,1594 ---- if (pg_strcasecmp(priv_type, "TRIGGER WITH GRANT OPTION") == 0) return ACL_GRANT_OPTION_FOR(ACL_TRIGGER); + if (pg_strcasecmp(priv_type, "TRUNCATE") == 0) + return ACL_TRUNCATE; + if (pg_strcasecmp(priv_type, "TRUNCATE WITH GRANT OPTION") == 0) + return ACL_GRANT_OPTION_FOR(ACL_TRUNCATE); + + if (pg_strcasecmp(priv_type, "VACUUM") == 0) + return ACL_VACUUM; + if (pg_strcasecmp(priv_type, "VACUUM WITH GRANT OPTION") == 0) + return ACL_GRANT_OPTION_FOR(ACL_VACUUM); + + if (pg_strcasecmp(priv_type, "ANALYZE") == 0) + return ACL_ANALYZE; + if (pg_strcasecmp(priv_type, "ANALYZE WITH GRANT OPTION") == 0) + return ACL_GRANT_OPTION_FOR(ACL_ANALYZE); + ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("unrecognized privilege type: \"%s\"", priv_type))); Index: src/bin/pg_dump/dumputils.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/dumputils.c,v retrieving revision 1.23 diff -c -r1.23 dumputils.c *** src/bin/pg_dump/dumputils.c 3 Dec 2005 21:06:18 -0000 1.23 --- src/bin/pg_dump/dumputils.c 4 Jan 2006 03:38:02 -0000 *************** *** 580,585 **** --- 580,588 ---- CONVERT_PRIV('d', "DELETE"); CONVERT_PRIV('x', "REFERENCES"); CONVERT_PRIV('t', "TRIGGER"); + CONVERT_PRIV('e', "TRUNCATE"); + CONVERT_PRIV('V', "VACUUM"); + CONVERT_PRIV('A', "ANALYZE"); } else { Index: src/bin/psql/tab-complete.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/psql/tab-complete.c,v retrieving revision 1.143 diff -c -r1.143 tab-complete.c *** src/bin/psql/tab-complete.c 18 Dec 2005 02:17:16 -0000 1.143 --- src/bin/psql/tab-complete.c 4 Jan 2006 03:38:02 -0000 *************** *** 1343,1349 **** { static const char *const list_privileg[] = {"SELECT", "INSERT", "UPDATE", "DELETE", "RULE", "REFERENCES", ! "TRIGGER", "CREATE", "TEMPORARY", "EXECUTE", "USAGE", "ALL", NULL}; COMPLETE_WITH_LIST(list_privileg); } --- 1343,1350 ---- { static const char *const list_privileg[] = {"SELECT", "INSERT", "UPDATE", "DELETE", "RULE", "REFERENCES", ! "TRIGGER", "TRUNCATE", "VACUUM", "ANALYZE", "CREATE", ! "TEMPORARY", "EXECUTE", "USAGE", "ALL", NULL}; COMPLETE_WITH_LIST(list_privileg); } Index: src/include/nodes/parsenodes.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v retrieving revision 1.298 diff -c -r1.298 parsenodes.h *** src/include/nodes/parsenodes.h 7 Dec 2005 15:20:55 -0000 1.298 --- src/include/nodes/parsenodes.h 4 Jan 2006 03:38:02 -0000 *************** *** 47,53 **** #define ACL_USAGE (1<<8) /* for languages and namespaces */ #define ACL_CREATE (1<<9) /* for namespaces and databases */ #define ACL_CREATE_TEMP (1<<10) /* for databases */ ! #define N_ACL_RIGHTS 11 /* 1 plus the last 1<<x */ #define ACL_NO_RIGHTS 0 /* Currently, SELECT ... FOR UPDATE/FOR SHARE requires UPDATE privileges */ #define ACL_SELECT_FOR_UPDATE ACL_UPDATE --- 47,56 ---- #define ACL_USAGE (1<<8) /* for languages and namespaces */ #define ACL_CREATE (1<<9) /* for namespaces and databases */ #define ACL_CREATE_TEMP (1<<10) /* for databases */ ! #define ACL_TRUNCATE (1<<11) /* for relations */ ! #define ACL_VACUUM (1<<12) /* for relations */ ! #define ACL_ANALYZE (1<<13) /* for relations */ ! #define N_ACL_RIGHTS 14 /* 1 plus the last 1<<x */ #define ACL_NO_RIGHTS 0 /* Currently, SELECT ... FOR UPDATE/FOR SHARE requires UPDATE privileges */ #define ACL_SELECT_FOR_UPDATE ACL_UPDATE Index: src/include/utils/acl.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/utils/acl.h,v retrieving revision 1.91 diff -c -r1.91 acl.h *** src/include/utils/acl.h 1 Dec 2005 02:03:01 -0000 1.91 --- src/include/utils/acl.h 4 Jan 2006 03:38:02 -0000 *************** *** 135,148 **** #define ACL_USAGE_CHR 'U' #define ACL_CREATE_CHR 'C' #define ACL_CREATE_TEMP_CHR 'T' /* string holding all privilege code chars, in order by bitmask position */ ! #define ACL_ALL_RIGHTS_STR "arwdRxtXUCT" /* * Bitmasks defining "all rights" for each supported object type */ ! #define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_RULE|ACL_REFERENCES|ACL_TRIGGER) #define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP) #define ACL_ALL_RIGHTS_FUNCTION (ACL_EXECUTE) #define ACL_ALL_RIGHTS_LANGUAGE (ACL_USAGE) --- 135,151 ---- #define ACL_USAGE_CHR 'U' #define ACL_CREATE_CHR 'C' #define ACL_CREATE_TEMP_CHR 'T' + #define ACL_TRUNCATE_CHR 'e' + #define ACL_VACUUM_CHR 'V' + #define ACL_ANALYZE_CHR 'A' /* string holding all privilege code chars, in order by bitmask position */ ! #define ACL_ALL_RIGHTS_STR "arwdRxtXUCTeVA" /* * Bitmasks defining "all rights" for each supported object type */ ! #define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_RULE|ACL_REFERENCES|ACL_TRIGGER|ACL_TRUNCATE|ACL_VACUUM|ACL_ANALYZE) #define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP) #define ACL_ALL_RIGHTS_FUNCTION (ACL_EXECUTE) #define ACL_ALL_RIGHTS_LANGUAGE (ACL_USAGE) Index: src/test/regress/expected/dependency.out =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/dependency.out,v retrieving revision 1.3 diff -c -r1.3 dependency.out *** src/test/regress/expected/dependency.out 21 Nov 2005 12:49:33 -0000 1.3 --- src/test/regress/expected/dependency.out 4 Jan 2006 03:38:02 -0000 *************** *** 26,32 **** ERROR: role "regression_user" cannot be dropped because some objects depend on it DETAIL: access to table deptest -- now we are OK to drop him ! REVOKE TRIGGER ON deptest FROM regression_user; DROP USER regression_user; -- we are OK too if we drop the privileges all at once REVOKE ALL ON deptest FROM regression_user2; --- 26,32 ---- ERROR: role "regression_user" cannot be dropped because some objects depend on it DETAIL: access to table deptest -- now we are OK to drop him ! REVOKE TRIGGER, TRUNCATE, VACUUM, ANALYZE ON deptest FROM regression_user; DROP USER regression_user; -- we are OK too if we drop the privileges all at once REVOKE ALL ON deptest FROM regression_user2; *************** *** 69,76 **** \z deptest1 Access privileges for database "regression" Schema | Name | Type | Access privileges ! --------+----------+-------+---------------------------------------------------------------------------------------------------------------------------------------- ! public | deptest1 | table | {regression_user0=arwdRxt/regression_user0,regression_user1=a*r*w*d*R*x*t*/regression_user0,regression_user2=arwdRxt/regression_user1} (1 row) DROP OWNED BY regression_user1; --- 69,76 ---- \z deptest1 Access privileges for database "regression" Schema | Name | Type | Access privileges ! --------+----------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------- ! public | deptest1 | table | {regression_user0=arwdRxteVA/regression_user0,regression_user1=a*r*w*d*R*x*t*e*V*A*/regression_user0,regression_user2=arwdRxteVA/regression_user1} (1 row) DROP OWNED BY regression_user1; *************** *** 78,85 **** \z deptest1 Access privileges for database "regression" Schema | Name | Type | Access privileges ! --------+----------+-------+--------------------------------------------- ! public | deptest1 | table | {regression_user0=arwdRxt/regression_user0} (1 row) -- table was dropped --- 78,85 ---- \z deptest1 Access privileges for database "regression" Schema | Name | Type | Access privileges ! --------+----------+-------+------------------------------------------------ ! public | deptest1 | table | {regression_user0=arwdRxteVA/regression_user0} (1 row) -- table was dropped Index: src/test/regress/expected/privileges.out =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/privileges.out,v retrieving revision 1.32 diff -c -r1.32 privileges.out *** src/test/regress/expected/privileges.out 15 Aug 2005 02:40:30 -0000 1.32 --- src/test/regress/expected/privileges.out 4 Jan 2006 03:38:02 -0000 *************** *** 67,72 **** --- 67,73 ---- ------+------ (0 rows) + TRUNCATE atest1; -- ok INSERT INTO atest1 VALUES (2, 'two'); -- ok INSERT INTO atest2 VALUES ('foo', true); -- fail ERROR: permission denied for relation atest2 *************** *** 85,90 **** --- 86,98 ---- ERROR: permission denied for relation atest2 DELETE FROM atest2; -- fail ERROR: permission denied for relation atest2 + TRUNCATE atest2; -- fail + ERROR: permission denied for relation atest2 + VACUUM atest2; -- fail + WARNING: skipping "atest2" --- only user with VACUUM privilege or database owner can vacuum it + ANALYZE atest1; -- ok + ANALYZE atest2; -- fail + WARNING: skipping "atest2" --- only user with ANALYZE privilege or database owner can analyze it LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail ERROR: permission denied for relation atest2 COPY atest2 FROM stdin; -- fail *************** *** 542,547 **** --- 550,576 ---- f (1 row) + select has_table_privilege(t1.oid,'truncate') + from (select oid from pg_class where relname = 'atest1') as t1; + has_table_privilege + --------------------- + f + (1 row) + + select has_table_privilege(t1.oid,'vacuum') + from (select oid from pg_class where relname = 'atest1') as t1; + has_table_privilege + --------------------- + f + (1 row) + + select has_table_privilege(t1.oid,'analyze') + from (select oid from pg_class where relname = 'atest1') as t1; + has_table_privilege + --------------------- + f + (1 row) + -- Grant options SET SESSION AUTHORIZATION regressuser1; CREATE TABLE atest4 (a int); Index: src/test/regress/sql/dependency.sql =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/dependency.sql,v retrieving revision 1.3 diff -c -r1.3 dependency.sql *** src/test/regress/sql/dependency.sql 21 Nov 2005 12:49:33 -0000 1.3 --- src/test/regress/sql/dependency.sql 4 Jan 2006 03:38:02 -0000 *************** *** 25,31 **** DROP USER regression_user; -- now we are OK to drop him ! REVOKE TRIGGER ON deptest FROM regression_user; DROP USER regression_user; -- we are OK too if we drop the privileges all at once --- 25,31 ---- DROP USER regression_user; -- now we are OK to drop him ! REVOKE TRIGGER, TRUNCATE, VACUUM, ANALYZE ON deptest FROM regression_user; DROP USER regression_user; -- we are OK too if we drop the privileges all at once Index: src/test/regress/sql/privileges.sql =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/privileges.sql,v retrieving revision 1.17 diff -c -r1.17 privileges.sql *** src/test/regress/sql/privileges.sql 15 Aug 2005 02:40:32 -0000 1.17 --- src/test/regress/sql/privileges.sql 4 Jan 2006 03:38:02 -0000 *************** *** 50,55 **** --- 50,56 ---- SELECT * FROM atest1; -- ok SELECT * FROM atest2; -- ok + TRUNCATE atest1; -- ok INSERT INTO atest1 VALUES (2, 'two'); -- ok INSERT INTO atest2 VALUES ('foo', true); -- fail INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok *************** *** 58,63 **** --- 59,68 ---- SELECT * FROM atest1 FOR UPDATE; -- ok SELECT * FROM atest2 FOR UPDATE; -- fail DELETE FROM atest2; -- fail + TRUNCATE atest2; -- fail + VACUUM atest2; -- fail + ANALYZE atest1; -- ok + ANALYZE atest2; -- fail LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail COPY atest2 FROM stdin; -- fail GRANT ALL ON atest1 TO PUBLIC; -- fail *************** *** 288,293 **** --- 293,304 ---- from (select oid from pg_class where relname = 'atest1') as t1; select has_table_privilege(t1.oid,'trigger') from (select oid from pg_class where relname = 'atest1') as t1; + select has_table_privilege(t1.oid,'truncate') + from (select oid from pg_class where relname = 'atest1') as t1; + select has_table_privilege(t1.oid,'vacuum') + from (select oid from pg_class where relname = 'atest1') as t1; + select has_table_privilege(t1.oid,'analyze') + from (select oid from pg_class where relname = 'atest1') as t1; -- Grant options
signature.asc
Description: Digital signature