Postgres Role access privileges

All we need is an easy explanation of the problem, so here it is.

I am trying to assign select privs via a role for current and future tables, and i cant see to figure this out. Please advise.

create role dev_role;
grant usage on schema address to dev_role
grant select on all tables in schema address to dev_role 
alter default privileges in schema address grant select on tables to dev_role; 

grant dev_role to test1;

Now, Test2 user creates a table in address schema that has grant all privileges.

\c dev test2
create table address.t1(t integer);

\c dev test1
select * from address_match.t1;
ERROR:  permission denied for table t1

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is a common misunderstanding. ALTER DEFAULT PRIVILEGES does not have the ability to define default privileges for all users. These rights apply only to objects that will be created by the user specified in the FOR ROLE clause.

If FOR ROLE is omitted, the current role is assumed.

If the tables in the database will be created by the user test2, then you need to connect to this database and execute

 alter default privileges 
   for role test2 
   in schema address 
   grant select on tables to dev_role;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply