Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1ea0c73

Browse files
committed
Improve documentation of the role-dropping process.
In general one may have to run both REASSIGN OWNED and DROP OWNED to getrid of all the dependencies of a role to be dropped. This was alluded toin the REASSIGN OWNED man page, but not really spelled out in full; and inany case the procedure ought to be documented in a more prominent placethan that. Add a section to the "Database Roles" chapter explaining this,and do a bit of wordsmithing in the relevant commands' man pages.
1 parentb292ee7 commit1ea0c73

File tree

5 files changed

+89
-23
lines changed

5 files changed

+89
-23
lines changed

‎doc/src/sgml/ref/drop_owned.sgml‎

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -90,19 +90,24 @@ DROP OWNED BY { <replaceable class="PARAMETER">name</replaceable> | CURRENT_USER
9090
<para>
9191
The <xref linkend="sql-reassign-owned"> command is an alternative that
9292
reassigns the ownership of all the database objects owned by one or
93-
more roles.
93+
more roles. However, <command>REASSIGN OWNED</> does not deal with
94+
privileges for other objects.
9495
</para>
9596

9697
<para>
9798
Databases and tablespaces owned by the role(s) will not be removed.
9899
</para>
100+
101+
<para>
102+
See <xref linkend="role-removal"> for more discussion.
103+
</para>
99104
</refsect1>
100105

101106
<refsect1>
102107
<title>Compatibility</title>
103108

104109
<para>
105-
The <command>DROP OWNED</command>statement is a
110+
The <command>DROP OWNED</command>command is a
106111
<productname>PostgreSQL</productname> extension.
107112
</para>
108113
</refsect1>

‎doc/src/sgml/ref/drop_role.sgml‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -39,10 +39,10 @@ DROP ROLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ...
3939
A role cannot be removed if it is still referenced in any database
4040
of the cluster; an error will be raised if so. Before dropping the role,
4141
you must drop all the objects it owns (or reassign their ownership)
42-
and revoke any privileges the role has been granted. The <xref
43-
linkend="sql-reassign-owned">
44-
and<xref linkend="sql-drop-owned">
45-
commands can be usefulforthis purpose.
42+
and revoke any privileges the role has been granted on other objects.
43+
The <xreflinkend="sql-reassign-owned"> and <xref linkend="sql-drop-owned">
44+
commands can be useful for this purpose; see<xref linkend="role-removal">
45+
formore discussion.
4646
</para>
4747

4848
<para>

‎doc/src/sgml/ref/drop_user.sgml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -29,7 +29,7 @@ DROP USER [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ...
2929
<title>Description</title>
3030

3131
<para>
32-
<command>DROP USER</command> isnow analias for
32+
<command>DROP USER</command> issimply analternate spelling of
3333
<xref linkend="sql-droprole">.
3434
</para>
3535
</refsect1>

‎doc/src/sgml/ref/reassign_owned.sgml‎

Lines changed: 13 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -31,8 +31,9 @@ REASSIGN OWNED BY { <replaceable class="PARAMETER">old_role</replaceable> | CURR
3131

3232
<para>
3333
<command>REASSIGN OWNED</command> instructs the system to change
34-
the ownership of database objects owned by one of the
35-
old_roles, to new_role.
34+
the ownership of database objects owned by any of the
35+
<replaceable class="PARAMETER">old_roles</replaceable> to
36+
<replaceable class="PARAMETER">new_role</replaceable>.
3637
</para>
3738
</refsect1>
3839

@@ -82,16 +83,18 @@ REASSIGN OWNED BY { <replaceable class="PARAMETER">old_role</replaceable> | CURR
8283

8384
<para>
8485
The <xref linkend="sql-drop-owned"> command is an alternative that
85-
drops all the database objects owned by one or more roles. Note
86-
also that <command>DROP OWNED</command> requires privileges only
87-
on the source role(s).
86+
simply drops all the database objects owned by one or more roles.
8887
</para>
8988

9089
<para>
91-
The <command>REASSIGN OWNED</command> command does not affect the
92-
privileges granted to the old_roles in objects that are not owned
93-
by them. Use <command>DROP OWNED</command> to revoke those
94-
privileges.
90+
The <command>REASSIGN OWNED</command> command does not affect any
91+
privileges granted to the <replaceable class="PARAMETER">old_roles</> for
92+
objects that are not owned by them. Use <command>DROP OWNED</command> to
93+
revoke such privileges.
94+
</para>
95+
96+
<para>
97+
See <xref linkend="role-removal"> for more discussion.
9598
</para>
9699

97100
</refsect1>
@@ -100,7 +103,7 @@ REASSIGN OWNED BY { <replaceable class="PARAMETER">old_role</replaceable> | CURR
100103
<title>Compatibility</title>
101104

102105
<para>
103-
The <command>REASSIGN OWNED</command>statement is a
106+
The <command>REASSIGN OWNED</command>command is a
104107
<productname>PostgreSQL</productname> extension.
105108
</para>
106109
</refsect1>

‎doc/src/sgml/user-manag.sgml‎

Lines changed: 64 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -7,8 +7,8 @@
77
<productname>PostgreSQL</productname> manages database access permissions
88
using the concept of <firstterm>roles</>. A role can be thought of as
99
either a database user, or a group of database users, depending on how
10-
the role is set up. Roles can own database objects (for example,
11-
tables) and can assign privileges on those objects to other roles to
10+
the role is set up. Roles can own database objects (for example, tables
11+
and functions) and can assign privileges on those objects to other roles to
1212
control who has access to which objects. Furthermore, it is possible
1313
to grant <firstterm>membership</> in a role to another role, thus
1414
allowing the member role to use privileges assigned to another role.
@@ -213,7 +213,7 @@ CREATE USER <replaceable>name</replaceable>;
213213
<para>
214214
A role must explicitly be given permission to initiate streaming
215215
replication (except for superusers, since those bypass all permission
216-
checks). A role used for streaming replication must always
216+
checks). A role used for streaming replication must
217217
have <literal>LOGIN</> permission as well. To create such a role, use
218218
<literal>CREATE ROLE <replaceable>name</replaceable> REPLICATION
219219
LOGIN</literal>.
@@ -408,9 +408,67 @@ RESET ROLE;
408408
DROP ROLE <replaceable>name</replaceable>;
409409
</synopsis>
410410
Any memberships in the group role are automatically revoked (but the
411-
member roles are not otherwise affected). Note however that any objects
412-
owned by the group role must first be dropped or reassigned to other
413-
owners; and any permissions granted to the group role must be revoked.
411+
member roles are not otherwise affected).
412+
</para>
413+
</sect1>
414+
415+
<sect1 id="role-removal">
416+
<title>Dropping Roles</title>
417+
418+
<para>
419+
Because roles can own database objects and can hold privileges
420+
to access other objects, dropping a role is often not just a matter of a
421+
quick <xref linkend="sql-droprole">. Any objects owned by the role must
422+
first be dropped or reassigned to other owners; and any permissions
423+
granted to the role must be revoked.
424+
</para>
425+
426+
<para>
427+
Ownership of objects can be transferred one at a time
428+
using <command>ALTER</> commands, for example:
429+
<programlisting>
430+
ALTER TABLE bobs_table OWNER TO alice;
431+
</programlisting>
432+
Alternatively, the <xref linkend="sql-reassign-owned"> command can be
433+
used to reassign ownership of all objects owned by the role-to-be-dropped
434+
to a single other role. Because <command>REASSIGN OWNED</> can only
435+
access objects in the current database, it is necessary to run it in each
436+
database that contains objects owned by the role.
437+
</para>
438+
439+
<para>
440+
Once any valuable objects have been transferred to new owners, any
441+
remaining objects owned by the role-to-be-dropped can be dropped with
442+
the <xref linkend="sql-drop-owned"> command. Again, this command can
443+
only access objects in the current database, so it is necessary to run it
444+
in each database that contains objects owned by the role.
445+
</para>
446+
447+
<para>
448+
<command>DROP OWNED</> also takes care of removing any privileges granted
449+
to the target role for objects that do not belong to it.
450+
Because <command>REASSIGN OWNED</> does not touch such objects, it's
451+
typically necessary to run both <command>REASSIGN OWNED</>
452+
and <command>DROP OWNED</> (in that order!) to fully remove the
453+
dependencies of a role to be dropped.
454+
</para>
455+
456+
<para>
457+
In short then, the most general recipe for removing a role that has been
458+
used to own objects is:
459+
460+
</para>
461+
<programlisting>
462+
REASSIGN OWNED BY doomed_role TO successor_role;
463+
DROP OWNED BY doomed_role;
464+
-- repeat the above commands in each database of the cluster
465+
DROP ROLE doomed_role;
466+
</programlisting>
467+
468+
<para>
469+
If <command>DROP ROLE</> is attempted while dependent objects still
470+
remain, it will issue messages identifying which objects need to be
471+
reassigned or dropped.
414472
</para>
415473
</sect1>
416474

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp