0

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
Improve this question
1
  • A few seconds after answering: create table address.t1 but from address_match.t1 - is the error in the question? Or actually different schemas? Commented Aug 12, 2021 at 17:14

1 Answer 1

Reset to default
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;
Improve this answer
1
  • thank you so much. you saved me valuable time. thanks a ton! Commented Aug 12, 2021 at 17:41

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.