Postgres

Overview

Postgres is a powerful open-source relational database management system (RDBMS) known for its extensibility, standards compliance, and advanced features, making it a popular choice for robust data storage and retrieval.

Entitle can manage the following resource types in Postgres:

• Databases
• Clusters
• Schemas
• Tables
• Servers - to manage custom Postgres roles

This page will provide you with instructions on how to integrate Entitle and Postgres.

💡

Supported Versions

Entitle supports all Postgres versions, up to 16 (including).

Prerequisites

• A high privileged user (superuser) to create an integration user.
• In case your Postgres instance resides in a VPC - you will be requested to install an Entitle agent.

General guidelines

To integrate Postgres in Entitle, you will need to choose one of the methods to do so:

1. Provide Entitle with the superuser credentials.
   1. If you choose this option, please continue to the last part of this guide - Creating the integration in Entitle.
2. Create a service account for Entitle, and grant the service account superuser permissions.
   1. If you choose that option - please continue to the next part of this guide - Set up Postgres to work with Entitle.

Set up Postgres to work with Entitle

Postgres Integration requires the user who is provided to have certain privileges.

Do the following steps to create a user and grant it the required permissions:

⚠️

Note

In case you need to manage permissions in a ‘Public’ Schema, please refer to the last section in this guide.

Option 1: RDS-managed Postgres instances

• Connect to any database (users are cluster-general) and run the following query on your Postgres cluster:

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

• These commands will create the Entitle service account and provide it with superuser permissions.
• Keep the username and password, as you will use them later in this guide.

Option 2: Non-RDS-managed Postgres instances

• Connect to any database (users are cluster-general) and run the following query on your Postgres cluster:

CREATE USER service_account_entitle WITH SUPERUSER PASSWORD '<YOUR_PASSWORD>';

• These commands will create the Entitle service account and provide it with superuser permissions.
• Keep the username and password, as you will use it later in this guide.

Managing permissions for 'public' schema

By default, users in Postgres have implicit permission through the 'public' role to create and modify new tables in the 'public' schema. This behavior will persist even after access is revoked by Entitle.

To completely revoke access to the 'public' schema using Entitle, the default permissions of the 'public' role should be manually revoked per database.

⚠️

Note

Revoking the default permissions of the 'public' role should be executed with caution.

It might revoke access from existing users, including those in production and service accounts. These users might rely on implicit permissions if their access wasn't set up explicitly. We advise that you first grant these permissions explicitly to these users, and only then remove them from the 'public' role.

Grant explicit permissions to existing users using the following commands (should be run per database):

GRANT ALL ON SCHEMA public TO "<USER_NAME>"; -- allows a specific user to modify new tables in the 'public' schema.
GRANT CONNECT ON DATABASE "<DATABASE_NAME>" TO "<USER_NAME>"; -- allows a specific user explicitly to connect to the database and get access to the 'public' schema.

Revoke default permissions of the 'public' role using the following commands (should be run per database):

REVOKE ALL ON SCHEMA public FROM public; -- prevents all users from being able to modify new tables in the 'public' schema.
REVOKE ALL ON DATABASE "<DATABASE_NAME>" FROM public; -- prevents all users from being able to connect to the database, get access to the 'public' schema, and create or modify new data.

Creating the integration in Entitle

All that is left to do is create an integration on the Entitle application.

1. Log into Entitle and navigate to the Integrations page.
2. After clicking the Add Integration button, type Postgres in the Application field.
3. Don’t forget to set the Save on field with your configuration, i.e. your own hosted agent or Entitle’s cloud.

4. Fill the integration user name in the user field.
5. Fill the integration password in the password field.
6. Paste the host in the host field.
7. Fill the port in the port field.
8. If you wish to narrow down the resource types managed by Entitle, select the resource types and add them to the "resource_types_constraints" field, otherwise keep that value empty ("resource_types_constraints": []").

Databases
Schemas
Tables
Cluster

Example Connection JSON:

{
  "user": "<Entitle Integration Username>",
  "password": "<Entitle Integration Password>",
  "host": "<Postgres Host>",
  "port": 5432,
  "options": {
    "resource_types_constraints": [],
    "databases_constraints": []
  }
}

9. Click Save and you are done!🎉