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.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO my_role;
. Any difference?ALTER DEFAULT PRIVILEGES
only affects new tables, not existing ones, so you want to execute it before creating the tables.