Hi,
This week I had a problem where I wanted to drop only the privileges a
certain role had in the system, while keeping all the objects. I
couldn't figure out a reasonable way to do that, so I've attached a
patch for this to this email. Please consider it for inclusion into
9.5. The syntax is:
DROP PRIVILEGES OWNED BY role [, ...]
I at some point decided to implement it as a new command instead of
changing DropOwnedStmt, and I think that might have been a mistake. It
might have made more sense to instead teach DROP OWNED to accept a
specification of which things to drop. But the proposal is more
important than such details, I think.
.marko
*** a/doc/src/sgml/ref/drop_owned.sgml
--- b/doc/src/sgml/ref/drop_owned.sgml
***************
*** 111,116 **** DROP OWNED BY <replaceable
class="PARAMETER">name</replaceable> [, ...] [ CASCAD
--- 111,117 ----
<title>See Also</title>
<simplelist type="inline">
+ <member><xref linkend="sql-drop-privileges-owned"></member>
<member><xref linkend="sql-reassign-owned"></member>
<member><xref linkend="sql-droprole"></member>
</simplelist>
*** /dev/null
--- b/doc/src/sgml/ref/drop_privileges_owned.sgml
***************
*** 0 ****
--- 1,72 ----
+ <!--
+ doc/src/sgml/ref/drop_privileges_owned.sgml
+ PostgreSQL documentation
+ -->
+
+ <refentry id="SQL-DROP-OWNED">
+ <indexterm zone="sql-drop-privileges-owned">
+ <primary>DROP PRIVILEGES OWNED</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>DROP PRIVILEGES OWNED</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>DROP PRIVILEGES OWNED</refname>
+ <refpurpose>remove privileges granted to a database role</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+ <synopsis>
+ DROP PRIVILEGES OWNED BY <replaceable class="PARAMETER">name</replaceable> [,
...]
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>DROP PRIVILEGES OWNED</command> revokes all privileges granted to
+ the given roles on objects in the current database and on shared objects
+ (databases, tablespaces).
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="PARAMETER">name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a role whose privileges will be revoked.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>DROP PRIVILEGES OWNED</command> statement is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-drop-owned"></member>
+ <member><xref linkend="sql-reassign-owned"></member>
+ <member><xref linkend="sql-droprole"></member>
+ </simplelist>
+ </refsect1>
+
+ </refentry>
*** a/src/backend/catalog/pg_shdepend.c
--- b/src/backend/catalog/pg_shdepend.c
***************
*** 1162,1174 **** isSharedObjectPinned(Oid classId, Oid objectId, Relation
sdepRel)
* interdependent objects in the wrong order.
*/
void
! shdepDropOwned(List *roleids, DropBehavior behavior)
{
Relation sdepRel;
ListCell *cell;
! ObjectAddresses *deleteobjs;
! deleteobjs = new_object_addresses();
/*
* We don't need this strong a lock here, but we'll call routines that
--- 1162,1175 ----
* interdependent objects in the wrong order.
*/
void
! shdepDropOwned(List *roleids, DropBehavior behavior, bool privilegesOnly)
{
Relation sdepRel;
ListCell *cell;
! ObjectAddresses *deleteobjs = NULL;
! if (!privilegesOnly)
! deleteobjs = new_object_addresses();
/*
* We don't need this strong a lock here, but we'll call routines that
***************
*** 1243,1249 **** shdepDropOwned(List *roleids, DropBehavior behavior)
break;
case SHARED_DEPENDENCY_OWNER:
/* If a local object, save it for
deletion below */
! if (sdepForm->dbid == MyDatabaseId)
{
obj.classId = sdepForm->classid;
obj.objectId = sdepForm->objid;
--- 1244,1250 ----
break;
case SHARED_DEPENDENCY_OWNER:
/* If a local object, save it for
deletion below */
! if (!privilegesOnly && sdepForm->dbid
== MyDatabaseId)
{
obj.classId = sdepForm->classid;
obj.objectId = sdepForm->objid;
***************
*** 1257,1268 **** shdepDropOwned(List *roleids, DropBehavior behavior)
systable_endscan(scan);
}
! /* the dependency mechanism does the actual work */
! performMultipleDeletions(deleteobjs, behavior, 0);
heap_close(sdepRel, RowExclusiveLock);
! free_object_addresses(deleteobjs);
}
/*
--- 1258,1274 ----
systable_endscan(scan);
}
! /*
! * Unless we were asked not to drop objects, now is the time to let the
! * dependency mechanism do the actual work of dropping them.
! */
! if (!privilegesOnly)
! performMultipleDeletions(deleteobjs, behavior, 0);
heap_close(sdepRel, RowExclusiveLock);
! if (deleteobjs)
! free_object_addresses(deleteobjs);
}
/*
*** a/src/backend/commands/event_trigger.c
--- b/src/backend/commands/event_trigger.c
***************
*** 264,269 **** check_ddl_tag(const char *tag)
--- 264,270 ----
pg_strcasecmp(tag, "ALTER DEFAULT PRIVILEGES") == 0 ||
pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0 ||
pg_strcasecmp(tag, "DROP OWNED") == 0 ||
+ pg_strcasecmp(tag, "DROP PRIVILEGES OWNED") == 0 ||
pg_strcasecmp(tag, "IMPORT FOREIGN SCHEMA") == 0)
return EVENT_TRIGGER_COMMAND_TAG_OK;
*** a/src/backend/commands/user.c
--- b/src/backend/commands/user.c
***************
*** 1308,1314 **** DropOwnedObjects(DropOwnedStmt *stmt)
}
/* Ok, do it */
! shdepDropOwned(role_ids, stmt->behavior);
}
/*
--- 1308,1340 ----
}
/* Ok, do it */
! shdepDropOwned(role_ids, stmt->behavior, false);
! }
!
! /*
! * DropOwnedPrivileges
! *
! * Revoke privileges granted to a given list of roles.
! */
! void
! DropOwnedPrivileges(DropPrivilegesOwnedStmt *stmt)
! {
! List *role_ids = roleNamesToIds(stmt->roles);
! ListCell *cell;
!
! /* Check privileges */
! foreach(cell, role_ids)
! {
! Oid roleid = lfirst_oid(cell);
!
! if (!has_privs_of_role(GetUserId(), roleid))
! ereport(ERROR,
!
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("permission denied to drop
privileges")));
! }
!
! /* Ok, do it */
! shdepDropOwned(role_ids, DROP_RESTRICT, true);
}
/*
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 3844,3849 **** _copyDropOwnedStmt(const DropOwnedStmt *from)
--- 3844,3859 ----
return newnode;
}
+ static DropPrivilegesOwnedStmt *
+ _copyDropPrivilegesOwnedStmt(const DropPrivilegesOwnedStmt *from)
+ {
+ DropPrivilegesOwnedStmt *newnode = makeNode(DropPrivilegesOwnedStmt);
+
+ COPY_NODE_FIELD(roles);
+
+ return newnode;
+ }
+
static ReassignOwnedStmt *
_copyReassignOwnedStmt(const ReassignOwnedStmt *from)
{
***************
*** 4616,4621 **** copyObject(const void *from)
--- 4626,4634 ----
case T_DropOwnedStmt:
retval = _copyDropOwnedStmt(from);
break;
+ case T_DropPrivilegesOwnedStmt:
+ retval = _copyDropPrivilegesOwnedStmt(from);
+ break;
case T_ReassignOwnedStmt:
retval = _copyReassignOwnedStmt(from);
break;
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 1978,1983 **** _equalDropOwnedStmt(const DropOwnedStmt *a, const
DropOwnedStmt *b)
--- 1978,1991 ----
}
static bool
+ _equalDropPrivilegesOwnedStmt(const DropPrivilegesOwnedStmt *a, const
DropPrivilegesOwnedStmt *b)
+ {
+ COMPARE_NODE_FIELD(roles);
+
+ return true;
+ }
+
+ static bool
_equalReassignOwnedStmt(const ReassignOwnedStmt *a, const ReassignOwnedStmt
*b)
{
COMPARE_NODE_FIELD(roles);
***************
*** 3043,3048 **** equal(const void *a, const void *b)
--- 3051,3059 ----
case T_DropOwnedStmt:
retval = _equalDropOwnedStmt(a, b);
break;
+ case T_DropPrivilegesOwnedStmt:
+ retval = _equalDropPrivilegesOwnedStmt(a, b);
+ break;
case T_ReassignOwnedStmt:
retval = _equalReassignOwnedStmt(a, b);
break;
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 256,262 **** static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
VariableResetStmt VariableSetStmt VariableShowStmt
ViewStmt CheckPointStmt CreateConversionStmt
DeallocateStmt PrepareStmt ExecuteStmt
! DropOwnedStmt ReassignOwnedStmt
AlterTSConfigurationStmt AlterTSDictionaryStmt
CreateMatViewStmt RefreshMatViewStmt
--- 256,262 ----
VariableResetStmt VariableSetStmt VariableShowStmt
ViewStmt CheckPointStmt CreateConversionStmt
DeallocateStmt PrepareStmt ExecuteStmt
! DropOwnedStmt DropPrivilegesOwnedStmt ReassignOwnedStmt
AlterTSConfigurationStmt AlterTSDictionaryStmt
CreateMatViewStmt RefreshMatViewStmt
***************
*** 807,812 **** stmt :
--- 807,813 ----
| DropOpFamilyStmt
| DropOwnedStmt
| DropPolicyStmt
+ | DropPrivilegesOwnedStmt
| DropPLangStmt
| DropRuleStmt
| DropStmt
***************
*** 5389,5394 **** DropOpFamilyStmt:
--- 5390,5396 ----
* QUERY:
*
* DROP OWNED BY username [, username ...] [ RESTRICT | CASCADE ]
+ * DROP PRIVILEGES OWNED BY username [, username ...]
* REASSIGN OWNED BY username [, username ...] TO username
*
*****************************************************************************/
***************
*** 5400,5405 **** DropOwnedStmt:
--- 5402,5416 ----
n->behavior = $5;
$$ = (Node *)n;
}
+ ;
+
+ DropPrivilegesOwnedStmt:
+ DROP PRIVILEGES OWNED BY role_list
+ {
+ DropPrivilegesOwnedStmt *n =
makeNode(DropPrivilegesOwnedStmt);
+ n->roles = $5;
+ $$ = (Node *)n;
+ }
;
ReassignOwnedStmt:
*** a/src/backend/tcop/utility.c
--- b/src/backend/tcop/utility.c
***************
*** 189,194 **** check_xact_readonly(Node *parsetree)
--- 189,195 ----
case T_AlterDefaultPrivilegesStmt:
case T_TruncateStmt:
case T_DropOwnedStmt:
+ case T_DropPrivilegesOwnedStmt:
case T_ReassignOwnedStmt:
case T_AlterTSDictionaryStmt:
case T_AlterTSConfigurationStmt:
***************
*** 1319,1324 **** ProcessUtilitySlow(Node *parsetree,
--- 1320,1329 ----
DropOwnedObjects((DropOwnedStmt *) parsetree);
break;
+ case T_DropPrivilegesOwnedStmt:
+ DropOwnedPrivileges((DropPrivilegesOwnedStmt *)
parsetree);
+ break;
+
case T_AlterDefaultPrivilegesStmt:
ExecAlterDefaultPrivilegesStmt((AlterDefaultPrivilegesStmt *) parsetree);
break;
***************
*** 2256,2261 **** CreateCommandTag(Node *parsetree)
--- 2261,2270 ----
tag = "DROP OWNED";
break;
+ case T_DropPrivilegesOwnedStmt:
+ tag = "DROP PRIVILEGES OWNED";
+ break;
+
case T_ReassignOwnedStmt:
tag = "REASSIGN OWNED";
break;
***************
*** 2813,2818 **** GetCommandLogLevel(Node *parsetree)
--- 2822,2831 ----
lev = LOGSTMT_DDL;
break;
+ case T_DropPrivilegesOwnedStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
case T_ReassignOwnedStmt:
lev = LOGSTMT_DDL;
break;
*** a/src/include/catalog/dependency.h
--- b/src/include/catalog/dependency.h
***************
*** 255,261 **** extern void copyTemplateDependencies(Oid templateDbId, Oid
newDbId);
extern void dropDatabaseDependencies(Oid databaseId);
! extern void shdepDropOwned(List *relids, DropBehavior behavior);
extern void shdepReassignOwned(List *relids, Oid newrole);
--- 255,261 ----
extern void dropDatabaseDependencies(Oid databaseId);
! extern void shdepDropOwned(List *relids, DropBehavior behavior, bool
privilegesOnly);
extern void shdepReassignOwned(List *relids, Oid newrole);
*** a/src/include/commands/user.h
--- b/src/include/commands/user.h
***************
*** 29,34 **** extern void DropRole(DropRoleStmt *stmt);
--- 29,35 ----
extern void GrantRole(GrantRoleStmt *stmt);
extern Oid RenameRole(const char *oldname, const char *newname);
extern void DropOwnedObjects(DropOwnedStmt *stmt);
+ extern void DropOwnedPrivileges(DropPrivilegesOwnedStmt *stmt);
extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt);
extern List *roleNamesToIds(List *memberNames);
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
***************
*** 342,347 **** typedef enum NodeTag
--- 342,348 ----
T_AlterObjectSchemaStmt,
T_AlterOwnerStmt,
T_DropOwnedStmt,
+ T_DropPrivilegesOwnedStmt,
T_ReassignOwnedStmt,
T_CompositeTypeStmt,
T_CreateEnumStmt,
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 2816,2821 **** typedef struct DropOwnedStmt
--- 2816,2830 ----
} DropOwnedStmt;
/*
+ * DROP PRIVILEGES OWNED statement
+ */
+ typedef struct DropPrivilegesOwnedStmt
+ {
+ NodeTag type;
+ List *roles;
+ } DropPrivilegesOwnedStmt;
+
+ /*
* REASSIGN OWNED statement
*/
typedef struct ReassignOwnedStmt
*** a/src/test/regress/expected/dependency.out
--- b/src/test/regress/expected/dependency.out
***************
*** 61,67 **** GRANT ALL ON deptest1 TO regression_user1 WITH GRANT OPTION;
--- 61,84 ----
SET SESSION AUTHORIZATION regression_user1;
CREATE TABLE deptest (a serial primary key, b text);
GRANT ALL ON deptest1 TO regression_user2;
+ GRANT ALL ON deptest TO regression_user2;
RESET SESSION AUTHORIZATION;
+ \z deptest
+ Access privileges
+ Schema | Name | Type | Access privileges |
Column privileges | Policies
+
--------+---------+-------+-------------------------------------------+-------------------+----------
+ public | deptest | table | regression_user1=arwdDxt/regression_user1+|
|
+ | | | regression_user2=arwdDxt/regression_user1 |
|
+ (1 row)
+
+ DROP PRIVILEGES OWNED BY regression_user2;
+ \z deptest
+ Access privileges
+ Schema | Name | Type | Access privileges |
Column privileges | Policies
+
--------+---------+-------+-------------------------------------------+-------------------+----------
+ public | deptest | table | regression_user1=arwdDxt/regression_user1 |
|
+ (1 row)
+
\z deptest1
Access privileges
Schema | Name | Type | Access privileges
| Column privileges | Policies
*** a/src/test/regress/sql/dependency.sql
--- b/src/test/regress/sql/dependency.sql
***************
*** 63,71 **** GRANT ALL ON deptest1 TO regression_user1 WITH GRANT OPTION;
SET SESSION AUTHORIZATION regression_user1;
CREATE TABLE deptest (a serial primary key, b text);
GRANT ALL ON deptest1 TO regression_user2;
RESET SESSION AUTHORIZATION;
- \z deptest1
DROP OWNED BY regression_user1;
-- all grants revoked
\z deptest1
--- 63,76 ----
SET SESSION AUTHORIZATION regression_user1;
CREATE TABLE deptest (a serial primary key, b text);
GRANT ALL ON deptest1 TO regression_user2;
+ GRANT ALL ON deptest TO regression_user2;
RESET SESSION AUTHORIZATION;
+ \z deptest
+ DROP PRIVILEGES OWNED BY regression_user2;
+ \z deptest
+
+ \z deptest1
DROP OWNED BY regression_user1;
-- all grants revoked
\z deptest1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
