Minimum Permissions for Scan Accounts

Forcepoint DSPM PostgreSQL structured data connectors scan PostgreSQL data sources to enable cataloging and classification. This section outlines the minimum PostgreSQL privileges needed for a scan account, enabling customers to create a least-privilege user instead of relying on superuser, or a postgres equivalent account.

Applies to: PostgreSQL 14 and later (pg_read_all_data predefined role used below was introduced in PostgreSQL 14). For older PostgreSQL versions, see the alternative script at the end of the Granting data access section.

Connection Prerequisites
Requirement Reason
Network reachability to the PostgreSQL server host:port (default 5432) Establish TCP connection
pg_hba.conf entry permitting the scan account from the connector host PostgreSQL host-based authentication
A PostgreSQL role with LOGIN and CONNECT on the target database Establish JDBC connection

The connector uses the official PostgreSQL JDBC driver. No SUPERUSER role attribute is required.

How PostgreSQL handles pg_catalog and information_schema access

Both pg_catalog.* and information_schema.* are readable by all authenticated roles by default. Row-level visibility is filtered to objects on which the current role has at least one privilege. Granting pg_read_all_data (PostgreSQL 14+) gives the scan account USAGE on all schemas and SELECT on all tables - which both enables data sampling and unlocks full row visibility in the catalog views the connector relies on.

Operations and Required Permissions

  1. Connection test and database metadata
    Operation Catalog objects read Minimum grant
    Test connection   LOGIN, CONNECT on database
    Schema enumeration information_schema.schemata Implicit
    Table/view listing pg_catalog.pg_class, pg_catalog.pg_namespace Implicit
    Column / PK / FK metadata (bulk prefetch) pg_catalog.pg_attribute, pg_catalog.pg_attrdef, pg_catalog.pg_constraint Implicit
    Approximate row count pg_catalog.pg_class.reltuples Implicit
    Approximate table & index size pg_catalog.pg_total_relation_size(), pg_catalog.pg_relation_size() on pg_class.oid Implicit
  2. Data sampling
    Operation Tables read Minimum grant
    TABLESAMPLE SYSTEM row sample Target user table/view USAGE on schema + SELECT on table/view
    Column payload sampling Target user table/view Same as above
  3. Trustee (roles) extraction
    Operation Catalog objects read Minimum grant
    List database roles (users and groups) pg_catalog.pg_roles Implicit
    Recursive group membership pg_catalog.pg_roles, pg_catalog.pg_auth_members, Implicit

    PostgreSQL roles with rolcanlogin = true are classified as users; the rest are classified as groups.

  4. Permissions extraction
    Operation Catalog objects read Minimum grant
    Database-level CONNECT grants pg_catalog.pg_database, pg_catalog.pg_roles (via has_database_privilege) Implicit
    Schema-level USAGE/CREATE grants pg_catalog.pg_namespace, pg_catalog.pg_roles (via has_schema_privilege) Implicit
    Table-level grants (single + streaming) pg_catalog.pg_class.relacl via aclexplode(); information_schema.table_privileges pg_read_all_data (for full visibility)
    Column-level grants information_schema.column_privileges pg_read_all_data
    Superuser holders pg_catalog.pg_roles (rolsuper = true) Implicit

Granting data access

The scan account must be granted full read access to the entire datasource. PostgreSQL 14 and later include a predefined built-in role specifically designed for this purpose.
GRANT pg_read_all_data TO fp_user;
pg_read_all_data grants:
  • SELECT on all tables, views, and sequences (current and future).
  • USAGE on all schemas (current and future).

pg_read_all_data do not grant INSERT, UPDATE, DELETE, or EXECUTE. This is PostgreSQL's read-only equivalent of SQL Server's db_datareader and applies cleanly across all user schemas without per-table maintenance.

Older PostgreSQL versions (< 14)

For PostgreSQL versions earlier than 14, replace the pg_read_all_data grant with explicit grants on every user schema, plus ALTER DEFAULT PRIVILEGES so that future tables inherit the grant.
-- Repeat for each user schema:
GRANT USAGE ON SCHEMA <schema> TO fp_user;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO fp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO fp_user;
Consolidated grant script
-- 1) Create the scan login role
CREATE ROLE fp_user WITH LOGIN PASSWORD '<STRONG_PASSWORD>';

-- 2) Allow JDBC login to the target database
GRANT CONNECT ON DATABASE <target_database> TO fp_user;

-- 3) Data + metadata access — full read access to the entire datasource (PostgreSQL 14+)
GRANT pg_read_all_data TO fp_user;

Verification

Connect as fp_user and run:
-- Connection + role memberships
SELECT current_user, current_database();
SELECT r.rolname AS member_of
FROM pg_catalog.pg_roles r
JOIN pg_catalog.pg_auth_members m ON m.roleid = r.oid
JOIN pg_catalog.pg_roles me ON me.oid = m.member
WHERE me.rolname = current_user;

-- Metadata & data scan
SELECT schema_name FROM information_schema.schemata;
SELECT relname FROM pg_catalog.pg_class WHERE relkind IN ('r','v','p') LIMIT 5;
SELECT * FROM <schema>.<table> LIMIT 10;

-- Trustee scan
SELECT rolname, rolcanlogin, rolsuper FROM pg_catalog.pg_roles WHERE rolname NOT LIKE 'pg_%' LIMIT 10;
SELECT * FROM pg_catalog.pg_auth_members LIMIT 5;

-- Permissions extraction
SELECT * FROM information_schema.table_privileges LIMIT 5;
SELECT * FROM information_schema.column_privileges LIMIT 5;

All queries should return without permission denied. Empty result sets are acceptable when the underlying object has no matching rows.

What is not required

The scan account does not need any of the following:

  • The SUPERUSER role attribute

  • CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS role attributes

  • INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER on any object

  • CREATE on any schema

  • pg_write_all_data, pg_signal_backend, pg_monitor, pg_read_all_settings predefined roles.