16

To query GRANTS granted to a table I can use a query like:

SELECT grantee, privilege_type 
FROM information_schema.role_table_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?

Community
  • 1
  • 1
markus
  • 6,258
  • 13
  • 41
  • 68

1 Answers1

17

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Thanks for your answer, but that's not really what I need – markus Sep 07 '11 at 23:13
  • What you need might not exist. The information_schema tables wer defined in the earliest SQL standards. But sequences weren't part of SQL standards until 2003. Most dbms that support sequences had that support before the standards were published, and most of them don't fully conform to either standard behavior or syntax. – Mike Sherrill 'Cat Recall' Sep 08 '11 at 15:23
  • I just added some info that makes what you want seem even more unlikely. You can find late drafts of the SQL 2003 standard online if you want to look at them yourself. – Mike Sherrill 'Cat Recall' Sep 08 '11 at 15:41
  • 2
    In addition to what explained @MikeSherrill'CatRecall', using the query on `pg_class` he posted, you can deduce the privileges granted on sequences using the `relacl` column contents. First set (preceding first comma) will be the sequence owner privileges: `owner=rwU/owner` where r will be read permission (`currval`), w will be write (`nextval` and `setval`) and U will be Usage (`currval` and `nextval`). As you would generally grant Usage and select privileges only, other acl would look like `grantee=rU/owner` – mimidatabase Aug 18 '21 at 07:33
  • 1
    I have found this on some other thread and it worked for me: `select * from information_schema.role_usage_grants` – enesaltinok Dec 06 '21 at 13:08