🔋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.

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

   
3. Click + Add database.

   
4. Populate the details of your PostgreSQL instance and click Next:

   • 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)

     
5. Ensure P0 can manage user access to PostgreSQL roles. On the resulting Access management screen, Step 1 applies to Google Cloud and Steps 2 and 3 apply to Postgres.

   Start by configuring Google Cloud, and copy the commands from Step 1.

   

Keep the Access management browser tab open. You'll need to copy information from this page in subsequent steps.

1. Open the Google Cloud Shell in a new browser tab, and paste and run the copied commands.

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. From Google Cloud Shell, connect to your Cloud SQL PostgreSQL instance using the psql command. Replace REGION with your actual region (e.g.,us-central1, us-east1, etc.).

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

   
3. Return to Google Cloud Shell and run the copied SQL commands.

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

   
5. 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.