GCP SQL Postgres problem with privileges : can't run a query with postgres user with generated symfony db

Question

I am struggling to solve this problem with Google Cloud Platform's Cloud SQL component. My tech stack consists of hosting my application in a Google Kubernetes Engine (GKE) deployment, using the Cloud SQL proxy sidecar to connect to the database within the pods. The backend is a Symfony project.

I follow these steps to create and populate the database (without success):

1. Create Cloud SQL Postgres instance
2. Add proxy to k8s container to connect to the Cloud SQL instance with all credentials, as described in the GCP documentation
3. Enter my Symfony (phpfpm) pod and run the command php bin/console doctrine:schema:update --force to update the schema. The queries are executed in the database, so the schema is created and so on.
4. I try to open the database from the SQL console connection within GCP with the postgres user and try to execute a simple select * from foo; query. The response is Insufficient privilege: 7 ERROR: permission denied for relation

How can I query the data in the database with the postgres user?

EDIT :

I have this situation about users :

     Role name     |                         Attributes                         |           Member of           
-------------------+------------------------------------------------------------+-------------------------------
 acando14          | Create role, Create DB                                     | {cloudsqlsuperuser,proxyuser}
 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,acando14}
 proxyuser         | Create role, Create DB                                     | {cloudsqlsuperuser}

And I have this situation in the tables :

              List of relations
 Schema |      Name       | Type  |  Owner   
--------+-----------------+-------+----------
 public | article         | table | acando14

If I use postgres user logged in my db symfony works :

symfony => select * from article;
 id | model_id | code | size 
----+----------+------+------
(0 rows)

But if I use the server to execute the code the response is :

SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for relation employee at PDOException(code: 42501): SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for relation .. at

And another problem is that I didn't generate all the tables with the command but I have to generate it executing all the queries, so strange...

Thank you, regards

Answer 1

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.