Background Info
I have a GCP project called abc-stage
which hosts all the deployments for my dev
and test
targets, along with the required resources. All applications are deployed to Cloud Run, and they use Cloud SQL for Postgres 15.
I have two databases in the Cloud SQL instance (let’s call the instance csql
) – dev
and test
. All of my cloud run applications have their own schema in the respective database. So for the following CR applications – svc1, svc2, svc3 – I have the following schemas under both the dev
and test
databases – svc1
, svc2
and svc3
.
I also try to isolate permissions for these schemas by creating a new Cloud SQL user for each service. So the user list looks something like this:
- dev_svc1_user
- test_svc1_user
- dev_scv2_user and so on
The creation of the databases is handled by a “shared” terraform config. The creation of the cloud sql users and the cloud run service is handled by the application-specific terraform config. They all use the same config, with just a few values changed.
The applications themselves are created with node and express. The schema creation, the granting of permissions for the user to the schema, and creation of tables along with granting permissions for the tables is handled in the express application with the node-pg-migrate
library.
This setup has worked perfectly fine for all of my services so far. I created a new service a few days back and it worked as expected in the dev
target. When deploying to the test
target though, I get the following error:
Error executing:
CREATE SCHEMA IF NOT EXISTS "svc4"
error: permission denied for database test
Fixes I have tried
I have tried doing a couple of things:
- Running
GRANT CONNECT ON database test to test_svc4_user;
- Running
GRANT CREATE ON database test to test_svc4_user;
- Running
GRANT ALL PRIVILEGES ON database test to test_svc4_user;
I executed all of these queries as thepostgres
user, as well as the other created users (test_svc1_user
,test_svc2_user
, etc.) with no luck.
Any idea on what I might be doing wrong, or what might be causing this issue?