PostgreSQL Permissions

When connecting Publisher to PostgreSQL databases, proper permission configuration ensures secure and efficient data access while maintaining database security policies. These permissions define how Publisher can interact with your schemas, tables, and system views through roles and user assignments.

This guide explains the minimum permissions required for PostgreSQL integration, covering role creation, schema access, and table-level permissions.

Concepts

• Schema: A namespace that contains named database objects such as tables, views, indexes, data types, functions, stored procedures, and operators.

• Role: A role is an entity that can own database objects and have database privileges. Depending on its use, a role can be considered a "user," "group," or both.

• User: A user is a role with the ability to log in (the role has the LOGIN attribute).

Minimal Configuration

To perform these operations, you must log in to your PostgreSQL database as an administrator.

The following will create a role we will use to assign permissions but not log in. It then creates a separate user who can log in and grant them access to all the tables in the target schema (this can be limited to a single table or even a single column within a table).

Create a Role

CREATE ROLE <ROLE_NAME>

Allow Connection to a Database

GRANT CONNECT ON DATABASE <DATABASE> TO <ROLE_NAME>;

Allow Role to use Schema

GRANT USAGE ON SCHEMA <SCHEMA> TO <ROLE_NAME>;

Grant Access to One or More Tables

All Tables in Schema:

GRANT SELECT ON ALL TABLES IN SCHEMA <SCHEMA> TO <ROLE_NAME>;

One Table in Schema:

GRANT SELECT ON <SCHEMA>.<TABLENAME> TO <ROLE_NAME>;

Specific Columns in Schema:

GRANT SELECT ON <SCHEMA>.<TABLENAME> (<COLUMN>,<COLUMN>) TO <ROLE_NAME>;

Create User and Assign them to Role

CREATE USER <USERNAME> WITH PASSWORD <PASSWORD>;
GRANT <ROLE> TO <USERNAME>

Limiting View to System Tables

In PostgreSQL, system tables and schemas are given the default PUBLIC roles when the database is first created. These tables are view-only tables used by Postgres directly and client-side tooling for running queries, accessing object metadata, query planning, auto-complete, etc. Limiting access to these tables is not recommended, and it is generally not considered a security risk to allow users to read access to them. Additionally, since they are views they only represent the set of permissions that the user actually possesses. IE They can only see the metadata for the tables they have permission to read from.