CODEWITHSUNDEEP
postgresqlgoogle-cloud-sqlgoogle-cloud-iam
pierroz
pierroz

Reputation: 7870

How to connect an IAM service account to a Cloud SQL instance

On Google Cloud SQL, IAM database authentication for PostgreSQL was made generally available recently.

So, I carefully followed the instructions here: Overview of Cloud SQL IAM database authentication.

I think I've configured my Cloud SQL instance correctly because for a user account, it works fine. In a nutshell:

  1. I used my own user account in my Google Cloud project and added the role CloudSQL Instance Viewer to it
  2. I added it as a user of my CloudSQL instance
  3. I granted it all the privilege to all the tables of my db
  4. And with the command described here: https://cloud.google.com/sql/docs/postgres/iam-logins, I could log in to my db:
PGPASSWORD=$(gcloud auth print-access-token) psql --host=HOSTNAME \
                                                  --username=EMAIL \
                                                  --dbname=DATABASE_NAME

Great !

But now, what I don't really get is how it is supposed to work in my application that runs with a service account? The doc doesn't say a lot about it. Does it mean that I have to do the equivalent of this gcloud auth print-access-token programmatically and pass its result as a password for the connection to my db?

Upvotes: 0

Views: 4927

Answers (1)

Samuel Romero
Samuel Romero

Reputation: 1263

In order to use IAM User or IAM Service Account User authentication on an instance the user or SA email (or fragment of SA email as we dealt with in this case) replaces the traditional username parameter when authenticating with the database.

Additionally the password component also changes in that you do not set a password for IAM users and instead use an OAuth2 access token which must be requested by the client via a separate API call. These access tokens are only valid for 60 minutes after which they expire - however once a token expires it does not disconnect clients but if that client connection is broken and must re-connect to the instance, and it's been more than an hour, then a new access token will need to be pulled and provided on that new connection attempt.

For this use case i.e., an unattended application, a service account IAM user is the best way to go. Your client implementation would want to make use of the API client library (for example java) which provides a convenience class for pulling in Service Account credentials via a JSON key file (downloadable for the SA from your Cloud Console) i.e.,

GoogleCredential credential = GoogleCredential.fromStream(new FileInputStream("MyProject-1234.json")).createScoped(Collections.singleton(SQLAdminScopes.SQLSERVICE_ADMIN)).refreshIfExpired();

Note that in order to get an appropriate access token the scope must be set to Cloud SQL Admin API - the above example shows how that works within the context of the aforementioned library and initializer. Note that the call to 'refreshIfExpired()' must be called before the access token is available.

Once you have the GoogleCredential instance you can get the access token from it by calling getAccessToken() on it which then can be supplied to your DB client's connection parameters/string as the password for the service account IAM username i.e.,

AccessToken token = credential.getAccessToken();

What you need to strongly consider here though is whether this flow makes sense for your client application. If your client application accesses the DB through a connection pooler or some other connection management library which automatically retries or sets up new connections with static credentials - the use of Postgres IAM user authentication may not be a good fit due to the need to generate a new 'password' (Access Token) on each connection attempt where either no Access Token is available (i.e., first login attempt) or where the Access Token has expired. This ultimately means taking on more responsibility in your client code for managing connection establishment, re-connecting on dropped/closed connections and sacrificing many of the efficiencies and conveniences of libraries designed to do this in the more traditional static credential environment.

Upvotes: 1

Related Questions