Use IAM Conditions

MySQL   |  PostgreSQL   |  SQL Server

IAM Conditions allows you to define and enforce conditional, attribute-based access control for Google Cloud resources, including Cloud SQL instances. For more information about IAM Conditions, see the Overview of IAM Conditions page.

Introduction

In Cloud SQL, you can enforce conditional access based on the following attributes:

• Date/time attributes: Used to set temporary (expiring), scheduled, or limited-duration access to Cloud SQL resources. For example, you can allow a user to access a database instance until a specified date. You can use date/time attributes at any level of the resource hierarchy. For more information, see Configuring temporary access.
• Resource attributes: Used to configure conditional access based on a tag, resource name, resource type, or resource service attribute. In Cloud SQL, you can use attributes of database instances to configure conditional access. For example, you can allow a user to only access instances with a specific tag. For more information, see Configuring resource-based access.

Use cases include:

• Allowing users to connect to specific instances.

• Allowing users to create instances with specific prefixes or suffixes (for example, "test").

• Limiting access to backup operations for test instances

• Allowing users to delete development and test instances, but not production instances.

• Allowing users to perform administrative operations on certain dates or at certain times.

Allow users to connect to specific instances

Suppose you want to let a user or service account have permission to connect to one specific Cloud SQL instance only. You can include an IAM Condition in the IAM policy binding that grants that account the permissions of a Cloud SQL role.

By default, the predefined Cloud SQL Client role (roles/cloudsql.client), which contains the cloudsql.instances.connect permission, authorizes its member to connect to all Cloud SQL instances in a project. By introducing an IAM Condition into the policy binding, you can grant permission to just the named instance.

Console

This example shows how to modify the existing IAM binding for the project to give a service account a Cloud SQL Client role for a specific instance.

This example uses the following variables:

• PROJECT_ID: Your Google Cloud project.
• INSTANCE_ID: The name of the instance you want to grant access to.

1. In the Google Cloud console, go to the IAM page.

   Go to IAM

2. Click Add.
3. In the New Members input box, enter the service account email.
4. Click the Role dropdown list and select the Cloud SQL Client role.
5. Click Add condition.
6. Enter a title and description.
7. Select the Condition Editor tab.
8. In the Condition Builder section:
   • For Condition type - Resource - Name, enter projects/PROJECT_ID/instances/INSTANCE_ID
   • Ensure that the AND conditional is selected.
   • For Condition type - Resource - Service, select sqladmin.googleapis.com.
9. Click Save to save the condition.
10. Click Save to save the policy.

gcloud

This example shows how to modify the existing IAM policy binding for the project to give a specific service account the Cloud SQL Client role, but only for a specific instance.

This example uses the following variables:

• PROJECT_ID: Your Google Cloud project.
• INSTANCE_ID: The name of the instance you want to grant access to.
• SERVICE_ACCOUNT_EMAIL: The complete email address of the service account whose access you want to modify.

1. Get the existing IAM policy bindings and output it to the file bindings.json:

gcloud projects get-iam-policy PROJECT_ID --format=json > bindings.json

2. Add the following conditional role binding to the bindings.json file:

   {
     "bindings": [
       {
         "role": "roles/cloudsql.client",
         "members": [
           "serviceAccount:SERVICE_ACCOUNT_EMAIL"
         ],
         "condition": {
           "expression": "resource.name == 'projects/PROJECT_ID/instances/INSTANCE_ID'
             && resource.service == 'sqladmin.googleapis.com'"
         }
       }
     ],
     "etag": "BwWKmjvelug=",
     "version": 3
   }
   

3. Update the IAM policy with the new bindings.json file.

   gcloud projects set-iam-policy PROJECT_ID bindings.json
   

Terraform

To allow users to connect to specific instances, use a Terraform google_iam_policy data resource and a google_project_iam_policy Terraform resource.

If you delete the google_project_iam_policy resource, anyone who doesn't have organization-level access to your Google Cloud project is locked out of the project. As a result, they can't access any resources associated with the project.

Use this resource only for Google Cloud projects that are fully managed by Terraform. If you use the resource, we strongly recommend that you import the policy before deleting it. This way, if any issues occur, you can reinstate the policy.

data "google_iam_policy" "sql_iam_policy" {
  binding {
    role = "roles/cloudsql.client"
    members = [
      "serviceAccount:${google_project_service_identity.gcp_sa_cloud_sql.email}",
    ]
    condition {
      expression  = "resource.name == 'projects/${data.google_project.project.project_id}/instances/${google_sql_database_instance.default.name}' && resource.type == 'sqladmin.googleapis.com/Instance'"
      title       = "created"
      description = "Cloud SQL instance creation"
    }
  }
}

resource "google_project_iam_policy" "project" {
  project     = data.google_project.project.project_id
  policy_data = data.google_iam_policy.sql_iam_policy.policy_data
}

Apply the changes

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly created main.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the -upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Note: Terraform samples typically assume that the required APIs are enabled in your Google Cloud project.

Delete the changes

To delete your changes, do the following:

1. To disable deletion protection, in your Terraform configuration file set the deletion_protection argument to false.

   deletion_protection =  "false"

2. Apply the updated Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

3. Remove resources previously applied with your Terraform configuration by running the following command and entering yes at the prompt:

   terraform destroy

Limit access to backup operations for test instances

Suppose your service's topology is configured so that all test instances have a prefix of test (for example, test-instance-1), and all production instances have a prefix of prod (for example, prod-instance-1).

You can limit access to backup operations to your test instances for a user or a service account. Limiting access includes restricting CREATE, GET, LIST, or DELETE operations to backups for your test instances.

Console

1. In the Google Cloud console, go to the IAM page.

   Go to IAM

2. Click the PRINCIPALS tab.
3. Locate the user's email address or service account (principal) to which you want to restrict access.
4. Click the Edit principal icon to the right of the principal. This icon appears as a pencil.
5. In the Edit permissions dialog box, click ADD ANOTHER ROLE.

6. In the Filter field of the subsequent dialog box, enter Cloud SQL Admin. Then, select the Cloud SQL Admin role that appears.

   The Edit permissions dialog box is active, and the Cloud SQL Admin role now appears in the dialog box.

7. To the right of the Cloud SQL Admin role, click the Add condition link.
8. In the Edit condition dialog box, supply the following information:
   1. In the Title field, enter a name for the condition that you're adding to limit access to backup operations for test instances. For example, you can enter Limit access to backup operations.

   2. Click the CONDITION EDITOR tab, and then add the following condition:

      resource.type == "sqladmin.googleapis.com/BackupRun" && 
      resource.name.startsWith("projects/PROJECT_ID/instances/test")
          

      This condition limits the scope of the Cloud SQL Admin role for the principal that you selected to those resources that have resource names that start with projects/PROJECT_ID/instances/test. Also, replace the PROJECT_ID placeholder variable with the name of your Google Cloud project.

9. Click SAVE.
10. In the Edit permissions dialog box, click SAVE.

gcloud

This example uses the following variables:

• PROJECT_ID: Your Google Cloud project.
• USER_EMAIL: The user's email address.
• SERVICE_ACCOUNT_EMAIL:The complete email address of the service account whose access you want to limit.

1. Limit the scope of the cloudsql.admin role for a user who has an email address of USER_EMAIL.

   The scope of the role is limited to those resources that have resource names that start with projects/PROJECT_ID/instances/test.

   gcloud projects add-iam-policy-binding PROJECT_ID \ --member=user:USER_EMAIL \
   --role=roles/cloudsql.admin \
   --condition=expression="resource.type == \"sqladmin.googleapis.com/BackupRun\" && resource.name.startsWith(\"projects/PROJECT_ID/instances/test-instance-1\")",title="test"
       

OR

2. Limit the scope of the cloudsql.admin role for a user who's logged in with a service account of SERVICE_ACCOUNT_EMAIL.

   gcloud projects add-iam-policy-binding PROJECT_ID \ --member=serviceAccount:SERVICE_ACCOUNT_EMAIL \
   --role=roles/cloudsql.admin \
   --condition=expression="resource.type == \"sqladmin.googleapis.com/BackupRun\" && resource.name.startsWith(\"projects/PROJECT_ID/instances/test-instance-1\")",title="test"
       

Allow users to delete test instances, but not production instances

Suppose you want to allow a service account to delete test instances, but not production instances. You can do this by using tags, and by adding the following two policy bindings for the service account:

• A Cloud SQL Editor role on the resource where you granted the role, and its descendants. If granted on the project the role applies to all of the instances in the project. The Cloud SQL Editor role does not contain the cloudsql.instances.delete permission.
• A Cloud SQL Admin role on instances with the test tag.

Console

1. In the Google Cloud console, go to the IAM page.

   Go to IAM

2. Click Add.
3. In the New Members field, enter the service account email.
4. Click the Role dropdown list and select the Cloud SQL Editor role. Add nothing further for this role.
5. Click Save to save the condition.
6. Click the Role menu for the same account and select the Cloud Cloud SQL Admin role.
7. Click Add condition.
8. Enter a title and description.
9. Select the Condition Editor tab.
10. In the Condition Builder section:
    • For Condition type - Resource - Name, enter a name for the condition.
    • For Condition type - Resource - Service, select sqladmin.googleapis.com.
    • For Condition type - Resource - Tag, enter the Tag key namespaced name. For this example, the Operator is matches and the value is 815471563813/env/test.
11. Click Save to save the condition.
12. Click Save to save the policy.

gcloud

This example uses the following variables:

• PROJECT_ID: Your Google Cloud project.
• INSTANCE_ID: Your Cloud SQL instance.
• REGION: The region your Cloud SQL instance is in.
• ORGANIZATION_ID: The ID of the organization to be the parent resource to this tag key; for example: 12345678901. To learn how to get your organization ID, see Creating and managing organizations.
• SERVICE_ACCOUNT_EMAIL:The complete email address of the service account whose access you want to modify.

1. Create a tag key named `env` with tag values `prod` and `test`. For more information, see Creating and defining a new tag.

   gcloud alpha resource-manager tags keys create env \
   --parent=organizations/ORGANIZATION_ID
   gcloud alpha resource-manager tags values create prod \
   --parent=env
   gcloud alpha resource-manager tags values create test \
   --parent=env
       

2. Attach the `env` tag with value `test` to your test environment Cloud SQL instances. For more information, see the Cloud SQL tags page.

gcloud alpha resource-manager tags bindings create \
--tag-value=test \
--parent=//sqladmin.googleapis.com/projects/PROJECT_ID/instances/INSTANCE_ID \
--location=REGION
  

3. Get the existing IAM policy bindings and output it to the file bindings.json:

   gcloud projects get-iam-policy PROJECT_ID --format=json >> bindings.json
   

4. Add the following conditional bindings to the bindings.json file:

   {
     "bindings": [
       {
         "role": "roles/cloudsql.editor",
         "members": [
             "serviceAccount:SERVICE_ACCOUNT_EMAIL"
           ]
       },
       {
         "role": "roles/cloudsql.admin",
         "members": [
             "serviceAccount:SERVICE_ACCOUNT_EMAIL"
           ],
         "condition": {
           "expression": "resource.matchTag('ORGANIZATION_ID/env', 'test')"
         }
       }
     ],
     "etag": "BwWKmjvelug="
     "version": 3
   }
   

5. Update the IAM policy bindings with the new bindings.json file.

   gcloud projects set-iam-policy PROJECT_ID bindings.json