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

1 files changed, 23 insertions, 7 deletions

diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index 583f65fad6c..f1d54f5aa35 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -106,9 +106,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>
@@ -120,6 +118,16 @@ REVOKE [ GRANT OPTION FOR ]
    <command>ALTER DEFAULT PRIVILEGES</command>.
   </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>
 
@@ -142,8 +150,8 @@ REVOKE [ GRANT OPTION FOR ]
       are altered for objects later created in that schema.
       If <literal>IN SCHEMA</literal> is omitted, the global default privileges
       are altered.
-      <literal>IN SCHEMA</literal> is not allowed when using <literal>ON SCHEMAS</literal>
-      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>
@@ -212,10 +220,18 @@ 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</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>