Connect to CloudSQL with Cloud Run and non-default user

[gcmarvel]

I have a Cloud Run instance which connects to Cloud SQL postgres database through Unix socket with url like: "postgresql+asyncpg://postgres:password@instance-db?host=/cloudsql/project:region:instance-db".

I need to connect with different user/role, but if I use url like: "postgresql+asyncpg://user:user_password@instance-db?host=/cloudsql/project:region:instance-db" I get an error "Database user does not exist". 

Is it possible to connect with non postgress role with Cloud Run without sidecaring Cloud Auth Proxy to cloud run service?

[ms4446 · Accepted solution]

Yes, it's indeed possible to connect to a Cloud SQL PostgreSQL database from Cloud Run using a non-default PostgreSQL role. However, there are several important considerations and steps to ensure this setup works correctly:

• User/Role Permissions in PostgreSQL

  • Create the necessary role: First, ensure that the PostgreSQL role (or user) you wish to use exists within your Cloud SQL instance. You can create or manage roles using SQL commands within the PostgreSQL environment.
  • Grant permissions: The role must have the necessary permissions for your application's requirements, such as SELECT, INSERT, UPDATE, DELETE on the needed tables. Emphasize: The principle of least privilege is crucial – grant only the absolutely necessary permissions to your application's role.

• Cloud SQL Configuration

  • PostgreSQL on Cloud SQL does not use IAM-based authentication for database users directly. Authentication is managed entirely within PostgreSQL through its system of roles and passwords. Emphasize: This is a key distinction between Cloud SQL's PostgreSQL and MySQL offerings.

• IAM Permissions for Cloud Run Service Account

  • Cloud SQL Client role: Ensure that the Cloud Run service's service account has the Cloud SQL Client role. This is essential for network-level access to the instance.

• Connection String Configuration

  • For connecting from Cloud Run, your connection string should look like this: postgresql+asyncpg://user:user_password@/dbname?host=/cloudsql/project:region:instance-id Emphasize: Replace the placeholders with your actual role name, password, database name, and instance connection name. Cloud Run's built-in connector provides secure Unix socket-based connections.

Important Considerations:

• Security: Even within Google Cloud's secure infrastructure, always tightly control database roles and permissions. Emphasize: Never use overly broad permissions, even during development.
• Cloud SQL Auth Proxy: While not strictly required within Cloud Run, the Cloud SQL Auth Proxy adds an extra security layer, especially when connecting from outside Google Cloud.

[gcmarvel]

@ms4446 It worked with proper url string, thank you.