Implement DROP OWNED and REASSIGN OWNED. These new commands facilitate the

process of dropping roles by dropping objects owned by them and privileges
granted to them, or giving the owned objects to someone else, through the
use of the data stored in the new pg_shdepend catalog.

Some refactoring of the GRANT/REVOKE code was needed, as well as ALTER OWNER
code.  Further cleanup of code duplication in the GRANT code seems necessary.

Implemented by me after an idea from Tom Lane, who also provided various kind
of implementation advice.

Regression tests pass.  Some tests for the new functionality are also added,
as well as rudimentary documentation.

2 files changed, 119 insertions, 0 deletions

diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out
index 2c31e581bfe..4781b5f9f29 100644
--- a/src/test/regress/expected/dependency.out
+++ b/src/test/regress/expected/dependency.out
@@ -38,6 +38,76 @@ DROP USER regression_user2;
 ALTER TABLE deptest OWNER TO regression_user3;
 DROP USER regression_user3;
 ERROR:  role "regression_user3" cannot be dropped because some objects depend on it
+\set VERBOSITY default
 -- if we drop the object, we can drop the user too
 DROP TABLE deptest;
 DROP USER regression_user3;
+-- Test DROP OWNED
+CREATE USER regression_user0;
+CREATE USER regression_user1;
+CREATE USER regression_user2;
+SET SESSION AUTHORIZATION regression_user0;
+-- permission denied
+DROP OWNED BY regression_user1;
+ERROR:  permission denied to drop objects
+DROP OWNED BY regression_user0, regression_user2;
+ERROR:  permission denied to drop objects
+REASSIGN OWNED BY regression_user0 TO regression_user1;
+ERROR:  permission denied to reassign objects
+REASSIGN OWNED BY regression_user1 TO regression_user0;
+ERROR:  permission denied to reassign objects
+-- this one is allowed
+DROP OWNED BY regression_user0;
+CREATE TABLE deptest1 ();
+GRANT ALL ON deptest1 TO regression_user1 WITH GRANT OPTION;
+SET SESSION AUTHORIZATION regression_user1;
+CREATE TABLE deptest (a serial primary key, b text);
+NOTICE:  CREATE TABLE will create implicit sequence "deptest_a_seq" for serial column "deptest.a"
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "deptest_pkey" for table "deptest"
+GRANT ALL ON deptest1 TO regression_user2;
+RESET SESSION AUTHORIZATION;
+\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;
+-- all grants revoked
+\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
+\d deptest
+-- Test REASSIGN OWNED
+GRANT ALL ON deptest1 TO regression_user1;
+SET SESSION AUTHORIZATION regression_user1;
+CREATE TABLE deptest (a serial primary key, b text);
+NOTICE:  CREATE TABLE will create implicit sequence "deptest_a_seq" for serial column "deptest.a"
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "deptest_pkey" for table "deptest"
+RESET SESSION AUTHORIZATION;
+REASSIGN OWNED BY regression_user1 TO regression_user2;
+\dt deptest
+              List of relations
+ Schema |  Name   | Type  |      Owner       
+--------+---------+-------+------------------
+ public | deptest | table | regression_user2
+(1 row)
+
+-- doesn't work: grant still exists
+DROP USER regression_user1;
+ERROR:  role "regression_user1" cannot be dropped because some objects depend on it
+DETAIL:  access to table deptest1
+DROP OWNED BY regression_user1;
+DROP USER regression_user1;
+\set VERBOSITY terse
+DROP USER regression_user2;
+ERROR:  role "regression_user2" cannot be dropped because some objects depend on it
+DROP OWNED BY regression_user2, regression_user0;
+DROP USER regression_user2;
+DROP USER regression_user0;
diff --git a/src/test/regress/sql/dependency.sql b/src/test/regress/sql/dependency.sql
index 3e4a232ea71..c1b189f5278 100644
--- a/src/test/regress/sql/dependency.sql
+++ b/src/test/regress/sql/dependency.sql
@@ -39,6 +39,55 @@ DROP USER regression_user2;
 ALTER TABLE deptest OWNER TO regression_user3;
 DROP USER regression_user3;
 
+\set VERBOSITY default
 -- if we drop the object, we can drop the user too
 DROP TABLE deptest;
 DROP USER regression_user3;
+
+-- Test DROP OWNED
+CREATE USER regression_user0;
+CREATE USER regression_user1;
+CREATE USER regression_user2;
+SET SESSION AUTHORIZATION regression_user0;
+-- permission denied
+DROP OWNED BY regression_user1;
+DROP OWNED BY regression_user0, regression_user2;
+REASSIGN OWNED BY regression_user0 TO regression_user1;
+REASSIGN OWNED BY regression_user1 TO regression_user0;
+-- this one is allowed
+DROP OWNED BY regression_user0;
+
+CREATE TABLE deptest1 ();
+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
+-- table was dropped
+\d deptest
+
+-- Test REASSIGN OWNED
+GRANT ALL ON deptest1 TO regression_user1;
+
+SET SESSION AUTHORIZATION regression_user1;
+CREATE TABLE deptest (a serial primary key, b text);
+RESET SESSION AUTHORIZATION;
+
+REASSIGN OWNED BY regression_user1 TO regression_user2;
+\dt deptest
+-- doesn't work: grant still exists
+DROP USER regression_user1;
+DROP OWNED BY regression_user1;
+DROP USER regression_user1;
+
+\set VERBOSITY terse
+DROP USER regression_user2;
+DROP OWNED BY regression_user2, regression_user0;
+DROP USER regression_user2;
+DROP USER regression_user0;