Security and Compliance

• 1: Security Model
• 2: Authentication
• 3: Access Control
• 4: Encrypted Communication
• 5: Data Security
• 6: Compliance

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' }

1 - Security Model

Identify the root of trust and defensive layers before selecting security features. The root of trust needs the strongest protection. Multiple layers preserve some protection if one fails.

Trust Boundaries

Pigsty uses Ansible for declarative deployment. Its administration node is the control plane and needs the strongest protection.

These roles have different capabilities, not a simple hierarchy. Protect these three assets in particular:

1. The pigsty.yml inventory contains component passwords and credentials. Strictly restrict administration node access and access to any Git configuration repository.
2. The CA private key, files/pki/ca/ca.key, anchors deployment trust. Its holder can issue any trusted certificate. The file uses mode 0600 inside a 0700 directory. Keep an offline backup.
3. The administration user’s SSH private key provides passwordless sudo access to all enrolled nodes. It effectively grants root access throughout the managed deployment.

Pigsty’s security policy defines this trust boundary. Attacks requiring administration node access, or both pigsty.yml and the CA private key, are not product vulnerabilities. These control-plane assets have high trust by design. Protect them accordingly.

Seven Defensive Layers

Defense in depth combines controls so that one failure does not remove all protection. Pigsty provides seven security layers:

Layers 2, 3, 4, 6, and 7 have dedicated chapters. The following sections cover the network and host layers.

Network Boundaries

Node provisioning enables firewalld or ufw, depending on the OS. node_firewall_mode defaults to zone. node_firewall_intranet places 10.0.0.0/8, 172.16.0.0/12, and 192.168.0.0/16 in the trusted zone. Public networks can reach only node_firewall_public_port ports. Defaults are 22 for SSH and 80/443 for web traffic.

The demo pigsty.yml also exposes 5432 for local evaluation. Remove this exception in production. If direct database access is needed, restrict sources to explicit CIDRs through security groups, host firewalls, and HBA.

PostgreSQL listens on all addresses by default: pg_listen is 0.0.0.0. Listen addresses, firewall rules, and HBA jointly control access. For stricter access, constrain the listener:

pg_listen: '${ip},${vip},${lo}'   # Host IP, cluster VIP, and loopback only

The default firewall blocks direct public access to Grafana, VictoriaMetrics, and other web infrastructure. External web traffic normally uses the Nginx portal. Database traffic uses HAProxy service ports. Fewer entry points simplify hardening and auditing.

Host Security

Give each OS user only the privileges its job requires.

• The database superuser postgres (pg_dbsu) has no password by default. It can enter the database only through local ident. pg_dbsu_sudo defaults to limit. This permits passwordless database service systemctl operations and log viewing, without unrestricted root access.
• Operators and playbooks use node_admin_username, default dba. It receives passwordless sudo (nopass) by default. For stricter control, set node_admin_sudo to all to require a password, or limit to restrict commands.
• node_selinux_mode defaults to permissive. SELinux records violations without blocking them, providing a baseline before enforcing.

Pigsty does not manage SSH server configuration. Put password-login restrictions, remote-root restrictions, and similar OS hardening in your host security baseline.

Hardening Levels

Harden the deployment in stages. Each level adds to the previous level:

Level 1: default baseline. Defaults provide SCRAM passwords, data checksums, a local CA, component certificates, layered HBA, four-tier roles, backups, and firewall zones. This suits development, testing, and evaluation on a trusted intranet. For production, review credentials and network boundaries, and configure client verification.

Level 2: randomized credentials. Change all public default passwords before any network-exposed deployment. Add -g to randomize recognized built-in parameters and example credentials:

./configure -g    # --generate: randomize recognized default credentials

This option does not replace pgBackRest cipher_pass, all Silo examples in ha/safe, or custom values. See the Default Credentials Checklist for coverage.

Level 3: policy hardening with ha/safe. conf/ha/safe.yml combines controls for further customization:

• TLS and certificates: Main TCP HBA rules use ssl. Public administrator access requires a client certificate. PgBouncer uses require, and Patroni API uses HTTPS. Local ident and selected localhost password rules remain.
• Password policy: The template preloads passwordcheck and sets expire_in for built-in users. Review and replace example passwords.
• Reduced exposure: Listen addresses are limited to ${ip},${vip},${lo}. Public connection-pool access is explicitly denied for monitoring and administration accounts.
• Backup encryption: pgBackRest uses the remote minio preset with AES-256-CBC. Replace the predictable example pgBR.${pg_cluster}.
• Security extensions: The template installs passwordcheck, credcheck, pgaudit, pgsodium, anonymizer, and related extensions. Installation does not preload, create, or configure them.

Level 4: database hardening with crit.yml. The safe template selects CRIT for workloads that prioritize consistency. Compared with oltp, CRIT:

• Requires checksums regardless of pg_checksum.
• Enables strict synchronous replication (synchronous_mode_strict). Writes needing synchronous acknowledgment block if no synchronous replica is available.
• Logs connections and disconnections. PostgreSQL 18 separates connection receipt, authentication, and authorization.
• Sets watchdog to automatic. It activates only if a usable device exists.

Strict synchronous mode aims to preserve acknowledged transactions. It depends on synchronous_commit, synchronous replica state, and failover eligibility. Verify RPO with failure exercises on the target topology.

You can select individual controls instead of the whole template:

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1 , pg_role: primary }
    10.10.10.11: { pg_seq: 2 , pg_role: replica }
    10.10.10.12: { pg_seq: 3 , pg_role: replica }
  vars:
    pg_cluster: pg-meta
    pg_conf: crit.yml                    # Use the CRIT database parameter template
    patroni_ssl_enabled: true            # Enable HTTPS for the Patroni API
    pgbouncer_sslmode: require           # Require TLS for PgBouncer
    pg_listen: '${ip},${vip},${lo}'      # Constrain listen addresses
    pg_libs: '$libdir/passwordcheck, pg_stat_statements, auto_explain'  # Password strength checks

Next

• 🔑 Authentication: HBA rules and password policy
• 👤 Access Control: roles and least privilege
• 🔐 Encrypted Communication: local CA and TLS
• 🔒 Data Security: integrity, backup, and audit
• ✅ Compliance: MLPS and SOC 2 mapping

2 - Authentication

pg_hba.conf defines PostgreSQL Host-Based Authentication. It controls who can connect, the source address, the database, and the required identity check.

Manual HBA maintenance across a cluster requires care. Primary and replica rules can differ, and each instance stores its configuration in its data directory. Without a shared declaration and refresh process, instance rules can diverge.

Pigsty declares HBA rules in the inventory through declarative configuration. Playbooks render and distribute them consistently.

HBA as Code

Cluster HBA combines global defaults in pg_default_hba_rules with cluster additions in pg_hba_rules. PgBouncer has separate equivalents: pgb_default_hba_rules and pgb_hba_rules.

Rules support two forms. The recommended alias form expresses one rule per line:

pg_hba_rules:
  - { user: dbuser_app ,db: app ,addr: 10.1.0.0/16 ,auth: ssl ,order: 50 ,title: 'app user access via ssl' }

Use the raw form for cases aliases cannot express. It contains a literal pg_hba.conf line.

In addition to user, address, database, and authentication method, each rule has two control fields:

• order sets render order. HBA uses the first matching rule, so order sets priority. By convention, 0-99 is for high-priority user rules and 100-999 for defaults. Rules without order come last.
• role filters by instance role. common and default apply everywhere. primary, replica, offline, standby, and delayed apply only to matching instances. role: offline also applies to instances marked with pg_offline_query. One declaration therefore generates appropriate rules without separate manual primary and replica files.

After editing the declaration, run the wrapper script to render and reload the rules:

bin/pgsql-hba pg-meta          # Render and apply HBA rules for pg-meta

pg_hba_rules adds rules but does not automatically restrict broader defaults. For stricter access, also review pg_default_hba_rules. Inspect the resulting pg_hba.conf on every instance.

Address and Authentication Aliases

Aliases name common cases. Each addr value expands into address blocks:

Values in auth select the authentication method and whether TLS is mandatory:

The user field supports four placeholders: ${dbsu} (superuser), ${repl} (replication user), ${monitor} (monitoring user), and ${admin} (administration user). Rendering replaces them with actual names. +role matches all members of that role.

auth: ssl requires TLS but does not require client verification of the server identity. Security-sensitive clients should use sslmode=verify-full with a trusted CA. See Encrypted Communication.

Default Rules Explained

The default HBA policy requires stronger authentication for more distant sources. These PostgreSQL defaults come from the source configuration:

pg_default_hba_rules:             # postgres default host-based authentication rules, order by `order`
  - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident'  ,order: 100}
  - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' ,order: 150}
  - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: pwd   ,title: 'replicator replication from localhost',order: 200}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' ,order: 250}
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,title: 'replicator postgres db from intranet' ,order: 300}
  - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' ,order: 350}
  - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: pwd   ,title: 'monitor from infra host with password',order: 400}
  - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl'   ,order: 450}
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: ssl   ,title: 'admin @ everywhere with ssl & pwd'    ,order: 500}
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket',order: 550}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password'     ,order: 600}
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet',order: 650}

Layer by layer:

• Local access: postgres can connect only through a local Unix socket with ident. It needs no password and cannot log in remotely. Thus, dbsu has no default password.
• Intranet access: Replication and application accounts use SCRAM password authentication. Remote monitoring and administration connections mainly come from INFRA nodes.
• Public access: Only the administrator can connect from any address by default. Both a password and TLS are required.

PgBouncer explicitly denies public access for monitoring and administration accounts. Application users can connect only from localhost and intranet sources.

The default +dbrole_offline rule has no role filter and applies to every instance. Add role: offline to restrict it to pg_role: offline or pg_offline_query: true instances.

The default policy lets application accounts connect from the intranet with passwords. ha/safe changes the main TCP rules to ssl. Administrators outside the intranet must present a client certificate (cert). Local ident and selected localhost password rules remain.

Password Policy

Pigsty defaults to PostgreSQL’s recommended scram-sha-256 password storage through pg_pwd_enc. Use md5 only for legacy clients.

Before ALTER USER ... PASSWORD, the workflow runs SET log_statement TO 'none' to temporarily disable statement logging. This keeps passwords out of PostgreSQL logs. The inventory still contains plaintext passwords. Rendered user SQL is stored at /pg/tmp/pg-user-<name>.sql with mode 0640. Related Ansible tasks do not consistently use no_log. Restrict access to the administration node, configuration repository, and automation output.

Avoid --diff for tasks that contain credentials.

Password strength checks are disabled by default. If required, preload passwordcheck or the more configurable credcheck:

pg_libs: '$libdir/passwordcheck, pg_stat_statements, auto_explain'   # Reject weak passwords

ha/safe sets this pg_libs value explicitly. Selecting CRIT alone does not load passwordcheck.

Set account lifetime with expire_in (days after creation) or expire_at (absolute date). Combine this with your organization’s rotation process:

pg_users:
  - { name: dbuser_app ,password: '<unique-random-password>' ,roles: [dbrole_readwrite] ,expire_in: 365 }

Certificate Authentication

Passwords can be phished, reused, or guessed. For privileged accounts, such as administrators, set HBA auth: cert to require client certificates. The local CA must sign the certificate, and its CN must match the database user name. If the rule accepts only cert, a leaked password alone cannot authenticate.

Issue client certificates with the built-in cert.yml playbook:

./cert.yml -e cn=dbuser_dba                  # Issue a 20-year client certificate by default
./cert.yml -e cn=dbuser_dba -e expire=365d  # Or specify a shorter lifetime

The files are files/pki/misc/<cn>.crt and files/pki/misc/<cn>.key. Deliver the private key through a controlled channel. The client should use verify-full to verify the database server. See Encrypted Communication.

Connection Pool and Component APIs

The database is not the only authenticated entry point.

PgBouncer has separate HBA rules and a separate user list. pgbouncer_auth_query is disabled by default. Only users with pgbouncer: true enter userlist.txt and can authenticate through the pool. Review permitted logins before enabling dynamic authentication queries.

The Patroni REST API controls operations such as restart, switchover, and configuration reload. Writes require HTTP Basic authentication through patroni_username and patroni_password. Source-address allowlists also restrict writes. Enable patroni_ssl_enabled to use HTTPS throughout.

The inventory also declares credentials for Grafana, HAProxy administration, the MINIO-selected object storage backend, etcd, and other components. See the Default Credentials Checklist for the full list and update instructions.

Next

• 📖 Complete HBA reference: HBA Rules
• 👤 Access Control: authorization after authentication
• 🔐 Encrypted Communication: TLS and client certificate infrastructure

3 - Access Control

Authentication verifies identity. Authorization determines what that identity can do.

PostgreSQL GRANT and REVOKE provide precise control. Privilege problems usually arise from missing default conventions. Examples include making an application account the owner, retaining temporary superuser access after troubleshooting, and omitting grants on new tables.

Pigsty provides four role tiers, default privileges, and database isolation. This starting model reduces manual grants per database. Operators must still assign roles to match business boundaries and regularly review effective privileges.

Role System

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

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

Customize the declarative role system through pg_default_roles. This parameter replaces the complete list. Retain all required system users and default roles. Also check references in HBA rules, default privileges, and scripts.

Default Privileges

Roles define who receives privileges. Default privileges give newly created objects the required grants 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 can query data and execute functions. The read-write role also has DML privileges. The administrator role adds privileges needed for object management.

Ownership Convention

Default privileges apply only to objects created by identities with those defaults configured. Pigsty configures them 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. Administration and release workflows can use dbuser_dba or first run SET ROLE dbrole_admin. Other users need their own ALTER DEFAULT PRIVILEGES configuration. Otherwise, objects they create do not receive this default privilege model.

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

Database Isolation

By default, PostgreSQL grants database CONNECT to PUBLIC. If HBA permits it, a login role can enter a database it does not own. Restrict this default when multiple 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 }

This revokes CONNECT from PUBLIC. It grants access explicitly to the replication, monitoring, and administration users and the database owner. The owner receives GRANT OPTION and can grant access to others. Without additional grants or inherited roles, app_a 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 cannot freely create objects in public databases or schemas. This reduces risks from unsafe search_path settings and object shadowing. PostgreSQL 15 restricted the default CREATE privilege on the public schema. Pigsty applies this boundary to all supported major versions.

Offline Role and Instance Isolation

dbrole_offline provides independent read-only privileges for ETL, reporting, and ad hoc queries. It controls object access, not which instance accepts connections.

The default intranet HBA rule for +dbrole_offline has no role filter, so it applies to every instance. To restrict it to pg_role: offline or a replica with pg_offline_query: true, change 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' }

pg_default_hba_rules replaces the entire list; do not use the example rule alone. HBA limits expensive queries to offline instances only if it filters by instance role. Users must also lack other inherited roles permitted by broader rules. Add a dedicated service endpoint, connection limits, and query resource controls for resource isolation.

Beyond the Database

Least privilege also applies at the host level:

• The postgres superuser has no password and permits only local ident login. Its sudo access defaults to required database service and log commands (pg_dbsu_sudo: limit).
• dbuser_monitor has pg_monitor, the read-only role, and privileges on the monitor schema. By default, it cannot write business tables.
• replicator receives only directory function privileges needed for backup and recovery, without general superuser access.

Next

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

4 - Encrypted Communication

TLS can provide transport encryption, server authentication, and client authentication. Configure each protection separately. Enabling server TLS does not make clients verify the server or make the server require client certificates.

Most TLS operational work involves certificate issuance, distribution, trust, and rotation. Without central management, services often skip verification or keep plaintext connections.

Pigsty manages PKI declaratively. Deployment creates a local self-signed CA, issues component certificates, and distributes trust. TLS is then ready for use.

Local CA

During initial deployment, Pigsty checks for a CA on the administration node. It creates one when required:

• ca_create controls CA behavior. Pigsty reuses an existing key and certificate unchanged. If only the key exists, it issues a replacement certificate with that key. ca_create: false prevents creation of a missing private key. If ca.key is absent, deployment stops instead of creating an unexpected trust root. Always back up and restore ca.key and ca.crt together.
• ca_cn sets the certificate CN, default pigsty-ca. The key is RSA 4096.
• The root CA is valid for 100 years. Component certificates default to 20 years (cert_validity: 7300d). The browser-facing Nginx certificate is an exception, with a current default of 397 days.

Long default lifetimes reduce initial maintenance for private infrastructure. Production still needs certificate rotation. Organizations with a certificate policy should shorten lifetimes and monitor expiration.

Trust Distribution

Every node must trust the CA. When managing a node, Pigsty copies the CA certificate to /etc/pki/ca.crt and links it into the OS trust store:

• EL family (RHEL, Rocky, Alma): link under /etc/pki/ca-trust/source/anchors/ and run update-ca-trust
• Debian and Ubuntu: link under /usr/local/share/ca-certificates/ and run update-ca-certificates

Clients that use the OS trust store, such as curl, can then verify Pigsty CA certificates. The Nginx portal also publishes ca.crt at its site root for browsers and external clients.

By default, PostgreSQL libpq clients look for ~/.postgresql/root.crt and use sslmode=prefer. They do not directly use the OS trust store to verify server identity.

Server Identity Verification

Security-sensitive PostgreSQL clients should use sslmode=verify-full and specify the Pigsty CA:

psql "host=pg-meta dbname=postgres user=dbuser_dba sslmode=verify-full sslrootcert=/etc/pki/ca.crt"

verify-full checks the certificate chain and connection hostname. The server certificate SAN must contain the client’s DNS name or IP address. External clients must install ca.crt or specify it with sslrootcert.

Certificate Matrix

The local CA issues certificates for these components under one trust chain:

These defaults distinguish available TLS from enabled TLS:

• Enabled at deployment: PostgreSQL accepts SSL connections. etcd uses TLS for clients and peers.
• Encrypted by default: Object storage backup traffic through MINIO and Nginx web traffic use HTTPS.
• Available but disabled: Patroni API and PgBouncer TLS is disabled, with certificates already installed. Enable the corresponding parameters. ha/safe enables both.

SSL support, mandatory encryption, and client verification of server identity are separate states. HBA enforces encryption with auth: ssl or cert. Client sslmode and trust settings control server verification. Default rules require TLS only for administrator connections from arbitrary sources. The safe template uses ssl or cert for main TCP rules. It retains local ident and selected localhost password rules.

Client Certificates

cert.yml issues client certificates. For HBA cert authentication, the certificate CN represents the database user name:

./cert.yml -e cn=dbuser_dba                  # Issue a 20-year client certificate by default
./cert.yml -e cn=dbuser_dba -e expire=365d  # Or specify a shorter lifetime

The files are files/pki/misc/<cn>.key and files/pki/misc/<cn>.crt. Deliver private keys through a controlled channel and allow only the corresponding user to read them. Client certificates let the server verify clients. The client must still use verify-full to verify the database server.

Using an Enterprise CA

Pigsty can issue certificates from an existing enterprise CA or an intermediate signed by its root. Put the certificate and private key at these paths. Playbooks reuse an existing CA:

files/pki/ca/ca.key    # CA or intermediate CA private key
files/pki/ca/ca.crt    # Corresponding CA certificate

Also set ca_create: false. Deployment then stops if the private key is missing, rather than creating a new trust root. However, an existing key with a missing certificate still causes certificate reissuance. Verify and restore both files together.

Key Protection and Rotation

• The CA private key exists only on the administration node. It and pigsty.yml are among the deployment’s highest-trust assets. Keep an offline backup. See Trust Boundaries.
• If the CA key is compromised, establish a new trust root and reissue every component and client certificate. Plan a period of trust in both CAs to avoid interrupting all connections together.
• Component certificate sources reside at files/pki/<component>/ on the administration node. Node files are deployment copies. Deleting only a node copy restores the same certificate. To rotate, update or remove the source, rerun the relevant playbook, and reload or roll the component as required.

Next

• 🔑 Authentication: use HBA to decide who must use SSL or client certificates
• 🔒 Data Security: encryption for stored data and backups
• ✅ Compliance: evidence for certificate management

5 - Data Security

Network boundaries, authentication, and access control reduce incident risk. Data controls must limit damage and support recovery after hardware failure, leaked credentials, or operator errors.

Data security must provide integrity, recoverability, confidentiality if copied, and records of what happened.

Integrity

Bad disk sectors, memory bit flips, and firmware defects can cause silent data corruption, with no immediate error. Pigsty enables pg_checksum: true by default. Initialization uses data-checksums. PostgreSQL calculates each page checksum on write and checks it on read.

Page checksums mainly detect corruption in storage, the I/O path, or pages after writing. They cannot detect every memory error, logical error, or incorrect application write. They do not replace backups.

CRIT requires checksums regardless of the parameter. Its strict synchronous replication (synchronous_mode_strict) blocks writes needing synchronous acknowledgment when no synchronous replica is available. This protects acknowledged transactions under specific conditions. Clients must not reduce synchronous_commit; a synchronous replica must participate in commits. Failover must select a node containing the required WAL. Verify RPO with failure exercises on the target topology.

Recoverability

Replicas mainly handle node failures. Backups cover accidental deletion, logical errors, cluster corruption, and wider disasters. HA can shorten interruptions after primary failure. However, replication copies accidental deletions to every replica, so backups remain essential.

Pigsty enables pgBackRest by default through pgbackrest_enabled. Base backups and continuous WAL archives provide Point-in-Time Recovery. Restore to a target within the retained backup and WAL window.

Select the backup repository with pgbackrest_method:

Two additional controls reduce damage from accidental deletion:

• Delayed replica: For a critical cluster, declare pg_delay: 1h. Before the error replays, pause replication and extract the required data. A delayed replica eventually catches up and does not replace backup.
• Removal safeguards: Enabling pg_safeguard or etcd_safeguard blocks the corresponding removal playbook. This reduces accidental cluster removal.

Test that backups can restore data through routine recovery exercises. See Backup and Recovery for mechanisms and procedures.

Confidentiality

Protect data at rest at three layers:

Backup encryption. pgbackrest_method: minio selects S3-compatible storage. Providers include MINIO-managed Silo, independently managed MinIO or RustFS, and external S3 services. The preset enables AES-256-CBC encryption. Change its public pgBackRest passphrase in production. ha/safe derives an example passphrase from the cluster name:

pgbackrest_repo:
  minio:
    cipher_type: aes-256-cbc
    cipher_pass: 'pgBR.${pg_cluster}'   # Example only; replace before deployment

pgBR.${pg_cluster} is predictable, and configure -g does not replace it. In production, use a unique random passphrase. Store it separately from the backup. Without it, recovery is impossible.

Local backups have no encryption by default. Encryption reduces disclosure if someone copies backup files or media separately. Protection is limited when keys and backups share a host.

Transport encryption. Uploads to Silo or external S3 use HTTPS. HBA can require SSL for PostgreSQL clients and replication. Clients should also verify server certificates. See Encrypted Communication.

Encryption at rest. Upstream PostgreSQL currently lacks general built-in transparent data encryption (TDE). Pigsty offers two options. Use pg_tde with Percona Distribution for PostgreSQL for table-level encryption; see pgtde. Use security extensions such as pgsodium, pgcrypto, and anonymizer for column encryption and masking. The safe template installs this category. Full-disk encryption, such as LUKS or dm-crypt, adds OS-level protection against stolen media.

Audit and Traceability

Incident records must identify who did what and when. Pigsty provides these logging layers:

Default baseline: Log all DDL with log_statement: ddl. Log statements longer than 100 ms with log_min_duration_statement: 100. PostgreSQL 18 and later also record connection authorization.

CRIT template: log_connections and log_disconnections record connection events. PostgreSQL 18 can distinguish receipt, authentication, and authorization.

pgaudit: Install pgaudit and preload it through pg_libs for detailed statement auditing. This includes object reads, writes, and role-based audit classes. The safe template installs the extension. Declare loading and audit policy explicitly.

With INFRA logging enabled and Vector configured, PostgreSQL logs go to VictoriaLogs for centralized storage. Retention defaults to 15 days and can meet other compliance periods. Logs and metrics support search, alerts, and incident reconstruction. Classification, response, and evidence preservation still require an operational process.

Next

• ⏰ Backup and Recovery: PITR principles and practice
• ♾️ High Availability: replicas and backups as complementary safeguards
• ✅ Compliance: audit logs as compliance evidence

6 - Compliance

Compliance requires continuous evidence of three elements:

• Configuration: Security controls must be enabled. Pigsty directly provides configuration.
• Process: The organization must establish access approval, change management, recovery exercises, and related procedures.
• Evidence: Records must show that configuration and processes remain effective. The inventory, runtime logs, and monitoring system provide part of this evidence.

This page provides a pre-launch checklist and maps Pigsty capabilities to common compliance frameworks. These mappings support architecture and gap analysis. They are not an MLPS assessment, SOC 2 audit opinion, or legal advice.

Default Credentials Checklist

Default credentials are public in documentation and source code. Use them only for demonstrations and local development. Change every applicable default before production or network-exposed deployment:

Use -g to randomize built-in parameters and example strings recognized by the configuration wizard:

./configure -g     # Generate the inventory and randomize recognized default credentials

The wizard prints generated passwords to the terminal. Protect terminal history and automation logs as sensitive data. Then inspect the configuration. Replace pgBackRest cipher_pass, uncovered MINIO values in ha/safe, and all custom credentials.

Launch Hardening Checklist

Before deployment:

• Define the network boundary. Do not expose database ports publicly. Remove the demo firewall exception for 5432.
• Select the built-in CA or enterprise PKI. See Using an Enterprise CA.
• Plan client verification. Set sslmode=verify-full and a trusted CA on database clients.
• Assign application accounts through the four-tier roles. Declare expire_in.
• Plan the backup repository, retention, encryption passphrase, and off-site copies.
• Decide whether to use ha/safe and the CRIT parameter template.

After deployment:

• Confirm that all default credentials have changed. Include values covered by configure -g and uncovered backup, object storage, and custom credentials.
• Compare /pg/data/pg_hba.conf with the declared HBA policy and intended access boundary.
• Query effective users, roles, default privileges, and database CONNECT grants. Compare them with the inventory.
• Run a full backup and recovery exercise to validate the backup path.
• Confirm log collection, monitoring alerts, and notification channels.

Periodically:

• Audit privileges: compare pg_users declarations with effective grants, and remove expired or departed-user accounts
• Rotate credentials and certificates
• Exercise recovery and failover
• Track security updates for Pigsty and upstream components

Compliance Evidence

Use declarative configuration as a starting point for audit evidence. Retain runtime state to prove that the configuration was applied and remains effective.

MLPS Level 3 Mapping

This table maps database-related capabilities to the secure computing environment controls in GB/T 22239-2019 Level 3:

MLPS also covers physical security, networks, and management systems beyond a database distribution. Pigsty supports database-related technical controls. The overall system must address facilities, network devices, and governance.

SOC 2 Mapping

Database-related controls in the SOC 2 Trust Services Criteria (TSC) include:

Supply Chain and Vulnerability Response

Compliance reviews also cover the software supply chain. Pigsty provides these distribution and response controls:

Package integrity: Pigsty repository RPM and DEB packages (repo.pigsty.io, repo.pigsty.cc) are GPG-signed. Verify fingerprint 9592 A7BC 7A68 2E73 3337 6E09 E793 5D8D B9BD 8B20 (B9BD8B20) before trusting the key. Deployment-generated repository definitions and the INFRA node local repository do not enforce verification for every package by default. In production, review package-manager trust and signature settings.

Vulnerability response: Report issues privately through GitHub vulnerability reporting or email as specified in SECURITY.md. The project targets acknowledgment within three business days and initial assessment within seven days.

Version support: Security fixes ship in the latest stable release. Update to receive them. Subscription services provide extended support for users who must retain a version longer.

Next

• 🛡️ Security Model: from the default baseline to hardening levels
• 🔰 Security Recommendations: minimum hardening for quick-start deployments
• 📄 ha/safe Template: hardening configuration example