Connecting to Cloud SQL using an IAM user

Question

I'm failing to test the ability to use an IAM user in Google Cloud's PostgreSQL offering.

Here's my thinking process:

1. I've set respective flag on my PostgreSQL instance on Google Cloud:

   $ gcloud sql instances describe [MY_DB_INSTANCE] --format json | jq '.settings.databaseFlags'
   [
     {
       "name": "cloudsql.iam_authentication",
       "value": "on"
     }
   ]
   $
   

2. I have an IAM user, which I've created in the database instance:

   gcloud sql users create [MY_EMAIL] --instance=[MY_DB_INSTANCE] --type=CLOUD_IAM_USER
   

   $ gcloud sql users list --instance [MY_DB_INSTANCE] | grep CLOUD_IAM_USER
   [MY_EMAIL]                                      CLOUD_IAM_USER
   $
   

3. I get an authentication error when I try to connect to the DB using either of commands below. In both cases I use the output of gcloud auth print-access-token as my password:

   • this method adds my IP to allowlist:

     $ gcloud sql connect [MY_DB_INSTANCE] --database=[DB_NAME] --user=[MY_EMAIL]
     Allowlisting your IP for incoming connection for 5 minutes...done.
     Connecting to database with SQL user [MY_EMAIL]. Password:
     psql: error: FATAL:  Cloud SQL IAM user authentication failed for user "[MY_EMAIL]"
     FATAL:  pg_hba.conf rejects connection for host "100.200.300.400", user "[MY_EMAIL]", database "[MY_EMAIL]", SSL off
     $
     

     May or may not be related to the failure, but the error message is confusing here. The last line states ...database "[MY_EMAIL]", while clearly I am not attempting to connect to the database of the same name as my email; I am connecting to a database with a very specific name, e.g. [DB_NAME].

     Upd. As of right now the "Known issues" page lists acknowledgement of this:

     The following only works with the default user ('postgres'): gcloud sql connect --user

   • this method uses Cloud SQL Proxy:

     $ gcloud beta sql connect [MY_DB_INSTANCE] --database=[DB_NAME] --user=[MY_EMAIL]
     Starting Cloud SQL Proxy: [/usr/local/Caskroom/google-cloud-sdk/latest/google-cloud-sdk/bin/cloud_sql_proxy -instances my-project-id:europe-west1:[MY_DB_INSTANCE]=tcp:9470 -credential_file /Users/eugene/.config/gcloud/legacy_credentials/[MY_EMAIL]/adc.json]]
     2021/01/26 16:35:03 Rlimits for file descriptors set to {&{8500 9223372036854775807}}
     2021/01/26 16:35:03 using credential file for authentication; path="/Users/eugene/.config/gcloud/legacy_credentials/[MY_EMAIL]/adc.json"
     2021/01/26 16:35:04 Listening on 127.0.0.1:9470 for my-project-id:europe-west1:[MY_DB_INSTANCE]
     2021/01/26 16:35:04 Ready for new connections
     Connecting to database with SQL user [MY_EMAIL].Password:
     psql: error: FATAL:  Cloud SQL IAM user authentication failed for user "[MY_EMAIL]"
     $
     

4. If I check the access logs from Cloud Console, for both login attempts I see the same error message:

   2021-01-26 14:20:11.988 UTC [594848]: [2-1] db=[DB_NAME],user=[MY_EMAIL] DETAIL:  Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.
   

At this point I am quite lost.

Perhaps my expectations are not aligned with how connecting to a DB should work. I expected that:

1. the would be no need to enter a password (e.g. the value of gcloud auth print-access-token) at all in the first place, as gcloud would generate and use a password for me automagically,

2. in case entering the password manually (by copy-pasting the output from gcloud auth print-access-token command from the paste-buffer) is necessary it would work (while it doesn't).

I was hoping that by relying on the IAM auth mechanism of authenticating to the DB, I would be able to avoid the necessity to create a user and set him with a password using psql (or similarly using gcloud sql users create ... --type=BUILD_IN).

What is it that I am possibly missing?

Upd. I am able to successfully connect if, instead of using gcloud sql command, I run the proxy and use the command directly:

$ cloud_sql_proxy -instances my-project-id:europe-west1:[MY_DB_INSTANCE]=tcp:9470
2021/01/26 17:29:56 Rlimits for file descriptors set to {&{8500 9223372036854775807}}
2021/01/26 17:29:56 Listening on 127.0.0.1:9470 for my-project-id:europe-west1:[MY_DB_INSTANCE]
2021/01/26 17:29:56 Ready for new connections

$ env PGPASSWORD=(gcloud auth print-access-token) psql --host 127.0.0.1 --port 9470 --username=[MY_EMAIL] --dbname=[MY_DB]
psql (13.1, server 13.0)
Type "help" for help.

[MY_DB]=>

Celestial

Answer 1

I was having the same issue. The key piece of information that is not well documented is that the Cloud SQL Proxy tool will automatically request fresh tokens for you behind the scenes. So you don't need to pass the token in manually, you just need to point whatever client you want to use at the Cloud SQL Proxy service.

To activate this mode, you need to specify the -enable_iam_login command line option, like so:

./cloud_sql_proxy -instances=[project]:[zone]:[server]=tcp:5432 -enable_iam_login

It will generate access keys for whatever user is currently authenticated using gcloud auth login

Be sure to disable sslmode when connecting to the Cloud SQL Proxy service. It handles its own encryption so if the postgres client is also trying to encrypt the connection will timeout. Since the Cloud SQL Proxy service is handling authentication, you only need to specify the user in your postgres client.

psql "host=127.0.0.1 dbname=postgres user=[the IAM email account] sslmode=disable"

It shouldn't ask for a password. If it does, just leave it blank.

I found the Cloud SQL Proxy readme more useful than the official documentation: https://github.com/GoogleCloudPlatform/cloudsql-proxy/blob/main/README.md

Headrick

For a complete example including terraform, see binx.io/blog/2021/05/19/… – Agony

Answer 2

After playing around with it, was able to pick up the proper command for connection. Here it is:

env PGPASSWORD=(gcloud auth print-access-token) gcloud beta sql connect [MY_DB_INSTANCE] --user=[MY_EMAIL] --database=[MY_DB]

The key thing here, it seems, was specifying the PGPASSWORD variable for the process and then, when prompted to enter the password, just hit enter 🤯

What I also tried, and what appears to be a bug in gcloud sql, is connecting via a non-beta gcloud sql connect:

$ env PGPASSWORD=(gcloud auth print-access-token) gcloud sql connect [MY_DB_INSTANCE] --user=[MY_EMAIL] --database=[MY_DB]
Allowlisting your IP for incoming connection for 5 minutes...done.
Connecting to database with SQL user [MY_EMAIL].Password:
psql: error: FATAL:  database "[MY_EMAIL]" does not exist
$

Note how it says database "[MY_EMAIL]" does not exist, while the database is specified as a command line flag --database=[MY_DB]. Seems like a bug to me.

Celestial

Indeed gcloud beta sql and gcloud sql always works in different ways, the objective of beta is to test new commands without break implementations that uses stable commands, this is an intended behaviour – Soundproof

Also you can use psql instead of gcloud as the GCP documentation mentions, but this method will not add your public IP in the allowed list. – Soundproof

Use the CloudSQL proxy with -enable_iam_login to connect to the database as an IAM user. – Agony

Answer 3

For automatic (passwordless) authentication I'm adding a checklist:

• Make the user on the database with this format: [email protected]
• Grant this user roles/cloudsql.instanceUser
• Grant this user roles/cloudsql.client
• Login also with the special username: [email protected]
• Run the cloud sql proxy with ./cloud-sql-proxy --auto-iam-authn

(I had created a user without @xyz.iam and it kept failing)

Yolandoyolane