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

Commitc9de6b9

Browse files
committed
Document the behavior of GRANT/REVOKE in cases where the privilege is
held by means of role membership, rather than directly. Per discussionand bug fix of a couple weeks ago.
1 parent7218aab commitc9de6b9

File tree

3 files changed

+143
-79
lines changed

3 files changed

+143
-79
lines changed

‎doc/src/sgml/ref/grant.sgml

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.49 2005/10/13 23:26:00 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.50 2005/10/20 19:18:01 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -343,6 +343,29 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...]
343343
by the containing role itself.)
344344
</para>
345345

346+
<para>
347+
<command>GRANT</> and <command>REVOKE</> can also be done by a role
348+
that is not the owner of the affected object, but is a member of the role
349+
that owns the object, or is a member of a role that holds privileges
350+
<literal>WITH GRANT OPTION</literal> on the object. In this case the
351+
privileges will be recorded as having been granted by the role that
352+
actually owns the object or holds the privileges
353+
<literal>WITH GRANT OPTION</literal>. For example, if table
354+
<literal>t1</> is owned by role <literal>g1</>, of which role
355+
<literal>u1</> is a member, then <literal>u1</> can grant privileges
356+
on <literal>t1</> to <literal>u2</>, but those privileges will appear
357+
to have been granted directly by <literal>g1</>. Any other member
358+
of role <literal>g1</> could revoke them later.
359+
</para>
360+
361+
<para>
362+
If the role executing <command>GRANT</> holds the required privileges
363+
indirectly via more than one role membership path, it is unspecified
364+
which containing role will be recorded as having done the grant. In such
365+
cases it is best practice to use <command>SET ROLE</> to become the
366+
specific role you want to do the <command>GRANT</> as.
367+
</para>
368+
346369
<para>
347370
Currently, <productname>PostgreSQL</productname> does not support
348371
granting or revoking privileges for individual columns of a table.

‎doc/src/sgml/ref/revoke.sgml

Lines changed: 26 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.34 2005/07/26 23:24:02 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.35 2005/10/20 19:18:01 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -158,6 +158,31 @@ REVOKE [ ADMIN OPTION FOR ]
158158
it is possible for a superuser to revoke all privileges, but this may
159159
require use of <literal>CASCADE</literal> as stated above.
160160
</para>
161+
162+
<para>
163+
<command>REVOKE</> can also be done by a role
164+
that is not the owner of the affected object, but is a member of the role
165+
that owns the object, or is a member of a role that holds privileges
166+
<literal>WITH GRANT OPTION</literal> on the object. In this case the
167+
command is performed as though it were issued by the containing role that
168+
actually owns the object or holds the privileges
169+
<literal>WITH GRANT OPTION</literal>. For example, if table
170+
<literal>t1</> is owned by role <literal>g1</>, of which role
171+
<literal>u1</> is a member, then <literal>u1</> can revoke privileges
172+
on <literal>t1</> that are recorded as being granted by <literal>g1</>.
173+
This would include grants made by <literal>u1</> as well as by other
174+
members of role <literal>g1</>.
175+
</para>
176+
177+
<para>
178+
If the role executing <command>REVOKE</> holds privileges
179+
indirectly via more than one role membership path, it is unspecified
180+
which containing role will be used to perform the command. In such cases
181+
it is best practice to use <command>SET ROLE</> to become the specific
182+
role you want to do the <command>REVOKE</> as. Failure to do so may
183+
lead to revoking privileges other than the ones you intended, or not
184+
revoking anything at all.
185+
</para>
161186
</refsect1>
162187

163188
<refsect1 id="SQL-REVOKE-examples">

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

Lines changed: 93 additions & 77 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/user-manag.sgml,v 1.32 2005/10/15 20:12:33 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/user-manag.sgml,v 1.33 2005/10/20 19:18:00 tgl Exp $
33
-->
44

55
<chapter id="user-manag">
@@ -267,6 +267,81 @@ ALTER ROLE myname SET enable_indexscan TO off;
267267
</para>
268268
</sect1>
269269

270+
<sect1 id="privileges">
271+
<title>Privileges</title>
272+
273+
<indexterm zone="privileges">
274+
<primary>privilege</primary>
275+
</indexterm>
276+
277+
<indexterm zone="privileges">
278+
<primary>owner</primary>
279+
</indexterm>
280+
281+
<indexterm zone="privileges">
282+
<primary>GRANT</primary>
283+
</indexterm>
284+
285+
<indexterm zone="privileges">
286+
<primary>REVOKE</primary>
287+
</indexterm>
288+
289+
<para>
290+
When an object is created, it is assigned an owner. The
291+
owner is normally the role that executed the creation statement.
292+
For most kinds of objects, the initial state is that only the owner
293+
(or a superuser) can do anything with the object. To allow
294+
other roles to use it, <firstterm>privileges</firstterm> must be
295+
granted.
296+
There are several different kinds of privilege: <literal>SELECT</>,
297+
<literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
298+
<literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
299+
<literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
300+
and <literal>USAGE</>. For more
301+
information on the different types of privileges supported by
302+
<productname>PostgreSQL</productname>, see the
303+
<xref linkend="sql-grant" endterm="sql-grant-title"> reference page.
304+
</para>
305+
306+
<para>
307+
To assign privileges, the <command>GRANT</command> command is
308+
used. So, if <literal>joe</literal> is an existing role, and
309+
<literal>accounts</literal> is an existing table, the privilege to
310+
update the table can be granted with
311+
<programlisting>
312+
GRANT UPDATE ON accounts TO joe;
313+
</programlisting>
314+
The special name <literal>PUBLIC</literal> can
315+
be used to grant a privilege to every role on the system. Writing
316+
<literal>ALL</literal> in place of a specific privilege specifies that all
317+
privileges that apply to the object will be granted.
318+
</para>
319+
320+
<para>
321+
To revoke a privilege, use the fittingly named
322+
<xref linkend="sql-revoke" endterm="sql-revoke-title"> command:
323+
<programlisting>
324+
REVOKE ALL ON accounts FROM PUBLIC;
325+
</programlisting>
326+
</para>
327+
328+
<para>
329+
The special privileges of an object's owner (i.e., the right to modify
330+
or destroy the object) are always implicit in being the owner,
331+
and cannot be granted or revoked. But the owner can choose
332+
to revoke his own ordinary privileges, for example to make a
333+
table read-only for himself as well as others.
334+
</para>
335+
336+
<para>
337+
An object can be assigned to a new owner with an <command>ALTER</command>
338+
command of the appropriate kind for the object. Superusers can always do
339+
this; ordinary roles can only do it if they are both the current owner
340+
of the object (or a member of the owning role) and a member of the new
341+
owning role.
342+
</para>
343+
</sect1>
344+
270345
<sect1 id="role-membership">
271346
<title>Role Membership</title>
272347

@@ -373,6 +448,22 @@ RESET ROLE;
373448
</para>
374449
</note>
375450

451+
<para>
452+
The role attributes <literal>LOGIN</>, <literal>SUPERUSER</>,
453+
<literal>CREATEDB</>, and <literal>CREATEROLE</> can be thought of as
454+
special privileges, but they are never inherited as ordinary privileges
455+
on database objects are. You must actually <command>SET ROLE</> to a
456+
specific role having one of these attributes in order to make use of
457+
the attribute. Continuing the above example, we might well choose to
458+
grant <literal>CREATEDB</> and <literal>CREATEROLE</> to the
459+
<literal>admin</> role. Then a session connecting as role <literal>joe</>
460+
would not have these privileges immediately, only after doing
461+
<command>SET ROLE admin</>.
462+
</para>
463+
464+
<para>
465+
</para>
466+
376467
<para>
377468
To destroy a group role, use <xref
378469
linkend="sql-droprole" endterm="sql-droprole-title">:
@@ -386,87 +477,12 @@ DROP ROLE <replaceable>name</replaceable>;
386477
</para>
387478
</sect1>
388479

389-
<sect1 id="privileges">
390-
<title>Privileges</title>
391-
392-
<indexterm zone="privileges">
393-
<primary>privilege</primary>
394-
</indexterm>
395-
396-
<indexterm zone="privileges">
397-
<primary>owner</primary>
398-
</indexterm>
399-
400-
<indexterm zone="privileges">
401-
<primary>GRANT</primary>
402-
</indexterm>
403-
404-
<indexterm zone="privileges">
405-
<primary>REVOKE</primary>
406-
</indexterm>
407-
408-
<para>
409-
When an object is created, it is assigned an owner. The
410-
owner is normally the role that executed the creation statement.
411-
For most kinds of objects, the initial state is that only the owner
412-
(or a superuser) can do anything with the object. To allow
413-
other roles to use it, <firstterm>privileges</firstterm> must be
414-
granted.
415-
There are several different kinds of privilege: <literal>SELECT</>,
416-
<literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
417-
<literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
418-
<literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
419-
and <literal>USAGE</>. For more
420-
information on the different types of privileges supported by
421-
<productname>PostgreSQL</productname>, see the
422-
<xref linkend="sql-grant" endterm="sql-grant-title"> reference page.
423-
</para>
424-
425-
<para>
426-
To assign privileges, the <command>GRANT</command> command is
427-
used. So, if <literal>joe</literal> is an existing role, and
428-
<literal>accounts</literal> is an existing table, the privilege to
429-
update the table can be granted with
430-
<programlisting>
431-
GRANT UPDATE ON accounts TO joe;
432-
</programlisting>
433-
The special name <literal>PUBLIC</literal> can
434-
be used to grant a privilege to every role on the system. Writing
435-
<literal>ALL</literal> in place of a specific privilege specifies that all
436-
privileges that apply to the object will be granted.
437-
</para>
438-
439-
<para>
440-
To revoke a privilege, use the fittingly named
441-
<xref linkend="sql-revoke" endterm="sql-revoke-title"> command:
442-
<programlisting>
443-
REVOKE ALL ON accounts FROM PUBLIC;
444-
</programlisting>
445-
</para>
446-
447-
<para>
448-
The special privileges of an object's owner (i.e., the right to modify
449-
or destroy the object) are always implicit in being the owner,
450-
and cannot be granted or revoked. But the owner can choose
451-
to revoke his own ordinary privileges, for example to make a
452-
table read-only for himself as well as others.
453-
</para>
454-
455-
<para>
456-
An object can be assigned to a new owner with an <command>ALTER</command>
457-
command of the appropriate kind for the object. Superusers can always do
458-
this; ordinary roles can only do it if they are both the current owner
459-
of the object (or a member of the owning role) and a member of the new
460-
owning role.
461-
</para>
462-
</sect1>
463-
464480
<sect1 id="perm-functions">
465481
<title>Functions and Triggers</title>
466482

467483
<para>
468484
Functions and triggers allow users to insert code into the backend
469-
server that other users may executewithout knowing it. Hence, both
485+
server that other users may executeunintentionally. Hence, both
470486
mechanisms permit users to <quote>Trojan horse</quote>
471487
others with relative ease. The only real protection is tight
472488
control over who can define functions.

0 commit comments

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