Access Control

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:

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:

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.

Related Documentation

• Access Control Concepts: roles, default privileges, and isolation boundaries
• Authentication: HBA, SCRAM, and client certificates
• User Configuration: user and role fields
• HBA Configuration: connection-entry rules