DROP PRIVILEGES OWNED BY

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