GCP PostgreSQL | Entitle

What is GCP PostgreSQL?

Google Cloud Platform (GCP) PostgreSQL is a managed relational database service built on the open-source PostgreSQL engine. It allows users to deploy scalable, secure databases without managing the underlying infrastructure. The service includes built-in support for high availability, automated backups, replication, point-in-time recovery, and encryption.

How is this integration useful?

Integrating GCP PostgreSQL with Entitle helps eliminate manual provisioning of database roles and credentials, reduces the risk of over-permissioned accounts, and ensures that access to sensitive data is granted only when needed and only to authorized users. With auditability and policy enforcement built in, teams can maintain least-privilege access across GCP PostgreSQL environments while improving operational efficiency and compliance posture.

With this integration, Entitle can manage the following resource types in GCP Postgres SQL:

• Databases
• Schemas
• Servers
• Tables

End-user experience

1. Upon receiving a JIT (Just-In-Time) access using Entitle, the employee will receive a pre-made command for accessing GCP PostgreSQL via Teams/Slack.

   

2. The employee will log in to GCP and create a login password with the following command:

   she

   gcloud auth login; gcloud sql generate-login-token
   

3. The employee will connect to PostgreSQL by following the instructions given in the Connect using the Cloud SQL Auth Proxy Google Cloud guide.

Prerequisites

• Make sure you have Entitle's agent installed before proceeding with this guide.

  📘

  For further details, see Entitle's agent guide.

• You need a Google organization owner account.
• Access to your GCP PostgreSQL service account in GCP, as well as service account keys in both GCP and Postgres.

Create a service account in GCP

📘

If you're using an existing service account, proceed to step 5 in the following section.

1. Navigate to the GCP instance console. Make sure the project you wish to create a service account for is the appropriate one.

2. In the search bar, search for Service Accounts and select it from the results.

   

3. On the Service Accounts screen, click Create Service Account.

   

4. In the Service account details: Choose a service account ID or otherwise choose a name of your choice, and this field will be filled in automatically. Click Create and continue.

   

5. In Grant this service account access to project, search for the following roles in the list and add them one after another:

   • Cloud SQL Admin

   • Project IAM Admin

     

6. Click Done to create a new service account or save an existing one.

Create a key for the service account in GCP

1. Navigate to the Service Accounts screen in the GCP instance console.

2. Click on your newly created service account email.

   

3. Click Keys > Add key > Create new key.

   

4. In the new pop-up window, choose JSON from the key type options and click Create. The key will download to your computer.

   

Configure domain-wide delegation

📘

Your service account needs to have domain-wide delegation on certain scopes.

1. Go to the Manage Domain-Wide Delegation page

2. Click Add new.

   

3. In the Client ID field, enter the 21-digit number obtained from the key JSON file under the client_id field.

4. In the OAuth scopes field, enter the following scopes:

   https://www.googleapis.com/auth/admin.directory.user.readonly,
   https://www.googleapis.com/auth/admin.directory.group.readonly
   

Create a service account in Postgres

1. Connect to any database (users are cluster-general) using the GCP console or any other Postgres client. If you're using the GCP console, run: gcloud sql connect <database-name> --user=postgres --quiet
2. Run the following query on your Postgres cluster, after you have replaced the following fields with your credentials:
   • service_account_entitle: You may leave it or replace it as you wish.
   • PASSWORD: Your password (appears once in the query).

CREATE ROLE service_account_entitle WITH LOGIN CREATEROLE INHERIT CONNECTION LIMIT -1 PASSWORD '<PASSWORD>';
GRANT "cloudsqlsuperuser" TO service_account_entitle;

Create the integration in Entitle

💡

Important

This applies to creating an integration either through Entitle on Pathfinder or through the Entitle standalone product.

1. Sign in to Entitle.
2. Click Add Integration.
3. In the Application field, enter GCP Postgres.
4. In the Save on dropdown, select Save on cloud or your hosted agent.
5. In the Connection section:
   • In your GCP instance console Cloud overview > Project info, locate:
     • The project ID: Insert it in the project id field
     • The project (instance) name: Insert it in the instance_name field
     • The region: Insert it in the region field
   • In the user and password fields, insert the username and the password you created in the Create a service account in Postgres step.
   • Optional: in the options field, you can add the database names you wish to connect to. If you wish to connect to all databases, leave it empty.
   • In the gcp_config field, insert the gcp_config part of the Service Account key created in the Create a key for the service account in GCP step.
6. Example Connection JSON:
   JSON

   {
     "project_id": "<project id found in the instance console overview>",
     "instance_name": "<instance name found in the instance console overview>",
     "region": "<region name found in the instance console overview>",
     "postgres_config": {
       "user": "<username>",
       "password": "<users password>",
       "options": {
         "databases_constraints": ["<To connect to specific databases, list their names. To connect to all, leave it empty.>"]
       }
     },
     "gcp_config": { 
   	"credential_subject": "<an email of an admin in your organization which has permission to view all users and groups>",
       "service_account_key": {
         "type": "<found in service account key json>",
         "project_id": "<found in service account key json>",
         "private_key_id": "<found in service account key json>",
         "private_key": "<found in service account key json>",
         "client_email": "<found in service account key json>",
         "client_id": "<found in service account key json>",
         "auth_uri": "<found in service account key json>",
         "token_uri": "<found in service account key json>",
         "auth_provider_x509_cert_url": "<found in service account key json>",
         "client_x509_cert_url": "<found in service account key json>"
       }
     }
   }
   

7. Click Save.