The default postgres user in Google Cloud Platform's (GCP) Cloud SQL (PostgreSQL) is not a superuser of the instance (GCP docs link):
When you create a new Cloud SQL for PostgreSQL instance... 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.
The superuser attribute grants a user the ability to bypass all permissions checks and, accordingly, it can go anywhere in the database instance to read data from any database (PostgreSQL docs link):
A database superuser bypasses all permission checks, except the right to log in.
In a typical PostgreSQL deployment outside GCP, the postgres user is indeed a superuser. This deviation in Cloud SQL breaks with the normal convention expected of Postgres that the postgres user can always access and mutate any object in the cluster by virtue of those superuser rights.
Without this privilege, you will need to be more careful about the roles used when creating database objects and the role used when attempting to access them, either interactively at the Postgres shell or programmatically in your application.
I assume you are using a separate, dedicated user for your k8s deployment to connect to the database, rather than the postgres user. By default, that user's role will own the objects created by the schema population operation. Per the GRANT documentation, this owner will by default have the full suite of privileges over the object:
If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner...
Alternatives
(Recommended) Create a dedicated role which can be shared between the postgres user and whatever other user(s) you log into the database with to populate its schema.
Configure the operation which populates the database objects in the schema creation operation to set its role prior to creating the objects to this shared role, such that all users have the ability to access, manage and view these objects. By default, new roles have the INHERIT attribute set, which means attempts to access the objects created by the role will succeed for members of the role in future.
For example, you could use the cloudsqlsuperuser role for this purpose, of which all users created in the console and the postgres built-in are automatically members. However, I would recommend creating a custom role for the purpose:
CREATE ROLE symfonyapp;
GRANT symfonyapp TO postgres;
GRANT symfonyapp TO <your_k8s_application_user>;
Later, when creating database objects, ensure you assume the symfonyapp role before doing so. At the console, run:
SET ROLE symfonyapp;
when logged in as a user to which the symfonyapp role has been granted. You should review the documentation of the libraries you are using to ascertain how to set the role when connecting to the database programmatically.
Create a role as above, and assign it to the postgres user. Additionally, give the role the LOGIN attribute and set a password, which permits you to log into the database instance using the role name and a password directly. In this case, the postgres user inherits privileges of the role (such as objects it owns), and the ability to log in directly obviates the need to call SET ROLE on first connection.
For objects already created, you can adjust their ownership to your custom role using the command ALTER <TYPE> <name> OWNER TO symfonyapp; for example:
ALTER TABLE mytable OWNER TO symfonyapp;
It is not possible to directly grant the SUPERUSER role attribute to the postgres user, as you do not have access to a user with SUPERUSER privileges to do so! (Only superusers can make other users superusers.) The Google Cloud SQL for Postgres documentation notes a specific exclusion of support for any features which require superuser privileges, so this route is not available to you. The only superuser is the cloudsqladmin user, created by default and used by Google to perform instance-level administrative operations on your behalf; you could reset the password and login as this account to grant superuser privileges, but I do not recommend doing so as this is likely to break other managed functions.
Worked example
The default set of roles present in a newly-created database cluster is as follows:
List of roles
Role name | Attributes | Member of
-------------------+------------------------------------------------------------+---------------------
cloudsqladmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cloudsqlagent | Create role, Create DB | {cloudsqlsuperuser}
cloudsqlreplica | Replication | {}
cloudsqlsuperuser | Create role, Create DB | {}
postgres | Create role, Create DB | {cloudsqlsuperuser}
Moreover, new databases created using the "Databases" tab in the Cloud Console have ownership assigned, by default, to the cloudsqlsuperuser role. (As shown in the above role listing, the cloudsqlsuperuser role is inherited by the postgres user.)
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------+-------------------+----------+------------+------------+-------------------
testdb | cloudsqlsuperuser | UTF8 | en_US.UTF8 | en_US.UTF8 |
Accordingly, members of the cloudsqlsuperuser role will have permissions to create objects in the database by default. However, when doing so, they will by default have their owner set to the user which created them, rather than the parent role:
testdb=> CREATE TABLE sometable (id SERIAL NOT NULL);
CREATE TABLE
testdb=> \dt sometable
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | sometable | table | testuser
If we call SET ROLE cloudsqlsuperuser before creating our table, the owner will now default to the cloudsqlsuperuser role, which will allow postgres and other members of the role the default permissions assigned to the role by default:
You can also use triggers and other approaches to set the role automatically on table creation.
testdb=> SET ROLE cloudsqlsuperuser;
SET
testdb=> CREATE TABLE anothertable (id SERIAL NOT NULL);
CREATE TABLE
testdb=> \dt anothertable;
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-------------------
public | anothertable | table | cloudsqlsuperuser
(1 row)
For production usage, as noted in the "Alternatives" section, I recommend using a dedicated role rather than the built-in cloudsqlsuperuser role.
