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?
some_id_sequence
in one example andsome_id_seq
in another. Are you sure you don't have two similarly named sequences? – dezso Jan 21 '19 at 13:00public
which also looks like it has been recreated as a normal non-public schema. – Daniel Vérité Jan 21 '19 at 17:40