diff options
| -rw-r--r-- | doc/src/sgml/ref/drop_owned.sgml | 9 | ||||
| -rw-r--r-- | doc/src/sgml/ref/drop_role.sgml | 8 | ||||
| -rw-r--r-- | doc/src/sgml/ref/drop_user.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/ref/reassign_owned.sgml | 23 | ||||
| -rw-r--r-- | doc/src/sgml/user-manag.sgml | 70 |
5 files changed, 89 insertions, 23 deletions
diff --git a/doc/src/sgml/ref/drop_owned.sgml b/doc/src/sgml/ref/drop_owned.sgml index 1cd8e60e400..a3f223bfc0b 100644 --- a/doc/src/sgml/ref/drop_owned.sgml +++ b/doc/src/sgml/ref/drop_owned.sgml @@ -90,19 +90,24 @@ DROP OWNED BY <replaceable class="PARAMETER">name</replaceable> [, ...] [ CASCAD <para> The <xref linkend="sql-reassign-owned"> command is an alternative that reassigns the ownership of all the database objects owned by one or - more roles. + more roles. However, <command>REASSIGN OWNED</> does not deal with + privileges for other objects. </para> <para> Databases and tablespaces owned by the role(s) will not be removed. </para> + + <para> + See <xref linkend="role-removal"> for more discussion. + </para> </refsect1> <refsect1> <title>Compatibility</title> <para> - The <command>DROP OWNED</command> statement is a + The <command>DROP OWNED</command> command is a <productname>PostgreSQL</productname> extension. </para> </refsect1> diff --git a/doc/src/sgml/ref/drop_role.sgml b/doc/src/sgml/ref/drop_role.sgml index 9a93da624a5..75b48f94f9a 100644 --- a/doc/src/sgml/ref/drop_role.sgml +++ b/doc/src/sgml/ref/drop_role.sgml @@ -39,10 +39,10 @@ DROP ROLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ... A role cannot be removed if it is still referenced in any database of the cluster; an error will be raised if so. Before dropping the role, you must drop all the objects it owns (or reassign their ownership) - and revoke any privileges the role has been granted. The <xref - linkend="sql-reassign-owned"> - and <xref linkend="sql-drop-owned"> - commands can be useful for this purpose. + and revoke any privileges the role has been granted on other objects. + The <xref linkend="sql-reassign-owned"> and <xref linkend="sql-drop-owned"> + commands can be useful for this purpose; see <xref linkend="role-removal"> + for more discussion. </para> <para> diff --git a/doc/src/sgml/ref/drop_user.sgml b/doc/src/sgml/ref/drop_user.sgml index 24e2a87ff2d..38e5418d078 100644 --- a/doc/src/sgml/ref/drop_user.sgml +++ b/doc/src/sgml/ref/drop_user.sgml @@ -29,7 +29,7 @@ DROP USER [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ... <title>Description</title> <para> - <command>DROP USER</command> is now an alias for + <command>DROP USER</command> is simply an alternate spelling of <xref linkend="sql-droprole">. </para> </refsect1> diff --git a/doc/src/sgml/ref/reassign_owned.sgml b/doc/src/sgml/ref/reassign_owned.sgml index d7d6abb9d8c..876ab5167d0 100644 --- a/doc/src/sgml/ref/reassign_owned.sgml +++ b/doc/src/sgml/ref/reassign_owned.sgml @@ -30,8 +30,9 @@ REASSIGN OWNED BY <replaceable class="PARAMETER">old_role</replaceable> [, ...] <para> <command>REASSIGN OWNED</command> instructs the system to change - the ownership of database objects owned by one of the - old_roles, to new_role. + the ownership of database objects owned by any of the + <replaceable class="PARAMETER">old_roles</replaceable> to + <replaceable class="PARAMETER">new_role</replaceable>. </para> </refsect1> @@ -81,16 +82,18 @@ REASSIGN OWNED BY <replaceable class="PARAMETER">old_role</replaceable> [, ...] <para> The <xref linkend="sql-drop-owned"> command is an alternative that - drops all the database objects owned by one or more roles. Note - also that <command>DROP OWNED</command> requires privileges only - on the source role(s). + simply drops all the database objects owned by one or more roles. </para> <para> - The <command>REASSIGN OWNED</command> command does not affect the - privileges granted to the old_roles in objects that are not owned - by them. Use <command>DROP OWNED</command> to revoke those - privileges. + The <command>REASSIGN OWNED</command> command does not affect any + privileges granted to the <replaceable class="PARAMETER">old_roles</> for + objects that are not owned by them. Use <command>DROP OWNED</command> to + revoke such privileges. + </para> + + <para> + See <xref linkend="role-removal"> for more discussion. </para> </refsect1> @@ -99,7 +102,7 @@ REASSIGN OWNED BY <replaceable class="PARAMETER">old_role</replaceable> [, ...] <title>Compatibility</title> <para> - The <command>REASSIGN OWNED</command> statement is a + The <command>REASSIGN OWNED</command> command is a <productname>PostgreSQL</productname> extension. </para> </refsect1> diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 177ac7aa8c5..4fce70060bc 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -7,8 +7,8 @@ <productname>PostgreSQL</productname> manages database access permissions using the concept of <firstterm>roles</>. A role can be thought of as either a database user, or a group of database users, depending on how - the role is set up. Roles can own database objects (for example, - tables) and can assign privileges on those objects to other roles to + the role is set up. Roles can own database objects (for example, tables + and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant <firstterm>membership</> in a role to another role, thus allowing the member role to use privileges assigned to another role. @@ -213,7 +213,7 @@ CREATE USER <replaceable>name</replaceable>; <para> A role must explicitly be given permission to initiate streaming replication (except for superusers, since those bypass all permission - checks). A role used for streaming replication must always + checks). A role used for streaming replication must have <literal>LOGIN</> permission as well. To create such a role, use <literal>CREATE ROLE <replaceable>name</replaceable> REPLICATION LOGIN</literal>. @@ -408,9 +408,67 @@ RESET ROLE; DROP ROLE <replaceable>name</replaceable>; </synopsis> Any memberships in the group role are automatically revoked (but the - member roles are not otherwise affected). Note however that any objects - owned by the group role must first be dropped or reassigned to other - owners; and any permissions granted to the group role must be revoked. + member roles are not otherwise affected). + </para> + </sect1> + + <sect1 id="role-removal"> + <title>Dropping Roles</title> + + <para> + Because roles can own database objects and can hold privileges + to access other objects, dropping a role is often not just a matter of a + quick <xref linkend="sql-droprole">. Any objects owned by the role must + first be dropped or reassigned to other owners; and any permissions + granted to the role must be revoked. + </para> + + <para> + Ownership of objects can be transferred one at a time + using <command>ALTER</> commands, for example: +<programlisting> +ALTER TABLE bobs_table OWNER TO alice; +</programlisting> + Alternatively, the <xref linkend="sql-reassign-owned"> command can be + used to reassign ownership of all objects owned by the role-to-be-dropped + to a single other role. Because <command>REASSIGN OWNED</> can only + access objects in the current database, it is necessary to run it in each + database that contains objects owned by the role. + </para> + + <para> + Once any valuable objects have been transferred to new owners, any + remaining objects owned by the role-to-be-dropped can be dropped with + the <xref linkend="sql-drop-owned"> command. Again, this command can + only access objects in the current database, so it is necessary to run it + in each database that contains objects owned by the role. + </para> + + <para> + <command>DROP OWNED</> also takes care of removing any privileges granted + to the target role for objects that do not belong to it. + Because <command>REASSIGN OWNED</> does not touch such objects, it's + typically necessary to run both <command>REASSIGN OWNED</> + and <command>DROP OWNED</> (in that order!) to fully remove the + dependencies of a role to be dropped. + </para> + + <para> + In short then, the most general recipe for removing a role that has been + used to own objects is: + + </para> +<programlisting> +REASSIGN OWNED BY doomed_role TO successor_role; +DROP OWNED BY doomed_role; +-- repeat the above commands in each database of the cluster +DROP ROLE doomed_role; +</programlisting> + + <para> + If <command>DROP ROLE</> is attempted while dependent objects still + remain, it will issue messages identifying which objects need to be + reassigned or dropped. </para> </sect1> |