0

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
      PROJECT_ID:europe-central2:myinstance
    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 = "PROJECT_ID"
}

resource "google_sql_database_instance" "test-auth" {
  # deletion_protection = false
  name = "myinstance"
  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 = 1
}

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

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

// replace PROJECT_ID
// uncomment deletion_protection if you're going to destroy the resources after experimenting
$ touch credentials.json
$ docker compose up -d
$ docker compose exec app gcloud auth application-default login
$ docker compose exec app terraform init
$ docker compose exec app terraform apply
// create the service account key
$ mv ... credentials.json
$ docker compose up -d
$ docker compose exec app psql -h proxy -U postgres
x-yuri
  • 2,141
  • 2
  • 24
  • 29
  • In your `main.tf` file, you are creating a user with password, so you would still need to provide the password .As this [document](https://cloud.google.com/sql/docs/mysql/sql-proxy#requirements) says You must provide the Cloud SQL Auth Proxy with a valid database user account and password.If you want to eliminate the need of username and password, you can follow the steps in this [blog](https://saturncloud.io/blog/what-is-the-username-and-password-for-a-cloud-sql-instance/#alternative-authentication-method-cloud-sql-proxy). – Sathi Aiswarya Aug 18 '23 at 05:46
  • There are two items that require authorization. The first is Cloud SQL Auth Proxy. The second is the PostgreSQL database(s). The proxy uses IAP to setup a network connection. Those credentials are not the same as the database which has its own authorization mechanism. You can also setup Cloud SQL IAM database authentication https://cloud.google.com/sql/docs/postgres/authentication#user_and_service_account_administration – John Hanley Aug 18 '23 at 19:17
  • @JohnHanley It's best to put it this way. For Cloud SQL Auth Proxy to be able connect the connection has to be *authorized* (`roles/cloudsql.client`). You also need to *authenticate* to the database, which is a different story. One of the ways is IAM authentication that is available on Cloud SQL. I've made it work. See [my answer](https://serverfault.com/a/1141938/162443). Also there's a link at the end with overview of authentication/authorization on Cloud SQL. – x-yuri Aug 27 '23 at 13:36
  • @x-yuri - I like your wording on Cloud SQL. Are you trying to correct me? I only posted a comment. Perhaps this [link](https://stackoverflow.com/tags/google-cloud-platform/topusers) might be useful. I have posted lots of answers on Cloud SQL. Sometimes, I don't have the time to post a complete answer so I give suggestions via comments. – John Hanley Aug 27 '23 at 15:27
  • @JohnHanley I'm not sure if you wanted me to answer. But if you did, I was trying to correct you. Your words can be interpreted in different ways, and I decided to phrase them more unambiguously. Also I wanted to share what I'd managed to find out (my answer and the [gist](https://gist.github.com/x-yuri/d2a257ecd151d8efef32193f46db398e) I mentioned). I've spent quite a while inspecting the documentation and experimenting with Cloud SQL. – x-yuri Aug 31 '23 at 22:58
  • @SathiAiswarya The Cloud SQL documentation is arguably confusing and not always correct (possibly out of date). Let's take the part about providing the proxy with a username and a password. Should I provide the password when starting the proxy? No. When connecting to a database. Am I providing the proxy with a password in this case? Yes and no. At least not only the proxy. Is this a requirement? No, with IAM database authenication there's no password. As for the blog post, to be frank, it's too unspecific to be useful. In my opinion, of course. – x-yuri Aug 31 '23 at 23:10

1 Answers1

1

The short answer is, password is not needed.

What I did:

  • created a service account
  • gave Cloud SQL Auth Proxy access to the service account by passing the --credentials-file flag
  • authorized connections from Cloud SQL Auth Proxy to the Cloud SQL instance by granting Cloud SQL Client role (roles/cloudsql.client) to the service account

What was missing:

You might also want to restrict access to a specific Cloud SQL instance by adding the following condition for both roles:

resource.name == 'projects/PROJECT_ID/instances/INSTANCE_NAME'
&& resource.service == 'sqladmin.googleapis.com'

Do note, the postgres user can't be used for IAM database authentication because the database username must match the service account name.

The final result:

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
      PROJECT_ID:europe-central2:myinstance
    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 = "PROJECT_ID"
}

resource "google_sql_database_instance" "test-auth" {
  # deletion_protection = false
  name = "myinstance"
  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 = 1
}

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

resource "google_project_iam_member" "test-auth-client" {
  project = "PROJECT_ID"
  role = "roles/cloudsql.client"
  member = "serviceAccount:${google_service_account.test-auth.email}"
  condition {
    title = "myinstance"
    expression = "resource.name == 'projects/PROJECT_ID/instances/myinstance' && resource.service == 'sqladmin.googleapis.com'"
  }
}

resource "google_project_iam_member" "test-auth-instance-user" {
  project = "PROJECT_ID"
  role = "roles/cloudsql.instanceUser"
  member = "serviceAccount:${google_service_account.test-auth.email}"
  condition {
    title = "myinstance"
    expression = "resource.name == 'projects/PROJECT_ID/instances/myinstance' && resource.service == 'sqladmin.googleapis.com'"
  }
}

resource "google_sql_user" "test-auth-iam" {
  name = "myaccount@PROJECT_ID.iam"
  instance = google_sql_database_instance.test-auth.name
  type = "CLOUD_IAM_SERVICE_ACCOUNT"
}

// replace PROJECT_ID
// uncomment deletion_protection if you're going to destroy the resources after experimenting
$ touch credentials.json
$ docker compose up -d
$ docker compose exec app gcloud auth application-default login
$ docker compose exec app terraform init
$ docker compose exec app terraform apply
// create the service account key
$ mv ... credentials.json
$ docker compose up -d
$ docker compose exec app psql -h proxy -U myaccount@PROJECT_ID.iam postgres

You can use the log to find out what's wrong.

In case you forgot to enable IAM database authentication on an instance (cloudsql.iam_authentication):

$ gcloud logging read "resource.type=cloudsql_database" --project=PROJECT_ID --limit=10
...
logName: projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
resource:
  labels:
    database_id: PROJECT_ID:myinstance
    project_id: PROJECT_ID
    region: europe-central2
  type: cloudsql_database
severity: INFO
textPayload: |-
  2023-08-19 00:33:39.798 UTC [857]: [2-1] db=postgres,user=myaccount@PROJECT_ID.iam DETAIL:  Cloud SQL IAM authentication not enabled
  Connection matched pg_hba.conf line 21: "local   all           +cloudsqliamserviceaccount         cloudsql-iam-svc-acct"
---
logName: projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
resource:
  labels:
    database_id: PROJECT_ID:myinstance
    project_id: PROJECT_ID
    region: europe-central2
  type: cloudsql_database
severity: ALERT
textPayload: '2023-08-19 00:33:39.798 UTC [857]: [1-1] db=postgres,user=myaccount@PROJECT_ID.iam
  FATAL:  Cloud SQL IAM service account authentication failed for user "myaccount@PROJECT_ID.iam"'
...

In case you forgot to grant the Cloud SQL Instance User role to your service account:

$ gcloud logging read "resource.type=cloudsql_database" --project=PROJECT_ID --limit=10
...
logName: projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
resource:
  labels:
    database_id: PROJECT_ID:myinstance
    project_id: PROJECT_ID
    region: europe-central2
  type: cloudsql_database
severity: INFO
textPayload: |-
  2023-08-19 00:39:21.656 UTC [991]: [2-1] db=postgres,user=myaccount@PROJECT_ID.iam DETAIL:  Not authorized to access resource. Possibly missing permission cloudsql.instances.login on resource projects/PROJECT_ID/instances/myinstance.
  Connection matched pg_hba.conf line 21: "local   all           +cloudsqliamserviceaccount         cloudsql-iam-svc-acct"
---
logName: projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
resource:
  labels:
    database_id: PROJECT_ID:myinstance
    project_id: PROJECT_ID
    region: europe-central2
  type: cloudsql_database
severity: ALERT
textPayload: '2023-08-19 00:39:21.656 UTC [991]: [1-1] db=postgres,user=myaccount@PROJECT_ID.iam
  FATAL:  Cloud SQL IAM service account authentication failed for user "myaccount@PROJECT_ID.iam"'
...

For more information see this overview.

x-yuri
  • 2,141
  • 2
  • 24
  • 29