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

Commit82e83f4

Browse files
committed
Add sequence USAGE privileges to information schema
The sequence USAGE privilege is sufficiently similar to the SQLstandard that it seems reasonable to show in the information schema.Also add some compatibility notes about it on the GRANT referencepage.
1 parentee7fa66 commit82e83f4

File tree

3 files changed

+59
-3
lines changed

3 files changed

+59
-3
lines changed

‎doc/src/sgml/information_schema.sgml

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3839,7 +3839,7 @@ ORDER BY c.ordinal_position;
38393839
<row>
38403840
<entry><literal>object_type</literal></entry>
38413841
<entry><type>character_data</type></entry>
3842-
<entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
3842+
<entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal> or <literal>SEQUENCE</literal></entry>
38433843
</row>
38443844

38453845
<row>
@@ -5859,7 +5859,7 @@ ORDER BY c.ordinal_position;
58595859
<literal>USAGE</literal> privileges granted on various kinds of
58605860
objects to a currently enabled role or by a currently enabled role.
58615861
In <productname>PostgreSQL</productname>, this currently applies to
5862-
collations, domains, foreign-data wrappers,andforeign servers. There is one
5862+
collations, domains, foreign-data wrappers, foreign servers, and sequences. There is one
58635863
row for each combination of object, grantor, and grantee.
58645864
</para>
58655865

@@ -5871,6 +5871,13 @@ ORDER BY c.ordinal_position;
58715871
object types, however, show real privileges.
58725872
</para>
58735873

5874+
<para>
5875+
In PostgreSQL, sequences also support <literal>SELECT</literal>
5876+
and <literal>UPDATE</literal> privileges in addition to
5877+
the <literal>USAGE</literal> privilege. These are nonstandard and therefore
5878+
not visible in the information schema.
5879+
</para>
5880+
58745881
<table>
58755882
<title><literal>usage_privileges</literal> Columns</title>
58765883

@@ -5918,7 +5925,7 @@ ORDER BY c.ordinal_position;
59185925
<row>
59195926
<entry><literal>object_type</literal></entry>
59205927
<entry><type>character_data</type></entry>
5921-
<entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
5928+
<entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal> or <literal>SEQUENCE</literal></entry>
59225929
</row>
59235930

59245931
<row>

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -642,6 +642,18 @@ GRANT admins TO joe;
642642
translations.
643643
</para>
644644

645+
<para>
646+
In the SQL standard, sequences only have a <literal>USAGE</literal>
647+
privilege, which controls the use of the <literal>NEXT VALUE FOR</literal>
648+
expression, which is equivalent to the
649+
function <function>nextval</function> in PostgreSQL. The sequence
650+
privileges <literal>SELECT</literal> and <literal>UPDATE</literal> are
651+
PostgreSQL extensions. The application of the
652+
sequence <literal>USAGE</literal> privilege to
653+
the <literal>currval</literal> function is also a PostgreSQL extension (as
654+
is the function itself).
655+
</para>
656+
645657
<para>
646658
Privileges on databases, tablespaces, schemas, and languages are
647659
<productname>PostgreSQL</productname> extensions.

‎src/backend/catalog/information_schema.sql

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2212,6 +2212,43 @@ CREATE VIEW usage_privileges AS
22122212
WHEREu_grantor.oid=srv.grantor
22132213
ANDgrantee.oid=srv.grantee
22142214
ANDsrv.prtypeIN ('USAGE')
2215+
AND (pg_has_role(u_grantor.oid,'USAGE')
2216+
OR pg_has_role(grantee.oid,'USAGE')
2217+
ORgrantee.rolname='PUBLIC')
2218+
2219+
UNION ALL
2220+
2221+
/* sequences*/
2222+
SELECT CAST(u_grantor.rolnameAS sql_identifier)AS grantor,
2223+
CAST(grantee.rolnameAS sql_identifier)AS grantee,
2224+
CAST(current_database()AS sql_identifier)AS object_catalog,
2225+
CAST(n.nspnameAS sql_identifier)AS object_schema,
2226+
CAST(c.relnameAS sql_identifier)AS object_name,
2227+
CAST('SEQUENCE'AS character_data)AS object_type,
2228+
CAST('USAGE'AS character_data)AS privilege_type,
2229+
CAST(
2230+
CASE WHEN
2231+
-- object owner always has grant options
2232+
pg_has_role(grantee.oid,c.relowner,'USAGE')
2233+
ORc.grantable
2234+
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_grantable
2235+
2236+
FROM (
2237+
SELECToid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*FROM pg_class
2238+
)AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
2239+
pg_namespace n,
2240+
pg_authid u_grantor,
2241+
(
2242+
SELECToid, rolnameFROM pg_authid
2243+
UNION ALL
2244+
SELECT0::oid,'PUBLIC'
2245+
)AS grantee (oid, rolname)
2246+
2247+
WHEREc.relnamespace=n.oid
2248+
ANDc.relkind='S'
2249+
ANDc.grantee=grantee.oid
2250+
ANDc.grantor=u_grantor.oid
2251+
ANDc.prtypeIN ('USAGE')
22152252
AND (pg_has_role(u_grantor.oid,'USAGE')
22162253
OR pg_has_role(grantee.oid,'USAGE')
22172254
ORgrantee.rolname='PUBLIC');

0 commit comments

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