Workforce Identity Federation authentication

MySQL | PostgreSQL | SQL Server

This page describes how to configure and use Workforce Identity Federation (also known as third-party IAM authentication) with Cloud SQL. Workforce Identity Federation lets you use your existing identity provider (IdP), such as Microsoft Active Directory or Okta, to access Cloud SQL instances without requiring a Google Account.

Key benefits of using Workforce Identity Federation include:

Reduced overhead: No need to verify domains or synchronize identities to Cloud Identity.
Enhanced security: Centralized management of database access through your existing enterprise IdP.
Ease of scale: Suitable for large organizations with complex identity management needs.

For a detailed description of Workforce Identity Federation, see the Workforce Identity Federation overview.

How it works

Workforce Identity Federation lets users authenticate to Google Cloud by using an external identity. For Cloud SQL, this means that principals from a workforce pool can connect to Cloud SQL for MySQL instances.

Cloud SQL supports Workforce Identity Federation through the CLOUD_IAM_WORKFORCE_IDENTITY user type. To grant access, Cloud SQL validates your workforce credentials and project-level IAM permission during login.

Before you begin

Before you configure Workforce Identity Federation database authentication, make sure that you meet the following prerequisites:

Install the latest gcloud CLI.
Configure a Workforce Identity Federation pool and provider in your Google Cloud organization.
Set up service account impersonation for your workload.
Ensure that your Cloud SQL instance is running MySQL version 8.0 or later.

Roles and permissions

To get the permissions that you need to configure and use Workforce Identity Federation authentication, ask your administrator to grant you the following IAM roles on the organization:

Manage an instance: Cloud SQL Admin (roles/cloudsql.admin)
Connect to instance: Cloud SQL Instance User (roles/cloudsql.instanceUser)
Connect to an instance using the Cloud SQL Auth Proxy: Cloud SQL Client (roles/cloudsql.client)
Manage IAM policy: Project IAM Admin (roles/resourcemanager.projectIamAdmin)

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Configure Workforce Identity Federation authentication

The following sections tell you how to configure your instance to use Workforce Identity Federation authentication.

Enable IAM authentication on the instance

To enable IAM authentication, set the cloudsql_iam_authentication flag to on.

Console

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
To open the Overview page of an instance, click the instance name.
Click Edit.
Expand the Customize your instance section.
Expand the Connections section.
Under Security, select the Enable Cloud SQL IAM authentication checkbox.
Click Save.

gcloud

Use the following command to enable IAM authentication:

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

Replace INSTANCE_NAME with the name of the instance.

Terraform

Add the database_flags block to your google_sql_database_instance resource:

resource "google_sql_database_instance" "instance" {
  name             = "INSTANCE_NAME"
  database_version = "MYSQL_8_0"
  region           = "REGION"

  settings {
    tier = "db-f1-micro"
    database_flags {
      name  = "cloudsql_iam_authentication"
      value = "on"
    }
  }
}

Replace the following:

INSTANCE_NAME: the name of the instance.
REGION: the region where the instance resides.

REST v1

Before using any of the request data, make the following replacements:

PROJECT_ID: the project ID
LOCATION_ID: the location ID
INSTANCE_ID: the desired instance ID
REGION: the desired region
DATABASE_VERSION: enum string of the database version. For example: MYSQL_8_0
PASSWORD: the password for the root user
MACHINE_TYPE: enum string of the machine (tier) type, as: db-custom-[CPUS]-[MEMORY_MBS]

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION_ID/instances

Request JSON body:

{
  "name": "INSTANCE_ID",
  "region": "REGION",
  "databaseVersion": "DATABASE_VERSION",
  "rootPassword": "PASSWORD",
  "settings": {
    "tier": "MACHINE_TYPE",
    "backupConfiguration": {
      "enabled": true
    }
    "databaseFlags":
    [
      {
        "name": "cloudsql_iam_authentication",
        "value": "on"
      }
    ]
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell, which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list.

Save the request body in a file named request.json, and execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION_ID/instances"

PowerShell (Windows)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list.

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION_ID/instances" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-01T19:13:21.834Z",
  "operationType": "CREATE",
  "name": "OPERATION_ID",
  "targetId": "INSTANCE_ID",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

REST v1beta4

Before using any of the request data, make the following replacements:

PROJECT_ID: the project ID
LOCATION_ID: the location ID
INSTANCE_ID: the desired instance ID
REGION: the desired region
DATABASE_VERSION: enum string of the database version. For example: MYSQL_8_0
PASSWORD: the password for the root user
MACHINE_TYPE: enum string of the machine (tier) type, as: db-custom-[CPUS]-[MEMORY_MBS]

HTTP method and URL:

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/locations/LOCATION_ID/instances

Request JSON body:

{
  "name": "INSTANCE_ID",
  "region": "REGION",
  "databaseVersion": "DATABASE_VERSION",
  "rootPassword": "PASSWORD",
  "settings": {
    "tier": "MACHINE_TYPE",
    "backupConfiguration": {
      "enabled": true
    }
    "databaseFlags":
    [
      {
        "name": "cloudsql_iam_authentication",
        "value": "on"
      }
    ]
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json, and execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/locations/LOCATION_ID/instances"

PowerShell (Windows)

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/locations/LOCATION_ID/instances" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-01T19:13:21.834Z",
  "operationType": "CREATE",
  "name": "OPERATION_ID",
  "targetId": "INSTANCE_ID",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

Add the workforce identity user to the instance

Add the external principal to your instance by using the CLOUD_IAM_WORKFORCE_IDENTITY type.

Make sure that the user ID that you use matches the value provided by the attribute mapping from your workforce identity provider. Typically, this is configured as an email address—for example, cruz@example.com.

Console

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
To open the Overview page of an instance, click the instance name.
Select Users from the SQL navigation menu.
Click Add user account. The Add a user account to instance INSTANCE_NAME pane opens.
Select Workforce Identity Federation.
In the Workforce user field, enter the user ID for the user that you want to add.
Click Add.

gcloud

Run the following command to create the user:

gcloud sql users create USER_ID \
    --instance=INSTANCE_NAME \
    --type=CLOUD_IAM_WORKFORCE_IDENTITY

Replace the following:

USER_ID: the email address for the user that you want to add—for example, cruz@example.com.
INSTANCE_NAME: the name of the instance.

Terraform

Use the google_sql_user resource to define the workforce identity user:

resource "google_sql_user" "workforce_user" {
  name     = "USER_ID" # e.g., "cruz@example.com"
  instance = "INSTANCE_NAME"
  type     = "CLOUD_IAM_WORKFORCE_IDENTITY"
}

Replace the following:

USER_ID: the user ID for the user that you want to add—for example, cruz@example.com.
INSTANCE_NAME: the name of the instance.

REST v1

Create a user account

Before using any of the request data, make the following replacements:

PROJECT_ID: the project ID
INSTANCE_ID: the instance ID for the instance you are adding the user to
USERNAME: the email address for the user

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID/users

Request JSON body:

{
  "name": "USERNAME",
  "type": "CLOUD_IAM_WORKFORCE_IDENTITY"
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json, and execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID/users"

PowerShell (Windows)

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID/users" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID",
  "status": "DONE",
  "user": "user@example.com",
  "insertTime": "2020-02-07T22:44:16.656Z",
  "startTime": "2020-02-07T22:44:16.686Z",
  "endTime": "2020-02-07T22:44:20.437Z",
  "operationType": "CREATE_USER",
  "name": "OPERATION_ID",
  "targetId": "INSTANCE_ID",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

REST v1beta4

Create a user account

Before using any of the request data, make the following replacements:

PROJECT_ID: the project ID
INSTANCE_ID: the instance ID for the instance you are adding the user to
USERNAME: the email address for the user

HTTP method and URL:

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/users

Request JSON body:

{
  "name": "USERNAME",
  "type": "CLOUD_IAM_WORKFORCE_IDENTITY"
  }

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json, and execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/users"

PowerShell (Windows)

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/users" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID",
  "status": "DONE",
  "user": "user@example.com",
  "insertTime": "2020-02-07T22:44:16.656Z",
  "startTime": "2020-02-07T22:44:16.686Z",
  "endTime": "2020-02-07T22:44:20.437Z",
  "operationType": "CREATE_USER",
  "name": "OPERATION_ID",
  "targetId": "INSTANCE_ID",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

Grant the roles/cloudsql.instanceUser role to the workforce identity principal or the entire pool.

Console

In the Google Cloud console, go to the Service accounts page.

Go to IAM
Click Grant Access.

Workforce Identity Federation authentication Stay organized with collections Save and categorize content based on your preferences.

How it works

Before you begin

Roles and permissions

Configure Workforce Identity Federation authentication

Enable IAM authentication on the instance

Console

gcloud

Terraform

REST v1

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

REST v1beta4

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Add the workforce identity user to the instance

Console

gcloud

Terraform

REST v1

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

REST v1beta4

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Grant instance user role

Console

Workforce Identity Federation authentication