2

On google cloud, with "postgres" user (which is not superuser), i do:

CREATE ROLE postgres_subuser1 LOGIN PASSWORD 'some_pass';
CREATE ROLE postgres_subuser2 LOGIN PASSWORD 'some_pass';

GRANT postgres TO postgres_subuser1;
GRANT postgres TO postgres_subuser2;

Above part wroks, though then I try to set users access on each other objects:

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;

gives: must be member of role "postgres_subuser1"

How can solve that?

BTW, if try same on local instance, it works without any error, but this error raises on google cloud.

oh no
  • 49
  • 1
  • 9

1 Answers1

5

You need to explicitly grant the postgres user the role. Eg:

GRANT postgres_subuser1 TO postgres;
Michael
  • 2,189
  • 16
  • 23
  • so instead of `GRANT postgres TO postgres_subuser1;` I need `GRANT postgres_subuser1 TO postgres;` ? – oh no Nov 15 '20 at 22:11
  • It depends on what you're trying to achieve, but in order to allow the postgres user to alter the default privileges for the postgres_subuser1 user then yes, otherwise it doesn't have permission to do so (this wouldn't be true if it was a true superuser, but is the case in gcp managed postgres) – Michael Nov 15 '20 at 22:16