Add support for privileges on types

This adds support for the more or less SQL-conforming USAGE privilege
on types and domains.  The intent is to be able restrict which users
can create dependencies on types, which restricts the way in which
owners can alter types.

reviewed by Yeb Havinga

16 files changed, 116 insertions, 24 deletions

diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 92d2f647190..d8e42e4f630 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -284,6 +284,8 @@
    the attributes of composite data types defined in the database.
    (Note that the view does not give information about table columns,
    which are sometimes called attributes in PostgreSQL contexts.)
+   Only those attributes are shown that the current user has access to (by way
+   of being the owner of or having some privilege on the type).
   </para>
 
   <table>
@@ -1915,8 +1917,10 @@
   <title><literal>domain_constraints</literal></title>
 
   <para>
-   The view <literal>domain_constraints</literal> contains all
-   constraints belonging to domains defined in the current database.
+   The view <literal>domain_constraints</literal> contains all constraints
+   belonging to domains defined in the current database.  Only those domains
+   are shown that the current user has access to (by way of being the owner or
+   having some privilege).
   </para>
 
   <table>
@@ -2052,8 +2056,9 @@
   <title><literal>domains</literal></title>
 
   <para>
-   The view <literal>domains</literal> contains all domains defined in
-   the current database.
+   The view <literal>domains</literal> contains all domains defined in the
+   current database.  Only those domains are shown that the current user has
+   access to (by way of being the owner or having some privilege).
   </para>
 
   <table>
@@ -5778,15 +5783,13 @@ ORDER BY c.ordinal_position;
   <title><literal>udt_privileges</literal></title>
 
   <para>
-   The view <literal>udt_privileges</literal> is intended to identify
-   <literal>USAGE</literal> privileges granted on user-defined types
-   to a currently enabled role or by a currently enabled role.  Since
-   data types do not have real privileges
-   in <productname>PostgreSQL</productname>, this view shows implicit
-   non-grantable <literal>USAGE</literal> privileges granted by the
-   owner to <literal>PUBLIC</literal> for all types, including
-   built-in ones (except domains,
-   see <xref linkend="infoschema-usage-privileges"> for that).
+   The view <literal>udt_privileges</literal> identifies
+   <literal>USAGE</literal> privileges granted on user-defined types to a
+   currently enabled role or by a currently enabled role.  There is one row for
+   each combination of column, grantor, and grantee.  This view shows only
+   composite types (see under <xref linkend="infoschema-user-defined-types">
+   for why); see
+   <xref linkend="infoschema-usage-privileges"> for domain privileges.
   </para>
 
   <table>
@@ -5861,10 +5864,10 @@ ORDER BY c.ordinal_position;
   </para>
 
   <para>
-   Since collations and domains do not have real privileges
+   Since collations do not have real privileges
    in <productname>PostgreSQL</productname>, this view shows implicit
    non-grantable <literal>USAGE</literal> privileges granted by the
-   owner to <literal>PUBLIC</literal> for all collations and domains.  The other
+   owner to <literal>PUBLIC</literal> for all collations.  The other
    object types, however, show real privileges.
   </para>
 
@@ -5940,6 +5943,8 @@ ORDER BY c.ordinal_position;
   <para>
    The view <literal>user_defined_types</literal> currently contains
    all composite types defined in the current database.
+   Only those types are shown that the current user has access to (by way
+   of being the owner or having some privilege).
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index f7b52ef9d1a..b5c8bb3a36b 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -42,6 +42,10 @@ GRANT { EXECUTE | ALL [ PRIVILEGES ] }
     ON FUNCTIONS
     TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+    ON TYPES
+    TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
 REVOKE [ GRANT OPTION FOR ]
     { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
     [, ...] | ALL [ PRIVILEGES ] }
@@ -61,6 +65,12 @@ REVOKE [ GRANT OPTION FOR ]
     ON FUNCTIONS
     FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
     [ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+    { USAGE | ALL [ PRIVILEGES ] }
+    ON TYPES
+    FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
+    [ CASCADE | RESTRICT ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -72,7 +82,7 @@ REVOKE [ GRANT OPTION FOR ]
    that will be applied to objects created in the future.  (It does not
    affect privileges assigned to already-existing objects.)  Currently,
    only the privileges for tables (including views and foreign tables),
-   sequences, and functions can be altered.
+   sequences, functions, and types (including domains) can be altered.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml
index 5c7a86fe87f..99e8e906d28 100644
--- a/doc/src/sgml/ref/alter_foreign_table.sgml
+++ b/doc/src/sgml/ref/alter_foreign_table.sgml
@@ -157,6 +157,8 @@ ALTER FOREIGN TABLE <replaceable class="PARAMETER">name</replaceable>
    the table's schema.  (These restrictions enforce that altering the owner
    doesn't do anything you couldn't do by dropping and recreating the table.
    However, a superuser can alter ownership of any table anyway.)
+   To add a column or alter a column type, you must also
+   have <literal>USAGE</literal> privilege on the data type.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 3b111a4c2b9..1976f6dcede 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -594,6 +594,9 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
    the table's schema.  (These restrictions enforce that altering the owner
    doesn't do anything you couldn't do by dropping and recreating the table.
    However, a superuser can alter ownership of any table anyway.)
+   To add a column or alter a column type or use the <literal>OF</literal>
+   clause, you must also have <literal>USAGE</literal> privilege on the data
+   type.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/alter_type.sgml b/doc/src/sgml/ref/alter_type.sgml
index a81fe6c340c..6386085a608 100644
--- a/doc/src/sgml/ref/alter_type.sgml
+++ b/doc/src/sgml/ref/alter_type.sgml
@@ -156,6 +156,8 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE <replacea
    the type's schema.  (These restrictions enforce that altering the owner
    doesn't do anything you couldn't do by dropping and recreating the type.
    However, a superuser can alter ownership of any type anyway.)
+   To add an attribute or alter an attribute type, you must also
+   have <literal>USAGE</literal> privilege on the data type.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml
index 7c2e1a9e738..d5e4e272fce 100644
--- a/doc/src/sgml/ref/create_aggregate.sgml
+++ b/doc/src/sgml/ref/create_aggregate.sgml
@@ -163,6 +163,13 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
    than</quote> or <quote>greater than</quote> strategy member of a B-tree
    index operator class.
   </para>
+
+  <para>
+   To be able to create an aggregate function, you must
+   have <literal>USAGE</literal> privilege on the argument types, the state
+   type, and the return type, as well as <literal>EXECUTE</literal> privilege
+   on the transition and final functions.
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/create_cast.sgml b/doc/src/sgml/ref/create_cast.sgml
index c0039edf752..964cbf4d41a 100644
--- a/doc/src/sgml/ref/create_cast.sgml
+++ b/doc/src/sgml/ref/create_cast.sgml
@@ -159,10 +159,11 @@ SELECT CAST ( 2 AS numeric ) + 4.0;
   </note>
 
   <para>
-   To be able to create a cast, you must own the source or the target
-   data type.  To create a binary-coercible cast, you must be superuser.
-   (This restriction is made because an erroneous binary-coercible cast
-   conversion can easily crash the server.)
+   To be able to create a cast, you must own the source or the target data type
+   and have <literal>USAGE</literal> privilege on the other type.  To create a
+   binary-coercible cast, you must be superuser.  (This restriction is made
+   because an erroneous binary-coercible cast conversion can easily crash the
+   server.)
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml
index 0f901d78ee9..49db069f895 100644
--- a/doc/src/sgml/ref/create_domain.sgml
+++ b/doc/src/sgml/ref/create_domain.sgml
@@ -59,6 +59,11 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea
    Define a domain rather than setting up each table's constraint
    individually.
   </para>
+
+  <para>
+   To be able to create a domain, you must have <literal>USAGE</literal>
+   privilege on the underlying type.
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 7f1cc4264db..2113d001b08 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -52,6 +52,11 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
    the foreign table.  Therefore, foreign tables cannot have the same
    name as any existing data type in the same schema.
   </para>
+
+  <para>
+   To be able to create a table, you must have <literal>USAGE</literal>
+   privilege on all column types.
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index a617f965427..2a87130356e 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -92,6 +92,11 @@ CREATE [ OR REPLACE ] FUNCTION
   <para>
    The user that creates the function becomes the owner of the function.
   </para>
+
+  <para>
+   To be able to create a function, you must have <literal>USAGE</literal>
+   privilege on the argument types and the return type.
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/create_operator.sgml b/doc/src/sgml/ref/create_operator.sgml
index 28b5e7e0da2..dd33f065afc 100644
--- a/doc/src/sgml/ref/create_operator.sgml
+++ b/doc/src/sgml/ref/create_operator.sgml
@@ -103,6 +103,13 @@ CREATE OPERATOR <replaceable>name</replaceable> (
    The other clauses specify optional operator optimization clauses.
    Their meaning is detailed in <xref linkend="xoper-optimization">.
   </para>
+
+  <para>
+   To be able to create an operator, you must have <literal>USAGE</literal>
+   privilege on the argument types and the return type, as well
+   as <literal>EXECUTE</literal> privilege on the underlying function.  If a
+   commutator or negator operator is specified, you must own these operators.
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index d7b0fcf73a6..30e41540479 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -124,6 +124,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
    a column constraint is only a notational convenience for use when the
    constraint only affects one column.
   </para>
+
+  <para>
+   To be able to create a table, you must have <literal>USAGE</literal>
+   privilege on all column types or the type in the <literal>OF</literal>
+   clause, respectively.
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml
index 3308ee72eaf..7d87a67b3f1 100644
--- a/doc/src/sgml/ref/create_type.sgml
+++ b/doc/src/sgml/ref/create_type.sgml
@@ -104,6 +104,11 @@ CREATE TYPE <replaceable class="parameter">name</replaceable>
    A stand-alone composite type is useful, for example, as the argument or
    return type of a function.
   </para>
+
+  <para>
+   To be able to create a composite type, you must
+   have <literal>USAGE</literal> privilege on all attribute types.
+  </para>
   </refsect2>
 
   <refsect2 id="SQL-CREATETYPE-enum">
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index aa9bbcbdab9..51dd2e05e75 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -43,6 +43,10 @@ GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
     TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
 GRANT { USAGE | ALL [ PRIVILEGES ] }
+    ON DOMAIN <replaceable>domain_name</replaceable> [, ...]
+    TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+GRANT { USAGE | ALL [ PRIVILEGES ] }
     ON FOREIGN DATA WRAPPER <replaceable>fdw_name</replaceable> [, ...]
     TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
@@ -71,6 +75,10 @@ GRANT { CREATE | ALL [ PRIVILEGES ] }
     ON TABLESPACE <replaceable>tablespace_name</replaceable> [, ...]
     TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+    ON TYPE <replaceable>type_name</replaceable> [, ...]
+    TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
 GRANT <replaceable class="PARAMETER">role_name</replaceable> [, ...] TO <replaceable class="PARAMETER">role_name</replaceable> [, ...] [ WITH ADMIN OPTION ]
 </synopsis>
  </refsynopsisdiv>
@@ -336,6 +344,15 @@ GRANT <replaceable class="PARAMETER">role_name</replaceable> [, ...] TO <replace
        <function>currval</function> and <function>nextval</function> functions.
       </para>
       <para>
+       For types and domains, this privilege allow the use of the type or
+       domain in the creation of tables, functions, and other schema objects.
+       (Note that it does not control general <quote>usage</quote> of the type,
+       such as values of the type appearing in queries.  It only prevents
+       objects from being created that depend on the type.  The main purpose of
+       the privilege is controlling which users create dependencies on a type,
+       which could prevent the owner from changing the type later.)
+      </para>
+      <para>
        For foreign-data wrappers, this privilege enables the grantee
        to create new servers using that foreign-data wrapper.
       </para>
@@ -616,7 +633,7 @@ GRANT admins TO joe;
    <para>
     The SQL standard provides for a <literal>USAGE</literal> privilege
     on other kinds of objects: character sets, collations,
-    translations, domains.
+    translations.
    </para>
 
    <para>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index f97929b1fc9..a9b1ed2699d 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1048,7 +1048,7 @@ testdb=&gt;
         pattern or the <literal>S</literal> modifier to include system
         objects.
         If <literal>+</literal> is appended to the command name, each object
-        is listed with its associated description.
+        is listed with its associated permissions and description.
         </para>
         </listitem>
       </varlistentry>
@@ -1387,8 +1387,8 @@ testdb=&gt;
         If <replaceable class="parameter">pattern</replaceable> is
         specified, only types whose names match the pattern are listed.
         If <literal>+</literal> is appended to the command name, each type is
-        listed with its internal name and size, as well as its allowed values
-        if it is an <type>enum</> type.
+        listed with its internal name and size, its allowed values
+        if it is an <type>enum</> type, and its associated permissions.
         By default, only user-created objects are shown;  supply a
         pattern or the <literal>S</literal> modifier to include system
         objects.
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 3229e4bcc71..a390375c174 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -52,6 +52,12 @@ REVOKE [ GRANT OPTION FOR ]
 
 REVOKE [ GRANT OPTION FOR ]
     { USAGE | ALL [ PRIVILEGES ] }
+    ON DOMAIN <replaceable>domain_name</replaceable> [, ...]
+    FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
+    [ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+    { USAGE | ALL [ PRIVILEGES ] }
     ON FOREIGN DATA WRAPPER <replaceable>fdw_name</replaceable> [, ...]
     FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
     [ CASCADE | RESTRICT ]
@@ -93,6 +99,12 @@ REVOKE [ GRANT OPTION FOR ]
     FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
     [ CASCADE | RESTRICT ]
 
+REVOKE [ GRANT OPTION FOR ]
+    { USAGE | ALL [ PRIVILEGES ] }
+    ON TYPE <replaceable>type_name</replaceable> [, ...]
+    FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
+    [ CASCADE | RESTRICT ]
+
 REVOKE [ ADMIN OPTION FOR ]
     <replaceable class="PARAMETER">role_name</replaceable> [, ...] FROM <replaceable class="PARAMETER">role_name</replaceable> [, ...]
     [ CASCADE | RESTRICT ]