Installing a Cloud SQL database

Install P0 on a Google Cloud SQL for PostgreSQL instance to grant just-in-time database access through a Cloud Run connector and GCP IAM database authentication.

Google Cloud SQL is a preview integration.

Follow this guide to install P0 on a Google Cloud SQL for PostgreSQL instance. P0 grants just-in-time, time-limited access to the instance using GCP IAM database authentication, reached through a P0-managed Cloud Run connector that runs inside your VPC.

Cloud SQL for MySQL is not supported. This integration manages PostgreSQL instances only.

How it works

P0 reaches your Cloud SQL instances through a connector: a Cloud Run service that P0 deploys into your project with direct access to the VPC your instances run on. The connector provisions database users and enforces access on P0's behalf.

When a user requests access to a Cloud SQL instance:

1. P0 provisions a dedicated IAM database user for that identity on the instance. P0 creates the user once, and it persists across requests.

2. After approval, P0 delegates the roles/cloudsql.instanceUser role to the requestor for the approved duration.

3. The requestor connects to the instance as their IAM database user using GCP IAM database authentication.

4. When the access duration expires or an approver revokes access, P0 removes the delegated role. The IAM database user remains in place for future requests.

Prerequisites

Before you begin, ensure you have:

• A P0 Google Cloud integration with IAM management installed on the project.

• A VPC and subnetwork that your Cloud SQL instances run on.

• Admin access to the Cloud SQL instance.

• IAM database authentication enabled on the instance. Set the cloudsql.iam_authentication database flag to on.

• Permissions to deploy a Cloud Run service, create IAM bindings, and reserve a Private Service Access range in the project.

• These Google Cloud APIs enabled on the project: compute, iam, run, servicenetworking, and sqladmin.

Private-IP Cloud SQL instances require a Private Service Access connection that peers a reserved internal IP range to servicenetworking.googleapis.com on the VPC. Establish this connection before you create the instance.

Install the GCP CloudSQL connector

Install the connector on the VPC that hosts your Cloud SQL instances. This step deploys the Cloud Run connector and grants it the roles it needs to manage database users.

Via the P0 app

1. Navigate to Integrations on p0.app and select the GCP CloudSQL integration.

2. Select the IAM management component.

3. Select the GCP project ID that hosts the VPC.

4. Enter the Subnetwork name that the connector should have direct VPC access to.

5. Click Next. P0 generates the connector's Cloud Run service name and service account, along with the Terraform to deploy them.

6. Apply the generated Terraform. It deploys the connector, grants the connector's service account the roles/cloudsql.admin and roles/cloudsql.instanceUser roles, and grants P0's service account the roles/run.invoker role on the connector.

7. Click Next. P0 verifies that the connector is reachable, then completes the install.

Via the P0 Terraform provider

1. Ensure you have configured the P0 Terraform provider with the root p0_gcp integration and p0_gcp_iam_write installed on the project.

2. Use the p0_gcp_cloudsql_staged resource to stage the install. Staging generates the connector identifiers you need to deploy the connector.

resource "p0_gcp_cloudsql_staged" "example" {
  id         = google_compute_network.example.name
  project_id = local.project
  subnetwork = google_compute_subnetwork.example.name
  depends_on = [p0_gcp_iam_write.example]
}

1. Deploy the connector with the p0-security/p0-connector/google module, then grant the connector's service account the required Cloud SQL roles.

module "gcp_cloudsql_vpc" {
  source  = "p0-security/p0-connector/google"
  version = "0.1.0"

  project_id                     = local.project
  service                        = "cloudsql"
  connector_name                 = p0_gcp_cloudsql_staged.example.connector_service_name
  connector_service_account_name = split("@", p0_gcp_cloudsql_staged.example.connector_service_account)[0]
  region                         = p0_gcp_cloudsql_staged.example.region
  vpc_network                    = google_compute_network.example.name
  vpc_subnetwork                 = google_compute_subnetwork.example.name
  invoker_service_account_email  = p0_gcp.example.service_account_email
}

resource "google_project_iam_member" "connector_cloudsql_admin" {
  project = local.project
  role    = "roles/cloudsql.admin"
  member  = "serviceAccount:${p0_gcp_cloudsql_staged.example.connector_service_account}"
}

resource "google_project_iam_member" "connector_instance_user" {
  project = local.project
  role    = "roles/cloudsql.instanceUser"
  member  = "serviceAccount:${p0_gcp_cloudsql_staged.example.connector_service_account}"
}

1. Register the connector's service account as an IAM database user on the instance.

resource "google_sql_user" "connector" {
  name     = trimsuffix(p0_gcp_cloudsql_staged.example.connector_service_account, ".gserviceaccount.com")
  instance = google_sql_database_instance.example.name
  project  = local.project
  type     = "CLOUD_IAM_SERVICE_ACCOUNT"
}

1. Use the p0_gcp_cloudsql resource to complete the install. Creating it verifies that the connector is reachable.

resource "p0_gcp_cloudsql" "example" {
  id         = p0_gcp_cloudsql_staged.example.id
  project_id = p0_gcp_cloudsql_staged.example.project_id
  subnetwork = p0_gcp_cloudsql_staged.example.subnetwork
  depends_on = [
    module.gcp_cloudsql_vpc,
    google_project_iam_member.connector_cloudsql_admin,
    google_project_iam_member.connector_instance_user,
    google_sql_user.connector,
  ]
}

1. Run terraform init and terraform apply.

For a complete, runnable example that also enables the required APIs and provisions the VPC, subnetwork, Private Service Access range, and a sample instance, see the p0_gcp_cloudsql provider documentation.

Add the instance to PostgreSQL (new)

After the connector is installed, add each Cloud SQL instance to the PostgreSQL (new) integration. Each instance on the VPC gets its own entry.

1. Navigate to Integrations on p0.app and select the PostgreSQL (new) integration.

2. Select the Access management component and click Add instance.

3. Enter a unique identifier for the instance. This can be any value you like.

4. Choose GCP CloudSQL hosting.

5. Select the GCP VPC ID that hosts the instance. The list shows only VPCs where the GCP CloudSQL connector is fully installed.

6. Enter the CloudSQL instance name.

7. Click Next.

8. Apply the generated Terraform. It registers the connector's service account as an IAM database user on the instance.

9. Grant that database user the cloudsqlsuperuser role. With version 7.19.0 or later of the Google Terraform provider, add the database_roles line that P0 shows to the same resource. Otherwise, run the gcloud sql users assign-roles command that P0 shows.

10. Click Next.

11. If desired, configure the port and default database.

12. Click Finish.

To let requestors request access to run specific SQL statements, also complete Enabling SQL statement access.

Enabling SQL statement access (optional)

By default, P0 grants role-based access to the instance. To also let requestors request access to run specific SQL statements, the connector's cloudsqlsuperuser role needs grant authority (WITH GRANT OPTION) on the tables and schemas you want P0 to manage. If you skip this section and a requestor asks for SQL statement access, P0 can't provision that request.

Set up table access, schema access, both, or neither, depending on what your requestors need to run.

Table access

To let requestors act on specific tables, first identify the roles that own tables in the schema:

SELECT tableowner, array_agg(tablename) AS tables
FROM pg_tables
WHERE schemaname = 'public'
GROUP BY tableowner;

Then, for each role that owns a table, connect as that role and grant cloudsqlsuperuser grant authority on its tables. The ALTER DEFAULT PRIVILEGES statement covers tables the role creates later:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO cloudsqlsuperuser WITH GRANT OPTION;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO cloudsqlsuperuser WITH GRANT OPTION;

Schema access

To let requestors perform schema-level actions on an existing schema, connect as the role that owns the schema and run the following once per schema you want P0 to manage. It grants cloudsqlsuperuser USAGE (to reference the schema's tables) and CREATE (to create new tables in it), with grant option. Run it again whenever you add a schema:

GRANT USAGE, CREATE ON SCHEMA public TO cloudsqlsuperuser WITH GRANT OPTION;

These statements target the public schema. To manage a different schema, replace public with the schema's name.

Once the database is connected, see Requesting PostgreSQL access to request and connect to it.

Troubleshooting

P0 reports that it could not reach the connector during install. The connector's Cloud Run service can be briefly unreachable right after deployment. Wait a moment and click Next again to retry verification. If the error persists, confirm that:

• The connector's Cloud Run service is deployed and running.

• P0's service account holds the roles/run.invoker role on the connector service.

• Cloud Run has direct VPC access to the configured subnetwork.

The GCP VPC ID list is empty when adding the instance. The list shows only VPCs where the GCP CloudSQL connector install is complete. It does not read VPCs from your Google Cloud project, so granting P0 extra viewer roles does not add entries. Finish installing the GCP CloudSQL connector on the VPC, then add the instance again.