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.
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 🙂