0

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?

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
oh no
  • 49
  • 1
  • 9

1 Answers1

1

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?

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • Thank you, but this still gives same result,I just created 2 new users, (to be fresh everything) Then I did `ALTER DEFAULT PRIVILEGES .. ` as you mentioned, for both of them – oh no Nov 15 '20 at 14:47
  • Though, table created by first user, is still not denied for second user – oh no Nov 15 '20 at 14:47
  • What do you mean by "still not denied"? It should be granted, no? – Bergi Nov 15 '20 at 14:52
  • @ohno Did you run the `ALTER DEFAULT PRIVILEGES …` before creating the tables? What do the privileges of the table look like now? – Bergi Nov 15 '20 at 14:52
  • What do you mean by "still not denied"? -- after `ALTER DEFAULT ..` for both users, I logged in with first user and created table `table1`, then I logged with second user and tried: `select count(*) from table1` – oh no Nov 15 '20 at 14:54
  • It shows error: `permission denied for table table1` – oh no Nov 15 '20 at 14:55
  • Of course, it gives result (0) if I try same query with table owner (first user) – oh no Nov 15 '20 at 14:56
  • "Did you run the ALTER DEFAULT PRIVILEGES" Yes, …" before creating the tables?" - Yes. "What do the privileges of the table look like now" There are privileges only for fisr user on this table, no single privilege for second user. I checked using query: – oh no Nov 15 '20 at 15:10
  • `SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee IN('postgres_subuser1', 'postgres_subuser2')` – oh no Nov 15 '20 at 15:10
  • "still not denied" should have read "still denied" or "still not granted" I guess, just a typo in your comment. Hm, that really should have worked. What happens when you try to grant the privileges directly using the `postgres` user, can he even grant them? You said your `postgres` is not a superuser, but notice that "*you can change default privileges only for objects that will be created by yourself or by roles that you are a member of.*" – Bergi Nov 15 '20 at 15:11
  • Yes, "still not granted". I created both users using "postgres" user and I tried run "ALTER DEFAULT PRIVILEGES ... GRANT .. " from "postgres" user too – oh no Nov 15 '20 at 15:19
  • Now I tested the same Thing, on my local DB instance, where "postgres" is SUPERUSER, though exactly same result – oh no Nov 15 '20 at 15:21
  • Thank you very much, for your time and attempt to help – oh no Nov 15 '20 at 15:24