GCP Cloud SQL Users in Terraform

Those of us who are using Cloud SQL noticed that the user setup is somewhat duplicated. First, you have your users in IAM, and then you have created users in the Cloud SQL instance. Once done, you have to give them the permissions within the database. The situation got better once IAM authentication was introduced. You no longer need to create users in both systems. You are done by just giving permissions within the database to the correct users. 

Terraform implementation

But even this seems to involve 2-3 Terraform resources. First, we define a user who is allowed to access the database. By stating a type attribute, you can enable the IAM access:

resource "google_sql_user" "user" {  
 instance             = "instance"  
 name                = "a_user@gmail.com"  
 project             = "test"  
 sql_server_user_details = []  
 type                 = "CLOUD_IAM_USER"  
}

Then you have to give IAM permissions for accessing the database with Cloud SQL proxy. Those are

• roles/cloudsql.client – needed for Cloud SQL proxy access
• roles/cloudsql.instanceUser– needed for user IAM access

That’s one google_project_iam_member for each permission:

resource "google_project_iam_member" "user" {  
 member  = "a_user@gmail.com"  
 project = "test"  
 role   = "roles/cloudsql.instanceUser"  
}  
resource "google_project_iam_member" "client" {  
 member  = "a_user@gmail.com"  
 project = "test"  
 role   = "roles/cloudsql.client"  
}

Now, when we are done, we also have to give some permissions in the database. I do prefer PSQL. Therefore I used cyrilgdn/postgresql provider. The cool thing is that you do not need to enable IP access permissions. Just use gcppostgres as the scheme for connection. Check GoCloud documentation if you are interested.

Once ready, you can define the permissions in the database:

resource "postgresql_grant" "permissions" {  
 database      = "test"  
 object_type   = "table"  
 privileges    = [  
     "SELECT",  
 ]  
 role          = "a_user@gmail.com"  
 schema        = "public"  
}

This has to be done for each object_type you use. Those are tables, sequences, …

To make it simple

I believe that all of us who had to add users to a database created a module for that. In Ackee, we also needed to create a secret in the Hashicorps Vault to keep the credentials in one place. For IAM users, this is not required.

It is generally more readable to create a map of users where each attribute states an attribute of the user. That means I can have a user with attributes like password, permissions, … Once created, those attributes will be translated to Terraform resources and created in the cloud.

Our module could be used like this:

module "database_users" {  
  source = "git::ssh://git@github.com/AckeeCZ/terraform-gcp-cloud-sql-user.git"  
  users = {  
    "ackee.fella" : {  
      permissions : ["DELETE", "SELECT", "INSERT"]  
      seq_permissions : ["USAGE", "UPDATE", "SELECT"]  
      seq_objects : ["id_seq"]  
    }  
    "reader-sa" : {  
      permissions : ["SELECT"]  
      create_sa : true  
    }  
    "mr.unicorn@ackee.cz" : {  
      permissions : ["SELECT"]  
      type : "CLOUD_IAM_USER"  
    }  
  }  
  vault_secret_path      = "cloud/sql/test/users"  
  database               = "test"  
  postgres_instance_name = "test"  
  project                = "test"  
}

This example creates three users in the database named test:

• The first user is created in the database, there is no IAM setup and a service account is generated. The user receives basic permissions on tables and sequences.
• The second user is an IAM service account. Therefore, a key is going to be generated. The user only receives a SELECT permission on tables.
• The third user is an IAM user with permissions to access the database and also with SELECT permissions in the database.

If a password or GCP IAM service account key is generated, it is saved in the vault under the path cloud/sql/test/users".

Conclusion

A module for creating users is nothing fancy. It gets the work done and makes infrastructure repositories a bit more readable. It wouldn’t even be required if the GCP IAM could manage databases' permissions – as it does for Big Query or Datastore. If interested, check it out at the Hashicorps Terraform module registry.