Connect PostgreSQL Clients to Cloud SQL for PostgreSQL using Manual IAM Authentication | by Somdyuti | Google Cloud – Community | Apr, 2025

Introduction

Cloud SQL for PostgreSQL supports IAM Authentication. It supports both IAM User and Group Authentication. IAM database authentication uses IAM to authenticate a user by using an access token. The IAM Authentication can either be automatic or manual.

Automatic IAM database authentication lets you hand off requesting and managing access tokens to an intermediary Cloud SQL connector, such as the Cloud SQL Auth Proxy or one of the Cloud SQL Language Connectors. With automatic IAM database authentication, users need to pass only the IAM database username in a connection request from the client. The connector submits the access token information for the password attribute on behalf of the client.

Manual IAM database authentication requires the IAM principal to explicitly pass the access token for the password attribute in the client connection request. Principals must first log in to Google Cloud and explicitly request the access token from IAM.

In the article we will look into the steps on how to use Manual IAM database authentication with PostgreSQL clients like psql and pgAdmin.

The Use Case

How to use psql/pgAdmin to connect to Cloud SQL for PostgreSQL Instance using Manual IAM Authentication. Cloud SQL AuthProxy is not being used.

Scenario

The User/principal is somdyuti@gmail.com

The GCE VM (Compute Engine) have clients/applications running (like psql , pgAdmin4) that want to connect to Cloud SQL for PostgreSQL Instance using IAM Authentication.

AuthProxy is not installed and so automatic IAM Authentication will not work

Solution

The user should impersonate the Compute Engine(CE) Default Service Account(SA) and create an OAuth2 Access token to authenticate itself with the Cloud SQL Instance.

The user or an application can authenticate to the database using IAM by manually requesting an access token from Google Cloud and presenting it to the database. Using the gcloud CLI, you can explicitly request an OAuth 2.0 token with the Cloud SQL Admin API scope that is used to log in to the database. When you log in as a database user with manual IAM database authentication, you use your email address as the username and the access token as the password.

Steps

1. Grant the “iam.serviceAccountTokenCreator” role to the user to impersonate the CE Default SA. In this example the Admin grants somdyuti@gmail.com IAM role to generate Service Account token on behalf of the Compute Engine default service account. The PostgreSQL Client applications run on the Compute Engine.

2. Authorize the gcloud CLI with your user identity(somdyuti@gmail.com) by running gcloud auth login

3. Setup the gcloud CLI to use the identity and access provided by a service account by default using gcloud config set auth/impersonate_service_account

4. Add theIAM role roles/cloudsql.instanceUser to the CE Default service account

5. Enable IAM Authentication in Cloud SQL PostgreSQL

Set the below Cloud SQL for PostgreSQL flag

6. Add the CE Default SA (without .gserviceaccount.com) as an IAM Service Account user to the Cloud SQL Instance

Solution at Work

a. Connect with psql using Manual IAM Authentication

b. Use pgAdmin4 to Connect using IAM Authentication

Generate the access token

Pass the token as password during “Connect to Server”

Conclusion

It is always preferred to use Automatic IAM Authentication using either Cloud SQL AuthProxy or the Language Connectors. Manual IAM Authentication requires the IAM Principal to generate the Auth Token and refresh it manually. OAuth 2.0 access tokens are short lived and are only valid for one hour. Any new connections or logins created after this time fail. For this reason, we recommend that you use automatic IAM database authentication for long-lived processes or applications that rely on connection pooling.