All we need is an easy explanation of the problem, so here it is.
In PostgreSQL, created these 2 roles and grant different permissions to them.
create user user1 with login;
create role operator;
grant operator to user1;
create user user2 with login;
create role readwrite;
grant readwrite to user2;
When created a table by user1, such as table1, user2 can’t use this table. It got permission denied.
Is it the right feature for PostgreSQL? If it is, how to make the table also can be used for user2?
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
user1 has to grant user2 permissions (via the role):
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON newtable TO readwrite;
You can also define default privileges to have that happen automatically:
ALTER DEFAULT PRIVILEGES FOR ROLE user1
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO readwrite;
Now all tables created by user1 will automatically have the right permissions.
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