gcp-cloud-sql-spring

name	gcp-cloud-sql-spring
description	Connecting Spring Boot to Cloud SQL (PostgreSQL/MySQL) on GCP — Cloud SQL Java Connector vs Auth Proxy, IAM database authentication, HikariCP sizing, private IP, and read replicas. Use this skill when wiring a Spring Boot service to Cloud SQL on GKE or Cloud Run, or when scaling an existing setup.
category	engineering
tags	["java","spring-boot","gcp","database","postgresql","jpa","integration","backend"]
keywords	["Cloud SQL","Cloud SQL Auth Proxy","Cloud SQL Java Connector","IAM database authentication","HikariCP","private IP","read replica","PgBouncer"]
related	["spring-boot-fundamentals","spring-data-jpa","gcp-fundamentals","gke-deployment","gcp-vertex-ai-rag"]

Cloud SQL from Spring Boot

Use the Java Connector for direct, mutual-TLS, IAM-authenticated access — no sidecar needed. Or use the proxy when ops prefers a uniform sidecar model.

When to Use This Skill

• Wiring a new Spring Boot service to Cloud SQL Postgres/MySQL
• Choosing between the Cloud SQL Auth Proxy and the Java Connector
• Replacing password auth with IAM database authentication
• Sizing HikariCP and the Cloud SQL instance for a given workload
• Adding read replicas or migrating to private IP

For JPA/Hibernate patterns, pair with spring-data-jpa.

---

Pick a Connection Strategy

Option	When
Cloud SQL Java Connector (com.google.cloud.sql:postgres-socket-factory)	Default. No sidecar, mutual TLS handled by JDBC, supports IAM auth.
Cloud SQL Auth Proxy sidecar	Org standard mandates a sidecar; non-Java consumers in same pod; you want the proxy's connection limits.
Direct private IP	Cluster and instance both on same VPC and you don't need IAM auth or short-lived certs. Lowest abstraction, no extra dependencies.

1. Default to the Java Connector for new Spring Boot services. One JAR, no sidecar, IAM auth out of the box.

2. Pick the proxy when there's a platform mandate or other languages share the workload.

3. Direct private IP is fine for simple setups but you lose IAM auth and the connector's automatic cert rotation.

---

Java Connector

Dependencies

implementation 'org.postgresql:postgresql'
implementation 'com.google.cloud.sql:postgres-socket-factory:1.19.1'

application.yml

spring:
  datasource:
    url: jdbc:postgresql:///orders?cloudSqlInstance=acme-orders-prod:asia-east1:orders-db&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=[e-mail]&enableIamAuth=true
    username: [e-mail]
    password: ""                # required placeholder; the connector substitutes a short-lived IAM token
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 3000
      max-lifetime: 1500000   # 25min, < Cloud SQL idle limit

4. cloudSqlInstance format: <project>:<region>:<instance>. Wrong regions are the #1 cause of "ConnectorException: instance not found".

5. enableIamAuth=true lets the SA authenticate without a static password. The connector fetches a short-lived token per connection.

6. password: "" is still required in the YAML. HikariCP runs a credentials check before the connector takes over; without the property it fails with "password" property is not set. The empty value is replaced by the connector's IAM token.

7. The DB user matches the SA email, with @<project>.iam suffix for service accounts and @acme.com for human users — minus the trailing .gserviceaccount.com.

Required IAM

gcloud projects add-iam-policy-binding acme-orders-prod \
    --member "serviceAccount:[e-mail]" \
    --role   "roles/cloudsql.client"

gcloud projects add-iam-policy-binding acme-orders-prod \
    --member "serviceAccount:[e-mail]" \
    --role   "roles/cloudsql.instanceUser"

8. cloudsql.client allows connecting; cloudsql.instanceUser is required for IAM auth specifically.

Create the DB user

gcloud sql users create [e-mail] \
    --instance=orders-db \
    --type=cloud_iam_service_account

-- Grant only what the service needs
GRANT CONNECT ON DATABASE orders TO "[e-mail]";
GRANT USAGE ON SCHEMA public TO "[e-mail]";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "[e-mail]";
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "[e-mail]";

9. Don't grant superuser to the app SA. Migrations should run as a separate user (or a separate SA with broader rights) used only by the migration job.

---

Cloud SQL Auth Proxy Sidecar

When the platform standard is sidecar:

# Deployment.spec.template.spec
containers:
  - name: app
    image: ...
    env:
      - { name: SPRING_DATASOURCE_URL, value: "jdbc:postgresql://127.0.0.1:5432/orders" }

  - name: cloud-sql-proxy
    image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.13.0
    args:
      - "--structured-logs"
      - "--port=5432"
      - "--auto-iam-authn"
      - "acme-orders-prod:asia-east1:orders-db"
    securityContext:
      runAsNonRoot: true
    resources:
      requests: { cpu: "100m", memory: "64Mi" }
      limits:   { cpu: "500m", memory: "128Mi" }
serviceAccountName: orders-app   # Workload Identity → GSA with roles/cloudsql.client

10. Same IAM requirements as the Java Connector. The proxy is just a cleaner UX in some environments.

11. Configure pod-level lifecycle so the proxy outlives the app. Otherwise the app loses DB during shutdown.

---

HikariCP Sizing

spring:
  datasource:
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 3000        # fail fast → K8s retries
      idle-timeout: 600000
      max-lifetime: 1500000           # < Cloud SQL idle/wal limits
      leak-detection-threshold: 5000  # log connections held > 5s

12. Pool size × pod count must fit the Cloud SQL max_connections quota. Cloud SQL Postgres scales max_connections with tier; check the live value with SHOW max_connections; (and the cloudsql.iam_authentication-specific overrides). Run the math before scaling pods, not after.

13. At scale, front Cloud SQL with PgBouncer (transaction pooling). Without it, autoscaling Spring pods can exhaust connections. Many teams self-host PgBouncer in the cluster; alternatively use Cloud SQL's built-in connection pooling on supported tiers.

14. max-lifetime < Cloud SQL idle disconnect. Otherwise pods carry stale connections until the next query fails.

15. leak-detection-threshold of 5s in non-prod helps catch missing @Transactional boundaries that hold connections too long.

---

Private IP

Production should use Private IP only:

gcloud sql instances create orders-db \
    --database-version=POSTGRES_15 \
    --tier=db-custom-4-8192 \
    --region=asia-east1 \
    --network=projects/acme-shared/global/networks/main \
    --no-assign-ip                                        # private only

16. No public IP in production. A private IP is reachable only from the VPC; the Java Connector still works because it talks to a private endpoint.

17. VPC Service Controls for compliance — they wrap the API itself, not just the network. Pair with private IP, not as an alternative.

---

Read Replicas

gcloud sql instances create orders-db-replica-1 \
    --master-instance-name=orders-db \
    --tier=db-custom-2-4096 \
    --region=asia-east1

# Spring config: two DataSources
app:
  datasource:
    primary:   "jdbc:postgresql:///orders?cloudSqlInstance=...orders-db&socketFactory=..."
    replica:   "jdbc:postgresql:///orders?cloudSqlInstance=...orders-db-replica-1&socketFactory=..."

@Configuration
public class DataSourceConfig {

    @Primary
    @Bean
    @ConfigurationProperties("spring.datasource.primary.hikari")
    public DataSource primary() { return DataSourceBuilder.create().url(props.primary()).build(); }

    @Bean
    @ConfigurationProperties("spring.datasource.replica.hikari")
    public DataSource replica() { return DataSourceBuilder.create().url(props.replica()).build(); }

    @Bean
    public DataSource routing(@Qualifier("primary") DataSource p, @Qualifier("replica") DataSource r) {
        var routing = new AbstractRoutingDataSource() {
            @Override protected Object determineCurrentLookupKey() {
                return TransactionSynchronizationManager.isCurrentTransactionReadOnly() ? "replica" : "primary";
            }
        };
        routing.setTargetDataSources(Map.of("primary", p, "replica", r));
        routing.setDefaultTargetDataSource(p);
        return routing;
    }
}

18. @Transactional(readOnly = true) routes to the replica. The whole flow depends on the read-only flag being set correctly on services.

19. Replicas lag. Don't read-after-write your own changes from a replica. Either pin to primary for the read in that flow, or use cursors that tolerate staleness.

---

Migrations

20. Run Flyway/Liquibase as a separate Job, not inside app startup, in production. App startup is the wrong place for a slow or risky DDL change.

# k8s Job that runs migrations before the app rolls out
apiVersion: batch/v1
kind: Job
metadata: { name: orders-migrate-1.42.0 }
spec:
  template:
    spec:
      serviceAccountName: orders-migrate   # broader permissions than the app SA
      restartPolicy: Never
      containers:
        - name: migrate
          image: asia-east1-docker.pkg.dev/.../orders:1.42.0
          command: ["java", "-jar", "/app/app.jar", "--spring.profiles.active=migrate"]

21. The migration SA has DDL rights, the app SA does not. Least privilege, again.

---

Local Development

# Option A: Cloud SQL Auth Proxy locally
cloud-sql-proxy acme-orders-dev:asia-east1:orders-db &

# Option B: Local Postgres in Docker
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=local postgres:15

22. Don't share a Cloud SQL dev instance across developers. Schema drift, fixture pollution. Local Docker > shared remote DB.

---

Common Pitfalls

Pitfall	Fix
org.postgresql.util.PSQLException: FATAL: PAM authentication failed	The DB user wasn't created as a cloud_iam_service_account user, or enableIamAuth is missing
Pods exhaust connections under autoscale	Add PgBouncer; reduce per-pod pool size
Connection is not available, request timed out after 30000ms	Pool too small, leaked connection (leak-detection-threshold), or DB instance saturated
Long startup blamed on Hibernate	Migrations are running on app startup; split into a Job
Read replica is hot, primary cool	Code uses readOnly = true everywhere; reads from replica even when consistency required
Connection drops every ~10 minutes	max-lifetime > Cloud SQL idle timeout — lower it

---

Pre-Production Checklist

• Cloud SQL instance with private IP only
• App SA: roles/cloudsql.client + roles/cloudsql.instanceUser
• DB user is an IAM SA user; no static password
• App SA has table-level grants only; no superuser
• Migrations run as a separate Job with broader privileges
• HikariCP pool size × replicas ≤ Cloud SQL max_connections
• max-lifetime < instance idle timeout
• leak-detection-threshold set in non-prod
• PgBouncer in front if pod count × pool > 50
• @Transactional(readOnly = true) audited if read replicas are used

---

Related Skills

• spring-data-jpa — entities, repositories, transactions
• spring-boot-fundamentals — DataSource auto-config and profiles
• gcp-fundamentals — Workload Identity, ADC, IAM
• gke-deployment — pod-side proxy / connector wiring
• database-migrations — Flyway/Liquibase strategy