1

I want all developers to be able to work with each other's code. When one person creates a table, others should be able to query it directly without permissions needing to be granted.

I thought the way to accomplish this was to create a role role_developers, have all developers be members of that role, and then do this:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO role_developer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO role_developer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO role_developer;

My expectation is that setting those default privileges as above means that every new instance of such objects in the public schema will be assigned those privileges to everyone who's a member of that role.

However, even after doing this (logged in as my personal user account, which is also a member of role_developer), I'm finding that after I create a new table, my colleagues aren't able to SELECT from it.

So obviously I'm missing something. I'm new to pgsql although I'm an experienced SQL Server developer, which is obviously clouding my perspective.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Chris Wuestefeld
  • 3,266
  • 2
  • 23
  • 23
  • 1
    I would have guessed what you did was correct and am surprised to hear it didn't work. As a work-around, whenever you all create a new object, can you just assign its owner to role_developer? `alter table my_new_table owner to role_Developer` – Hambone Dec 21 '18 at 07:07

1 Answers1

1

The ALTER DEFAULT PRIVILEGES statements you show will only affect tables created by the user who ran these statements.

You'll have to run

ALTER DEFAULT PRIVILEGES FOR ROLE someuser ....

for all the users that can possible create such objects.

This is annoying, and it still doesn't allow other users to drop or alter tables that I created, because that is restricted to the table owner.

It might be a better setup to have a role created like this:

CREATE ROLE object_owner NOINHERIT;

and grant that role to all your users.

Then whenever somebody wants to create, alter or drop an object, they first have to do

SET ROLE object_owner;

Then you only need to run one set of ALTER DEFAULT PRIVILEGES commands for that role.

You can enforce this by granting CREATE on the schemas only to object_owner.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263