4

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.

5
  • Docs: ALTER DEFAULT PRIVILEGES
    – bma
    Dec 6, 2017 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. Dec 6, 2017 at 15:16
  • 1
    Try ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO my_role;. Any difference?
    – bma
    Dec 6, 2017 at 15:25
  • Keep in mind ALTER DEFAULT PRIVILEGES only affects new tables, not existing ones, so you want to execute it before creating the tables.
    – Randall
    Apr 14, 2020 at 19:09
  • I'm having this same problem.
    – RonJohn
    Dec 18, 2020 at 12:23

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.