CODEWITHSUNDEEP
sqlpostgresqlpermissionspostgresql-9.3database-permissions
animalito
animalito

Reputation: 382

Postgresql permissions keep failing

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.

Upvotes: 4

Views: 248

Answers (1)

Ramfjord
Ramfjord

Reputation: 939

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.

Upvotes: 1

Related Questions