Connect to AlloyDB for PostgreSQL

As a BigQuery administrator, you can create a connection to access AlloyDB data. This connection lets data analysts query data in AlloyDB.

To connect to AlloyDB, you must perform the following steps:

  1. Create an AlloyDB connection.

  2. Grant access to the service account.

Before you begin

  1. Enable the BigQuery Connection API.

    Enable the API

  2. To get the permissions that you need to create an AlloyDB connection, ask your administrator to grant you the BigQuery Connection Admin (roles/bigquery.connectionAdmin) IAM role on the project. 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.

Create an AlloyDB connection

As a best practice, use connections to handle database credentials when you are connecting to AlloyDB. Connections are encrypted and stored securely in the BigQuery connection service. If the user credentials are valid for other data in the source, you can reuse the connection. For example, you can use one connection to query the same database in an AlloyDB instance multiple times.

Select one of the following options to create an AlloyDB connection:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, click Add.

  3. In the Add dialog, click Connections to external data sources:

  4. In the External data source dialog, enter the following information:

    • For Connection type, select AlloyDB.
    • For Connection ID, enter an identifier for the connection resource. Letter, numbers, and underscores are allowed. For example, bq_alloydb_connection.
    • For Data location, select a BigQuery location (or region) that is compatible with your external data source region.
    • Optional: For Friendly name, enter a user-friendly name for the connection, such as My connection resource. The friendly name can be any value that helps you identify the connection resource if you need to modify it later.
    • Optional: For Description, enter a description for this connection resource.
    • Optional: Encryption If you want to use a customer-managed encryption key (CMEK) to encrypt your credentials, select Customer-managed encryption key (CMEK) and then select a customer-managed key. Otherwise, your credentials are protected by the default Google-owned and Google-managed encryption key.
    • For Database name, enter the name of the database.
    • For Database username, enter the username for the database.
    • For Database password, enter the password for the database.
      • Optional: To see the password, click Show password.

    • For AlloyDB Instance, enter the connection URI of the AlloyDB primary or read instance with the //alloydb.googleapis.com prefix.

      • Sample URI: //alloydb.googleapis.com/projects/PROJECT_ID/locations/REGION_ID/clusters/CLUSTER_NAME/instances/INSTANCE_ID

  5. Click Create connection.

  6. Click Go to connection.

  7. In the Connection Info pane, copy the service account ID for use in the next step to grant the correct IAM permissions.

bq

Enter the bq mk command with the following flags:

  bq mk \
  --connection \
  --location=LOCATION \
  --project_id=PROJECT_ID \
  --connector_configuration '{
    "connector_id": "google-alloydb",
    "asset": {
      "database": "DATABASE",
      "google_cloud_resource": "RESOURCE_PATH"
    },
    "authentication": {
      "username_password": {
        "username": "USERNAME",
        "password": {
          "plaintext": "PASSWORD"
        }
      }
    }
  }' \
  CONNECTION_ID

Replace the following:

  • LOCATION: Specify a region of the BigQuery dataset to be combined with the data from AlloyDB. Queries that use this connection must be run from this region.
  • PROJECT_ID: Enter your Google Cloud project ID.
  • DATABASE: Enter the database name.
  • RESOURCE_PATH: Enter the connection URI of the AlloyDB primary or read instance with the //alloydb.googleapis.com prefix.
    • Sample URI: //alloydb.googleapis.com/projects/PROJECT_ID/locations/REGION_ID/clusters/CLUSTER_NAME/instances/INSTANCE_ID
  • USERNAME: Enter the database user's name.
  • PASSWORD: Enter the database user's password.
  • CONNECTION_ID: Enter a connection ID to identify this connection.

API

Within the BigQuery Connection API, you can invoke CreateConnection within the ConnectionService to instantiate a connection. See the client library page for more details.

Grant access to the service account

A service account is automatically created when you create the first connection within a project. The service account's name is BigQuery Connection Service Agent. The service account ID is of the following format:

service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com.

To connect to AlloyDB, you must give the new connection access to AlloyDB so that BigQuery can access data on behalf of users. The service account must have the following permission:

  • alloydb.instances.connect

You can grant the service account associated with the connection the AlloyDB Client IAM role, which already has this permission assigned. You can omit this step if the service account already has the required permission.

Console

  1. Go to the IAM & Admin page.

    Go to IAM & Admin

  2. Click Grant Access.

    The Add principals dialog opens.

  3. In the New principals field, enter the service account name BigQuery Connection Service Agent or the service account ID taken from the connection information.

  4. In the Select a role field, select AlloyDB, and then select AlloyDB Client.

  5. Click Save.

gcloud

Use the gcloud projects add-iam-policy-binding command:

gcloud projects add-iam-policy-binding PROJECT_ID --member=serviceAccount:SERVICE_ACCOUNT_ID --role=roles/alloydb.client

Provide the following values:

  • PROJECT_ID: Your Google Cloud project ID.
  • SERVICE_ACCOUNT_ID: Replace project number in service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com and use it.

Share connections with users

You can grant the following roles to let users query data and manage connections:

  • roles/bigquery.connectionUser: enables users to use connections to connect with external data sources and run queries on them.

  • roles/bigquery.connectionAdmin: enables users to manage connections.

For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Select one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

    Connections are listed in your project, in a group called External connections.

  2. In the Explorer pane, click your project name > External connections > connection.

  3. In the Details pane, click Share to share a connection. Then do the following:

    1. In the Connection permissions dialog, share the connection with other principals by adding or editing principals.

    2. Click Save.

bq

Use the following set-iam-policy command:

  bq set-iam-policy RESOURCE FILE_NAME

Replace the following:

  • RESOURCE: Enter the resource name in the project_id.region.connection_id or region.connection_id format.
  • FILE_NAME: Enter the filename that contains the IAM policy in a JSON format.

For more information about the set-iam-policy command, see Control access to resources with IAM.

API

Use the projects.locations.connections.setIAM method in the BigQuery Connections REST API reference section and supply an instance of the policy resource.

What's next