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