Join Log in
Score:0
avatar Server

Is password needed when connecting via Cloud SQL Auth Proxy?

cn flag
x-yuri

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
157
1 + 6
Sathi Aiswarya avatar
in flag
Sathi Aiswarya
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).
0
Reply
John Hanley avatar
cn flag
John Hanley
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
0
Reply
cn flag
x-yuri
@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.
0
Reply
John Hanley avatar
cn flag
John Hanley
@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.
0
Reply
cn flag
x-yuri
@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.
0
Reply
cn flag
x-yuri
@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.
0
Reply
Score:1
avatar Server
cn flag
x-yuri

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.

+ 0
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.