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

Commit7449427

Browse files
committed
Clean up some loose ends from the column privileges patch: add
has_column_privilege and has_any_column_privilege SQL functions; fix theinformation_schema views that are supposed to pay attention to columnprivileges; adjust pg_stats to show stats for any column you have selectprivilege on; and fix COPY to allow copying a subset of columns if the userhas suitable per-column privileges for all the columns.To improve efficiency of some of the information_schema views, extend thehas_xxx_privilege functions to allow inquiring about the OR of a set ofprivileges in just one call. This is just exposing capability that alreadyexisted in the underlying aclcheck routines.In passing, make the information_schema views report the owner's ownprivileges as being grantable, since Postgres assumes this even when the grantoption bit is not set in the ACL. This is a longstanding oversight.Also, make the new has_xxx_privilege functions for foreign data objects followthe same coding conventions used by the older ones.Stephen Frost and Tom Lane
1 parent0274e1b commit7449427

File tree

14 files changed

+1428
-561
lines changed

14 files changed

+1428
-561
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 102 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.470 2009/01/15 18:19:58 heikki Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.471 2009/02/06 21:15:11 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -11599,6 +11599,38 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
1159911599
</thead>
1160011600

1160111601
<tbody>
11602+
<row>
11603+
<entry><literal><function>has_any_column_privilege</function>(<parameter>user</parameter>,
11604+
<parameter>table</parameter>,
11605+
<parameter>privilege</parameter>)</literal>
11606+
</entry>
11607+
<entry><type>boolean</type></entry>
11608+
<entry>does user have privilege for any column of table</entry>
11609+
</row>
11610+
<row>
11611+
<entry><literal><function>has_any_column_privilege</function>(<parameter>table</parameter>,
11612+
<parameter>privilege</parameter>)</literal>
11613+
</entry>
11614+
<entry><type>boolean</type></entry>
11615+
<entry>does current user have privilege for any column of table</entry>
11616+
</row>
11617+
<row>
11618+
<entry><literal><function>has_column_privilege</function>(<parameter>user</parameter>,
11619+
<parameter>table</parameter>,
11620+
<parameter>column</parameter>,
11621+
<parameter>privilege</parameter>)</literal>
11622+
</entry>
11623+
<entry><type>boolean</type></entry>
11624+
<entry>does user have privilege for column</entry>
11625+
</row>
11626+
<row>
11627+
<entry><literal><function>has_column_privilege</function>(<parameter>table</parameter>,
11628+
<parameter>column</parameter>,
11629+
<parameter>privilege</parameter>)</literal>
11630+
</entry>
11631+
<entry><type>boolean</type></entry>
11632+
<entry>does current user have privilege for column</entry>
11633+
</row>
1160211634
<row>
1160311635
<entry><literal><function>has_database_privilege</function>(<parameter>user</parameter>,
1160411636
<parameter>database</parameter>,
@@ -11738,6 +11770,12 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
1173811770
</tgroup>
1173911771
</table>
1174011772

11773+
<indexterm>
11774+
<primary>has_any_column_privilege</primary>
11775+
</indexterm>
11776+
<indexterm>
11777+
<primary>has_column_privilege</primary>
11778+
</indexterm>
1174111779
<indexterm>
1174211780
<primary>has_database_privilege</primary>
1174311781
</indexterm>
@@ -11766,11 +11804,71 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
1176611804
<primary>pg_has_role</primary>
1176711805
</indexterm>
1176811806

11807+
<para>
11808+
<function>has_table_privilege</function> checks whether a user
11809+
can access a table in a particular way. The user can be
11810+
specified by name or by OID
11811+
(<literal>pg_authid.oid</literal>), or if the argument is
11812+
omitted
11813+
<function>current_user</function> is assumed. The table can be specified
11814+
by name or by OID. (Thus, there are actually six variants of
11815+
<function>has_table_privilege</function>, which can be distinguished by
11816+
the number and types of their arguments.) When specifying by name,
11817+
the name can be schema-qualified if necessary.
11818+
The desired access privilege type
11819+
is specified by a text string, which must evaluate to one of the
11820+
values <literal>SELECT</literal>, <literal>INSERT</literal>,
11821+
<literal>UPDATE</literal>, <literal>DELETE</literal>, <literal>TRUNCATE</>,
11822+
<literal>REFERENCES</literal>, or <literal>TRIGGER</literal>. Optionally,
11823+
<literal>WITH GRANT OPTION</> can be added to a privilege type to test
11824+
whether the privilege is held with grant option. Also, multiple privilege
11825+
types can be listed separated by commas, in which case the result will
11826+
be <literal>true</> if any of the listed privileges is held.
11827+
(Case of the privilege string is not significant, and extra whitespace
11828+
is allowed between but not within privilege names.)
11829+
Some examples:
11830+
<programlisting>
11831+
SELECT has_table_privilege('myschema.mytable', 'select');
11832+
SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION');
11833+
</programlisting>
11834+
</para>
11835+
11836+
<para>
11837+
<function>has_any_column_privilege</function> checks whether a user can
11838+
access any column of a table in a particular way. The possibilities for
11839+
its arguments are the same as for <function>has_table_privilege</>,
11840+
except that the desired access privilege type must evaluate to some
11841+
combination of
11842+
<literal>SELECT</literal>,
11843+
<literal>INSERT</literal>,
11844+
<literal>UPDATE</literal>, or
11845+
<literal>REFERENCES</literal>. Note that having any of these privileges
11846+
at the table level implicitly grants it for each column of the table,
11847+
so <function>has_any_column_privilege</function> will always return
11848+
<literal>true</> if <function>has_table_privilege</> does for the same
11849+
arguments. But <function>has_any_column_privilege</> also succeeds if
11850+
there is a column-level grant of the privilege for at least one column.
11851+
</para>
11852+
11853+
<para>
11854+
<function>has_column_privilege</function> checks whether a user
11855+
can access a column in a particular way. The possibilities for its
11856+
arguments are analogous to <function>has_table_privilege</function>,
11857+
with the addition that the column can be specified either by name
11858+
or attribute number.
11859+
The desired access privilege type must evaluate to some combination of
11860+
<literal>SELECT</literal>,
11861+
<literal>INSERT</literal>,
11862+
<literal>UPDATE</literal>, or
11863+
<literal>REFERENCES</literal>. Note that having any of these privileges
11864+
at the table level implicitly grants it for each column of the table.
11865+
</para>
11866+
1176911867
<para>
1177011868
<function>has_database_privilege</function> checks whether a user
1177111869
can access a database in a particular way. The possibilities for its
1177211870
arguments are analogous to <function>has_table_privilege</function>.
11773-
The desired access privilege type must evaluate to
11871+
The desired access privilege type must evaluate to some combination of
1177411872
<literal>CREATE</literal>,
1177511873
<literal>CONNECT</literal>,
1177611874
<literal>TEMPORARY</literal>, or
@@ -11813,7 +11911,7 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
1181311911
<function>has_schema_privilege</function> checks whether a user
1181411912
can access a schema in a particular way. The possibilities for its
1181511913
arguments are analogous to <function>has_table_privilege</function>.
11816-
The desired access privilege type must evaluate to
11914+
The desired access privilege type must evaluate to some combination of
1181711915
<literal>CREATE</literal> or
1181811916
<literal>USAGE</literal>.
1181911917
</para>
@@ -11826,29 +11924,6 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
1182611924
<literal>USAGE</literal>.
1182711925
</para>
1182811926

11829-
<para>
11830-
<function>has_table_privilege</function> checks whether a user
11831-
can access a table in a particular way. The user can be
11832-
specified by name or by OID
11833-
(<literal>pg_authid.oid</literal>), or if the argument is
11834-
omitted
11835-
<function>current_user</function> is assumed. The table can be specified
11836-
by name or by OID. (Thus, there are actually six variants of
11837-
<function>has_table_privilege</function>, which can be distinguished by
11838-
the number and types of their arguments.) When specifying by name,
11839-
the name can be schema-qualified if necessary.
11840-
The desired access privilege type
11841-
is specified by a text string, which must evaluate to one of the
11842-
values <literal>SELECT</literal>, <literal>INSERT</literal>,
11843-
<literal>UPDATE</literal>, <literal>DELETE</literal>, <literal>TRUNCATE</>,
11844-
<literal>REFERENCES</literal>, or <literal>TRIGGER</literal>.
11845-
(Case of the string is not significant, however.)
11846-
An example is:
11847-
<programlisting>
11848-
SELECT has_table_privilege('myschema.mytable', 'select');
11849-
</programlisting>
11850-
</para>
11851-
1185211927
<para>
1185311928
<function>has_tablespace_privilege</function> checks whether a user
1185411929
can access a tablespace in a particular way. The possibilities for its
@@ -11861,7 +11936,7 @@ SELECT has_table_privilege('myschema.mytable', 'select');
1186111936
<function>pg_has_role</function> checks whether a user
1186211937
can access a role in a particular way. The possibilities for its
1186311938
arguments are analogous to <function>has_table_privilege</function>.
11864-
The desired access privilege type must evaluate to
11939+
The desired access privilege type must evaluate to some combination of
1186511940
<literal>MEMBER</literal> or
1186611941
<literal>USAGE</literal>.
1186711942
<literal>MEMBER</literal> denotes direct or indirect membership in
@@ -11870,12 +11945,6 @@ SELECT has_table_privilege('myschema.mytable', 'select');
1187011945
are immediately available without doing <command>SET ROLE</>.
1187111946
</para>
1187211947

11873-
<para>
11874-
To test whether a user holds a grant option on the privilege,
11875-
append <literal>WITH GRANT OPTION</literal> to the privilege key
11876-
word; for example <literal>'UPDATE WITH GRANT OPTION'</literal>.
11877-
</para>
11878-
1187911948
<para>
1188011949
<xref linkend="functions-info-schema-table"> shows functions that
1188111950
determine whether a certain object is <firstterm>visible</> in the

‎doc/src/sgml/information_schema.sgml

Lines changed: 12 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.37 2009/01/20 09:10:20 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.38 2009/02/06 21:15:11 tgl Exp $ -->
22

33
<chapter id="information-schema">
44
<title>The Information Schema</title>
@@ -686,18 +686,11 @@
686686
</para>
687687

688688
<para>
689-
In <productname>PostgreSQL</productname>, you can only grant
690-
privileges on entire tables, not individual columns. Therefore,
691-
this view contains the same information as
692-
<literal>table_privileges</literal>, just represented through one
693-
row for each column in each appropriate table, but it only covers
689+
If a privilege has been granted on an entire table, it will show up in
690+
this view as a grant for each column, but only for the
694691
privilege types where column granularity is possible:
695692
<literal>SELECT</literal>, <literal>INSERT</literal>,
696-
<literal>UPDATE</literal>, <literal>REFERENCES</literal>. If you
697-
want to make your applications fit for possible future
698-
developments, it is generally the right choice to use this view
699-
instead of <literal>table_privileges</literal> if one of those
700-
privilege types is concerned.
693+
<literal>UPDATE</literal>, <literal>REFERENCES</literal>.
701694
</para>
702695

703696
<table>
@@ -2727,8 +2720,10 @@ ORDER BY c.ordinal_position;
27272720

27282721
<para>
27292722
The view <literal>referential_constraints</literal> contains all
2730-
referential (foreign key) constraints in the current database that
2731-
belong to a table owned by a currently enabled role.
2723+
referential (foreign key) constraints in the current database.
2724+
Only those constraints are shown for which the current user has
2725+
write access to the referencing table (by way of being the
2726+
owner or having some privilege other than SELECT).
27322727
</para>
27332728

27342729
<table>
@@ -3157,8 +3152,8 @@ ORDER BY c.ordinal_position;
31573152

31583153
<para>
31593154
The view <literal>routine_privileges</literal> identifies all
3160-
privileges granted to a currently enabled role or by a currently
3161-
enabled role. There is one row for each combination of function,
3155+
privileges grantedon functionsto a currently enabled role or by a
3156+
currentlyenabled role. There is one row for each combination of function,
31623157
grantor, and grantee.
31633158
</para>
31643159

@@ -4500,7 +4495,7 @@ ORDER BY c.ordinal_position;
45004495
<para>
45014496
The view <literal>table_constraints</literal> contains all
45024497
constraints belonging to tables that the current user owns or has
4503-
some privilege on.
4498+
somenon-SELECTprivilege on.
45044499
</para>
45054500

45064501
<table>
@@ -4777,7 +4772,7 @@ ORDER BY c.ordinal_position;
47774772
<para>
47784773
The view <literal>triggers</literal> contains all triggers defined
47794774
in the current database on tables that the current user owns or has
4780-
some privilege on.
4775+
somenon-SELECTprivilege on.
47814776
</para>
47824777

47834778
<table>

0 commit comments

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