3

I am trying to grant select access to sequences from one user/role to another. There are no errors when I run the command but once run, the second role is unable to view the sequences. I have run the exact same command on several other services/database instances which succeeded, this is only misbehaving one.

I have run both:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA schema_name TO new_role;

As per the recommendation here:

And as I mention above, this has been successful on other database schema on different machines. I have also tried individually:

GRANT SELECT ON SEQUENCE some_id_sequence TO new_role;

and

GRANT SELECT ON SEQUENCE public.some_id_sequence TO new_role;

This also has no effect. When logged in from new_role I see:

select * from information_schema.role_usage_grants ; 
...
(0 rows)

Similar results (or lack thereof) when running \ds.

I can see from the previous role that the sequence should be "grantable" (whatever that means, I can't find any documentation on it)

live@live ~ => select * from information_schema.role_usage_grants limit 1;
┌──────────┬──────────┬────────────────┬───────────────┬──────────────┬─────────────┬────────────────┬──────────────┐
│ grantor  │ grantee  │ object_catalog │ object_schema │  object_name │ object_type │ privilege_type │ is_grantable │
├──────────┼──────────┼────────────────┼───────────────┼──────────────┼─────────────┼────────────────┼──────────────┤
│ old_role │ old_role │ old_role       │ public        │ some_id_seq  │ SEQUENCE    │ USAGE          │ YES          │
└──────────┴──────────┴────────────────┴───────────────┴──────────────┴─────────────┴────────────────┴──────────────┘
(1 row) 

So I don't really know where to look at this point. The old role appears to have the ability to grant select to other roles, and doesn't error when attempting to run the command, however the new role still has no access.

The results of \dn+

\dn+
                  List of schemas
┌───────────┬──────────┬────────────────────────┬─────────────┐
│  Name     │ Owner    │ Access privileges      │ Description │
├───────────┼──────────┼────────────────────────┼─────────────┤
│ new_role  │ old_role │ old_role=UC/old_role  ↵│             │
│           │          │ new_role=U/old_role    │             │
│ public    │ old_role │                        │             │
└───────────┴──────────┴────────────────────────┴─────────────┘
(2 rows)

\du+ new_role
                   List of roles
┌───────────┬────────────┬───────────┬─────────────┐
│ Role name │ Attributes │ Member of │ Description │
├───────────┼────────────┼───────────┼─────────────┤
│ new_role  │            │ {}        │             │
└───────────┴────────────┴───────────┴─────────────┘

The results from \dp

\dp some_id_sequence
                                     Access privileges
┌────────┬──────────────────┬──────────┬───────────────────────┬───────────────────┬──────────┐
│ Schema │       Name       │   Type   │ Access privileges     │ Column privileges │ Policies │
├────────┼──────────────────┼──────────┼───────────────────────┼───────────────────┼──────────┤
│ public │ some_id_sequence │ sequence │ old_role=rwU/old_role │                   │          │
│        │                  │          │ new_role=r/old_role   │                   │          │
└────────┴──────────────────┴──────────┴───────────────────────┴───────────────────┴──────────┘

Question: How do I establish what is preventing the sequence grants from being applied?

  • A shot in the dark: did you disable autocommit in psql? If yes, you need to commit those GRANTs – a_horse_with_no_name Jan 21 at 11:14
  • A fair shot :) But no, automcommit is enabled. I tried committing to be sure and it complains that no transaction is in progress. – Mitch Kent Jan 21 at 11:20
  • One more question: it is some_id_sequence in one example and some_id_seq in another. Are you sure you don't have two similarly named sequences? – dezso Jan 21 at 13:00
  • 1
    It looks like the new role has no privilege on the schema public which also looks like it has been recreated as a normal non-public schema. – Daniel Vérité Jan 21 at 17:40
  • 1
    To avoid more confusion, I took the liberty to straighten out the various dummy names meant to be the same. Please check whether the update is correct. – Erwin Brandstetter Jan 21 at 23:15
2

GRANT'ing SELECT (or USAGE) on the sequence is not sufficient if it's contained in a schema for which the user has no permission. I believe that's the case because your schema named public is not public. If it was, it would have permissions that would look like that:

test=> \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 

as opposed to the lack of access privileges shown in the question. This is also consistent with the fact that the same commands work in you other instances: presumably the schema public of these other databases is the original, not a dropped/recreated-differently version or with its permissions removed.

As possible solutions, consider doing, as the owner of the schema:

 GRANT ALL ON SCHEMA public TO public;

or the more limited

 GRANT ALL ON SCHEMA public TO new_role;

or the even more limited

 GRANT USAGE ON SCHEMA public TO new_role;
  • Perfect - a simple explanation and exactly finding the problem - I started with the most limited possible solution and indeed that made the difference. I have no idea of the who/what/when/where/why someone may have altered the public schema but this is not-coincidentally the oldest of our services :) Thank you! – Mitch Kent Jan 22 at 16:57
2

The manual:

The view role_usage_grants identifies USAGE privileges granted on various kinds of objects where the grantor or grantee is a currently enabled role.

Nothing additional will show up there after GRANT SELECT .... Try instead:

GRANT USAGE ON SEQUENCE public.some_id_sequence TO new_role;

.. if that's, indeed, what you want. The SELECT privilege has limited use for a SEQUENCE, it allows currval() and lastval().
Typically, you want the USAGE privilege which also allows the crucial nextval() in addition to the above. To work with a serial column, for instance. See:

So why do you see this confusing line in your test after granting SELECT?

live@live ~ => select * from information_schema.role_usage_grants limit 1;
┌──────────┬──────────┬────────────────┬───────────────┬──────────────┬─────────────┬────────────────┬──────────────┐
│ grantor  │ grantee  │ object_catalog │ object_schema │  object_name │ object_type │ privilege_type │ is_grantable │
├──────────┼──────────┼────────────────┼───────────────┼──────────────┼─────────────┼────────────────┼──────────────┤
│ old_role │ old_role │ old_role       │ public        │ some_id_seq  │ SEQUENCE    │ USAGE          │ YES          │
└──────────┴──────────┴────────────────┴───────────────┴──────────────┴─────────────┴────────────────┴──────────────┘

The owner of an object has all privileges on it automatically. Hence pg_class.relacl starts out as NULL - signifying default privileges. Once that owner grants privileges to another role, his own privilege is entered explicitly in addition - thus popping up in the information schema views as well. Or, quoting the source:

If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, {miriam=arwdDxt/miriam}) and then modify them per the specified request.

Generally, to debug non-trivial stuff, I'd rather look at pg_catalog tables, which are the primary source of truth. pg_class.relacl in this particular case:

SELECT relnamespace::regnamespace, relname, relacl
FROM   pg_class
WHERE  relname = 'some_id_sequence';

Or \z (short for \dp) in psql:

\z some_id_sequence

Assuming old_role is the owner, you should see:

{old_role=rwU/old_role,new_role=r/old_role} -- after GRANT SELECT...
{old_role=rwU/old_role,new_role=U/old_role} -- after GRANT USAGE ...
{old_role=rwU/old_role,new_role=rU/old_role} -- after granting both

And NULL (empty in psql) before granting anything.

Your added output shows that new_role actually has the SELECT privilege, you have just been looking up the wrong view. Seems like new_role is lacking privileges on the public schema - which are normally given to PUBLIC by default.


Aside: IDENTITY columns in Postgres 11 or later avoid the fuss. Those use sequences just as well, internally, but implicitly owned by the IDENTITY column and with the appropriate privileges automatically. Then you need no separate grants for sequences. See:

  • Hi @Erwin and many thanks for your detailed response. I am afraid that the assumption I want usage is incorrect - the purpose of my changes is to allow a DB monitoring tool access to our schemas and I want to create a reduced privilege role for this. Select is only required so that the tool can check for integer overflows, etc. I'm on my way into the office now and will include any results from \dp and the pg_class table when I get there. Thanks again. – Mitch Kent Jan 22 at 9:10
  • Included the results :) – Mitch Kent Jan 22 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.