# 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](https://cloud.google.com/sql/docs/postgres/iam-authentication) 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:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1707950287950/878ef2e4-f413-4842-8de2-cf232decbe3d.png align="center")

To enable IAM authentication, add the IAM auth flag **cloudsql.iam\_authentication** while in Edit Instance mode:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1707950338475/5c889549-1c4f-4df7-9d1c-5ac71a27cf79.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1707950446342/72115f27-4de3-422d-b675-3042e31a82a4.png align="center")

Assign proper IAM roles to the service account:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1707984279738/1c327083-fd5b-4bb2-8220-c81b00607f44.png align="center")

Add a database IAM user:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1707980612539/61a8b6a6-7f1a-4f2e-a860-644094bccb32.png align="center")

Once it is done, it looks like this:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1707980519164/e7964f95-c351-4e60-890a-0832f0614242.png align="center")

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

Log in as the default `postgres` user:

```bash
➜  ~ 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:

```bash
➜  ~ ./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:

```bash
➜  ~ 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.