Postgres Role access privileges

Question

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

• Melkij: A few seconds after answering: create table address.t1 but from address_match.t1 - is the error in the question? Or actually different schemas?

Answer 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;

• nick: thank you so much. you saved me valuable time. thanks a ton!