Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3cb5d65

Browse files
committed
Support column-level privileges, as required by SQL standard.
Stephen Frost, with help from KaiGai Kohei and others
1 parentbf136cf commit3cb5d65

File tree

59 files changed

+2312
-720
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

59 files changed

+2312
-720
lines changed

‎doc/src/sgml/catalogs.sgml‎

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.190 2009/01/2217:27:54 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.191 2009/01/2220:15:59 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -1028,6 +1028,16 @@
10281028
</entry>
10291029
</row>
10301030

1031+
<row>
1032+
<entry><structfield>attacl</structfield></entry>
1033+
<entry><type>aclitem[]</type></entry>
1034+
<entry></entry>
1035+
<entry>
1036+
Column-level access privileges, if any have been granted specifically
1037+
on this column
1038+
</entry>
1039+
</row>
1040+
10311041
</tbody>
10321042
</tgroup>
10331043
</table>
@@ -4250,6 +4260,17 @@
42504260
<entry>The OID of the specific dependent object</entry>
42514261
</row>
42524262

4263+
<row>
4264+
<entry><structfield>objsubid</structfield></entry>
4265+
<entry><type>int4</type></entry>
4266+
<entry></entry>
4267+
<entry>
4268+
For a table column, this is the column number (the
4269+
<structfield>objid</> and <structfield>classid</> refer to the
4270+
table itself). For all other object types, this column is zero
4271+
</entry>
4272+
</row>
4273+
42534274
<row>
42544275
<entry><structfield>refclassid</structfield></entry>
42554276
<entry><type>oid</type></entry>

‎doc/src/sgml/ref/grant.sgml‎

Lines changed: 50 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.73 2008/12/19 16:25:16 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.74 2009/01/22 20:15:59 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -26,6 +26,11 @@ GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
2626
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
2727
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
2828

29+
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
30+
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
31+
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
32+
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
33+
2934
GRANT { { USAGE | SELECT | UPDATE }
3035
[,...] | ALL [ PRIVILEGES ] }
3136
ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
@@ -68,7 +73,7 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
6873

6974
<para>
7075
The <command>GRANT</command> command has two basic variants: one
71-
that grants privileges on a database object (table, view, sequence,
76+
that grants privileges on a database object (table,column,view, sequence,
7277
database, foreign-data wrapper, foreign server, function,
7378
procedural language, schema, or tablespace), and one that grants
7479
membership in a role. These variants are similar in many ways, but
@@ -125,7 +130,8 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
125130
<para>
126131
Depending on the type of object, the initial default privileges might
127132
include granting some privileges to <literal>PUBLIC</literal>.
128-
The default is no public access for tables, schemas, and tablespaces;
133+
The default is no public access for tables, columns, schemas, and
134+
tablespaces;
129135
<literal>CONNECT</> privilege and <literal>TEMP</> table creation privilege
130136
for databases;
131137
<literal>EXECUTE</> privilege for functions; and
@@ -145,7 +151,8 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
145151
<listitem>
146152
<para>
147153
Allows <xref linkend="sql-select" endterm="sql-select-title"> from
148-
any column of the specified table, view, or sequence.
154+
any column, or the specific columns listed, of the specified table,
155+
view, or sequence.
149156
Also allows the use of
150157
<xref linkend="sql-copy" endterm="sql-copy-title"> TO.
151158
This privilege is also needed to reference existing column values in
@@ -162,7 +169,9 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
162169
<listitem>
163170
<para>
164171
Allows <xref linkend="sql-insert" endterm="sql-insert-title"> of a new
165-
row into the specified table.
172+
row into the specified table. If specific columns are listed,
173+
only those columns may be assigned to in the <command>INSERT</>
174+
command (other columns will therefore receive default values).
166175
Also allows <xref linkend="sql-copy" endterm="sql-copy-title"> FROM.
167176
</para>
168177
</listitem>
@@ -173,14 +182,14 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
173182
<listitem>
174183
<para>
175184
Allows <xref linkend="sql-update" endterm="sql-update-title"> of any
176-
column of the specified table.
185+
column, or the specific columns listed, of the specified table.
177186
(In practice, any nontrivial <command>UPDATE</> command will require
178187
<literal>SELECT</> privilege as well, since it must reference table
179188
columns to determine which rows to update, and/or to compute new
180189
values for columns.)
181190
<literal>SELECT ... FOR UPDATE</literal>
182191
and <literal>SELECT ... FOR SHARE</literal>
183-
also require this privilege, in addition to the
192+
also require this privilege on at least one column, in addition to the
184193
<literal>SELECT</literal> privilege. For sequences, this
185194
privilege allows the use of the <function>nextval</function> and
186195
<function>setval</function> functions.
@@ -217,7 +226,8 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
217226
<para>
218227
To create a foreign key constraint, it is
219228
necessary to have this privilege on both the referencing and
220-
referenced tables.
229+
referenced columns. The privilege may be granted for all columns
230+
of a table, or just specific columns.
221231
</para>
222232
</listitem>
223233
</varlistentry>
@@ -373,6 +383,14 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
373383
to revoke access privileges.
374384
</para>
375385

386+
<para>
387+
A user may perform <command>SELECT</>, <command>INSERT</>, etc. on a
388+
column if he holds that privilege for either the specific column or
389+
its whole table. Granting the privilege at the table level and then
390+
revoking it for one column will not do what you might wish: the
391+
table-level grant is unaffected by a column-level operation.
392+
</para>
393+
376394
<para>
377395
When a non-owner of an object attempts to <command>GRANT</> privileges
378396
on the object, the command will fail outright if the user has no
@@ -428,33 +446,27 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
428446
</para>
429447

430448
<para>
431-
Granting permission on a table does not automatically extend
432-
permissions to any sequences used by the table, including
433-
sequences tied to <type>SERIAL</> columns. Permissions on
434-
sequence must be set separately.
449+
Granting permission on a table does not automatically extend
450+
permissions to any sequences used by the table, including
451+
sequences tied to <type>SERIAL</> columns. Permissions on
452+
sequences must be set separately.
435453
</para>
436454

437455
<para>
438-
Currently, <productname>PostgreSQL</productname> does not support
439-
granting or revoking privileges for individual columns of a table.
440-
One possible workaround is to create a view having just the desired
441-
columns and then grant privileges to that view.
442-
</para>
443-
444-
<para>
445-
Use <xref linkend="app-psql">'s <command>\z</command> command
446-
to obtain information about existing privileges, for example:
456+
Use <xref linkend="app-psql">'s <command>\dp</command> command
457+
to obtain information about existing privileges for tables and
458+
columns. For example:
447459
<programlisting>
448-
=&gt; \z mytable
449-
Access privileges
450-
Schema | Name | Type | Access privileges
451-
--------+---------+-------+-----------------------
452-
public | mytable | table | miriam=arwdDxt/miriam
453-
: =r/miriam
454-
: admin=arw/miriam
460+
=&gt; \dp mytable
461+
Access privileges
462+
Schema | Name | Type |Access privileges | Column access privileges
463+
--------+---------+-------+-----------------------+--------------------------
464+
public | mytable | table | miriam=arwdDxt/miriam | col1:
465+
: =r/miriam : miriam_rw=rw/miriam
466+
: admin=arw/miriam
455467
(1 row)
456468
</programlisting>
457-
The entries shown by <command>\z</command> are interpreted thus:
469+
The entries shown by <command>\dp</command> are interpreted thus:
458470
<programlisting>
459471
rolename=xxxx -- privileges granted to a role
460472
=xxxx -- privileges granted to PUBLIC
@@ -471,7 +483,7 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
471483
C -- CREATE
472484
c -- CONNECT
473485
T -- TEMPORARY
474-
arwdDxt -- ALL PRIVILEGES (for tables)
486+
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
475487
* -- grant option for preceding privilege
476488

477489
/yyyy -- role that granted this privilege
@@ -483,9 +495,15 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
483495
<programlisting>
484496
GRANT SELECT ON mytable TO PUBLIC;
485497
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
498+
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
486499
</programlisting>
487500
</para>
488501

502+
<para>
503+
For non-table objects there are other <command>\d</> commands
504+
that can display their privileges.
505+
</para>
506+
489507
<para>
490508
If the <quote>Access privileges</> column is empty for a given object,
491509
it means the object has default privileges (that is, its privileges column
@@ -495,7 +513,8 @@ GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
495513
<command>REVOKE</> on an object
496514
will instantiate the default privileges (producing, for example,
497515
<literal>{miriam=arwdDxt/miriam}</>) and then modify them per the
498-
specified request.
516+
specified request. Entries are shown in <quote>Column access
517+
privileges</> only for columns with nondefault privileges.
499518
</para>
500519

501520
<para>
@@ -562,11 +581,6 @@ GRANT admins TO joe;
562581
<quote>_SYSTEM</>, the owner cannot revoke these rights.
563582
</para>
564583

565-
<para>
566-
<productname>PostgreSQL</productname> does not support the SQL-standard
567-
functionality of setting privileges for individual columns.
568-
</para>
569-
570584
<para>
571585
The SQL standard provides for a <literal>USAGE</literal> privilege
572586
on other kinds of objects: character sets, collations,

‎doc/src/sgml/ref/insert.sgml‎

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.37 2008/11/14 10:22:47 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.38 2009/01/22 20:15:59 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -69,11 +69,14 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
6969

7070
<para>
7171
You must have <literal>INSERT</literal> privilege on a table in
72-
order to insert into it, and <literal>SELECT</> privilege on it to
73-
use <literal>RETURNING</>. If you use the <replaceable
72+
order to insert into it. If a column list is specified, you only
73+
need <literal>INSERT</literal> privilege on the listed columns.
74+
Use of the <literal>RETURNING</> clause requires <literal>SELECT</>
75+
privilege on all columns mentioned in <literal>RETURNING</>.
76+
If you use the <replaceable
7477
class="PARAMETER">query</replaceable> clause to insert rows from a
75-
query, youalso need to have <literal>SELECT</literal> privilege on
76-
any table used in the query.
78+
query, youof course need to have <literal>SELECT</literal> privilege on
79+
any tableor columnused in the query.
7780
</para>
7881
</refsect1>
7982

‎doc/src/sgml/ref/revoke.sgml‎

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.50 2008/12/19 16:25:16 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.51 2009/01/22 20:15:59 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -28,6 +28,13 @@ REVOKE [ GRANT OPTION FOR ]
2828
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
2929
[ CASCADE | RESTRICT ]
3030

31+
REVOKE [ GRANT OPTION FOR ]
32+
{ { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
33+
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
34+
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
35+
FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
36+
[ CASCADE | RESTRICT ]
37+
3138
REVOKE [ GRANT OPTION FOR ]
3239
{ { USAGE | SELECT | UPDATE }
3340
[,...] | ALL [ PRIVILEGES ] }
@@ -131,6 +138,11 @@ REVOKE [ ADMIN OPTION FOR ]
131138
was also granted through other users.
132139
</para>
133140

141+
<para>
142+
When revoking privileges on a table, the corresponding column privileges
143+
(if any) are automatically revoked on each column of the table, as well.
144+
</para>
145+
134146
<para>
135147
When revoking membership in a role, <literal>GRANT OPTION</> is instead
136148
called <literal>ADMIN OPTION</>, but the behavior is similar.
@@ -143,9 +155,11 @@ REVOKE [ ADMIN OPTION FOR ]
143155
<title>Notes</title>
144156

145157
<para>
146-
Use <xref linkend="app-psql">'s <command>\z</command> command to
147-
display the privileges granted on existing objects. See <xref
148-
linkend="sql-grant" endterm="sql-grant-title"> for information about the format.
158+
Use <xref linkend="app-psql">'s <command>\dp</command> command to
159+
display the privileges granted on existing tables and columns. See <xref
160+
linkend="sql-grant" endterm="sql-grant-title"> for information about the
161+
format. For non-table objects there are other <command>\d</> commands
162+
that can display their privileges.
149163
</para>
150164

151165
<para>

‎doc/src/sgml/ref/select.sgml‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.117 2009/01/12 14:06:20 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.118 2009/01/22 20:15:59 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -186,10 +186,11 @@ TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
186186
</para>
187187

188188
<para>
189-
You must have <literal>SELECT</literal> privilege on a table to
190-
read its values. The use of <literal>FOR UPDATE</literal> or
191-
<literal>FOR SHARE</literal> requires
192-
<literal>UPDATE</literal> privilege as well.
189+
You must have <literal>SELECT</literal> privilege on each column used
190+
in a <command>SELECT</> command. The use of <literal>FOR UPDATE</literal>
191+
or <literal>FOR SHARE</literal> requires
192+
<literal>UPDATE</literal> privilege as well (for at least one column
193+
of each table so selected).
193194
</para>
194195
</refsect1>
195196

‎doc/src/sgml/ref/update.sgml‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.48 2008/11/16 17:34:28 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.49 2009/01/22 20:16:00 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -66,9 +66,10 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
6666
</para>
6767

6868
<para>
69-
You must have the <literal>UPDATE</literal> privilege on the table
70-
to update it, as well as the <literal>SELECT</literal>
71-
privilege to any table whose values are read in the
69+
You must have the <literal>UPDATE</literal> privilege on the table,
70+
or at least on the column(s) that are listed to be updated.
71+
You must also have the <literal>SELECT</literal>
72+
privilege on any column whose values are read in the
7273
<replaceable class="parameter">expressions</replaceable> or
7374
<replaceable class="parameter">condition</replaceable>.
7475
</para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp