Security and Compliance

The database is usually the most sensitive component in an information system: it stores the most valuable data, so attacks and failures can have the most serious consequences. Database security is not a feature that can be enabled with one switch. It is the combined answer to a series of questions: Who can connect? What can they do after connecting? Can traffic be intercepted? Are operations recorded? Can damaged, lost, or deleted data be recovered?

Pigsty turns these answers into an out-of-the-box security baseline and manages it through declarative configuration: HBA rules, roles and privileges, certificates, encryption, backups, and audit policies are declared as parameters in the inventory, then rendered and applied by idempotent playbooks.

This Security as Code approach is itself an important security practice. Policies can be versioned, reviewed, and traced, while one inventory provides a consistent baseline across many instances. When an auditor asks who can access a database, you can start from a readable YAML declaration, then verify the generated HBA rules and database grants against the running system.

Security as Code

In traditional operations, security settings are often scattered across the environment: pg_hba.conf on one server, a GRANT statement executed manually by a DBA, or a firewall rule opened temporarily during an incident. Over time, documentation and actual state can drift, making it difficult to determine which rule set each instance is using.

Pigsty takes a different approach: security policy is part of the cluster definition and lives alongside other cluster 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' }

Users, privileges, and HBA rules are described declaratively, and playbooks apply them idempotently to every cluster instance. New instances inherit the same policy, and Git history records security configuration changes. Manual GRANT statements, runtime parameter changes, and edits to node files can still cause drift, so production environments should compare declared and actual state regularly.

Default Security Baseline

Reasonable defaults reduce omissions. The following capabilities are enabled in the default Pigsty configuration:

Hardening with Trade-offs

The default configuration targets deployments on a trusted intranet. Some controls require explicit enablement because they impose performance or compatibility costs, or require decisions from the operator:

• Default configurations and examples contain publicly documented default passwords for quick starts and local testing. Before production deployment, use ./configure -g to randomize the credentials it recognizes, then check the pgBackRest encryption passphrase, Silo users in ha/safe, and all custom values.
• TLS is disabled by default for the Patroni REST API and PgBouncer (patroni_ssl_enabled, pgbouncer_sslmode); enable it explicitly with the certificates already issued.
• Password strength checks (passwordcheck) and the audit extension (pgaudit) are disabled by default. Confirm package availability, then configure preloading and policy before use.
• SELinux defaults to permissive. Demo configurations also expose port 5432 through the firewall; remove that exception in production.
• The local backup repository is not encrypted by default. The remote minio repository preset uses AES-256 encryption by default, but its default encryption passphrase must be changed.

The ha/safe hardening template combines TLS, certificate authentication, password checks, and backup encryption. Together with the consistency-first CRIT parameter template, it provides a practical starting point. Public credentials, audit extensions, and the failure model still require explicit review. See the Security Model for the complete upgrade path.

This Chapter

Related Topics

Beyond the conceptual model, 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