Postgresql permissions keep failing

Question

I am running PostgreSQL 9.3.2 on Linux.

I have a group role 'data_scientist' and permissions to a particular (already populated) schema like so

grant usage on schema schemaname to data_scientist;

grant select, references, trigger
    on all tables in schema schemaname
    to data_scientist;

This fixes the problem of data_scientist using past tables. For future tables I added

alter default privileges in schema schemaname
grant select, references on tables
   to data_scientist;

Still, whenever a new table is added, other data_scientist's permissions fail on the new tables.

http://stackoverflow.com/questions/10352695/grant-all-on-a-specific-schema-in-the-db-to-a-group-role-in-postgresql seems to answer this by specifying DEFAULT PRIVILEGES to specify access to new objects created in a schema — mc110, Jun 17 '14 at 20:42
In fact that is the second part I add. Still, it does not work. — animalito, Jun 17 '14 at 23:40
@mc110 it is important to keep FOR in mind as well since default privileges are per user/role and it default to a current user who issues the statement. — mlt, Jun 18 '14 at 18:54
@mlt I'm not sure I understand what you mean with the FOR, could you exemplify? — animalito, Jun 18 '14 at 21:24
If you do **not** have a role *data_scientist* when connected to DB while issuing *alter* statements, then you **must** use *for data_scientist* role, otherwise default privileges would be applied for new tables created only by your user you used to issue *alter* statement. [This](http://stackoverflow.com/questions/21513996/alter-default-privileges-for-a-group-role-in-postgresql) also might be relevant. That is make sure `set role` was used if *data_scientist* as a group role before creating a table. I believe that this Q is a duplicate of the one I linked. — mlt, Jun 18 '14 at 22:09

score 1 · Answer 1 · answered Nov 18 '14 at 21:48

By default, ALTER DEFAULT PRIVILEGES only applies to the role that ran the command. Suppose we have 2 users: ramfjord and animalito. If I (ramfjord) run

ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO public; CREATE TABLE ramfjord_table;

Then animalito will be able to see it. If animalito runs

CREATE TABLE animalito_table

Then ramfjord won't be able to see it, because ramfjord's default privileges don't apply. See default privileges and their owners with \ddp

Because of this, we've stopped using default privileges at my company, and started using explicit GRANT's. To remove default privs, you have to run

ALTER DEFAULT PRIVILEGES FOR ROLE <owner> REVOKE...

The owner, schema, relation type and privileges have to match those listed in \ddp for this command to do anything. Do not just delete everything from the internal table that stores default privileges... trust me.

Postgresql permissions keep failing

1 Answers1