Cloud SQL IAM Authentication - Terraform

Cloud SQL IAM Authentication - Terraform Image Description: A Comprehensive Guide to Choosing the Right Cloud Provider Cloud SQL provides two methods for authentication:

• Built-in authentication using username & password.

• IAM authentication using an access token to authenticate an IAM User or Service Account.
  

By default Cloud SQL creates a default admin user with superadmin privileges to access the database. Any subsequent user can be provisioned manually through the console or through an automated method (Infrastructure-as-Code/API/CLI). Built-in authentication provides users with a cloudsqlsuperuser role. The role provides super user permissions to the user. In most cases the default permissions provided to the user are more than necessary. These credentials also introduce the following issues:

• Accountability: Providing a set of credentials to multiple applications or users can make it hard to locate the source of queries or an attack.

• Password Rotation: Passwords have to be rotated on a frequent basis to reduce vulnerability to password-based attacks and exploits.

• Administration: Managing the overhead of provisioning multiple users and granting credentials can be hard to scale.

IAM Authentication alleviates the need for long-lived credentials, passwords and ensures the user’s activity is tracked in logs. To enable IAM authentication for users and service accounts, the IAM authentication flag has to be switched on.

Provisioning IAM users — Terrafrom

An IAM user needs to be provisioned on the database and provided a role for the user to be able to access the database and perform actions against it.

Provisioning user

resource "google_sql_user" "iam_user" {  name     = "firtname.lastname@evilcorp.com"  instance = "${google_sql_database_instance.master.name}"  type     = "CLOUD_IAM_USER"}

By default an IAM user needs to be provided a “Cloud SQL Instance user” role for the user to login to the provisioned database. The role is limited to login permissions and does not allow for users to perform actions against the database.

Provisioning access for service accounts

Note: As a pre-requisite, provision 2 service accounts in GCP and provide both with “cloudsql.instances.login” and “cloudsql.instances.get” permissions. The two service accounts can be named after their capabilities respectively:

1. Read Only Service Account

2. Read Write Service Account

These service accounts can then be provisioned on the Cloud SQL database to allow for permissions to access the Database and perform their respective actions. Applications that require read-only access will be provided the “Read Only” service account, and applications that require read-write access will be provided the “Read Write” service account.

Once the service accounts are provisioned in GCP, Terraform can be used to provision the service accounts on the Cloud SQL instance.

#Adding a read only IAM service accountresource "google_sql_user" "iam_read_service_account_user" {  name     = replace(var.read_only_sa, ".gserviceaccount.com", "")  instance = "${google_sql_database_instance.master.name}"  type     = "CLOUD_IAM_SERVICE_ACCOUNT"}

#Adding a read write IAM service accountresource "google_sql_user" "iam_read_write_service_account_user" {  name     = replace(var.read_write_sa, ".gserviceaccount.com", "")  instance = "${google_sql_database_instance.master.name}"  type     = "CLOUD_IAM_SERVICE_ACCOUNT"}

Note: By default service accounts provisioned on the Cloud SQL database do not receive any permissions.

Users and service accounts can be attached roles specific to their function. We can categorize applications under two roles:

• Read only

• Read Write

Provisioning Roles for service accounts

• With your Postgres admin user, authenticate against Cloud SQL instance.

• Run the following commands:

CREATE ROLE "read-only";CREATE ROLE "read-write";

Granting SQL role read access

• With your Postgres admin user, authenticate against Cloud SQL instance.

• Connect to relevant Database (for eg: \c PROD-DB)

• Grant permissions on relevant schema in Database:

GRANT CONNECT ON DATABASE <database-name> TO "read-only";GRANT USAGE ON SCHEMA <schema-name> to "read-only";GRANT SELECT ON ALL TABLES IN SCHEMA <schema-name> TO "read-only";

Granting SQL role read-write access

• With your Postgres admin user, authenticate against Cloud SQL instance.

• Connect to relevant Database (for eg: \c PROD-DB)

• Grant permissions on relevant schema in Database:

GRANT CONNECT ON DATABASE <db-name> TO "read-write"; GRANT USAGE ON SCHEMA <schema-name> TO "read-write"; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema-name> TO "read-write"; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema-name> TO "read-write"; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema-name> TO "read-write"; GRANT ALL PRIVILEGES ON DATABASE <db-name> TO "read-write";

Granting SQL roles to Service Accounts

GRANT "read-only" TO "app-readonly@<project-id>.iam";GRANT "read-write" TO "app-readwrite@<project-id>.iam";

After granting the roles to respective service accounts, your applications can consume these roles and authenticate against Cloud SQL to perform their required function.