Authenticating to Cloud SQL for PostgreSQL with IAM service accounts

In October of 2020 we announced the launch of a preview of IAM Authentication to Cloud SQL for PostgreSQL.  This blog post’s goal is to provide an ELI5 procedural outline of the steps that one needs to take in order to that led to perform:

1. Configure Cloud SQL for IAM Authentication

2. Configure a user agent to connect to a given database using assigned service account credentials. 

Note: The same process will work for individually assigned accounts - just skipping the steps where we activate or impersonate a service account.

There are three main steps in order to implement Cloud SQL IAM authentication:

1. Configure IAM

2. Configure the SQL Instance

3. Connect to a SQL Database on that instance

The intention of this post is not to go into deep details regarding Google Cloud IAM. Instead, we will specify what’s needed for an account to authenticate properly to a Cloud SQL database and If you want, you can read more here. The account can be either a user account or (recommended) a service account. 

1. At a minimum, this account must have privileges to the DB instance in question. 

2. In the case of a service account, if we want to enable users to connect to the database using that service account, the user account must be granted privileges to generate OAUTH2 tokens on the Service Account’s behalf. This will enable impersonation of that service account when calling the database as that account.

Administrative User Account Privileges - OAUTH2 token impersonation (necessary for the user account that wishes to call a service as a Service Account:  

Cloud SQL Configuration for IAM Authentication

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 sa-name@projectid.iam - 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 "sa-name@projectid.iam";

*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 sa-name@project-id.iam.gserviceaccount.com

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 

• The trick is in feeding the dynamically rotating tokens as the password
  
  

• 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.

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:

• Ex: sa-name@project-id.iam.gserviceaccount.com

• Gcloud SDK: Establish session; Grab OAUTH token for your SA: [gcloud auth print-access-token --impersonate-service-account sa-name@project-id.iam.gserviceaccount.com]

• Either store as env variable, or update a local password file

• Connect

Enjoy your IAM authenticated Cloud SQL databases!