LogoLogo
Documentation and Guides
Documentation and Guides
  • ABOUT APONO
    • Why Choose Apono
    • Security and Architecture
    • Glossary
  • GETTING STARTED
    • How Apono Works
    • Getting started
    • Access Discovery
    • Integrating with Apono
  • CONNECTORS AND SECRETS
    • Apono Integration Secret
    • High Availability for Connectors
    • Installing a connector with Docker
    • Manage integrations
    • Manage connectors
    • S3 Storage
  • AWS ENVIRONMENT
    • AWS Overview
    • Apono Connector for AWS
      • Installing a connector on EKS Using Terraform
      • Updating a connector in AWS
      • Installing a connector on AWS ECS using Terraform
    • AWS Integrations
      • Integrate an AWS account or organization
        • Auto Discover AWS RDS Instances
        • AWS Best Practices
      • Amazon Redshift
      • RDS PostgreSQL
      • AWS RDS MySQL
      • Integrate with EKS
      • AWS Lambda Custom Integration
      • EC2 via Systems Manager Agent (SSM)
  • AZURE ENVIRONMENT
    • Apono Connector for Azure
      • Install an Azure connector on ACI using Azure CLI
      • Install an Azure connector on ACI using PowerShell
      • Install an Azure connector on ACI using Terraform
      • Updating a connector in Azure
    • Azure Integrations
      • Integrate with Azure Management Group or Subscription
        • Auto Discover Azure SQL Databases
      • Azure MySQL
      • Azure PostgreSQL
      • Integrate with AKS
  • GCP ENVIRONMENT
    • Apono Connector for GCP
      • Installing a GCP connector on Cloud Run using CLI
      • Installing a GCP connector on GKE using CLI (Helm)
      • Installing a GCP connector on GKE using Terraform
      • Updating a connector in Google Cloud
    • GCP Integrations
      • Integrate a GCP organization or project
      • CloudSQL - MySQL
      • CloudSQL - PostgreSQL
      • Google Cloud Functions
      • Integrate with GKE
      • AlloyDB
  • KUBERNETES ENVIRONMENT
    • Apono Connector for Kubernetes
      • Installing a connector on Kubernetes with AWS permissions
      • Updating a Kubernetes connector
    • Kubernetes Integrations
      • Integrate with Self-Managed Kubernetes
  • ADDITIONAL INTEGRATIONS
    • Databases and Data Repositories
      • Microsoft SQL Server
      • MongoDB
      • MongoDB Atlas
      • MongoDB Atlas Portal
      • MySQL
      • Oracle Database
      • PostgreSQL
      • RabbitMQ
      • Redis Cloud (Redislabs)
      • Snowflake
      • Vertica
      • MariaDB
    • Network Management
      • SSH Servers
      • RDP Servers
      • Windows Domain Controller
      • AWS EC2 SSH Servers
      • Azure VM SSH Servers
      • Installing the Apono HTTP Proxy
    • Development Tools
      • GitHub
      • Rancher
    • Identity Providers
      • Okta SCIM
      • Okta Groups
      • Okta SSO for Apono logins
      • Google Workspace (Gsuite)
      • Google Workspace (GSuite) Groups
      • Azure Active Directory (Microsoft Entra ID)
      • Azure Active Directory (Entra ID) Groups
      • Jumpcloud
      • JumpCloud Groups
      • OneLogin
      • OneLogin Group
      • LDAP Groups
      • The Manager Attribute in Access Flows
      • HiBob
      • Ping Identity SSO
    • Incident Response Integrations
      • incident.io
      • Opsgenie
      • PagerDuty
      • VictorOps (Splunk On-Call)
      • Zenduty
    • ChatOps Integrations
      • Slack integration
      • Teams integration
      • Backstage Integration
    • Secret Management
      • 1Password
  • WEBHOOK INTEGRATIONS
    • Webhooks Overview
    • Anomaly Webhook
    • Audit Log Webhook
    • Request Webhook
      • Custom Webhooks
      • Communications and Notifications
        • Slack Outbound Webhooks
        • Teams
        • Outlook and Gmail (Using Azure Logic App)
      • ITSM
        • Freshdesk
        • Jira
        • ServiceNow
        • Zendesk
        • Freshservice
        • ServiceDesk Plus
      • Logs and SIEMs
        • Coralogix
        • Datadog
        • Logz.io
        • Grafana
        • New Relic
        • SolarWinds
        • Sumo Logic
        • Cortex
        • Logpoint
        • Splunk
        • Microsoft Sentinel
      • Orchestration and workflow builders
        • Okta Workflows
        • Torq
    • Integration Webhook
    • Webhook Payload References
      • Audit Log Webhook Payload Schema Reference
      • Webhook Payload Schema Reference
    • Manage webhooks
    • Troubleshoot a webhook
    • Manual Webhook
      • ITSM
        • PagerDuty
  • ACCESS FLOWS
    • Access Flows
      • What are Access Flows?
    • Create Access Flows
      • Self Serve Access Flows
      • Automatic Access Flows
      • Access Duration
    • Manage Access Flows
      • Right Sizing
    • Revoke Access
    • Dynamic Access Management
      • Resource and Integration Owners
    • Common Use Cases
      • Ensuring SLA
      • Protecting PII and Customer Data
      • Production Stability and Management
      • Break Glass Protocol
    • Create Bundles
    • Manage Bundles
  • ACCESS REQUESTS AND APPROVALS
    • Slack
      • Requesting Access with Slack
      • Approving Access with Slack
      • Reviewing historical requests with Slack
    • Teams
      • Requesting Access with Teams
      • Approving Access with Teams
    • CLI
      • Install and manage the Apono CLI
      • Requesting Access with CLI
    • Web Portal
      • Requesting Access with the Web Portal
      • Approving Access with the Web Portal
      • Reviewing historical requests with the Web Portal
    • Freshservice
    • Favorites
  • Inventory
    • Inventory Overview
    • Inventory
    • Access Scopes
    • Risk Scores
    • Apono Query Language
  • AUDITS AND REPORTS
    • Activity Overview
      • Activity
      • Create Reports
      • Manage Reports
    • Compliance: Audit and Reporting
    • Auditing Access in Apono
    • Admin Audit Log (Syslog)
  • HELP AND DEBUGGING
    • Integration Status Page
    • Troubleshooting Errors
  • ARCHITECTURE AND SECURITY
    • Anomaly Detection
    • Multi-factor Authentication
    • Credentials Rotation Policy
    • Periodic User Cleanup & Deletion
    • End-user Authentication
    • Personal API Tokens
  • User Administration
    • Role-Based Access Control (RBAC) Reference
    • Create Identities
    • Manage Identities
Powered by GitBook
On this page
  • Prerequisites
  • Create a PostgreSQL user
  • Integrate Google Cloud SQL - PostgreSQL

Was this helpful?

Export as PDF
  1. GCP ENVIRONMENT
  2. GCP Integrations

CloudSQL - PostgreSQL

Create an integration to manage access to PostgreSQL instances on Google Cloud SQL

PreviousCloudSQL - MySQLNextGoogle Cloud Functions

Last updated 3 months ago

Was this helpful?

Google Cloud SQL PostgreSQL is a fully managed relational database service built for the cloud. It provides a high-performance, scalable, and highly available PostgreSQL database instance without the overhead of managing infrastructure. With Google Cloud SQL, users benefit from Google Cloud's robust infrastructure, which ensures high availability, security, and scalability for their databases.

Through this integration, Apono helps you securely manage access to your Google Cloud SQL PostgreSQL database instances.

To enable Apono to manage Google Cloud SQL PostgreSQL user access, you must create a user and then configure the integration within the Apono UI.


Prerequisites

Item
Description

Apono Connector

Cloud SQL Admin API

Cloud SQL Admin Role

(Cloud IAM authentication only) Google Cloud role that the Apono connector's service user must have at the instance's project or organization level

PostgreSQL Info

Information for the database instance to be integrated:


Create a PostgreSQL user

You must create a user in your PostgreSQL instance for the Apono connector and grant that user permissions to your databases.

You must use the admin account and password to connect to your database.

Following these steps to create a user and grant it permissions:

  1. In the Google Cloud console, with either Built-in authentication or Cloud IAM authentication.

Use apono_connector for the username.

This authentication method grants the user the cloudsqlsuperuser role. Be sure to set a strong password for the user.

As an alternative, you can run the following command from your Postgre client:

CREATE USER 'apono_connector'@'%' IDENTIFIED BY 'password'

Use apono-connector-iam-sa@[PROJECT_ID].iam.gserviceaccount.com for the Principal.

This authentication method does not grant the user account database privileges.

Be sure that the Apono connector GCP service account (apono-connector-iam-sa@[PROJECT_ID].iam.gserviceaccount.com) has the Cloud SQL Admin role.

  1. (Cloud IAM only) In your preferred client tool, grant cloudsqlsuperuser access to the user account.

ALTER ROLE "<CONNECTOR_USERNAME>" WITH CREATEROLE;
GRANT cloudsqlsuperuser TO "<CONNECTOR_USERNAME>";
  1. In your preferred client tool, grant the cloudsqlsuperuser role privileges on all databases except template0 and cloudsqladmin. This allows Apono to perform tasks that are not restricted to a single schema or object within the database, such as creating, altering, and dropping database objects.

DO $$
DECLARE
  database_name text;
BEGIN
  FOR database_name IN (SELECT datname FROM pg_database WHERE datname != 'template0' AND datname != 'cloudsqladmin') LOOP
    EXECUTE 'GRANT ALL PRIVILEGES ON DATABASE ' || quote_ident(database_name) || ' TO cloudsqlsuperuser WITH GRANT OPTION';
  END LOOP;
END; $$
  1. For each database to be managed through Apono, connect to the database and grant cloudsqlsuperuser privileges on all objects in the schemas. This allows Apono to perform tasks that are restricted to schemas within the database, such as modifying table structures, creating new sequences, or altering functions.

DO $$
DECLARE
  schema text;
BEGIN
  FOR schema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema' AND schema_name != 'cron') LOOP
    EXECUTE 'GRANT ALL PRIVILEGES ON SCHEMA ' || quote_ident(schema) || ' TO cloudsqlsuperuser WITH GRANT OPTION';
    EXECUTE 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ' || quote_ident(schema) || ' TO cloudsqlsuperuser WITH GRANT OPTION';
    EXECUTE 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA ' || quote_ident(schema) || ' TO cloudsqlsuperuser WITH GRANT OPTION';
    EXECUTE 'GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA ' || quote_ident(schema) || ' TO cloudsqlsuperuser WITH GRANT OPTION';
  END LOOP;
  EXECUTE 'ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON TABLES TO cloudsqlsuperuser WITH GRANT OPTION';
  EXECUTE 'ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON SEQUENCES TO cloudsqlsuperuser WITH GRANT OPTION';
  EXECUTE 'ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON FUNCTIONS TO cloudsqlsuperuser WITH GRANT OPTION';
  EXECUTE 'ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON SCHEMAS TO cloudsqlsuperuser WITH GRANT OPTION';
END; $$
  1. Connect to the template1 database and grant cloudsqlsuperuser privileges on all objects in the schemas. For any new databases created in the future, this allows Apono to perform tasks that are restricted to schemas within the database, such as modifying table structures, creating new sequences, or altering functions.

DO $$
DECLARE
  schema text;
BEGIN
  FOR schema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema' AND schema_name != 'cron') LOOP
    EXECUTE 'GRANT ALL PRIVILEGES ON SCHEMA ' || quote_ident(schema) || ' TO cloudsqlsuperuser WITH GRANT OPTION';
    EXECUTE 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ' || quote_ident(schema) || ' TO cloudsqlsuperuser WITH GRANT OPTION';
    EXECUTE 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA ' || quote_ident(schema) || ' TO cloudsqlsuperuser WITH GRANT OPTION';
    EXECUTE 'GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA ' || quote_ident(schema) || ' TO cloudsqlsuperuser WITH GRANT OPTION';
  END LOOP;
  EXECUTE 'ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON TABLES TO cloudsqlsuperuser WITH GRANT OPTION';
  EXECUTE 'ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON SEQUENCES TO cloudsqlsuperuser WITH GRANT OPTION';
  EXECUTE 'ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON FUNCTIONS TO cloudsqlsuperuser WITH GRANT OPTION';
  EXECUTE 'ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON SCHEMAS TO cloudsqlsuperuser WITH GRANT OPTION';
END; $$

When using Cloud IAM authentication, the service account and its permissions are managed through Google Cloud IAM roles and policies. The service account is used to authenticate to the Cloud SQL instance.

A secret does not need to be created.


Integrate Google Cloud SQL - PostgreSQL

You can also use the steps below to integrate with Apono using Terraform.

In step 11, instead of clicking Confirm, follow the Are you integrating with Apono using Terraform? guidance.

Follow these steps to complete the integration:

  1. Under Discovery, click one or more resource types and cloud services to sync with Apono.

Apono automatically discovers and syncs all the instances in the environment. After syncing, you can manage access flows to these resources.

  1. Click Next. The Apono connector section expands.

  2. From the dropdown menu, select a connector.

  1. Click Next. The Integration Config section expands.

  2. Define the Integration Config settings.\

    Setting
    Description

    Integration Name

    Unique, alphanumeric, user-friendly name used to identify this integration when constructing an access flow

    Auth Type

    Authorization type for the MySQL service account user:

    • IAM Auth: Cloud IAM authentication

    • User / Password: Built-in authentication

    Project ID

    ID of the project where the PostgreSQL instance is deployed

    Region

    Location where the PostgreSQL instance is deployed

    Instance ID

    ID of the PostgreSQL instance

    Instance ID User Override

    (Optional) Allows overriding the instance ID for the user

    Database Name

    Name of the database to integrate By default, Apono sets this value to postgre.

    SSL Mode

    (Optionl) Mode of Secure Sockets Layer (SSL) encryption used to secure the connection with the SQL database server:

    • require: An SSL-encrypted connection must be used.

    • allow: An SSL-encrypted or unencrypted connection is used. If an SSL-encrypted connection is unavailable, the unencrypted connection is used.

    • disable: An unencrypted connection is used.

    • prefer: An SSL-encrypted connection is attempted. If the encrypted connection is unavailable, the unencrypted connection is used.

    • verify-ca: An SSL-encrypted connection must be used and a server certification verification against the provided CA certificates must pass.

    • verify-full: An SSL-encrypted connection must be used and a server certification verification against the provided CA certificates must pass. Additionally, the server hostname is checked against the certificate's names.

  3. Click Next. The Secret Store section expands.

A secret is not needed or Cloud IAM authentication.

  1. Click Next. The Get more with Apono section expands.

  2. Define the Get more with Apono settings.\

    Setting
    Description

    Credential Rotation

    User cleanup after access is revoked (in days)

    (Optional) Defines the number of days after access has been revoked that the user should be deleted

    Custom Access Details

    (Optional) Instructions explaining how to access this integration's resources Upon accessing an integration, a message with these instructions will be displayed to end users in the User Portal. The message may include up to 400 characters. To view the message as it appears to end users, click Preview.

    Integration Owner

    1. From the Attribute dropdown menu, select User or Group under the relevant identity provider (IdP) platform.

    2. From the Value dropdown menu, select one or multiple users or groups.

    NOTE: When Resource Owner is defined, an Integration Owner must be defined.

    Resource Owner

    1. Enter a Key name. This value is the name of the tag created in your cloud environment.

    2. From the Attribute dropdown menu, select an attribute under the IdP platform to which the key name is associated. Apono will use the value associated with the key (tag) to identify the resource owner. When you update the membership of the group or role in your IdP platform, this change is also reflected in Apono.

    NOTE: When this setting is defined, an Integration Owner must also be defined.

  3. Click Confirm.

💡Are you integrating with Apono using Terraform?

If you want to integrate with Apono using Terraform, follow these steps instead of clicking Confirm:

  1. At the top of the screen, click View as Code. A modal appears with the completed Terraform configuration code.

  2. Click to copy the code.

  3. Make any additional edits.

  4. Deploy the code in your Terraform.

On-prem serving as a bridge between your Google Cloud PostgreSQL databases and Apono Minimum Required Version: 1.4.1 Use the following steps to .

for managing database instances with resources, such as BackupRuns, Databases, and Instances

(Built-in authentication only) with the credentials from step 1.

On the tab, click Google Cloud SQL - PostgreSQL. The Connect Integration page appears.

If the desired connector is not listed, click + Add new connector and follow the instructions for creating a connector.

(User/Password only) .

(Optional) Number of days after which the database credentials must be rotated Learn more about the .

Learn more about .

(Optional) Fallback approver if no is found Follow these steps to define one or several integration owners:

(Optional) Group or role responsible for managing access approvals or rejections for the resource Follow these steps to define one or several :

Refer to for more details about the schema definition.

Now that you have completed this integration, you can that grant permission to your Google Cloud SQL PostgreSQL instance.

create a new user
Create a secret
Catalog
GCP
Associate the secret or credentials
Integration Config Metadata
create access flows
connection
update an existing connector
API
Project ID
Dataset Name
Credentials Rotation Policy
Periodic User Cleanup & Deletion
resource owner
resource owners
Google Cloud SQL - PostgreSQL