Query GRANTS granted to a sequence in postgres
To query GRANTS granted to a table I can use a query like:
SELECT grantee, privilege_type
FROM information_schema.role_t开发者_运维技巧able_grants
WHERE table_name='mytable'
(see my old question here: Query grants for a table in postgres)
But how I query the GRANTS grated to a sequence?
I've looked through the source code, and I can't find any place that exposes the ACL for sequences through the information_schema tables. (I could have missed something, though.)
PostgreSQL does expose the ACL for sequences in the system catalog pg_class.
SELECT relname, relacl
FROM pg_class
WHERE relkind = 'S'
AND relacl is not null
AND relnamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
);
As far as the information_schema and standard SQL sequences go, PostgreSQL doesn't support them.
select feature_name, is_supported
from information_schema.sql_features
where feature_name = 'Sequence generator support';
PostgreSQL is nonconforming in that respect, because it exposes information_schema.sequences without returning "YES" for 'Sequence generator support'. (That's an observation, not a criticism of PostgreSQL.)
But, having said all that, I couldn't find anything in the 2003 SQL standard that exposed those privileges, either. It's easy to find PRIVILEGE_TYPE in the definition of the ROLE_TABLE_GRANTS view, but there's nothing like that for sequences in the standard, as far as I can tell.
GRANT USAGE, SELECT ON SEQUENCE sequence_name TO user_role_name;
as seen here: https://www.postgresql.org/docs/current/sql-grant.html
精彩评论