Track dependencies on shared objects (which is to say, roles; we already

have adequate mechanisms for tracking the contents of databases and
tablespaces).  This solves the longstanding problem that you can drop a
user who still owns objects and/or has access permissions.
Alvaro Herrera, with some kibitzing from Tom Lane.

9 files changed, 89 insertions, 3 deletions

diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index 7fa83a5723d..aa8e967269d 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -385,5 +385,6 @@ SELECT * FROM clstr_1;
 -- clean up
 \c -
 DROP TABLE clstr_1;
+DROP TABLE clstr_2;
 DROP TABLE clstr_3;
 DROP USER clstr_user;
diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out
new file mode 100644
index 00000000000..4ee3e8b6a8f
--- /dev/null
+++ b/src/test/regress/expected/dependency.out
@@ -0,0 +1,39 @@
+--
+-- DEPENDENCIES
+--
+CREATE USER regression_user;
+CREATE USER regression_user2;
+CREATE USER regression_user3;
+CREATE GROUP regression_group;
+CREATE TABLE deptest ();
+GRANT SELECT ON TABLE deptest TO GROUP regression_group;
+GRANT ALL ON TABLE deptest TO regression_user, regression_user2;
+-- can't drop neither because they have privileges somewhere
+DROP USER regression_user;
+ERROR:  role "regression_user" cannot be dropped because some objects depend on it
+DETAIL:  access to table deptest
+DROP GROUP regression_group;
+ERROR:  role "regression_group" cannot be dropped because some objects depend on it
+DETAIL:  access to table deptest
+-- if we revoke the privileges we can drop the group
+REVOKE SELECT ON deptest FROM GROUP regression_group;
+DROP GROUP regression_group;
+-- can't drop the user if we revoke the privileges partially
+REVOKE SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES ON deptest FROM regression_user;
+DROP USER regression_user;
+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;
+DROP USER regression_user2;
+-- can't drop the owner of an object
+ALTER TABLE deptest OWNER TO regression_user3;
+DROP USER regression_user3;
+ERROR:  role "regression_user3" cannot be dropped because some objects depend on it
+DETAIL:  owner of table deptest
+-- if we drop the object, we can drop the user too
+DROP TABLE deptest;
+DROP USER regression_user3;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 8fa8bb18ce3..293d84e8f4d 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -601,6 +601,7 @@ DROP TABLE atest3;
 DROP TABLE atest4;
 DROP GROUP regressgroup1;
 DROP GROUP regressgroup2;
+REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
 DROP USER regressuser1;
 DROP USER regressuser2;
 DROP USER regressuser3;
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index 3593bc356bd..5c0e5ca8951 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -56,6 +56,7 @@ SELECT relname, relhasindex
  pg_operator         | t
  pg_proc             | t
  pg_rewrite          | t
+ pg_shdepend         | t
  pg_statistic        | t
  pg_tablespace       | t
  pg_trigger          | t
@@ -65,7 +66,7 @@ SELECT relname, relhasindex
  shighway            | t
  tenk1               | t
  tenk2               | t
-(55 rows)
+(56 rows)
 
 --
 -- another sanity check: every system catalog that has OIDs should have
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 9a3f7927328..3e52f6f558a 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -68,7 +68,7 @@ test: misc
 # ----------
 # The fifth group of parallel test
 # ----------
-test: select_views portals_p2 rules foreign_key cluster
+test: select_views portals_p2 rules foreign_key cluster dependency
 
 # ----------
 # The sixth group of parallel test
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index bb60dc0a105..bf9517fee20 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -1,4 +1,4 @@
-# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.27 2005/06/17 22:32:50 tgl Exp $
+# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.28 2005/07/07 20:40:01 tgl Exp $
 # This should probably be in an order similar to parallel_schedule.
 test: boolean
 test: char
@@ -98,3 +98,4 @@ test: polymorphism
 test: rowtypes
 test: stats
 test: tablespace
+test: dependency
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index f669922b031..db300b19981 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -156,5 +156,6 @@ SELECT * FROM clstr_1;
 -- clean up
 \c -
 DROP TABLE clstr_1;
+DROP TABLE clstr_2;
 DROP TABLE clstr_3;
 DROP USER clstr_user;
diff --git a/src/test/regress/sql/dependency.sql b/src/test/regress/sql/dependency.sql
new file mode 100644
index 00000000000..6d52b62dee1
--- /dev/null
+++ b/src/test/regress/sql/dependency.sql
@@ -0,0 +1,41 @@
+--
+-- DEPENDENCIES
+--
+
+CREATE USER regression_user;
+CREATE USER regression_user2;
+CREATE USER regression_user3;
+CREATE GROUP regression_group;
+
+CREATE TABLE deptest ();
+
+GRANT SELECT ON TABLE deptest TO GROUP regression_group;
+GRANT ALL ON TABLE deptest TO regression_user, regression_user2;
+
+-- can't drop neither because they have privileges somewhere
+DROP USER regression_user;
+DROP GROUP regression_group;
+
+-- if we revoke the privileges we can drop the group
+REVOKE SELECT ON deptest FROM GROUP regression_group;
+DROP GROUP regression_group;
+
+-- can't drop the user if we revoke the privileges partially
+REVOKE SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES ON deptest FROM regression_user;
+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
+REVOKE ALL ON deptest FROM regression_user2;
+DROP USER regression_user2;
+
+-- can't drop the owner of an object
+ALTER TABLE deptest OWNER TO regression_user3;
+DROP USER regression_user3;
+
+-- if we drop the object, we can drop the user too
+DROP TABLE deptest;
+DROP USER regression_user3;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index aa65bf599d2..ce65fefe61b 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -339,6 +339,7 @@ DROP TABLE atest4;
 DROP GROUP regressgroup1;
 DROP GROUP regressgroup2;
 
+REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
 DROP USER regressuser1;
 DROP USER regressuser2;
 DROP USER regressuser3;