0

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?

1 Answer 1

2

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.

7
  • Does the ALTER DEFAULT PRIVILEGES FOR ROLE ... ON TABLES ... also works for the new tables user1 create in the future but not just for the current tables?
    – Miantian
    Commented Sep 4, 2021 at 3:48
  • It only works for future tables, existing ones are not affected at all. Commented Sep 6, 2021 at 5:27
  • Thank you for your reply! Then for the existing ones, is there a way to set? Or may I consider that, after we create a new schema, run the above grant command immediately(when 0 tables), for all the tables create later will work well between the two roles relation?
    – Miantian
    Commented Sep 6, 2021 at 6:42
  • GRANT ... ON ALL TABLES IN SCHEMA ... Commented Sep 6, 2021 at 7:18
  • Great! I have set GRANT SELECT, INSERT, DELETE, UPDATE, TRUNCATE ON ALL TABLES IN SCHEMA main TO readwrite;. Then ALTER DEFAULT PRIVILEGES IN SCHEMA main FOR ROLE user1 GRANT ... ON TABLES to readwrite. May I run GRANT SELECT, INSERT, DELETE, UPDATE, TRUNCATE FOR ROLE user1 ON ALL TABLES IN SCHEMA main TO readwrite;(it includes FOR ROLE user1) for all?
    – Miantian
    Commented Sep 6, 2021 at 8:34

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.