Interesting -- Sharing postgres tables between 2 r/w users automatically

user_5244865 0
Viewed 54

I am new to postgres and am seeing an ownership/accessibility issue where we have 2 users, master and user_rw in the db. The tables table_abc => created owned by master table_xyz => created and owned by user_rw

The issue: Any new tables created by master are not accessible (select *, write etc) to user_rw and vice versa. I dont want to explicitly grant permissions for new tables to both users every time new tables are created.

I want the following requirements:

  • All tables created by user_rw should be accessible and read/writable by user master
  • All tables created by user master should be accessible and read/writable by user user_rw
  • Any new tables created in the db hence forth should automatically be accessible (read/writable) by both users

I only have public schema in my postgres. While creating the user_rw I had done the following

GRANT CONNECT ON DATABASE database_test TO user_rw;
GRANT USAGE ON SCHEMA public TO user_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_rw;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO user_rw;

What am I missing?

postgresql sql
2 Answers

You need to use alter default privileges to set the privileges for objects that will be created in the future.

As both users should be able to read/write tables created by the other, I would recommend to create a role to which all privileges are granted. Then grant that role to the two users:

create role table_access
  role user_rw, master; -- immediately grants this new role to the two existing users

alter default privileges 
  for role user_rw, master --<< tables created by one of those users
  in schema public
  grant all privileges on tables to table_access;
user_3303150

You can probably use roles for this.

  1. Create a role for both users

  2. Assign this role to both users

  3. When the user connects it will have to change to this role explicitly (SET role)

  4. Users will then have to do all operations on these tables (create, select, etc) under this role.

user_23257020
Related