Editing article

Title

Summary

Content

<h3>Introduction</h3><a href="https://cloud.google.com/alloydb#documentation">AlloyDB</a> 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 <a href="https://github.com/jackc/pgx/">pgxpool</a> and <a href="https://github.com/brettwooldridge/HikariCP/blob/dev/README.md">HikariCP</a> for connection pooling in “Go” and “Java” applications respectively.There are two prominent ways to connect securely to AlloyDB:<ul><li>AlloyDB Auth Proxy</li><li>AlloyDB Language connectors</li></ul><h4>AlloyDB Auth Proxy</h4><ul><li>IAM-based connection authorization (AuthZ): The Auth Proxy uses the credentials and permissions of an IAM principal to authorize connections to AlloyDB instances.</li><li>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.</li></ul><h4>AlloyDB Language connectors</h4>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) :<ul><li>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.</li><li>(optionally) IAM DB Authentication: provides support for <a href="https://github.com/GoogleCloudPlatform/alloydb-go-connector?tab=readme-ov-file#automatic-iam-database-authentication">AlloyDB’s automatic IAM DB AuthN</a> 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.</li></ul>AlloyDB Language connectors are available for Go, Java and Python at this time.<h3>Purpose</h3>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 (<a href="https://github.com/bijeshos/go-postgresql-pgx-example/blob/main/main.go">example</a>) which will be deployed on GCE for this article.<h3>Assumptions</h3><ol><li>GCP Project is already created</li><li>VPC network exists for this project and has a subnet defined for GCE/GKE</li><li>AlloyDB, Service Networking, Compute APIs are enabled</li></ol><h3>Steps</h3><h4>Create a service account</h4>This is the service account which will be configured as AlloyDB user and will be used by application to connect to database.<pre>read -p &quot;project_id: &quot; PROJECT_ID read -p &quot;region: &quot; REGION read -p &quot;serviceaccount: &quot; SERVICEACCOUNT gcloud iam service-accounts create ${SERVICEACCOUNT} --display-name=&quot;alloydb service-account&quot; --project ${PROJECT_ID}</pre><h4>Add roles to the service account</h4>As per <a href="https://cloud.google.com/alloydb/docs/manage-iam-authn#role,">https://cloud.google.com/alloydb/docs/manage-iam-authn#role,</a> we need to assign roles alloydb.client, alloydb.databaseUser and serviceusage.serviceUsageConsumer to the service account.<pre>gcloud projects add-iam-policy-binding ${PROJECT_ID} --member=&#39;serviceAccount:${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com&#39; --role=&#39;roles/alloydb.client&#39; gcloud projects add-iam-policy-binding ${PROJECT_ID} --member=&#39;serviceAccount:${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com&#39; --role=&#39;roles/alloydb.databaseUser&#39; gcloud projects add-iam-policy-binding ${PROJECT_ID} --member=&#39;serviceAccount:${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com&#39; --role=&#39;roles/serviceusage.serviceUsageConsumer&#39;</pre><h4>Create private service access</h4>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<pre>read -p &quot;region : &quot; REGION read -p &quot;projectid : &quot; PROJECT_ID read -p &quot;postgres_password: &quot; PASSWORD read -p &quot;vpc network: &quot; VPC_NETWORK gcloud compute addresses create alloydbpsa \ --global \ --purpose=VPC_PEERING \ --prefix-length=16 \ --description=&quot;Private service access&quot; \ --network=$VPC_NETWORK \ --project ${PROJECT_ID}</pre><h4>Create VPC private connection to alloydb service</h4>Private connection enables private access for AlloyDB Instances<pre>gcloud services vpc-peerings connect \ --service=servicenetworking.googleapis.com \ --ranges=alloydbpsa \ --network=$VPC_NETWORK \ --project ${PROJECT_ID}</pre><h4>Create AlloyDB Cluster and Primary Instance</h4>Below commands will create an AlloyDB Cluster and Instance. Please update the cluster and Instance names as per requirements.<pre>read -p &quot;region : &quot; REGION read -p &quot;projectid : &quot; PROJECT_ID read -p &quot;postgres_password: &quot; PASSWORD read -p &quot;vpc network: &quot; 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}</pre>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.<pre>gcloud alloydb instances update $INSTANCE --cluster=$CLUSTER \ --region=$REGION --database-flags=alloydb.iam_authentication=on</pre><h4>Create a AlloyDB user with name same as the service account</h4>Username must be a same as the service account leaving the suffix “.gserviceaccount.com” and authentication type must be “IAM_BASED”.<pre>read -p &quot;serviceaccount: &quot; SERVICEACCOUNT gcloud alloydb users create ${SERVICEACCOUNT}@${PROJECT_ID}.iam \ --cluster=alloydb-cls-$(date +%d%m%Y) --type=IAM_BASED \ --region=${REGION} --project=${PROJECT_ID}</pre><h4>Create Application schema and grant appropriate roles</h4>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.<pre>create database application; ---replace the ${SERVICEACCOUNT}@${PROJECT_ID}.iam with actual SA grant all privileges on database application to &quot;${SERVICEACCOUNT}@${PROJECT_ID}.iam&quot;;</pre><h4>Create GCE Instance</h4>Create GCE spot Instance (experimentation purposes only) where our application will be deployed.<pre>read -p &quot;GCE subnet:&quot; 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=&#39;#! /bin/bash apt update -y apt -y install golang unzip git&#39;</pre>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.<h4>Connecting to postgres using pgx pool (no connector)</h4>To make a connection to your database you would typically be using a function such as below. This uses <a href="https://pkg.go.dev/github.com/jackc/pgx/v5/pgxpool#pkg-overview">pgxpool package</a> to create a connection pool.<pre>func connectPostgres() (*pgxpool.Pool, error) { ctx := context.Background() var ( dsn string dbname = os.Getenv(&quot;DBNAME&quot;) user = os.Getenv(&quot;DBUSER&quot;) host = os.Getenv(&quot;PGHOSTNAME&quot;) password = os.Getenv(&quot;PGPASSWORD&quot;) 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. &quot;user=%s password=%s dbname=%s sslmode=disable host=%s&quot;, user, password, dbname, host, ) config, err := pgxpool.ParseConfig(dsn) if err != nil { return nil, fmt.Errorf(&quot;failed to parse pgx config: %v&quot;, err) } // Establish the connection. pool, connErr := pgxpool.NewWithConfig(ctx, config) if connErr != nil { return nil, fmt.Errorf(&quot;failed to connect: %s&quot;, connErr) } return pool, nil }</pre>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.<h4>Connecting to postgres using pgx pool and Go Connector</h4>Update the definition of connectPostgres() in your application to the below.<pre>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(&quot;DBNAME&quot;) user = os.Getenv(&quot;DBUSER&quot;) instURI = os.Getenv(&quot;INSTURI&quot;) ) // 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(&quot;failed to init Dialer: %v&quot;, err) } dsn = fmt.Sprintf( // sslmode is disabled, because the Dialer will handle the SSL // connection instead. &quot;user=%s dbname=%s sslmode=disable&quot;, user, dbname, ) config, err := pgxpool.ParseConfig(dsn) if err != nil { return nil, fmt.Errorf(&quot;failed to parse pgx config: %v&quot;, 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(&quot;failed to connect: %s&quot;, connErr) } return pool, nil }</pre>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.<pre> db, err := connectPostgres() if err != nil { return err } // just an example data, err = getEmployeesPG(db) if err != nil { log.Printf(&quot;func getEmployeesPG: failed to get data: %v&quot;, err) return err }</pre>Then we can use that connection to query the database in our application.<h4>Grant access to IAM user on application database</h4>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.<pre> grant all privileges on table employees to &quot;${SERVICEACCOUNT}@${PROJECT_ID}.iam&quot;;</pre>You will need to manage the permissions on the relations as per your application requirements.<h4>Deploy and Execute the code on GCE VM</h4>This is just an example for demonstration.<pre>###Suppose you are in application directory read -p &quot;region : &quot; REGION read -p &quot;projectid : &quot; PROJECT_ID read -p &quot;serviceaccount: &quot; 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</pre>Application can successfully connect to database to read/write data.<h3>For applications deployed on GKE</h3><ul><li>You should have a valid docker container image of your application.</li><li>Workload identity must be enabled at the GKE cluster level.</li><li>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 <a href="https://cloud.google.com/sql/docs/mysql/connect-kubernetes-engine#workload-identity">workload identity</a>.</li><li>We assign iam.workloadIdentityUser role to workload identity service account on your google service account.</li><li>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.</li><li>This Kubernetes service account is then used by the application “deployment” kubernetes resource.</li><li>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.</li></ul><h3>Other Takeaways</h3><ul><li>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</li></ul><a href="https://github.com/GoogleCloudPlatform/alloydb-java-connector/blob/HEAD/alloydb-jdbc-connector/src/test/java/com/google/cloud/alloydb/AlloyDbJdbcConnectorDataSourceFactory.java">alloydb-java-connector/alloydb-jdbc-connector/src/test/java/com/google/cloud/alloydb/AlloyDbJdbcConnectorDataSourceFactory.java at 80864f2b1e3548f3dbfa87f87c95f42f79d363d5 · GoogleCloudPlatform/alloydb-java-connector</a>and set config.addDataSourceProperty(&quot;alloydbEnableIAMAuth&quot;, &quot;true&quot;);for Automatic IAM Authentication.<ul><li>If you desire to use Go connector to connect to your AlloyDB Instance but don’t want to use IAM Authentication, you can use <a href="https://github.com/GoogleCloudPlatform/alloydb-go-connector/blob/HEAD/pgxpool_test.go">https://github.com/GoogleCloudPlatform/alloydb-go-connector/blob/HEAD/pgxpool_test.go</a></li><li>You can configure AlloyDB to accept the connections only through connectors i.e <a href="https://cloud.google.com/alloydb/docs/enforce-connectors">connector enforcement</a>.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ec29c4ee5d2b" width="1" height="1" alt=""><hr><a href="https://medium.com/google-cloud/using-alloydb-connector-for-automatic-iam-authentication-service-account-ec29c4ee5d2b">Using AlloyDB Go connector for automatic IAM authentication (service account)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

Author

Link

Published date

Image url

Feed url

Guid

Hidden blurb

--- !ruby/object:Feedjira::Parser::RSSEntry
title: Using AlloyDB Go connector for automatic IAM authentication (service account)
published: 2024-04-05 04:49:36.000000000 Z
categories:
- go
- google-cloud-platform
- data
- postgres
- alloydb
entry_id: !ruby/object:Feedjira::Parser::GloballyUniqueIdentifier
 is_perma_link: 'false'
 guid: https://medium.com/p/ec29c4ee5d2b
carlessian_info:
 news_filer_version: 2
 newspaper: Google Cloud - Medium
 macro_region: Blogs
content: '<h3>Introduction</h3><a href="https://cloud.google.com/alloydb#documentation">AlloyDB</a>
 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 <a href="https://github.com/jackc/pgx/">pgxpool</a> and <a href="https://github.com/brettwooldridge/HikariCP/blob/dev/README.md">HikariCP</a>
 for connection pooling in “Go” and “Java” applications respectively.There
 are two prominent ways to connect securely to AlloyDB:<ul><li>AlloyDB
 Auth Proxy</li><li>AlloyDB Language connectors</li></ul><h4>AlloyDB Auth Proxy</h4><ul><li>IAM-based
 connection authorization (AuthZ): The Auth Proxy uses the credentials and
 permissions of an IAM principal to authorize connections to AlloyDB instances.</li><li>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.</li></ul><h4>AlloyDB Language connectors</h4>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) :<ul><li>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.</li><li>(optionally) IAM
 DB Authentication: provides support for <a href="https://github.com/GoogleCloudPlatform/alloydb-go-connector?tab=readme-ov-file#automatic-iam-database-authentication">AlloyDB’s
 automatic IAM DB AuthN</a> 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.</li></ul>AlloyDB
 Language connectors are available for Go, Java and Python at this time.<h3>Purpose</h3>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
 (<a href="https://github.com/bijeshos/go-postgresql-pgx-example/blob/main/main.go">example</a>)
 which will be deployed on GCE for this article.<h3>Assumptions</h3><ol><li>GCP
 Project is already created</li><li>VPC network exists for this project and has a
 subnet defined for GCE/GKE</li><li>AlloyDB, Service Networking, Compute APIs are enabled</li></ol><h3>Steps</h3><h4>Create
 a service account</h4>This is the service account which will be configured as
 AlloyDB user and will be used by application to connect to database.<pre>read
 -p &quot;project_id: &quot; PROJECT_ID read -p &quot;region: &quot; REGION read
 -p &quot;serviceaccount: &quot; SERVICEACCOUNT gcloud iam service-accounts create
 ${SERVICEACCOUNT} --display-name=&quot;alloydb service-account&quot; --project ${PROJECT_ID}</pre><h4>Add
 roles to the service account</h4>As per <a href="https://cloud.google.com/alloydb/docs/manage-iam-authn#role,">https://cloud.google.com/alloydb/docs/manage-iam-authn#role,</a>
 we need to assign roles alloydb.client, alloydb.databaseUser and serviceusage.serviceUsageConsumer
 to the service account.<pre>gcloud projects add-iam-policy-binding ${PROJECT_ID}
 --member=&#39;serviceAccount:${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com&#39;
 --role=&#39;roles/alloydb.client&#39; gcloud projects add-iam-policy-binding
 ${PROJECT_ID} --member=&#39;serviceAccount:${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com&#39;
 --role=&#39;roles/alloydb.databaseUser&#39; gcloud projects add-iam-policy-binding
 ${PROJECT_ID} --member=&#39;serviceAccount:${SERVICEACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com&#39;
 --role=&#39;roles/serviceusage.serviceUsageConsumer&#39;</pre><h4>Create private
 service access</h4>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<pre>read -p &quot;region : &quot; REGION read
 -p &quot;projectid : &quot; PROJECT_ID read -p &quot;postgres_password: &quot;
 PASSWORD read -p &quot;vpc network: &quot; VPC_NETWORK gcloud compute
 addresses create alloydbpsa \ --global \ --purpose=VPC_PEERING \ --prefix-length=16
 \ --description=&quot;Private service access&quot; \ --network=$VPC_NETWORK
 \ --project ${PROJECT_ID}</pre><h4>Create VPC private connection to alloydb service</h4>Private
 connection enables private access for AlloyDB Instances<pre>gcloud services
 vpc-peerings connect \ --service=servicenetworking.googleapis.com \ --ranges=alloydbpsa
 \ --network=$VPC_NETWORK \ --project ${PROJECT_ID}</pre><h4>Create AlloyDB
 Cluster and Primary Instance</h4>Below commands will create an AlloyDB Cluster
 and Instance. Please update the cluster and Instance names as per requirements.<pre>read
 -p &quot;region : &quot; REGION read -p &quot;projectid : &quot; PROJECT_ID read
 -p &quot;postgres_password: &quot; PASSWORD read -p &quot;vpc network: &quot;
 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}</pre>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.<pre>gcloud alloydb
 instances update $INSTANCE --cluster=$CLUSTER \ --region=$REGION --database-flags=alloydb.iam_authentication=on</pre><h4>Create
 a AlloyDB user with name same as the service account</h4>Username must be a same
 as the service account leaving the suffix “.gserviceaccount.com” and authentication
 type must be “IAM_BASED”.<pre>read -p &quot;serviceaccount: &quot; SERVICEACCOUNT gcloud
 alloydb users create ${SERVICEACCOUNT}@${PROJECT_ID}.iam \ --cluster=alloydb-cls-$(date
 +%d%m%Y) --type=IAM_BASED \ --region=${REGION} --project=${PROJECT_ID}</pre><h4>Create
 Application schema and grant appropriate roles</h4>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.<pre>create database application; ---replace
 the ${SERVICEACCOUNT}@${PROJECT_ID}.iam with actual SA grant all privileges on
 database application to &quot;${SERVICEACCOUNT}@${PROJECT_ID}.iam&quot;;</pre><h4>Create
 GCE Instance</h4>Create GCE spot Instance (experimentation purposes only) where
 our application will be deployed.<pre>read -p &quot;GCE subnet:&quot; 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=&#39;#!
 /bin/bash apt update -y apt -y install golang unzip git&#39;</pre>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.<h4>Connecting to postgres
 using pgx pool (no connector)</h4>To make a connection to your database you would
 typically be using a function such as below. This uses <a href="https://pkg.go.dev/github.com/jackc/pgx/v5/pgxpool#pkg-overview">pgxpool
 package</a> to create a connection pool.<pre>func connectPostgres() (*pgxpool.Pool,
 error) { ctx := context.Background() var ( dsn string dbname =
 os.Getenv(&quot;DBNAME&quot;) user = os.Getenv(&quot;DBUSER&quot;) host =
 os.Getenv(&quot;PGHOSTNAME&quot;) password = os.Getenv(&quot;PGPASSWORD&quot;) 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. &quot;user=%s
 password=%s dbname=%s sslmode=disable host=%s&quot;, user, password, dbname,
 host, ) config, err := pgxpool.ParseConfig(dsn) if err != nil { return
 nil, fmt.Errorf(&quot;failed to parse pgx config: %v&quot;, err) } // Establish
 the connection. pool, connErr := pgxpool.NewWithConfig(ctx, config) if connErr
 != nil { return nil, fmt.Errorf(&quot;failed to connect: %s&quot;, connErr) 
 } return pool, nil }</pre>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.<h4>Connecting to postgres using pgx pool and Go Connector</h4>Update
 the definition of connectPostgres() in your application to the below.<pre>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(&quot;DBNAME&quot;) user = os.Getenv(&quot;DBUSER&quot;) instURI
 = os.Getenv(&quot;INSTURI&quot;) ) // 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(&quot;failed to init Dialer: %v&quot;,
 err) } dsn = fmt.Sprintf( // sslmode is disabled, because the Dialer
 will handle the SSL // connection instead. &quot;user=%s dbname=%s sslmode=disable&quot;, user,
 dbname, ) config, err := pgxpool.ParseConfig(dsn) if err != nil { return
 nil, fmt.Errorf(&quot;failed to parse pgx config: %v&quot;, 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(&quot;failed
 to connect: %s&quot;, connErr) } return pool, nil }</pre>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.<pre> db, err := connectPostgres() if err != nil { return err 
 } // just an example data, err = getEmployeesPG(db) if err != nil { log.Printf(&quot;func
 getEmployeesPG: failed to get data: %v&quot;, err) return err }</pre>Then
 we can use that connection to query the database in our application.<h4>Grant
 access to IAM user on application database</h4>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.<pre> grant
 all privileges on table employees to &quot;${SERVICEACCOUNT}@${PROJECT_ID}.iam&quot;;</pre>You
 will need to manage the permissions on the relations as per your application requirements.<h4>Deploy
 and Execute the code on GCE VM</h4>This is just an example for demonstration.<pre>###Suppose
 you are in application directory read -p &quot;region : &quot; REGION read
 -p &quot;projectid : &quot; PROJECT_ID read -p &quot;serviceaccount: &quot; 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</pre>Application can successfully connect to database to read/write
 data.<h3>For applications deployed on GKE</h3><ul><li>You should have a valid
 docker container image of your application.</li><li>Workload identity must be enabled
 at the GKE cluster level.</li><li>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 <a href="https://cloud.google.com/sql/docs/mysql/connect-kubernetes-engine#workload-identity">workload
 identity</a>.</li><li>We assign iam.workloadIdentityUser role to workload identity
 service account on your google service account.</li><li>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.</li><li>This Kubernetes service account is
 then used by the application “deployment” kubernetes resource.</li><li>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.</li></ul><h3>Other
 Takeaways</h3><ul><li>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</li></ul><a href="https://github.com/GoogleCloudPlatform/alloydb-java-connector/blob/HEAD/alloydb-jdbc-connector/src/test/java/com/google/cloud/alloydb/AlloyDbJdbcConnectorDataSourceFactory.java">alloydb-java-connector/alloydb-jdbc-connector/src/test/java/com/google/cloud/alloydb/AlloyDbJdbcConnectorDataSourceFactory.java
 at 80864f2b1e3548f3dbfa87f87c95f42f79d363d5 · GoogleCloudPlatform/alloydb-java-connector</a>and
 set config.addDataSourceProperty(&quot;alloydbEnableIAMAuth&quot;, &quot;true&quot;);for
 Automatic IAM Authentication.<ul><li>If you desire to use Go connector to connect
 to your AlloyDB Instance but don’t want to use IAM Authentication, you can use <a
 href="https://github.com/GoogleCloudPlatform/alloydb-go-connector/blob/HEAD/pgxpool_test.go">https://github.com/GoogleCloudPlatform/alloydb-go-connector/blob/HEAD/pgxpool_test.go</a></li><li>You
 can configure AlloyDB to accept the connections only through connectors i.e <a href="https://cloud.google.com/alloydb/docs/enforce-connectors">connector
 enforcement</a>.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ec29c4ee5d2b"
 width="1" height="1" alt=""><hr><a href="https://medium.com/google-cloud/using-alloydb-connector-for-automatic-iam-authentication-service-account-ec29c4ee5d2b">Using
 AlloyDB Go connector for automatic IAM authentication (service account)</a> was
 originally published in <a href="https://medium.com/google-cloud">Google Cloud -
 Community</a> on Medium, where people are continuing the conversation by highlighting
 and responding to this story.'
rss_fields:
- title
- published
- categories
- entry_id
- content
- url
- author
url: https://medium.com/google-cloud/using-alloydb-connector-for-automatic-iam-authentication-service-account-ec29c4ee5d2b?source=rss----e52cf94d98af---4
author: Harinderjit Singh

Language

Active

Ricc internal notes

Ricc source

Show this article Back to articles