CloudSQL with IAM Database Authentication

Recently learned about this feature, but the documentation was in a handful of different places on Google docs. Let me just say, its fantastic! No more rounds of pass the parcel password in Slack. Normally, our small team passes around DB passwords and it drives me up the wall. Setting this up allows any dev to access the DB with their GCP account. It’s available for PostgreSQL and MySQL, but, unfortunately, not for MSSQL.

Enabling IAM Authentication #

By default, IAM authentication is disabled when creating a CloudSQL database. It is, however, possible to enable this option during creation when using the gcloud command. For the examples below, it is assumed there is already a CloudSQL instance running.

For a quick check, run the following, and it will display if any databaseFlags are currently set.

gcloud sql instances describe ${INSTANCE_NAME} | grep databaseFlags

Chances are there won’t be any options to display. If there are, run the command again without the grep part to see which.

Make sure you’re on an account with proper credentials to modify CloudSQL, and run the commands based on the database type.

PostgreSQL #

gcloud sql instances patch ${INSTANCE_NAME} \
    --database-flags=cloudsql.iam_authentication=on

MySQL #

For some reason it’s not in the list of available flags in their documentation. However, it can still be enabled with the following command:

gcloud sql instances patch ${INSTANCE_NAME} \
    --database-flags=cloudsql_iam_authentication=on

Yes, its almost identical to the PostgreSQL command, except the flag is slightly different. Notice its cloudsql_ and not cloudsql..

Adding Users #

There’s a slight difference when adding users to the SQL database, and this is based on the principals type. Whether or not you’re adding in a Service Account or an IAM user. If you do this within the console, there’s no need for this differentiation, but on the command line, it must be specified.

Cloud IAM Users #

gcloud sql users create ${IAM_USER_EMAIL} \
    --instance testdb-psql \
    --type CLOUD_IAM_USER

Service Accounts #

Let’s quickly create and switch to a service account. If there is already a service account to be used, be sure they have the following roles assigned:

• roles/cloudsql.instanceUser allows user access to CloudSQL
• roles/cloudsql.client allows connections by CloudSQL Proxy

The following will create a service account on the project and bind the required roles to it.

PROJECT=redacted
SA_NAME=dummysa
SA_EMAIL="${SA_NAME}@${PROJECT}.iam.gserviceaccount.com"

# create account
gcloud iam service-accounts create ${SA_NAME} \
    --display-name "dummy service account for sql demo"

# bind policy(s)
gcloud projects add-iam-policy-binding ${PROJECT} \
    --member serviceAccount:${SA_EMAIL} \
    --role roles/cloudsql.instanceUser

gcloud projects add-iam-policy-binding ${PROJECT} \
    --member serviceAccount:${SA_EMAIL} \
    --role roles/cloudsql.client

Adding the service account to the SQL instance is the same except for its --type.

# add user to DB
gcloud sql users create "${SA_NAME}@${PROJECT}.iam" \
    --instance ${INSTANCE_NAME} \
    --type CLOUD_IAM_SERVICE_ACCOUNT

# generate key
gcloud iam service-accounts keys create ./mykey.json --iam-account ${SA_EMAIL}

# switch config to user
gcloud auth activate-service-account ${SA_EMAIL} --key-file mykey.json

Running the gcloud sql users create command automatically runs a CREATE USER command on the database.

Accessing the Server #

It’s highly recommended to use cloud_sql_proxy to connect to the database. It will automatically handle the password by generating a login token and passing it to the DB on connection. However, token/passwords can be generated manually if cloud_sql_proxy is not available.

gcloud sql generate-login-token

Examples use the psql and the mysql commands, but can be applied to any database connection software.

PostgreSQL and Service Account #

# service account
psql -h 127.0.0.1 -U ${SA_NAME}@${PROJECT}.iam -d postgres

MySQL and IAM User #

# iam user
mysql -h 127.0.0.1 -u ${IAM_USER_EMAIL}

Granting Permission #

By default, users can log into the database, but do not have any access. Tweak as necessary, the following will give all access to the user.

-- PostgreSQL
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO iam_user_account;

-- MySQL
GRANT ALL PRIVILEGES ON *.* TO 'iam_user_account'@'localhost';