Support column-level privileges, as required by SQL standard.

Stephen Frost, with help from KaiGai Kohei and others

6 files changed, 109 insertions, 55 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index bd267dcb758..4fae50cd326 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.190 2009/01/22 17:27:54 petere Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.191 2009/01/22 20:15:59 tgl Exp $ -->
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
  -->
@@ -1028,6 +1028,16 @@
       </entry>
      </row>
 
+     <row>
+      <entry><structfield>attacl</structfield></entry>
+      <entry><type>aclitem[]</type></entry>
+      <entry></entry>
+      <entry>
+       Column-level access privileges, if any have been granted specifically
+       on this column
+      </entry>
+     </row>
+
     </tbody>
    </tgroup>
   </table>
@@ -4251,6 +4261,17 @@
      </row>
 
      <row>
+      <entry><structfield>objsubid</structfield></entry>
+      <entry><type>int4</type></entry>
+      <entry></entry>
+      <entry>
+       For a table column, this is the column number (the
+       <structfield>objid</> and <structfield>classid</> refer to the
+       table itself).  For all other object types, this column is zero
+      </entry>
+     </row>
+
+     <row>
       <entry><structfield>refclassid</structfield></entry>
       <entry><type>oid</type></entry>
       <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index dafb8ffb523..ceda72c141d 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.73 2008/12/19 16:25:16 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.74 2009/01/22 20:15:59 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -26,6 +26,11 @@ GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
     ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
     TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
+GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
+    [,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
+    ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
+    TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
 GRANT { { USAGE | SELECT | UPDATE }
     [,...] | ALL [ PRIVILEGES ] }
     ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
@@ -68,7 +73,7 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
 
   <para>
    The <command>GRANT</command> command has two basic variants: one
-   that grants privileges on a database object (table, view, sequence,
+   that grants privileges on a database object (table, column, view, sequence,
    database, foreign-data wrapper, foreign server, function,
    procedural language, schema, or tablespace), and one that grants
    membership in a role.  These variants are similar in many ways, but
@@ -125,7 +130,8 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
   <para>
    Depending on the type of object, the initial default privileges might
    include granting some privileges to <literal>PUBLIC</literal>.
-   The default is no public access for tables, schemas, and tablespaces;
+   The default is no public access for tables, columns, schemas, and
+   tablespaces;
    <literal>CONNECT</> privilege and <literal>TEMP</> table creation privilege
    for databases;
    <literal>EXECUTE</> privilege for functions; and
@@ -145,7 +151,8 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
      <listitem>
       <para>
        Allows <xref linkend="sql-select" endterm="sql-select-title"> from
-       any column of the specified table, view, or sequence.
+       any column, or the specific columns listed, of the specified table,
+       view, or sequence.
        Also allows the use of
        <xref linkend="sql-copy" endterm="sql-copy-title"> TO.
        This privilege is also needed to reference existing column values in
@@ -162,7 +169,9 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
      <listitem>
       <para>
        Allows <xref linkend="sql-insert" endterm="sql-insert-title"> of a new
-       row into the specified table.
+       row into the specified table.  If specific columns are listed,
+       only those columns may be assigned to in the <command>INSERT</>
+       command (other columns will therefore receive default values).
        Also allows <xref linkend="sql-copy" endterm="sql-copy-title"> FROM.
       </para>
      </listitem>
@@ -173,14 +182,14 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
      <listitem>
       <para>
        Allows <xref linkend="sql-update" endterm="sql-update-title"> of any
-       column of the specified table.
+       column, or the specific columns listed, of the specified table.
        (In practice, any nontrivial <command>UPDATE</> command will require
        <literal>SELECT</> privilege as well, since it must reference table
        columns to determine which rows to update, and/or to compute new
        values for columns.)
        <literal>SELECT ... FOR UPDATE</literal>
        and <literal>SELECT ... FOR SHARE</literal>
-       also require this privilege, in addition to the
+       also require this privilege on at least one column, in addition to the
        <literal>SELECT</literal> privilege.  For sequences, this
        privilege allows the use of the <function>nextval</function> and
        <function>setval</function> functions.
@@ -217,7 +226,8 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
       <para>
        To create a foreign key constraint, it is
        necessary to have this privilege on both the referencing and
-       referenced tables.
+       referenced columns.  The privilege may be granted for all columns
+       of a table, or just specific columns.
       </para>
      </listitem>
     </varlistentry>
@@ -374,6 +384,14 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
    </para>
 
    <para>
+    A user may perform <command>SELECT</>, <command>INSERT</>, etc. on a
+    column if he holds that privilege for either the specific column or
+    its whole table.  Granting the privilege at the table level and then
+    revoking it for one column will not do what you might wish: the
+    table-level grant is unaffected by a column-level operation.
+   </para>
+
+   <para>
     When a non-owner of an object attempts to <command>GRANT</> privileges
     on the object, the command will fail outright if the user has no
     privileges whatsoever on the object.  As long as some privilege is
@@ -428,33 +446,27 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
    </para>
 
    <para>
-    Granting permission on a table does not automatically extend 
-    permissions to any sequences used by the table, including 
-    sequences tied to <type>SERIAL</> columns.  Permissions on 
-    sequence must be set separately.
+    Granting permission on a table does not automatically extend
+    permissions to any sequences used by the table, including
+    sequences tied to <type>SERIAL</> columns.  Permissions on
+    sequences must be set separately.
    </para>
 
    <para>
-    Currently, <productname>PostgreSQL</productname> does not support
-    granting or revoking privileges for individual columns of a table.
-    One possible workaround is to create a view having just the desired
-    columns and then grant privileges to that view.
-   </para>
-
-   <para>
-    Use <xref linkend="app-psql">'s <command>\z</command> command
-    to obtain information about existing privileges, for example:
+    Use <xref linkend="app-psql">'s <command>\dp</command> command
+    to obtain information about existing privileges for tables and
+    columns.  For example:
 <programlisting>
-=&gt; \z mytable
-                Access privileges
- Schema |  Name   | Type  |  Access privileges   
---------+---------+-------+-----------------------
- public | mytable | table | miriam=arwdDxt/miriam
-                          : =r/miriam
-                          : admin=arw/miriam
+=&gt; \dp mytable
+                              Access privileges
+ Schema |  Name   | Type  |   Access privileges   | Column access privileges 
+--------+---------+-------+-----------------------+--------------------------
+ public | mytable | table | miriam=arwdDxt/miriam | col1:
+                          : =r/miriam             :   miriam_rw=rw/miriam
+                          : admin=arw/miriam        
 (1 row)
 </programlisting>
-    The entries shown by <command>\z</command> are interpreted thus:
+    The entries shown by <command>\dp</command> are interpreted thus:
 <programlisting>
       rolename=xxxx -- privileges granted to a role
               =xxxx -- privileges granted to PUBLIC
@@ -471,7 +483,7 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
                   C -- CREATE
                   c -- CONNECT
                   T -- TEMPORARY
-            arwdDxt -- ALL PRIVILEGES (for tables)
+            arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
                   * -- grant option for preceding privilege
 
               /yyyy -- role that granted this privilege
@@ -483,10 +495,16 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
 <programlisting>
 GRANT SELECT ON mytable TO PUBLIC;
 GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
+GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
 </programlisting>
    </para>
 
    <para>
+    For non-table objects there are other <command>\d</> commands
+    that can display their privileges.
+   </para>
+
+   <para>
     If the <quote>Access privileges</> column is empty for a given object,
     it means the object has default privileges (that is, its privileges column
     is null).  Default privileges always include all privileges for the owner,
@@ -495,7 +513,8 @@ GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
     <command>REVOKE</> on an object
     will instantiate the default privileges (producing, for example,
     <literal>{miriam=arwdDxt/miriam}</>) and then modify them per the
-    specified request.
+    specified request.  Entries are shown in <quote>Column access
+    privileges</> only for columns with nondefault privileges.
    </para>
 
    <para>
@@ -563,11 +582,6 @@ GRANT admins TO joe;
    </para>
 
    <para>
-    <productname>PostgreSQL</productname> does not support the SQL-standard 
-    functionality of setting privileges for individual columns.
-   </para>
-
-   <para>
     The SQL standard provides for a <literal>USAGE</literal> privilege
     on other kinds of objects: character sets, collations,
     translations, domains.
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 98a1ca28b79..a2a52d8ba42 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.37 2008/11/14 10:22:47 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.38 2009/01/22 20:15:59 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -69,11 +69,14 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
 
   <para>
    You must have <literal>INSERT</literal> privilege on a table in
-   order to insert into it, and <literal>SELECT</> privilege on it to
-   use <literal>RETURNING</>.  If you use the <replaceable
+   order to insert into it.  If a column list is specified, you only
+   need <literal>INSERT</literal> privilege on the listed columns.
+   Use of the <literal>RETURNING</> clause requires <literal>SELECT</>
+   privilege on all columns mentioned in <literal>RETURNING</>.
+   If you use the <replaceable
    class="PARAMETER">query</replaceable> clause to insert rows from a
-   query, you also need to have <literal>SELECT</literal> privilege on
-   any table used in the query.
+   query, you of course need to have <literal>SELECT</literal> privilege on
+   any table or column used in the query.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index c8e91e0a159..0f967770c11 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.50 2008/12/19 16:25:16 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.51 2009/01/22 20:15:59 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -29,6 +29,13 @@ REVOKE [ GRANT OPTION FOR ]
     [ CASCADE | RESTRICT ]
 
 REVOKE [ GRANT OPTION FOR ]
+    { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
+    [,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
+    ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
+    FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
+    [ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
     { { USAGE | SELECT | UPDATE }
     [,...] | ALL [ PRIVILEGES ] }
     ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
@@ -132,6 +139,11 @@ REVOKE [ ADMIN OPTION FOR ]
   </para>
 
   <para>
+   When revoking privileges on a table, the corresponding column privileges
+   (if any) are automatically revoked on each column of the table, as well.
+  </para>
+
+  <para>
    When revoking membership in a role, <literal>GRANT OPTION</> is instead
    called <literal>ADMIN OPTION</>, but the behavior is similar.
    Note also that this form of the command does not
@@ -143,9 +155,11 @@ REVOKE [ ADMIN OPTION FOR ]
   <title>Notes</title>
 
   <para>
-   Use <xref linkend="app-psql">'s <command>\z</command> command to
-   display the privileges granted on existing objects.  See <xref
-   linkend="sql-grant" endterm="sql-grant-title"> for information about the format.
+   Use <xref linkend="app-psql">'s <command>\dp</command> command to
+   display the privileges granted on existing tables and columns.  See <xref
+   linkend="sql-grant" endterm="sql-grant-title"> for information about the
+   format.  For non-table objects there are other <command>\d</> commands
+   that can display their privileges.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index ba14437764f..5cedb1cf318 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.117 2009/01/12 14:06:20 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.118 2009/01/22 20:15:59 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -186,10 +186,11 @@ TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
   </para>
 
   <para>
-   You must have <literal>SELECT</literal> privilege on a table to
-   read its values.  The use of <literal>FOR UPDATE</literal> or
-   <literal>FOR SHARE</literal> requires
-   <literal>UPDATE</literal> privilege as well.
+   You must have <literal>SELECT</literal> privilege on each column used
+   in a <command>SELECT</> command.  The use of <literal>FOR UPDATE</literal>
+   or <literal>FOR SHARE</literal> requires
+   <literal>UPDATE</literal> privilege as well (for at least one column
+   of each table so selected).
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 2464bf16f93..8f32c83dc44 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.48 2008/11/16 17:34:28 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.49 2009/01/22 20:16:00 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -66,9 +66,10 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
   </para>
 
   <para>
-   You must have the <literal>UPDATE</literal> privilege on the table
-   to update it, as well as the <literal>SELECT</literal>
-   privilege to any table whose values are read in the
+   You must have the <literal>UPDATE</literal> privilege on the table,
+   or at least on the column(s) that are listed to be updated.
+   You must also have the <literal>SELECT</literal>
+   privilege on any column whose values are read in the
    <replaceable class="parameter">expressions</replaceable> or
    <replaceable class="parameter">condition</replaceable>.
   </para>