How can I grant permissions on tables created in the future to members of a particular role?

Question

I would like to create a role which will be granted to several individual users. This role should have full permissions on all tables, sequences, functions, etc. in a specified schema. When new tables, etc. are created in that schema in the future, I do not want to modify permissions - members of this role should by default have permissions for these newly created objects. How can I do this?

Currently, I am able to grant permissions on existing tables, but if I drop and re-create them I get the error permission denied for relation my_table.

Here is my role definition script:

create role my_role;
grant all on database my_database to my_role;
grant all privileges on schema public to my_role;
grant all privileges on all tables in schema public to my_role;
grant all privileges on all sequences in schema public to my_role;
grant all privileges on all functions in schema public to my_role;

alter default privileges in schema public
    grant all privileges on tables to my_role;
alter default privileges in schema public
    grant all privileges on sequences to my_role;
alter default privileges in schema public
    grant all privileges on functions to my_role;

grant my_role to user1;
grant my_role to user2;

If user1 creates a table, I'd like user2 (and other members of my_role) to have permissions on it, but that is currently not the case.

I am new to Postgres (coming from MySQL) - thanks in advance for any assistance, and my apologies if I am wildly misunderstanding these concepts somehow.

Answer 1

If you want the user2, or userN, who has role my_role to have access to the future tables of user1, you must run the code below under user1 (who creates new tables), because ALTER DEFAULT PRIVILEGES... works only for objects by that user under whom you run ALTER DEFAULT PRIVILEGES...

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO my_role;