Skip to content

Access Control

Configuration reference for Pigsty built-in roles, users, default privileges, and database ACLs.

Access control combines roles, object privileges, database ACLs, and HBA. This page covers configuration parameters; see Access Control Concepts for design and boundaries.

Pigsty provides a compact ACL model described by these parameters:

  • pg_default_roles: system roles and system users.
  • pg_users: application users and roles.
  • pg_default_privileges: default privileges on objects created by managed administrators and owners.
  • pg_revoke_public, pg_default_schemas, and pg_default_extensions: default behavior for template1.

Manage these parameters together with HBA and database definitions to produce reproducible access-control configuration.

Default Role System (pg_default_roles)

The defaults contain four business roles and four system users:

Name Type Description
dbrole_readonly NOLOGIN Shared read-only role with SELECT and USAGE
dbrole_readwrite NOLOGIN Inherits read-only and adds INSERT, UPDATE, and DELETE
dbrole_admin NOLOGIN Inherits pg_monitor and read-write; can create objects and triggers
dbrole_offline NOLOGIN Independent read-only role; instance scope must be restricted explicitly through HBA
postgres User System superuser; same name as pg_dbsu
replicator User Streaming replication and backup; inherits monitoring and read-only privileges
dbuser_dba User Primary administration account, also synchronized to PgBouncer
dbuser_monitor User Monitoring account with pg_monitor; records slow SQL by default

These definitions live in pg_default_roles. The parameter is a complete list. When customizing it, copy and retain the required default roles and system users, then add new roles in dependency order. If a role name changes, update references in HBA, default privileges, and scripts.

Default Users and Credential Parameters

These parameters control system-user names and passwords:

Parameter Default Purpose
pg_dbsu postgres Database and OS superuser
pg_dbsu_password Empty string dbsu password, disabled by default
pg_replication_username replicator Replication user name
pg_replication_password DBUser.Replicator Replication password
pg_admin_username dbuser_dba Administrator user name
pg_admin_password DBUser.DBA Administrator password
pg_monitor_username dbuser_monitor Monitoring user
pg_monitor_password DBUser.Monitor Monitoring password

After changing these parameters, update the corresponding user definitions in pg_default_roles so user names and role attributes remain consistent.

Application Roles and Grants (pg_users)

Declare application users with pg_users; see User Configuration for field details. The roles field grants business roles.

Example read-only and read-write users:

pg_users:
  - { name: app_reader,  password: DBUser.Reader,  roles: [dbrole_readonly],  pgbouncer: true }
  - { name: app_writer,  password: DBUser.Writer,  roles: [dbrole_readwrite], pgbouncer: true }

Application users inherit default object privileges through dbrole_*. Database CONNECT privileges and pg_hba_rules continue to control which databases and sources can connect.

For finer ACLs, use standard GRANT and REVOKE in baseline SQL or a later playbook, and include those additional grants in reviews.

Default Privilege Template (pg_default_privileges)

pg_default_privileges applies to pg_dbsu, pg_admin_username, dbrole_admin, and every declared database owner. The default template is:

pg_default_privileges:
  - 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

Objects created by these identities receive the corresponding privileges automatically. Other object creators need their own ALTER DEFAULT PRIVILEGES configuration.

Additional notes:

  • pg_revoke_public defaults to true, revoking CREATE from PUBLIC on databases and the public schema.
  • pg_default_schemas and pg_default_extensions control schemas and extensions created in template1/postgres, usually for monitoring objects such as the monitor schema and pg_stat_statements.

Common Scenarios

Read-only Account for a Partner 

pg_users:
  - name: partner_ro
    password: Partner.Read
    roles: [dbrole_readonly]
pg_hba_rules:
  - { user: partner_ro, db: analytics, addr: 203.0.113.0/24, auth: ssl }

This adds an HBA rule allowing the partner to reach analytics over TLS from the specified CIDR. pg_hba_rules does not remove broader default rules. If the account must reach only this database, also narrow the default HBA policy and configure database CONNECT privileges.

DDL for an Application Administrator 

pg_users:
  - name: app_admin
    password: DBUser.AppAdmin
    roles: [dbrole_admin]

app_admin inherits DDL privileges from dbrole_admin. To apply the default privileges configured for dbrole_admin to new objects, run SET ROLE dbrole_admin first. If app_admin is a declared database owner, it can also create objects directly as that owner.

Custom Default Privileges 

pg_default_privileges:
  - GRANT INSERT,UPDATE,DELETE ON TABLES TO dbrole_admin
  - GRANT SELECT,UPDATE ON SEQUENCES TO dbrole_admin
  - GRANT SELECT ON TABLES TO reporting_group

This parameter replaces the complete default privilege list. Referenced roles must already exist. Changes affect only objects created afterward; grant privileges separately on existing objects.

Integration with Other Components

  • HBA rules: use pg_hba_rules to bind roles, databases, and sources. To restrict dbrole_offline, set role: offline on its rule.
  • PgBouncer: users with pgbouncer: true are written to userlist.txt; pool_mode and pool_connlimit control pool-level quotas.
  • Database monitoring: dbuser_monitor receives privileges from pg_default_roles. When adding another monitoring user, grant pg_monitor and check access to the monitor schema.

These parameters can be versioned with the inventory. Continue to review effective privileges through PostgreSQL catalogs.