CODEWITHSUNDEEP
databasepostgresqlpermissionsprivilegessql-grant
xof
xof

Reputation: 23

Postgres: Grant access to future tables to various users

There are several threads already on this topic. Yet I'm unable to get it done and keep getting "permission denied" errors. I must be doing something very wrong.

My goal: on my postgres-database "mydatabase" I would like to have

  1. a large group of users belonging to role "group_x"
  2. each member of role "group_x" should have access to a certain schema "schema_x"
  3. within that schema, all group-members should be able to create new tables and edit each others (future) tables.

How I'm trying it (roughly): 

psql -h /var/run/postgresql -p5433 -U postgres

create role group_x;

create role user_a LOGIN INHERIT PASSWORD <password>;
create role user_b LOGIN INHERIT PASSWORD <password>;

grant group_x to user_a;
grant group_x to user_b;

\connect mydatabase;

grant all on schema schema_x to group_x;
grant all on all tables in schema schema_x to group_x;
grant all on all sequences in schema schema_x to group_x;
grant all on all functions in schema schema_x to group_x;

set role group_x;

alter default privileges for role group_x in schema schema_x
    grant all on tables to group_x;
alter default privileges for role group_x in schema schema_x
    grant all on sequences to group_x;
alter default privileges for role group_x in schema schema_x
    grant all on functions to group_x;

Thanks for pointing out my errors!

Upvotes: 2

Views: 3033

Answers (2)

CommonSenseCode
CommonSenseCode

Reputation: 25359

You can create an event trigger that is triggered on create table to always add custom privileges example:

CREATE OR REPLACE FUNCTION grant_CRUD_permissions_on_future_tables()
RETURNS event_trigger AS $$
BEGIN
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES  IN SCHEMA public TO {user_example};
    grant all on all sequences in schema public to {user_example};
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER grant_CRUD_permissions_trigger
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
EXECUTE PROCEDURE grant_CRUD_permissions_on_future_tables();

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246043

If you ALTER DEFAULT PRIVILEGES FOR ROLE group_x, that means that the privileges are only granted on future objects created by user group_x.

So you need to specify the user that creates tables in the FOR ROLE clause.

Upvotes: 1

Related Questions