diff options
| -rw-r--r-- | doc/src/sgml/information_schema.sgml | 13 | ||||
| -rw-r--r-- | doc/src/sgml/ref/grant.sgml | 12 | ||||
| -rw-r--r-- | src/backend/catalog/information_schema.sql | 37 |
3 files changed, 59 insertions, 3 deletions
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index d8e42e4f630..df806a3c853 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -3839,7 +3839,7 @@ ORDER BY c.ordinal_position; <row> <entry><literal>object_type</literal></entry> <entry><type>character_data</type></entry> - <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry> + <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal> or <literal>SEQUENCE</literal></entry> </row> <row> @@ -5859,7 +5859,7 @@ ORDER BY c.ordinal_position; <literal>USAGE</literal> privileges granted on various kinds of objects to a currently enabled role or by a currently enabled role. In <productname>PostgreSQL</productname>, this currently applies to - collations, domains, foreign-data wrappers, and foreign servers. There is one + collations, domains, foreign-data wrappers, foreign servers, and sequences. There is one row for each combination of object, grantor, and grantee. </para> @@ -5871,6 +5871,13 @@ ORDER BY c.ordinal_position; object types, however, show real privileges. </para> + <para> + In PostgreSQL, sequences also support <literal>SELECT</literal> + and <literal>UPDATE</literal> privileges in addition to + the <literal>USAGE</literal> privilege. These are nonstandard and therefore + not visible in the information schema. + </para> + <table> <title><literal>usage_privileges</literal> Columns</title> @@ -5918,7 +5925,7 @@ ORDER BY c.ordinal_position; <row> <entry><literal>object_type</literal></entry> <entry><type>character_data</type></entry> - <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry> + <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal> or <literal>SEQUENCE</literal></entry> </row> <row> diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index c5edaed153a..05f98bb6bb5 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -643,6 +643,18 @@ GRANT admins TO joe; </para> <para> + In the SQL standard, sequences only have a <literal>USAGE</literal> + privilege, which controls the use of the <literal>NEXT VALUE FOR</literal> + expression, which is equivalent to the + function <function>nextval</function> in PostgreSQL. The sequence + privileges <literal>SELECT</literal> and <literal>UPDATE</literal> are + PostgreSQL extensions. The application of the + sequence <literal>USAGE</literal> privilege to + the <literal>currval</literal> function is also a PostgreSQL extension (as + is the function itself). + </para> + + <para> Privileges on databases, tablespaces, schemas, and languages are <productname>PostgreSQL</productname> extensions. </para> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index f591f64caf7..c4f8f0f4eaa 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -2214,6 +2214,43 @@ CREATE VIEW usage_privileges AS AND srv.prtype IN ('USAGE') AND (pg_has_role(u_grantor.oid, 'USAGE') OR pg_has_role(grantee.oid, 'USAGE') + OR grantee.rolname = 'PUBLIC') + + UNION ALL + + /* sequences */ + SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor, + CAST(grantee.rolname AS sql_identifier) AS grantee, + CAST(current_database() AS sql_identifier) AS object_catalog, + CAST(n.nspname AS sql_identifier) AS object_schema, + CAST(c.relname AS sql_identifier) AS object_name, + CAST('SEQUENCE' AS character_data) AS object_type, + CAST('USAGE' AS character_data) AS privilege_type, + CAST( + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, c.relowner, 'USAGE') + OR c.grantable + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable + + FROM ( + SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class + ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable), + pg_namespace n, + pg_authid u_grantor, + ( + SELECT oid, rolname FROM pg_authid + UNION ALL + SELECT 0::oid, 'PUBLIC' + ) AS grantee (oid, rolname) + + WHERE c.relnamespace = n.oid + AND c.relkind = 'S' + AND c.grantee = grantee.oid + AND c.grantor = u_grantor.oid + AND c.prtype IN ('USAGE') + AND (pg_has_role(u_grantor.oid, 'USAGE') + OR pg_has_role(grantee.oid, 'USAGE') OR grantee.rolname = 'PUBLIC'); GRANT SELECT ON usage_privileges TO PUBLIC; |