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.

4 files changed, 194 insertions, 2 deletions

diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index c08b09b67d4..07dd0b0ee74 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.66 2005/07/29 15:13:11 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.67 2005/11/21 12:49:30 alvherre Exp $
 PostgreSQL documentation
 Complete list of usable sgml source files in this directory.
 -->
@@ -70,6 +70,7 @@ Complete list of usable sgml source files in this directory.
 <!entity dropLanguage       system "drop_language.sgml">
 <!entity dropOperator       system "drop_operator.sgml">
 <!entity dropOperatorClass  system "drop_opclass.sgml">
+<!entity dropOwned          system "drop_owned.sgml">
 <!entity dropRole           system "drop_role.sgml">
 <!entity dropRule           system "drop_rule.sgml">
 <!entity dropSchema         system "drop_schema.sgml">
@@ -93,6 +94,7 @@ Complete list of usable sgml source files in this directory.
 <!entity notify             system "notify.sgml">
 <!entity prepare            system "prepare.sgml">
 <!entity prepareTransaction system "prepare_transaction.sgml">
+<!entity reassignOwned      system "reassign_owned.sgml">
 <!entity reindex            system "reindex.sgml">
 <!entity releaseSavepoint   system "release_savepoint.sgml">
 <!entity reset              system "reset.sgml">
diff --git a/doc/src/sgml/ref/drop_owned.sgml b/doc/src/sgml/ref/drop_owned.sgml
new file mode 100644
index 00000000000..dc8b01fe984
--- /dev/null
+++ b/doc/src/sgml/ref/drop_owned.sgml
@@ -0,0 +1,99 @@
+<!--
+$PostgreSQL: pgsql/doc/src/sgml/ref/drop_owned.sgml,v 1.1 2005/11/21 12:49:30 alvherre Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-DROP-OWNED">
+ <refmeta>
+  <refentrytitle id="SQL-DROP-OWNED-TITLE">DROP OWNED</refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>DROP OWNED</refname>
+  <refpurpose>remove database objects owned by a database role</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-drop-owned">
+  <primary>DROP OWNED</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+DROP OWNED <replaceable class="PARAMETER">name</replaceable> [, ...] [ RESTRICT | CASCADE ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   The <command>DROP OWNED</command> instructs the system to drop those
+   database objects owned by one of the given roles which reside on the
+   current database.  All privileges granted to the given roles will also be
+   revoked.
+  </para>
+
+  <para>
+   If <literal>CASCADE</> is specified, <command>DROP OWNED</command>
+   will behave like a <command>DROP ... CASCADE</command> was issued
+   for each object, that is, objects dependent on the ones owned by the
+   given users will be dropped as well.  
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+  <para>
+   The <command>DROP OWNED</command> command is mostly used in preparation to
+   drop the roles.  It may be necessary to issue the command in more than one
+   database.
+  </para>
+
+  <para>
+   Using the <literal>CASCADE</literal> option may make the command recurse to
+   objects owned by other users.
+  </para>
+
+  <para>
+   See the <command>REASSIGN OWNED</command> command for an alternative that
+   gives the objects away to another role.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Compatibility</title>
+
+  <para>
+   The <command>DROP OWNED</command> statement is a
+   <productname>PostgreSQL</productname> extension.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-reassign-owned" endterm="sql-reassign-owned-title"></member>
+   <member><xref linkend="sql-droprole" endterm="sql-droprole-title"></member>
+  </simplelist>
+ </refsect1>
+
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/ref/reassign_owned.sgml b/doc/src/sgml/ref/reassign_owned.sgml
new file mode 100644
index 00000000000..a54e4c82698
--- /dev/null
+++ b/doc/src/sgml/ref/reassign_owned.sgml
@@ -0,0 +1,89 @@
+<!--
+$PostgreSQL: pgsql/doc/src/sgml/ref/reassign_owned.sgml,v 1.1 2005/11/21 12:49:30 alvherre Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-REASSIGN-OWNED">
+ <refmeta>
+  <refentrytitle id="SQL-REASSIGN-OWNED-TITLE">REASSIGN OWNED</refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>REASSIGN OWNED</refname>
+  <refpurpose>change ownership of database objects owned by a database role</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-reassign-owned">
+  <primary>REASSIGN OWNED</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+REASSIGN OWNED <replaceable class="PARAMETER">old_role</replaceable> [, ...] TO <replaceable class="PARAMETER">new_role</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   The <command>REASSIGN OWNED</command> instructs the system to change
+   the ownership of the database objects owned by one of the old_roles,
+   to new_role.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+   The <command>REASSIGN OWNED</command> command is mostly used in preparation to
+   drop the roles.  See the <command>DROP OWNED</command> command for an
+   alternative that drops the objects.
+  </para>
+
+  <para>
+   The <command>REASSIGN OWNED</command> command does not affect the privileges
+   granted to the old_roles in objects not owned by them.  Use
+   <command>DROP OWNED</command> to remove them.
+  </para>
+
+ </refsect1>
+
+ <refsect1>
+  <title>Compatibility</title>
+
+  <para>
+   The <command>REASSIGN 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" endterm="sql-drop-owned-title"></member>
+   <member><xref linkend="sql-droprole" endterm="sql-droprole-title"></member>
+  </simplelist>
+ </refsect1>
+
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 0191ec8b3c3..f320a132d04 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -1,5 +1,5 @@
 <!-- reference.sgml
-$PostgreSQL: pgsql/doc/src/sgml/reference.sgml,v 1.56 2005/07/29 15:13:11 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/reference.sgml,v 1.57 2005/11/21 12:49:30 alvherre Exp $
 
 PostgreSQL Reference Manual
 -->
@@ -102,6 +102,7 @@ PostgreSQL Reference Manual
    &dropLanguage;
    &dropOperator;
    &dropOperatorClass;
+   &dropOwned;
    &dropRole;
    &dropRule;
    &dropSchema;
@@ -125,6 +126,7 @@ PostgreSQL Reference Manual
    &notify;
    &prepare;
    &prepareTransaction;
+   &reassignOwned;
    &reindex;
    &releaseSavepoint;
    &reset;