Doc: clarify behavior of ALTER DEFAULT PRIVILEGES ... IN SCHEMA.

tglsfdc · tglsfdc · commit d30e0ed55c6b · 2019-11-19T14:21:41.000-05:00
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
diff --git 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>
