Security and Compliance

Databases usually store the most valuable and sensitive data in an information system. Attacks and failures can therefore have severe consequences. Security requires multiple controls: who can connect, what they can do, and whether traffic is protected. It must also record operations and support recovery of damaged, lost, or deleted data.

Pigsty provides a default security baseline through declarative configuration. Declare HBA rules, roles and privileges, certificates, encryption, backups, and audit policies as parameters in the inventory. Idempotent playbooks render and apply these settings.

Security as Code supports policy versioning, review, and traceability. One inventory provides a consistent baseline across instances. To audit database access, start with the YAML declaration. Then verify generated HBA rules and database grants against the running system.

Security as Code

Manual operations can scatter security settings across pg_hba.conf, DBA-issued GRANT statements, and temporary incident firewall rules. Documentation can then diverge from the running configuration. This makes each instance’s effective rules difficult to determine.

Pigsty stores security policy in the cluster definition alongside other properties.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users:                     # Who may log in: account, role, and expiration
      - { name: dbuser_app ,password: '<unique-random-password>' ,roles: [dbrole_readwrite] ,expire_in: 365 }
    pg_databases:                 # Databases and their isolation policy
      - { name: app ,owner: dbuser_app ,revokeconn: true }
    pg_hba_rules:                 # Who may connect, from where, and how
      - { user: dbuser_app ,db: app ,addr: 10.1.0.0/16 ,auth: ssl ,order: 50 ,title: 'app access via ssl' }

Declare users, privileges, and HBA rules. Playbooks apply them idempotently to each instance, including new instances. Git history records configuration changes. Manual GRANT statements, runtime parameter changes, and node file edits can still cause drift. In production, regularly compare declared and actual state.

Default Security Baseline

The default configuration enables these controls to reduce omissions:

Hardening with Trade-offs

Defaults target a trusted intranet. Some controls need explicit enablement because of performance costs, compatibility limits, or required operator decisions:

• Examples contain public default passwords for quick starts and local tests. Before production, run ./configure -g to randomize recognized credentials. Then check the pgBackRest passphrase, Silo users in ha/safe, and all custom values.
• The Patroni REST API and PgBouncer disable TLS by default. Explicitly enable patroni_ssl_enabled and pgbouncer_sslmode with the issued certificates.
• Password checks (passwordcheck) and auditing (pgaudit) are disabled by default. Confirm package availability, then configure preloading and policy.
• SELinux defaults to permissive. Remove the demo firewall exception for port 5432 in production.
• Local backups have no encryption by default. The remote minio preset enables AES-256 encryption. Change its default encryption passphrase.

The ha/safe template combines TLS, certificate authentication, password checks, and backup encryption. Use it with the consistency-first CRIT parameter template as a starting point. Review public credentials, audit extensions, and the failure model explicitly. See Security Model for the full hardening path.

This Chapter

Related Topics

These pages provide operational security guidance:

• 🔰 Security Recommendations: minimum hardening for a quick single-node deployment
• 🛡️ Security Considerations: production hardening checklist
• 📄 ha/safe Template: complete hardening configuration reference
• 🔑 HBA Rules: detailed PGSQL HBA configuration
• 👤 Access Control: role and privilege parameter reference
• ♾️ High Availability: business continuity
• ⏰ Backup and Recovery: PITR and disaster recovery