------------------------------
Title: Using AlloyDB Go connector for automatic IAM authentication (service account)
[content]
IntroductionAlloyDB 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 ProxyAlloyDB Language connectorsAlloyDB Auth ProxyIAM-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 connectorsAlloyDB 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.PurposeIf 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.AssumptionsGCP Project is already createdVPC network exists for this project and has a subnet defined for GCE/GKEAlloyDB, Service Networking, Compute APIs are enabledStepsCreate a service accountThis 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_IDread -p "region: " REGIONread -p "serviceaccount: " SERVICEACCOUNTgcloud iam service-accounts create ${SERVICEACCOUNT} --display-name="alloydb service-account" --project ${PROJECT_ID}Add roles to the service accountAs 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 accessUse 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 connectionread -p "region : " REGIONread -p "projectid : " PROJECT_IDread -p "postgres_password: " PASSWORDread -p "vpc network: " VPC_NETWORKgcloud 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 servicePrivate connection enables private access for AlloyDB Instancesgcloud services vpc-peerings connect \     --service=servicenetworking.googleapis.com \     --ranges=alloydbpsa \     --network=$VPC_NETWORK  \--project ${PROJECT_ID}Create AlloyDB Cluster and Primary InstanceBelow commands will create an AlloyDB Cluster and Instance. Please update the cluster and Instance names as per requirements.read -p "region : " REGIONread -p "projectid : " PROJECT_IDread -p "postgres_password: " PASSWORDread -p "vpc network: " VPC_NETWORKgcloud 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=onCreate a AlloyDB user with name same as the service accountUsername must be a same as the service account leaving the suffix “.gserviceaccount.com” and authentication type must be “IAM_BASED”.read -p "serviceaccount: " SERVICEACCOUNTgcloud 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 rolesConnect 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 SAgrant all privileges on database application to "${SERVICEACCOUNT}@${PROJECT_ID}.iam";Create GCE InstanceCreate GCE spot Instance (experimentation purposes only) where our application will be deployed.read -p "GCE subnet:" GCE_SUBNETgcloud 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 ConnectorUpdate the definition of connectPostgres() in your application to the below.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 databaseAssuming 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 VMThis is just an example for demonstration.###Suppose you are in application directoryread -p "region : " REGIONread -p "projectid : " PROJECT_IDread -p "serviceaccount: " SERVICEACCOUNTexport DBNAME=applicationexport DBUSER=${SERVICEACCOUNT}@${PROJECT_ID}.iamexport 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/alloydbconngo get github.com/jackc/pgx/v5/pgxpoolgo run ./main.goApplication can successfully connect to database to read/write data.For applications deployed on GKEYou 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 TakeawaysIf 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 usealloydb-java-connector/alloydb-jdbc-connector/src/test/java/com/google/cloud/alloydb/AlloyDbJdbcConnectorDataSourceFactory.java at 80864f2b1e3548f3dbfa87f87c95f42f79d363d5 · GoogleCloudPlatform/alloydb-java-connectorand 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.goYou can configure AlloyDB to accept the connections only through connectors i.e connector enforcement.Using AlloyDB Go connector for automatic IAM authentication (service account) was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
[/content]

Author: Harinderjit Singh
PublishedDate: 2024-04-05
Category: Blogs
NewsPaper: Google Cloud - Medium
Tags: go, google-cloud-platform, data, postgres, alloydb