Reg: Default Privileges on PostgreSQL Schemas

Question

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.

• Erwin Brandstetter: pg_dumb_user is a nice typo for pg_dump_user. Just in the question or is it part of the problem?
• Aneesh Mon N: It's just a name mentioned for the thread. Real name is different.

Answer 1

The documentation about ALTER DEFAULT PRIVILEGES tells you a possible reason - it is not that clearly described, though.

Let's see, what is said:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

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 schema

First, we are logged in as alice. Then, in a newly created schema, we create a table and grant some rights to bob:

SELECT current_user;
 current_user 
──────────────
 alice
SHOW search_path ;
 search_path 
──────────────
 test, public
CREATE SCHEMA alicetest;
ALTER DEFAULT PRIVILEGES 
 FOR ROLE alice 
 IN SCHEMA alicetest 
 GRANT ALL ON TABLES TO alice;
GRANT SELECT ON alicetest.a TO bob; 
-- this I do only for showing the privileges - 
-- the owner has by default ALL and is not shown by \dp
\dp alicetest.a
 Access privileges
 Schema │ Name │ Type │ Access privileges │ Column access privileges 
───────────┼──────┼───────┼─────────────────────┼──────────────────────────
 alicetest │ a │ table │ alice=arwdDxt/alice↵│ 
 │ │ │ bob=r/alice │ 

alice now has all rights on her table, as expected.

bob' table in the same schema

Now, after obtaining access to this schema, bob tries to create a table:

SELECT current_user;
 current_user 
──────────────
 bob
CREATE TABLE alicetest.b (id integer);
GRANT SELECT ON alicetest.b TO alice;
\dp alicetest.b
 Access privileges
 Schema │ Name │ Type │ Access privileges │ Column access privileges 
───────────┼──────┼───────┼───────────────────┼──────────────────────────
 alicetest │ b │ table │ bob=arwdDxt/bob ↵│ 
 │ │ │ alice=r/bob │ 

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 because alice is not a member of bob.

Let's check this membership thing, too, and try to define default privileges by alice again, this time for another role:

ALTER DEFAULT PRIVILEGES 
 FOR ROLE charlie 
 IN SCHEMA alicetest 
 GRANT ALL ON TABLES TO alice;
ERROR: must be member of role "charlie"

So, some mighty enough user grants her a membership in charlie, then she tries again, with success:

ALTER DEFAULT PRIVILEGES 
 FOR ROLE charlie 
 IN SCHEMA alicetest 
 GRANT ALL ON TABLES TO alice;
ALTER DEFAULT PRIVILEGES

charlie's round

Then charlie creates a new table:

CREATE TABLE alicetest.c (id integer);

And the privileges:

\dp alicetest.c
 Access privileges
 Schema │ Name │ Type │ Access privileges │ Column access privileges 
───────────┼──────┼───────┼─────────────────────────┼──────────────────────────
 alicetest │ c │ table │ charlie=arwdDxt/charlie↵│ 
 │ │ │ alice=arwdDxt/charlie │ 

As you see, alice, as a member of charlie, 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 of bobs here, not getting the necessary privileges. One way to get around this (as we do it at work) is to do a

SET ROLE TO schema_owner;

every 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 a psql command.

• Aneesh Mon N: thanks for trying this out for me. I have done the same thing that you have tried out in second part of the answer by setting "--Default privileges set for each user". Or did I miss to understand this?
• András Váczi: I cannot tell for sure without seeing which role is a member of which other role. If dev1, dev2 and devN are all a member of table_creator, and the default privileges are set for table_creator and the tables are all created as table_creator, you'll get the desired effect.
• Aneesh Mon N: I don't think I can add all the users under one role and set read access for that role as some data are meant only for some users.
• András Váczi: how do you expect to get the same dump for everyone then?
• Aneesh Mon N: @deszo, in actual all developers has access across the schemas and we refresh our dev every week from prod. This setup has to be done at prod. But this makes sense to create an umbrella role for all the users in prod and attach that to dump user in dev. Let me give that a try

Answer 2

I don't think it is possible to do what you want in a single command.

---

Your First Command

--Default privileges set for each schema

ALTER DEFAULT PRIVILEGES IN SCHEMA xyz GRANT SELECT ON TABLES to ro_user';

This doesn't work because it only sets this default for the user who issues the command. And as you said, "many users can create database objects across all the available schema." Hence, each one would have to set this default (on each schema). Or a superuser could set it for each of them as you tried next.

---

Your Second Command

--Default privileges set for each user

ALTER DEFAULT PRIVILEGES FOR ROLE abc_user GRANT SELECT ON TABLES to ro_user' 

;

This should work, but it might appear as if it is decaying over time if you are adding new users (and not setting this). Are you sure that default permissions stopped working for new tables created (and owned) by a user where you set this?

Did you run \ddp in psql to see default privileges? Did they have them set originally and then not set later or was it that later (even with it set) it didn't go grant the privileges to ro_user?

---

What I wish worked (and think you want)

-- set default privileges for a whole team to deliver a specific schema to another whole team

ALTER DEFAULT PRIVILEGES FOR ROLE engineering IN SCHEMA GRANT SELECT ON TABLES to analytics;

The key concept here is that I want every user in the engineering team to inherit these default privileges so that every table one of them creates is readable by every member of reporting. But defaults are not inherited like this. This surprised me because the permissions are inherited by every member of reporting. So, the only way is as you did above, setting the defaults for every user that creates tables.

I only mention this in case that is why the default privileges appeared to decay - i.e. if you assumed (like me) that the defaults would be inherited by members of the table-creating role.