How to Implement IAM Authentication in Cloud SQL PostgreSQL: A Step-by-Step Tutorial

Background

IAM Authentication in Cloud SQL for PostgreSQL lets you manage database user access using Google Cloud Identity and Access Management (IAM) identities instead of traditional PostgreSQL usernames and passwords. This integrates database access control with your existing Google Cloud IAM policies, providing better security and easier management.

Recently, I helped one of my customers set it up. The official documentation is great, but it doesn't offer a step-by-step guide, so I want to document it here for anyone who wants to set it up.

Now, let's head to my lab!

Set it up

First, create a PostgreSQL 15 database on Cloud SQL:

To enable IAM authentication, add the IAM auth flag cloudsql.iam_authentication while in Edit Instance mode:

Create a Service Account that will be associated to a database user later:

Assign proper IAM roles to the service account:

Add a database IAM user:

Once it is done, it looks like this:

Lastly, grant database privileges to the IAM user because, by default, it doesn't have any permissions.

Log in as the default postgres user:

➜  ~ gcloud sql connect pg-15 --user=postgres --quiet
Allowlisting your IP for incoming connection for 5 minutes...done.                                    
Connecting to database with SQL user [postgres].Password: 
psql (16.1 (Debian 16.1-1.pgdg110+1), server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=> \du
                                        List of roles
           Role name            |                         Attributes                         
--------------------------------+------------------------------------------------------------
 cloudsqladmin                  | Superuser, Create role, Create DB, Replication, Bypass RLS
 cloudsqlagent                  | Create role, Create DB
 cloudsqliamserviceaccount      | Cannot login
 cloudsqliamuser                | Cannot login
 cloudsqlimportexport           | Create role, Create DB
 cloudsqllogical                | Cannot login, Replication
 cloudsqlreplica                | Replication
 cloudsqlsuperuser              | Create role, Create DB
 postgres                       | Create role, Create DB
 test-iam-auth@airflow-talk.iam | 

postgres=> GRANT cloudsqlsuperuser to "test-iam-auth@airflow-talk.iam";                                
GRANT ROLE

All done!

Testing

To test whether the IAM authentication is working, we set up the connection to Cloud SQL using the Cloud SQL Auth Proxy.

First, we run the proxy with service account impersonation:

➜  ~ ./cloud-sql-proxy --auto-iam-authn --impersonate-service-account=test-iam-auth@airflow-talk.iam.gserviceaccount.com airflow-talk:us-central1:pg-15
2024/02/15 08:03:17 Impersonating service account with Application Default Credentials
2024/02/15 08:03:18 [airflow-talk:us-central1:pg-15] Listening on 127.0.0.1:5432
2024/02/15 08:03:18 The proxy has started successfully and is ready for new connections!

Then, connect to Postgres through the proxy, and you will see it is working:

➜  ~ psql -h 127.0.0.1 \
 -U "test-iam-auth@airflow-talk.iam" \
 --port 5432 \
 --dbname=postgres

psql (16.1 (Debian 16.1-1.pgdg110+1), server 15.4)
Type "help" for help.

postgres=>

That’s it. Hope this works for you.