How can I change the owner of the public schema in databases created via the Google Console?

Question

In Google's SQL Cloud Postgres service, when I create a database via the Web Console for a PostgreSQL instance, it automatically sets the owner of the database's default "public" schema to be cloudsqladmin. It seems I cannot change the ownership:

mydb=> \dn
    List of schemas
  Name  |     Owner
--------+---------------
 public | cloudsqladmin
(1 row)

mydb=> alter schema public owner to postgres;
ERROR:  must be owner of schema public


mydb=> \du
                                      List of roles
     Role name     |                   Attributes                   |      Member of
-------------------+------------------------------------------------+---------------------
 cloudsqladmin     | Superuser, Create role, Create DB, Replication | {}
 cloudsqlagent     | Create role, Create DB                         | {cloudsqlsuperuser}
 cloudsqlreplica   | Replication                                    | {}
 cloudsqlsuperuser | Create role, Create DB                         | {}
 pg_signal_backend | Cannot login                                   | {}
 postgres          | Create role, Create DB                         | {cloudsqlsuperuser}
 mynewuser         | Create role, Create DB                         | {cloudsqlsuperuser}

I also created a "mynewuser" through the web console, and cannot remove the "mynewuser" from the "cloudsqlsuperuser" group:

mydb=> alter group cloudsqlsuperuser drop user mynewuser;
ERROR:  "cloudsqlsuperuser" can't be altered

If I wanted to create a database with a public schema that only a new user has access to (and owns), should I be doing this outside of the Google web ui? It seems like any databases I create are owned by cloudsqladmin, and any users I create are those "cloudsqlsuperuser" members. If I wanted to restrict permissions for a user, should I create that user normally via psql and bypass the web ui altogether?

Nucleotide

Answer 1

Basically what happens here is that a usual CREATE DATABASE statement seems to create a new database based on the template0 database. This database is owned by cloudsqladmin. A role only Google has access to. When the gcloud or web GUI is used, it executes the following query:

CREATE DATABASE mydb TEMPLATE template1;

For template1 the owner is set to cloudsqlsuperuser a role that gets assigned to the postgres user, and other users created through the GUI.

So if you would like to create a database using sql with the appropriate privileges, just execute the statement above, and your public schema will then be owned by the cloudsqlsuperuser, and can be altered using the default postgres user, or other users created through the web GUI.

Biosphere

Answer 2

From my experience, you seem to have to bypass the web ui / cli tool entirely. When you create a database through the cli tool:

gcloud sql databases create DBNAME --instance INSTANCE

It assigns ownership to cloudsqlsuperuser, same as through the gui from the sounds of it.

When I have created a user specifically through the CLI tool:

gcloud sql users create USER 'host' --instance INSTANCE

Those users get the same permissions as cloudsqlsuperuser. In this case, it is possible to alter the ownership of the database. I had success through psql command connecting as the user I wanted to own the database and running:

ALTER DATABASE database OWNER TO user;

However if the user was created via psql (not glcoud cli), then the permission are not the same and the above failed.

I'd be tempted to create your instance, set the 'postgres' users password through the tool, then psql into the instance from there and do everything you need via sql commands. I think the tool does some things very nicely (as does the UI), but its a pain later on.

If anyone knows better, I'd love to hear how you can work with the default gcloud user.

Pau

Answer 3

Connect to the database mydb by owner user (for exaple, it is mynewuser). If you want to change the public schema owner, first you should make the user postgres owner of your database mydb:

mydb=> ALTER DATABASE mydb OWNER TO postgres;

After that, you can change the public schema owner:

mydb=> ALTER SCHEMA public OWNER TO postgres;

Besides, to remove your mynewuser from the cloudsqlsuperuser group (role) use:

mydb=> REVOKE cloudsqlsuperuser FROM mynewuser;

Note: The default postgres user in Google Cloud Platform's (GCP) Cloud SQL (PostgreSQL) is not a superuser of the instance. Also, all users created from the GCP web UI have cloudsqlsuperuser role by default, and the following attributes (privileges): CREATEROLE, CREATEDB and LOGIN. They don't have the SUPERUSER or REPLICATION attributes.

Stradivari

While, for some reason, you can change the ownership of the db and schema, you cannot change the ownership of tables this way, regardless of whether you are a cloudsqlsuperuser. – Polyzoarium