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
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?
    – Melkij
    Aug 12, 2021 at 17:14

1 Answer 1

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;
1
  • thank you so much. you saved me valuable time. thanks a ton!
    – nick
    Aug 12, 2021 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.