CODEWITHSUNDEEP
postgresqlgoogle-cloud-platformpostgresql-12
oh no
oh no

Reputation: 59

Create multiple users, to have all privileges on each others relations/objects

I'm logged with postgres user (BTW, this is not superuser, DB is hosted on google cloud).

I need that create multiple users, and all that users to be have access on each other's objects

So, with postgres user I created 2 users like:

CREATE USER postgres_subuser1  PASSWORD 'some_password';
GRANT postgres TO postgres_subuser1;

CREATE USER postgres_subuser2  PASSWORD 'some_password';
GRANT postgres TO postgres_subuser2;

Then I logged with "postgres_subuser1" and crated table table1

Then I logged with "postgres_subuser2", and tried insert into table1, but error

permission denied for table table1 appears

This even happens when I try to insert with "postgres" user, only table owner "postgres_subuser1" can insert into table.

Question: how can I manage like so, that all users created by current user, have all privileges on each others objects?

Upvotes: 0

Views: 1840

Answers (1)

Bergi
Bergi

Reputation: 664969

You can alter the default privileges for objects created by these users to grant each other access:

ALTER DEFAULT PRIVILEGES FOR ROLE postgres_subuser1 GRANT ALL PRIVILEGES ON TABLES TO postgres_subuser2;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres_subuser2 GRANT ALL PRIVILEGES ON TABLES TO postgres_subuser1;

Also I think you have the GRANT postgres TO postgres_subuser2; backwards, surely you meant giving the postgres user the permission to change into the postgres_subuser2 role, not the other way round?

Upvotes: 1

Related Questions