Home / server / Questions / 1141846
Accepted
x-yuri
Asked: 2023-08-18 10:06:39 +0800 CST

Is password needed when connecting via Cloud SQL Auth Proxy?

  • 772

It seems like it's needed, because it asks me for the password. But if so, then what's the point in having 2 credentials (a credentials file + password)?

If not, then what am I missing?

The docs is not too revealing about this:

If prompted, enter the password.

To test it from the local machine I did:

docker-compose.yml:

services:
  app:
    build: .
    command: sleep infinity
    init: true
    volumes:
      - .:/app
    depends_on:
      - proxy

  proxy:
    image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.6.0
    command:
      --address 0.0.0.0
      --credentials-file /credentials.json
      --debug
      myprj:europe-central2:db
    volumes:
      - ./credentials.json:/credentials.json

Dockerfile:

FROM google/cloud-sdk:435.0.1-alpine
RUN apk add terraform postgresql15-client
WORKDIR /app

main.tf:

provider "google" {
  project = "myprj"
}

resource "google_sql_database_instance" "test-auth" {
  deletion_protection = false
  name = "db"
  region = "europe-central2"
  database_version = "POSTGRES_11"
  settings {
    tier = "db-f1-micro"
  }
}

resource "google_sql_user" "test-auth" {
  name = "postgres"
  instance = google_sql_database_instance.test-auth.name
  password = 123456
}

resource "google_service_account" "test-auth" {
  account_id = "dbaccount"
}

resource "google_project_iam_member" "test-auth" {
  project = "myprj"
  role    = "roles/cloudsql.client"
  member  = "serviceAccount:${google_service_account.test-auth.email}"
}

$ gcloud auth application-default login
$ terraform init
$ terraform apply
// create the service account key, copy to ./credentials.json, restart the container
$ psql -h proxy -U postgres
google-cloud-platform

1 Answers

  1. Best Answer

    What I was missing is:

    Also:

    • the postgres user can't be used for IAM database authentication because the database username must match the service account name

    That all results into:

    docker-compose.yml:

    services:
  app:
    build: .
    command: sleep infinity
    init: true
    volumes:
      - .:/app
    depends_on:
      - proxy

  proxy:
    image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.6.0
    command:
      --address 0.0.0.0
      --credentials-file /credentials.json
      --auto-iam-authn
      myprj:europe-central2:db
    volumes:
      - ./credentials.json:/credentials.json

    Dockerfile:

    FROM google/cloud-sdk:435.0.1-alpine
RUN apk add terraform postgresql15-client
WORKDIR /app

    main.tf:

    provider "google" {
  project = "myprj"
}

resource "google_sql_database_instance" "test-auth" {
  deletion_protection = false
  name = "mydb"
  region = "europe-central2"
  database_version = "POSTGRES_11"
  settings {
    tier = "db-f1-micro"
    database_flags {
      name = "cloudsql.iam_authentication"
      value = "on"
    }
  }
}

resource "google_sql_user" "test-auth-built-in" {
  name = "postgres"
  instance = google_sql_database_instance.test-auth.name
  password = 123456
}

resource "google_service_account" "test-auth" {
  account_id = "myaccount"
}

resource "google_project_iam_member" "test-auth" {
  project = "myprj"
  role    = "roles/cloudsql.client"
  member  = "serviceAccount:${google_service_account.test-auth.email}"
}

resource "google_project_iam_member" "test-auth-instance-user" {
  project = "myprj"
  role    = "roles/cloudsql.instanceUser"
  member  = "serviceAccount:${google_service_account.test-auth.email}"
}

resource "google_sql_user" "test-auth-iam" {
  name = "[email protected]"
  instance = google_sql_database_instance.test-auth.name
  type = "CLOUD_IAM_SERVICE_ACCOUNT"
}

    $ gcloud auth application-default login
$ terraform init
$ terraform apply
// create the service account key, copy to ./credentials.json, restart the container
$ psql -h proxy -U [email protected] postgres
    • 0