Access Control

Authentication answers “Who are you?” Authorization answers “What may you do?”

Privilege failures rarely result from a lack of mechanisms—PostgreSQL GRANT and REVOKE are sufficiently precise. The usual problem is the absence of conventions that are applied by default: an application account is made the owner at launch, temporary superuser access is not revoked after troubleshooting, or grants are missed when new tables are created and cause failures in production.

Pigsty provides an out-of-the-box access control model as a starting point: four role tiers, default privileges, and database isolation. It reduces per-database manual grants, but operators must still assign roles according to business boundaries and review effective privileges regularly.

Role System

Pigsty creates four business roles by default. They cannot log in and are used as privilege groups:

Pigsty also creates four system users, each with a specific responsibility:

Application accounts join role groups through the roles field and inherit their privileges:

pg_users:
  - { name: dbuser_app    ,password: '...' ,roles: [dbrole_readwrite] }  # Regular application account
  - { name: dbuser_report ,password: '...' ,roles: [dbrole_readonly]  }  # Read-only reporting account
  - { name: dbuser_etl    ,password: '...' ,roles: [dbrole_offline]   }  # Offline ETL account

The role system is itself declarative (pg_default_roles) and can be customized. This parameter is a complete list. Preserve all required system users and default roles when changing it, and check references from HBA rules, default privileges, and scripts at the same time.

Default Privileges

Roles answer “Who receives a privilege?” The other half of the problem is: How do newly created objects receive the correct privileges automatically?

PostgreSQL provides ALTER DEFAULT PRIVILEGES. Pigsty declares these rules through pg_default_privileges:

pg_default_privileges:            # Apply these privileges to new objects created by managed identities
  - GRANT USAGE      ON SCHEMAS   TO dbrole_readonly
  - GRANT SELECT     ON TABLES    TO dbrole_readonly
  - GRANT SELECT     ON SEQUENCES TO dbrole_readonly
  - GRANT EXECUTE    ON FUNCTIONS TO dbrole_readonly
  - GRANT USAGE      ON SCHEMAS   TO dbrole_offline
  - GRANT SELECT     ON TABLES    TO dbrole_offline
  - GRANT SELECT     ON SEQUENCES TO dbrole_offline
  - GRANT EXECUTE    ON FUNCTIONS TO dbrole_offline
  - GRANT INSERT     ON TABLES    TO dbrole_readwrite
  - GRANT UPDATE     ON TABLES    TO dbrole_readwrite
  - GRANT DELETE     ON TABLES    TO dbrole_readwrite
  - GRANT USAGE      ON SEQUENCES TO dbrole_readwrite
  - GRANT UPDATE     ON SEQUENCES TO dbrole_readwrite
  - GRANT TRUNCATE   ON TABLES    TO dbrole_admin
  - GRANT REFERENCES ON TABLES    TO dbrole_admin
  - GRANT TRIGGER    ON TABLES    TO dbrole_admin
  - GRANT CREATE     ON SCHEMAS   TO dbrole_admin

The read-only role receives query and function execution privileges, the read-write role adds DML, and the administrator role adds the supporting privileges required for object management.

Ownership Convention

Default privileges have an often-missed prerequisite: they apply only to objects created by identities for which those defaults were configured. Pigsty configures default privileges for:

• the database OS user pg_dbsu, which defaults to postgres;
• the administration user pg_admin_username, which defaults to dbuser_dba;
• dbrole_admin;
• each database owner declared in pg_databases.

Application DDL should normally run as the declared database owner. Platform administration and release workflows can use dbuser_dba or first execute SET ROLE dbrole_admin. Objects created directly by other users do not enter this default privilege model unless ALTER DEFAULT PRIVILEGES is also configured for those users.

This is PostgreSQL behavior, not a Pigsty limitation: default privileges follow the object creator; they do not automatically propagate from the database or the session login name.

Database Isolation

PostgreSQL grants CONNECT on databases to PUBLIC by default. If HBA also permits a connection, a login role may enter a database it does not own. This default is particularly important to tighten when several applications share a cluster.

Set revokeconn in a database definition to revoke public connection access:

pg_databases:
  - { name: app_a ,owner: dbuser_a ,revokeconn: true }
  - { name: app_b ,owner: dbuser_b ,revokeconn: true }

When enabled, CONNECT is revoked from PUBLIC and granted explicitly to the replication, monitoring, and administration users and to the database owner. The owner receives GRANT OPTION and can decide who else may connect. Without additional grants or inherited roles, the app_a account cannot connect to app_b.

Cluster initialization also revokes CREATE from PUBLIC on the database and the public schema:

REVOKE CREATE ON DATABASE app FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Ordinary users can no longer create objects freely in public databases or schemas, reducing risks from unsafe search_path settings and object shadowing. PostgreSQL 15 tightened the default CREATE privilege on the public schema; Pigsty applies the same boundary consistently across all supported major versions.

Offline Role and Instance Isolation

dbrole_offline provides an independent set of read-only privileges for ETL, reporting, and ad hoc queries. The role controls object privileges only; it does not automatically restrict which instance a user may connect to.

In the current default HBA rules, the intranet rule for +dbrole_offline does not set role and therefore applies to every instance. To restrict it to a dedicated pg_role: offline instance, or to a regular replica marked with pg_offline_query: true, modify that rule in the complete pg_default_hba_rules list:

pg_default_hba_rules:
  # Copy and retain all other default rules; change only the offline-role rule
  - { user: '+dbrole_offline', db: all, addr: intra, auth: pwd, role: offline, order: 650,
      title: 'allow offline users on offline instances' }

Defining pg_default_hba_rules replaces the entire default list; the example rule cannot be used alone. Expensive queries are limited to offline instances only when HBA filters by instance role and the user does not inherit another role allowed by broader rules. Resource isolation should also use a dedicated service endpoint, connection limits, and query resource controls.

Beyond the Database

Least privilege also applies at the host level:

• The postgres superuser has no password and can log in only through local ident. Its sudo access defaults to a restricted set of database service and log commands (pg_dbsu_sudo: limit).
• The monitoring user dbuser_monitor holds pg_monitor, the read-only role, and privileges on the dedicated monitor schema; it cannot write business tables by default.
• The replication user replicator receives only the directory function privileges required for backup and recovery instead of broad superuser access.

Next

• 📖 Complete role and privilege reference: Access Control Configuration
• 🔑 Authentication: the first gate before authorization
• 🔒 Data Security: protection beyond privileges