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

user_142249 0
Viewed 8831

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?

google-cloud-sql google-cloud-platform postgresql
3 Answers

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.

user_102434740

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.

user_129238800
Related