Clean up some loose ends from the column privileges patch: add

has_column_privilege and has_any_column_privilege SQL functions; fix the
information_schema views that are supposed to pay attention to column
privileges; adjust pg_stats to show stats for any column you have select
privilege on; and fix COPY to allow copying a subset of columns if the user
has suitable per-column privileges for all the columns.

To improve efficiency of some of the information_schema views, extend the
has_xxx_privilege functions to allow inquiring about the OR of a set of
privileges in just one call.  This is just exposing capability that already
existed in the underlying aclcheck routines.

In passing, make the information_schema views report the owner's own
privileges as being grantable, since Postgres assumes this even when the grant
option bit is not set in the ACL.  This is a longstanding oversight.

Also, make the new has_xxx_privilege functions for foreign data objects follow
the same coding conventions used by the older ones.

Stephen Frost and Tom Lane

2 files changed, 114 insertions, 50 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 341a37fb715..0f8635dc5e2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.470 2009/01/15 18:19:58 heikki Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.471 2009/02/06 21:15:11 tgl Exp $ -->
 
  <chapter id="functions">
   <title>Functions and Operators</title>
@@ -11600,6 +11600,38 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
 
      <tbody>
       <row>
+       <entry><literal><function>has_any_column_privilege</function>(<parameter>user</parameter>,
+                                  <parameter>table</parameter>,
+                                  <parameter>privilege</parameter>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>does user have privilege for any column of table</entry>
+      </row>
+      <row>
+       <entry><literal><function>has_any_column_privilege</function>(<parameter>table</parameter>,
+                                  <parameter>privilege</parameter>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>does current user have privilege for any column of table</entry>
+      </row>
+      <row>
+       <entry><literal><function>has_column_privilege</function>(<parameter>user</parameter>,
+                                  <parameter>table</parameter>,
+                                  <parameter>column</parameter>,
+                                  <parameter>privilege</parameter>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>does user have privilege for column</entry>
+      </row>
+      <row>
+       <entry><literal><function>has_column_privilege</function>(<parameter>table</parameter>,
+                                  <parameter>column</parameter>,
+                                  <parameter>privilege</parameter>)</literal>
+       </entry>
+       <entry><type>boolean</type></entry>
+       <entry>does current user have privilege for column</entry>
+      </row>
+      <row>
        <entry><literal><function>has_database_privilege</function>(<parameter>user</parameter>,
                                   <parameter>database</parameter>,
                                   <parameter>privilege</parameter>)</literal>
@@ -11739,6 +11771,12 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
    </table>
 
    <indexterm>
+    <primary>has_any_column_privilege</primary>
+   </indexterm>
+   <indexterm>
+    <primary>has_column_privilege</primary>
+   </indexterm>
+   <indexterm>
     <primary>has_database_privilege</primary>
    </indexterm>
    <indexterm>
@@ -11767,10 +11805,70 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
    </indexterm>
 
    <para>
+    <function>has_table_privilege</function> checks whether a user
+    can access a table in a particular way.  The user can be
+    specified by name or by OID
+    (<literal>pg_authid.oid</literal>), or if the argument is
+    omitted
+    <function>current_user</function> is assumed.  The table can be specified
+    by name or by OID.  (Thus, there are actually six variants of
+    <function>has_table_privilege</function>, which can be distinguished by
+    the number and types of their arguments.)  When specifying by name,
+    the name can be schema-qualified if necessary.
+    The desired access privilege type
+    is specified by a text string, which must evaluate to one of the
+    values <literal>SELECT</literal>, <literal>INSERT</literal>,
+    <literal>UPDATE</literal>, <literal>DELETE</literal>, <literal>TRUNCATE</>,
+    <literal>REFERENCES</literal>, or <literal>TRIGGER</literal>.  Optionally,
+    <literal>WITH GRANT OPTION</> can be added to a privilege type to test
+    whether the privilege is held with grant option.  Also, multiple privilege
+    types can be listed separated by commas, in which case the result will
+    be <literal>true</> if any of the listed privileges is held.
+    (Case of the privilege string is not significant, and extra whitespace
+    is allowed between but not within privilege names.)
+    Some examples:
+<programlisting>
+SELECT has_table_privilege('myschema.mytable', 'select');
+SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION');
+</programlisting>
+   </para>
+
+   <para>
+    <function>has_any_column_privilege</function> checks whether a user can
+    access any column of a table in a particular way.  The possibilities for
+    its arguments are the same as for <function>has_table_privilege</>,
+    except that the desired access privilege type must evaluate to some
+    combination of
+    <literal>SELECT</literal>,
+    <literal>INSERT</literal>,
+    <literal>UPDATE</literal>, or
+    <literal>REFERENCES</literal>.  Note that having any of these privileges
+    at the table level implicitly grants it for each column of the table,
+    so <function>has_any_column_privilege</function> will always return
+    <literal>true</> if <function>has_table_privilege</> does for the same
+    arguments.  But <function>has_any_column_privilege</> also succeeds if
+    there is a column-level grant of the privilege for at least one column.
+   </para>
+
+   <para>
+    <function>has_column_privilege</function> checks whether a user
+    can access a column in a particular way.  The possibilities for its
+    arguments are analogous to <function>has_table_privilege</function>,
+    with the addition that the column can be specified either by name
+    or attribute number.
+    The desired access privilege type must evaluate to some combination of
+    <literal>SELECT</literal>,
+    <literal>INSERT</literal>,
+    <literal>UPDATE</literal>, or
+    <literal>REFERENCES</literal>.  Note that having any of these privileges
+    at the table level implicitly grants it for each column of the table.
+   </para>
+
+   <para>
     <function>has_database_privilege</function> checks whether a user
     can access a database in a particular way.  The possibilities for its
     arguments are analogous to <function>has_table_privilege</function>.
-    The desired access privilege type must evaluate to
+    The desired access privilege type must evaluate to some combination of
     <literal>CREATE</literal>,
     <literal>CONNECT</literal>,
     <literal>TEMPORARY</literal>, or
@@ -11813,7 +11911,7 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
     <function>has_schema_privilege</function> checks whether a user
     can access a schema in a particular way.  The possibilities for its
     arguments are analogous to <function>has_table_privilege</function>.
-    The desired access privilege type must evaluate to
+    The desired access privilege type must evaluate to some combination of
     <literal>CREATE</literal> or
     <literal>USAGE</literal>.
    </para>
@@ -11827,29 +11925,6 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
    </para>
 
    <para>
-    <function>has_table_privilege</function> checks whether a user
-    can access a table in a particular way.  The user can be
-    specified by name or by OID
-    (<literal>pg_authid.oid</literal>), or if the argument is
-    omitted
-    <function>current_user</function> is assumed.  The table can be specified
-    by name or by OID.  (Thus, there are actually six variants of
-    <function>has_table_privilege</function>, which can be distinguished by
-    the number and types of their arguments.)  When specifying by name,
-    the name can be schema-qualified if necessary.
-    The desired access privilege type
-    is specified by a text string, which must evaluate to one of the
-    values <literal>SELECT</literal>, <literal>INSERT</literal>,
-    <literal>UPDATE</literal>, <literal>DELETE</literal>, <literal>TRUNCATE</>,
-    <literal>REFERENCES</literal>, or <literal>TRIGGER</literal>.
-    (Case of the string is not significant, however.)
-    An example is:
-<programlisting>
-SELECT has_table_privilege('myschema.mytable', 'select');
-</programlisting>
-   </para>
-
-   <para>
     <function>has_tablespace_privilege</function> checks whether a user
     can access a tablespace in a particular way.  The possibilities for its
     arguments are analogous to <function>has_table_privilege</function>.
@@ -11861,7 +11936,7 @@ SELECT has_table_privilege('myschema.mytable', 'select');
     <function>pg_has_role</function> checks whether a user
     can access a role in a particular way.  The possibilities for its
     arguments are analogous to <function>has_table_privilege</function>.
-    The desired access privilege type must evaluate to
+    The desired access privilege type must evaluate to some combination of
     <literal>MEMBER</literal> or
     <literal>USAGE</literal>.
     <literal>MEMBER</literal> denotes direct or indirect membership in
@@ -11871,12 +11946,6 @@ SELECT has_table_privilege('myschema.mytable', 'select');
    </para>
 
   <para>
-   To test whether a user holds a grant option on the privilege,
-   append <literal>WITH GRANT OPTION</literal> to the privilege key
-   word; for example <literal>'UPDATE WITH GRANT OPTION'</literal>.
-  </para>
-
-  <para>
    <xref linkend="functions-info-schema-table"> shows functions that
    determine whether a certain object is <firstterm>visible</> in the
    current schema search path.
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 96cbf1f12ad..06ccb6d2dea 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.37 2009/01/20 09:10:20 petere Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.38 2009/02/06 21:15:11 tgl Exp $ -->
 
 <chapter id="information-schema">
  <title>The Information Schema</title>
@@ -686,18 +686,11 @@
   </para>
 
   <para>
-   In <productname>PostgreSQL</productname>, you can only grant
-   privileges on entire tables, not individual columns.  Therefore,
-   this view contains the same information as
-   <literal>table_privileges</literal>, just represented through one
-   row for each column in each appropriate table, but it only covers
+   If a privilege has been granted on an entire table, it will show up in
+   this view as a grant for each column, but only for the
    privilege types where column granularity is possible:
    <literal>SELECT</literal>, <literal>INSERT</literal>,
-   <literal>UPDATE</literal>, <literal>REFERENCES</literal>.  If you
-   want to make your applications fit for possible future
-   developments, it is generally the right choice to use this view
-   instead of <literal>table_privileges</literal> if one of those
-   privilege types is concerned.
+   <literal>UPDATE</literal>, <literal>REFERENCES</literal>.
   </para>
 
   <table>
@@ -2727,8 +2720,10 @@ ORDER BY c.ordinal_position;
 
   <para>
    The view <literal>referential_constraints</literal> contains all
-   referential (foreign key) constraints in the current database that
-   belong to a table owned by a currently enabled role.
+   referential (foreign key) constraints in the current database.
+   Only those constraints are shown for which the current user has
+   write access to the referencing table (by way of being the
+   owner or having some privilege other than SELECT).
   </para>
 
   <table>
@@ -3157,8 +3152,8 @@ ORDER BY c.ordinal_position;
 
   <para>
    The view <literal>routine_privileges</literal> identifies all
-   privileges granted to a currently enabled role or by a currently
-   enabled role.  There is one row for each combination of function,
+   privileges granted on functions to a currently enabled role or by a
+   currently enabled role.  There is one row for each combination of function,
    grantor, and grantee.
   </para>
 
@@ -4500,7 +4495,7 @@ ORDER BY c.ordinal_position;
   <para>
    The view <literal>table_constraints</literal> contains all
    constraints belonging to tables that the current user owns or has
-   some privilege on.
+   some non-SELECT privilege on.
   </para>
 
   <table>
@@ -4777,7 +4772,7 @@ ORDER BY c.ordinal_position;
   <para>
    The view <literal>triggers</literal> contains all triggers defined
    in the current database on tables that the current user owns or has
-   some privilege on.
+   some non-SELECT privilege on.
   </para>
 
   <table>