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:
Configure Cloud SQL for IAM Authentication
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:
Configure IAM
Configure the SQL Instance
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.
At a minimum, this account must have privileges to the DB instance in question.
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.
Calling Account Privileges – minimum privileges below should be added to an existing user account, or (recommended) a Service Account. You can use the role below, or a custom role that bundles these privileges:
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
Enable IAM Authentication via configuration flag – IAM authentication does not just work when you add the accounts – you must enable the flag. For an existing instance see the examples below – Cloud UI and Gcloud, respectively:
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:
IAM User Account (or SA) – your “badge” to the cloud
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 hasprivileges 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
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.
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]]
Either store as env variable, or update a local password file
Connect
Enjoy your IAM authenticated Cloud SQL databases!
Cloud BlogRead More
