Upgrade user to superuser in Postgres in Google Cloud

Question

How do I create a user with the superuser role in Postgres server with Google Cloud console?

When I create databases in Google Cloud, the default user is a non-superuser. From this role, it is not possible to upgrade.

Rhee

Answer 1

no way:

https://cloud.google.com/sql/docs/postgres/users

The postgres user is part of the cloudsqlsuperuser role, and has the following attributes (privileges): CREATEROLE, CREATEDB, and LOGIN. It does not have the SUPERUSER or REPLICATION attributes.

Cyclopedia

Thanks. I read it, but I hoped somehow there was a way to corner it, maybe in powerShell connected to google cloud. :-) – Rhee

Really?? No way? – Reaper

An opdate to this: I changed from Google Cloud Postgres to a VS and installed Postgres on that one instead. It works like a charm. – Rhee

Answer 2

Here's my own script:

GRANT cloudsqlimportexport, cloudsqlsuperuser, pg_read_all_data, pg_write_all_data, postgres TO myawsumuser WITH ADMIN OPTION;

GRANT ALL PRIVILEGES ON DATABASE mytestdb1 TO myawsumuser WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON DATABASE postgres TO myawsumuser WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myawsumuser with grant option;
GRANT USAGE ON SCHEMA public TO myawsumuser with grant option;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myawsumuser with admin option;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myawsumuser with grant option;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO myawsumuser with grant option;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO myawsumuser with grant option;

Good links: https://cloud.google.com/sql/docs/postgres/users

How to grant all privileges on views to arbitrary user

Garald

does not work also this command, fails: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myawsumuser with admin option; in pg 14 – Resnick