1

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.

  • Docs: ALTER DEFAULT PRIVILEGES – bma Dec 6 '17 at 15:10
  • 1
    I've reviewed this page but am still stuck. I modified the question to clarify which user is creating tables and which user seems to be missing permissions. – speedyturkey Dec 6 '17 at 15:16
  • 1
    Try ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO my_role;. Any difference? – bma Dec 6 '17 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.