Postgres: Grant access to future tables to various users

Question

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!

Answer 1

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.