postgres.git / summary / log / commit / refs
commit d30e0ed55c6bba632f491fa8db2c2bbde33330c3
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue Nov 19 19:21:41 2019 +0000
First released in: 13.0, 9.4.26, 9.5.21, 9.6.17, 10.12, 11.7, 12.2
Doc: clarify behavior of ALTER DEFAULT PRIVILEGES ... IN SCHEMA.
The existing text stated that "Default privileges that are specified
per-schema are added to whatever the global default privileges are for
the particular object type". However, that bare-bones observation is
not quite clear enough, as demonstrated by the complaint in bug #16124.
Flesh it out by stating explicitly that you can't revoke built-in
default privileges this way, and by providing an example to drive
the point home.
Back-patch to all supported branches, since it's been like this
from the beginning.
Discussion: https://postgr.es/m/16124-423d8ee4358421bc@postgresql.org
doc/src/sgml/ref/alter_default_privileges.sgml | 34 +++++++++++++++++++-------
1 file changed, 25 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index e3363f868a4..207fdcc0da8 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -100,9 +100,7 @@ REVOKE [ GRANT OPTION FOR ]
You can change default privileges only for objects that will be created by
yourself or by roles that you are a member of. The privileges can be set
globally (i.e., for all objects created in the current database),
- or just for objects created in specified schemas. Default privileges
- that are specified per-schema are added to whatever the global default
- privileges are for the particular object type.
+ or just for objects created in specified schemas.
</para>
<para>
@@ -114,6 +112,16 @@ REVOKE [ GRANT OPTION FOR ]
<command>ALTER DEFAULT PRIVILEGES</>.
</para>
+ <para>
+ Default privileges that are specified per-schema are added to whatever
+ the global default privileges are for the particular object type.
+ This means you cannot revoke privileges per-schema if they are granted
+ globally (either by default, or according to a previous <command>ALTER
+ DEFAULT PRIVILEGES</command> command that did not specify a schema).
+ Per-schema <literal>REVOKE</literal> is only useful to reverse the
+ effects of a previous per-schema <literal>GRANT</literal>.
+ </para>
+
<refsect2>
<title>Parameters</title>
@@ -134,10 +142,10 @@ REVOKE [ GRANT OPTION FOR ]
<para>
The name of an existing schema. If specified, the default privileges
are altered for objects later created in that schema.
- If <literal>IN SCHEMA</> is omitted, the global default privileges
+ If <literal>IN SCHEMA</literal> is omitted, the global default privileges
are altered.
- <literal>IN SCHEMA</> is not allowed when using <literal>ON SCHEMAS</>
- as schemas can't be nested.
+ <literal>IN SCHEMA</literal> is not allowed when setting privileges
+ for schemas, since schemas can't be nested.
</para>
</listitem>
</varlistentry>
@@ -206,11 +214,19 @@ ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser
<para>
Remove the public EXECUTE permission that is normally granted on functions,
- for all functions subsequently created by role <literal>admin</>:
-
+ for all functions subsequently created by role <literal>admin</literal>:
<programlisting>
ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
-</programlisting></para>
+</programlisting>
+ Note however that you <emphasis>cannot</emphasis> accomplish that effect
+ with a command limited to a single schema. This command has no effect,
+ unless it is undoing a matching <literal>GRANT</literal>:
+<programlisting>
+ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
+</programlisting>
+ That's because per-schema default privileges can only add privileges to
+ the global setting, not remove privileges granted by it.
+ </para>
</refsect1>
<refsect1>
[parent: a1b2cf0950ea]