Using AlloyDB Go connector for automatic IAM authentication (service account)

Introduction

AlloyDB is a fully managed PostgreSQL compatible database service for your most demanding enterprise workloads. AlloyDB combines the best of Google with PostgreSQL, for superior performance, scale, and availability.

Since AlloyDB is PostgreSQL compatible, you can use pgxpool and HikariCP for connection pooling in “Go” and “Java” applications respectively.

There are two prominent ways to connect securely to AlloyDB:

• AlloyDB Auth Proxy
• AlloyDB Language connectors

AlloyDB Auth Proxy

• IAM-based connection authorization (AuthZ): The Auth Proxy uses the credentials and permissions of an IAM principal to authorize connections to AlloyDB instances.
• Secure, encrypted communication: The Auth Proxy automatically creates, uses, and maintains a TLS 1.3 connection using a 256-bit AES cipher between your client and an AlloyDB instance.

AlloyDB Language connectors

AlloyDB connectors are the language specific libraries for connecting securely to your AlloyDB instances. Using an AlloyDB connector provides the following additional benefits (besides the ones provided by AlloyDB Auth Proxy) :

• Convenience: removes the requirement to use and distribute SSL certificates, as well as manage firewalls or source/destination IP addresses. You also don’t need to manage a separate Auth proxy container or process.
• (optionally) IAM DB Authentication: provides support for AlloyDB’s automatic IAM DB AuthN feature. That means you can configure service accounts to connect to database. For applications deployed on GKE you can use workload identity to authenticate to the backend AlloyDB database.

AlloyDB Language connectors are available for Go, Java and Python at this time.

Purpose

If you’re deploying a Go application on GCE/GKE and want to streamline secure connections to your AlloyDB database, the AlloyDB Go connector with IAM authentication is the way to go.

In this post I will walk you through the process of configuring your application and AlloyDB Instance for using AlloyDB Go connector such that your application can use a service account to connect to AlloyDB database. You don’t need to configure any vault to store the DB user password, no need to store any keyfile for the service account.

We are considering a hypothetical Go application (example) which will be deployed on GCE for this article.

Assumptions

1. GCP Project is already created
2. VPC network exists for this project and has a subnet defined for GCE/GKE
3. AlloyDB, Service Networking, Compute APIs are enabled

Steps

Create a service account

This is the service account which will be configured as AlloyDB user and will be used by application to connect to database.

read -p "project_id: " PROJECT_ID
read -p "region: " REGION
read -p "serviceaccount: " SERVICEACCOUNT
gcloud iam service-accounts create ${SERVICEACCOUNT} --display-name="alloydb service-account" --project ${PROJECT_ID}

Add roles to the service account

As per https://cloud.google.com/alloydb/docs/manage-iam-authn#role, we need to assign roles alloydb.client, alloydb.databaseUser and serviceusage.serviceUsageConsumer to the service account.

gcloud projects add-iam-policy-binding ${PROJECT_ID} --member='serviceAccount:${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com' --role='roles/alloydb.client'
gcloud projects add-iam-policy-binding ${PROJECT_ID} --member='serviceAccount:${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com' --role='roles/alloydb.databaseUser'
gcloud projects add-iam-policy-binding ${PROJECT_ID} --member='serviceAccount:${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com' --role='roles/serviceusage.serviceUsageConsumer'

Create private service access

Use Private Services Access to connect to AlloyDB service.

Private services access requires you to first allocate an internal IPv4 address range and then create a private connection

read -p "region : " REGION
read -p "projectid : " PROJECT_ID
read -p "postgres_password: " PASSWORD
read -p "vpc network: " VPC_NETWORK

gcloud compute addresses create alloydbpsa \
    --global \
    --purpose=VPC_PEERING \
    --prefix-length=16 \
    --description="Private service access" \
    --network=$VPC_NETWORK \
 --project ${PROJECT_ID}

Create VPC private connection to alloydb service

Private connection enables private access for AlloyDB Instances

gcloud services vpc-peerings connect \
     --service=servicenetworking.googleapis.com \
     --ranges=alloydbpsa \
     --network=$VPC_NETWORK  \
--project ${PROJECT_ID}

Create AlloyDB Cluster and Primary Instance

Below commands will create an AlloyDB Cluster and Instance. Please update the cluster and Instance names as per requirements.

read -p "region : " REGION
read -p "projectid : " PROJECT_ID
read -p "postgres_password: " PASSWORD
read -p "vpc network: " VPC_NETWORK
gcloud alloydb clusters create alloydb-cls-$(date +%d%m%Y) \
--region=${REGION} --password=$PASSWORD --network=${VPC_NETWORK} \
--project=${PROJECT_ID}

gcloud beta alloydb instances create alloydb-ins-primary-$(date +%d%m%Y) \
--cluster=alloydb-cls-$(date +%d%m%Y)  --region=${REGION} \
--instance-type=PRIMARY --cpu-count=2 \
--database-flags=alloydb.iam_authentication=on,alloydb.enable_auto_explain=on \
--availability-type=ZONAL --project=${PROJECT_ID}

Notice that AlloyDB Instance has flag alloydb.iam_authentication set to on. This flag enables IAM authentication on an AlloyDB instance. If you already have an AlloyDB Instance, you can use below command to enable this flag.

gcloud alloydb instances update $INSTANCE --cluster=$CLUSTER \
 --region=$REGION --database-flags=alloydb.iam_authentication=on

Create a AlloyDB user with name same as the service account

Username must be a same as the service account leaving the suffix “.gserviceaccount.com” and authentication type must be “IAM_BASED”.

read -p "serviceaccount: " SERVICEACCOUNT
gcloud alloydb users create ${SERVICEACCOUNT}@${PROJECT_ID}.iam \
--cluster=alloydb-cls-$(date +%d%m%Y) --type=IAM_BASED \
--region=${REGION} --project=${PROJECT_ID}

Create Application schema and grant appropriate roles

Connect to AlloyDB database postgres using a BUILTIN user (postgres) to create “application” database and grant appropriate roles to AlloyDB IAM user on “application” database.

To connect you may use AlloyDB SQL studio or psql.

create database application;
---replace the ${SERVICEACCOUNT}@${PROJECT_ID}.iam with actual SA
grant all privileges on database application to "${SERVICEACCOUNT}@${PROJECT_ID}.iam";

Create GCE Instance

Create GCE spot Instance (experimentation purposes only) where our application will be deployed.

read -p "GCE subnet:" GCE_SUBNET
gcloud compute instances create instance-$(date +%d%m%Y) \
--project=${PROJECT_ID} --zone=${REGION}-a --machine-type=e2-medium \
--network-interface=network-tier=PREMIUM,stack-type=IPV4_ONLY,subnet=$GCE_SUBNET \
--provisioning-model=SPOT --service-account=${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com \
--scopes=https://www.googleapis.com/auth/cloud-platform \
--create-disk=auto-delete=yes,boot=yes,device-name=instance-$(date +%d%m%Y)-230433,image=projects/debian-cloud/global/images/debian-12-bookworm-v20240213,mode=rw,size=10,type=projects/${PROJECT_ID}/zones/${REGION}-a/diskTypes/pd-balanced \
--no-shielded-secure-boot --shielded-vtpm --shielded-integrity-monitoring \
--labels=goog-ec-src=vm_add-gcloud --reservation-affinity=any \
--preemptible --metadata=startup-script='#! /bin/bash
  apt update -y
  apt -y install golang unzip git'

Notice that we bind the service account (DB User Service Account) that we created earlier to the GCE VM.

Now any process running on GCE VM can use that service account to authenticate to allowed APIs using assigned roles.

Connecting to postgres using pgx pool (no connector)

To make a connection to your database you would typically be using a function such as below. This uses pgxpool package to create a connection pool.

func connectPostgres() (*pgxpool.Pool, error) {
 ctx := context.Background()
 var (
  dsn      string
  dbname   = os.Getenv("DBNAME")
  user     = os.Getenv("DBUSER")
  host     = os.Getenv("PGHOSTNAME")
  password = os.Getenv("PGPASSWORD")
  err      error
 )

 dsn = fmt.Sprintf(
  //user=jack password=secret host=pg.example.com port=5432 dbname=mydb sslmode=verify-ca pool_max_conns=10
  // connection instead.
  "user=%s password=%s dbname=%s sslmode=disable host=%s",
  user, password, dbname, host,
 )
 config, err := pgxpool.ParseConfig(dsn)
 if err != nil {
  return nil, fmt.Errorf("failed to parse pgx config: %v", err)
 }
 // Establish the connection.
 pool, connErr := pgxpool.NewWithConfig(ctx, config)
 if connErr != nil {
  return nil, fmt.Errorf("failed to connect: %s", connErr)
 }
 return pool, nil

}

This requires you to define environment variables DBNAME, DBUSER, PGHOSTNAME and PGPASSWORD.

If you want to encrypt the data in transit, you have to configure sslmode and configure TLS certificates to encrypt the data in transit. You will need to manage the certificates on the application host.

Connecting to postgres using pgx pool and Go Connector

Update the definition of connectPostgres() in your application to the below. File containing the function definition must have “cloud.google.com/go/alloydbconn” in import section.

func connectPostgres() (*pgxpool.Pool, error) {
 ctx := context.Background()
// export DBNAME=application
// export DBUSER=${SERVICEACCOUNT}@${PROJECT_ID}.iam
// export INSTURI=projects/${PROJECT_ID}/locations/${REGION}/clusters/alloydb-cls--$(date +%d%m%Y)/instances/alloydb-ins-primary--$(date +%d%m%Y)
 var (
  dsn     string
  dbname  = os.Getenv("DBNAME")
  user    = os.Getenv("DBUSER")
  instURI = os.Getenv("INSTURI")
 )
// A Dialer can be configured to connect to an AlloyDB instance 
// using automatic IAM database authentication with the WithIAMAuthN Option.
 d, err := alloydbconn.NewDialer(ctx, alloydbconn.WithIAMAuthN())
if err != nil {
  return nil, fmt.Errorf("failed to init Dialer: %v", err)
 }
 dsn = fmt.Sprintf(
  // sslmode is disabled, because the Dialer will handle the SSL
  // connection instead.
  "user=%s  dbname=%s sslmode=disable",
  user, dbname,
 )
 config, err := pgxpool.ParseConfig(dsn)
 if err != nil {
  return nil, fmt.Errorf("failed to parse pgx config: %v", err)
 }
 // Tell pgx to use alloydbconn.Dialer to connect to the instance.
 config.ConnConfig.DialFunc = func(ctx context.Context, _ string, _ string) (net.Conn, error) {
  return d.Dial(ctx, instURI)
 }
 // Establish the connection.
 pool, connErr := pgxpool.NewWithConfig(ctx, config)
 if connErr != nil {
  return nil, fmt.Errorf("failed to connect: %s", connErr)
 }
 return pool, nil
}

This code excerpt is where the AlloyDB Go Connector is configured for pgxpool connection pool. alloydbconn.WithIAMAuthN() allows us to use IAM authentication when creating a connection.

We pass DBNAME, DBUSER and INSTURI as environment variables. “sslmode” is disabled, because the Dialer handles the SSL. You don’t need to manage any certificates and yet the data in transit is encrypted.

You would notice that we did not pass DB user password (PGPASSWORD) as parameter. Also we didn’t have to pass the IP for AlloyDB Instance as host, instead we used the Instance URI.

Below call to function connectPostgres() creates a connection pool.

 db, err := connectPostgres()
 if err != nil {
  return err
 }
// just an example
 data, err = getEmployeesPG(db)
 if err != nil {
  log.Printf("func getEmployeesPG: failed to get data: %v", err)
  return err
 }

Then we can use that connection to query the database in our application.

Grant access to IAM user on application database

Assuming there is a table called employees in this database “application”. We need to connect to the application databases as BUILTIN User and need to grant appropriate privileges to the IAM DB user.

grant all privileges on table employees to "${SERVICEACCOUNT}@${PROJECT_ID}.iam";

You will need to manage the permissions on the relations as per your application requirements.

Deploy and Execute the code on GCE VM

This is just an example for demonstration.

###Suppose you are in application directory
read -p "region : " REGION
read -p "projectid : " PROJECT_ID
read -p "serviceaccount: " SERVICEACCOUNT
export DBNAME=application
export DBUSER=${SERVICEACCOUNT}@${PROJECT_ID}.iam
export INSTURI=projects/${PROJECT_ID}/locations/${REGION}/clusters/alloydb-cls-$(date +%d%m%Y)/instances/alloydb-ins-primary-$(date +%d%m%Y)
go get cloud.google.com/go/alloydbconn
go get github.com/jackc/pgx/v5/pgxpool
go run ./main.go

Application can successfully connect to database to read/write data.

For applications deployed on GKE

• You should have a valid docker container image of your application.
• Workload identity must be enabled at the GKE cluster level.
• Main difference is how the GKE uses Kubernetes service account to authenticate to AlloyDB Database on behalf of Google Service account which is also a Database user in this case. This is done using workload identity.
• We assign iam.workloadIdentityUser role to workload identity service account on your google service account.
• This enables your Kubernetes service account (example alloydb-ksa) to retrieve the authentication token as your google service account (example alloydb-sa) which then can be used to authenticate to AlloyDB as IAM_BASED Database user.
• This Kubernetes service account is then used by the application “deployment” kubernetes resource.
• The environment variables such as DBNAME, DBUSER and INSTURI can be defined in a config map and then that config map can be used by the application “deployment” kubernetes resource.

Other Takeaways

• If you application is written in Java, except the connection pool + Java connector code all steps are same. For connection pool using AlloyDB Java connector, you can use

alloydb-java-connector/alloydb-jdbc-connector/src/test/java/com/google/cloud/alloydb/AlloyDbJdbcConn…

and set config.addDataSourceProperty("alloydbEnableIAMAuth", "true");for Automatic IAM Authentication.

• If you desire to use Go connector to connect to your AlloyDB Instance but don’t want to use IAM Authentication, you can use https://github.com/GoogleCloudPlatform/alloydb-go-connector/blob/HEAD/pgxpool_test.go
• You can configure AlloyDB to accept the connections only through connectors i.e connector enforcement.