I have a situation where many users can create database objects across all the available schema.
There is a generic user pg_dump_user used for by all the developers to dump the whole database to their local db whenever required. So this user should have read access to all the database objects.
To achieve this, I have tried setting the default privilege at schema level as well as at the user level.
–Default privileges set for each schema
ALTER DEFAULT PRIVILEGES IN SCHEMA xyz GRANT SELECT ON TABLES to ro_user';
–Default privileges set for each user
ALTER DEFAULT PRIVILEGES FOR ROLE abc_user GRANT SELECT ON TABLES to ro_user' ;
Even though above setup is there, after certain times these are getting vanished as the new objects created are not accessible by ro_user.
Any pointer on this would help, my aim is just to achieve select access for all the objects current and future for a user so as to do pg_dump.
Best Answer
The documentation about
ALTER DEFAULT PRIVILEGES
tells you a possible reason - it is not that clearly described, though.Let's see, what is said:
This means that the default privileges defined by this statement applies only to objects that are created by you (or the role you are a member of). Let's see this in action!
alice
's schemaFirst, we are logged in as
alice
. Then, in a newly created schema, we create a table and grant some rights tobob
:alice
now has all rights on her table, as expected.bob
' table in the same schemaNow, after obtaining access to this schema,
bob
tries to create a table:As you can see, despite creating the table in
alice
's schema where she set the default privileges,bob
's table doesn't have all those permissions. This happens becausealice
is not a member ofbob
.Let's check this membership thing, too, and try to define default privileges by
alice
again, this time for another role:So, some mighty enough user grants her a membership in
charlie
, then she tries again, with success:charlie
's roundThen
charlie
creates a new table:And the privileges:
As you see,
alice
, as a member ofcharlie
, gets her access to this table.To answer your question,
I guess you defined the default privileges for yourself (
alice
in the example), but you developers act as a bunch ofbob
s here, not getting the necessary privileges. One way to get around this (as we do it at work) is to do aevery time before creating a new object in the schema. This should be a role that all developers are a member of (otherwise you'd get an error).
NOTE that
\dp
is apsql
command.