🔋PostgreSQL Integration Setup

Fully Google Cloud Platform (GCP) managed relational database service for PostgreSQL.

This topic explains how to configure access control for P0’s fully managed PostgreSQL database service on Google Cloud Platform (GCP). You can use this integration to:

• Configure secure access to your PostgreSQL database.

• Enable authorized connections to the Cloud SQL instance.

• Perform other database access management tasks.

This guide contains the following sections:

1. Prerequisites

2. Set up PostgreSQL Integration

Prerequisites

• Existing Google Cloud (GCP) integration

• Permissions to create roles and add IAM bindings:

  • iam.roleAdmin

  • iam.securityAdmin

• Permissions to connect to the Cloud SQL instance, as an authorized user:

  • Grant access (GRANT) to other users

  • Create functions (CREATE FUNCTION) within the database

• Access to Google Cloud Shell and/or gcloud CLI installed and configured

This example uses Google Cloud Shell.

Set up PostgreSQL Integration

To set up the PostgreSQL integration:

1. Go to P0.app in your browser. Select Integrations, then under the Resources section, click PostgreSQL.

1. From the list of Available components, click Access management.

1. Click + Add database.

1. Populate the details of your PostgreSQL instance and click Go to next step:

   • Database identifier: Unique label to identify the database (e.g. p0-production-db)

   • Installation type: Platform hosting the PostgreSQL database (e.g. Google CloudSQL)

   • GCP project ID: Google Cloud project ID (e.g. p0-demo)

   • GCP region: Region where the CloudSQL instance is located (e.g. us-central1)

   • CloudSQL instance ID: ID of the CloudSQL instance which must have public IP access enabled (e.g. my-cloudsql-instance-001)

   • Database name: Name of the database to manage (e.g. app_main_db)

1. Keep the Access management browser tab open. In a new browser tab, open the Google Cloud Shell.

Keep this browser tab open as well. The next steps require you to toggle back and forth, between the P0.app's Access management page and the Google Cloud Shell browser tabs.

1. Ensure P0 can manage user access to PostgreSQL roles.

   1. Return to the command steps on the Access management browser tab. Step 1 applies to Google Cloud and Steps 2 and 3 apply to Postgres.

   2. Copy the commands from Step 1.

      
   3. Switch to the Google Cloud Shell tab. Paste and run the copied commands.

      
   4. From Google Cloud Shell, connect to Cloud SQL using the Postgres terminal. Replace REGION with your actual region (e.g.,us-central1, us-east1, etc.).

      
   5. Switch back to the Access management browser tab and copy the commands from Step 2 and Step 3.

      
   6. Return to the Postgres terminal and run the copied SQL commands.

      
   7. After running the commands, switch back to the Access management browser tab and click Next.

1. Click Finish to finalize your configuration.

You can manage your connections (e.g. request access to this PostgreSQL instance through Slack) on the PostgreSQL Integrations page and add new configurations as needed.

Congratulations! You've now set up PostgreSQL for P0.