Authenticating to Cloud SQL for PostgreSQL with IAM service accountsAuthenticating to Cloud SQL for PostgreSQL with IAM service accountsCustomer Engineer, Google Cloud

gcloud sql instances patch INSTANCE_NAME --database-flags cloudsql.iam_authentication=on

Going forward, any instances on which we want to enable IAM Authentication should add:  --database-flags cloudsql.iam_authentication=on

Also from the Guide Linked Above, create a SQL user for the accounts, named by email address:

gcloud sql users create EMAIL --instance=INSTANCE_NAME --type=cloud_iam_service_account

(The service account email should be of the form [email protected] – OMITTING the “.gserviceaccount.com” suffix, or it will throw an error)

To complete the configuration, the IAM SQL user should be granted privileges to the database.  Connect, first, as the default postgres user, or a locally created account to enable privilege creation.  In psql:

postgres=> grant all on all tables in schema SCHEMA_NAME to "[email protected]";

*Note: While it is possible to revoke privileges from local accounts once we have elevated IAM accounts, we don’t recommend this as you should maintain at least a single local cloudsqlsuperuser account with a vaulted password as a fail-safe access in case any impediments to IAM authentication need to be bypassed.

There are two layers of authentication needed to connect to a GCP Cloud Resource:

  1. IAM User Account (or SA) – your “badge” to the cloud

  2. Psql user account – your “badge” to the instance

Prerequisite: GCloud SDK – You must have access to the gcloud SDK from any calling server or workstation resource.  We’re assuming, below, that you have gcloud sdk running, and a user account authenticated.  

Your IAM user account may be granted direct access, so you can skip the SA key management portion to follow if that is the case.

We assume, below, that your user account has privileges to impersonate the SA attached to the database instance:

A current OAUTH2 access token can be accessed on behalf of the Service Account by this command:

gcloud auth print-access-token --impersonate-service-account [email protected]

This token has a 60 minute expiration time.

  • Note: If the user has the Service Account User privilege (distinct from Service Account Token Creator role, allowing iam.serviceAccounts.actAs privilege), a different method can be employed where the administrator downloads the SA Access Key (sensitive!!), and directly activates their gcloud auth session as the service account. Both methods, in this case, can be used to generate the OAUTH2 token. There may be benefits to automation via downloaded SA JSON keys, but take care to safeguard them, as they are keys to the services!

  • psql: There are a lot of various tools used to connect to a database, so we won’t attempt to cover them all here. Fundamentally, it’s as simple as building a connect string using:

    • Hostname: Your hostname or IP address

    • Username: SA email address

    • Password: the OAUTH2 token 

  • For postgres, The author found success configuring the ~/.pgpass file with the proper access credentials. 

This file is a flat file, with a plain-text format:

hostname:port:database:username:password

Pay attention to the file configuration requirements, or the connection configuration is ignored with no warning – permissions, env variables etc.

pgAdmin – The credentials worked when pasted into the password configuration field. Of course, most psql management tools extend the psql paradigm and support the user of a pgpass file.  (pgAdmin also supports a password file)  It’s a fairly quick process to script out authentication and update of the password file prior to connect, making this process fairly operationally transparent.

Final Connect Workflow:

Once a team has properly configured databases, and teams accessing the data, each database could have its own IAM Service Account association, and each User account can be granted privileges to impersonate any service accounts.

When an administrator wishes to connect to a database, an individual connect workflow could be as simple as follows: (also, with a key/value list of pairs of DB instances and SA ID’s, this, below, could be scripted into a pretty smooth process)

  • Look up the IAM SA “email” address:

  • Gcloud SDK: Establish session; Grab OAUTH token for your SA: [gcloud auth print-access-token –impersonate-service-account [email protected]]

  • Connect

Enjoy your IAM authenticated Cloud SQL databases!

Leave a Comment