Data Security

Network boundaries, authentication, and access control reduce the likelihood of an incident. When hardware fails, credentials leak, or an operator makes a mistake, data-layer controls must limit the impact and support recovery.

Data security answers four questions: Is the data intact? Can it be recovered? If copied, does it remain confidential? Can you determine what happened?

Integrity

Bad disk sectors, memory bit flips, and storage firmware defects can cause silent data corruption: the data is damaged without an immediate error. Pigsty enables page checksums by default (pg_checksum: true). The cluster is initialized with data-checksums, so PostgreSQL calculates a checksum when writing a page and verifies it when reading.

Page checksums primarily detect corruption in storage media, the I/O path, or pages after they were written. They do not detect every memory error, logical error, or incorrect application write, and they do not replace backups.

The CRIT parameter template goes further: checksums are mandatory regardless of the parameter, and strict synchronous replication (synchronous_mode_strict) blocks writes that require synchronous acknowledgment when no synchronous replica is available. This mode targets preservation of acknowledged transactions, but it still assumes clients have not reduced synchronous_commit, a synchronous replica participates in the commit, and failover selects only a node containing the required WAL. Validate RPO through failure exercises on the target topology.

Recoverability

Replicas primarily handle node failures; backups handle accidental deletion, logical errors, cluster corruption, and broader disasters. High availability can shorten an interruption after primary failure, but replication also copies an accidental deletion to every replica. Backups are therefore indispensable.

Pigsty enables pgBackRest by default (pgbackrest_enabled). Base backups plus continuous WAL archiving provide Point-in-Time Recovery (PITR), allowing recovery to a target time within the retained backup and WAL window.

Select the backup repository with pgbackrest_method:

Two additional controls reduce damage from accidental deletion:

• Delayed replica: declare a pg_delay: 1h replica for a critical cluster. Before an erroneous operation is replayed, pause replication and extract the required data. A delayed replica eventually catches up and does not replace a backup.
• Removal safeguards: when pg_safeguard or etcd_safeguard is enabled, the corresponding removal playbook refuses to run, reducing the risk of accidental cluster removal.

Having a backup is not the same as being able to restore. Recovery exercises should be routine; see Backup and Recovery for mechanisms and procedures.

Confidentiality

Protect data at rest at three layers:

Backup encryption. The MinIO repository uses AES-256-CBC by default, but its default encryption passphrase (pgBackRest) is public and must be changed in production. The ha/safe template 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. Use a unique random passphrase in production and store it separately from the backup. Losing the passphrase makes the backup unrecoverable.

The local backup repository is not encrypted by default. Encryption reduces disclosure if backup files or media are copied separately, but offers limited protection when the key and backup remain on the same host.

Transport encryption. Backup uploads to MinIO use HTTPS. PostgreSQL client and replication traffic can require SSL through HBA. Clients should also verify the server certificate; see Encrypted Communication.

Encryption at rest. Upstream PostgreSQL currently has no general built-in transparent data encryption (TDE). Pigsty provides two practical options: use the pg_tde extension with Percona Distribution for PostgreSQL for table-level transparent encryption (see the pgtde configuration template); or use security extensions such as pgsodium, pgcrypto, and anonymizer for column-level encryption and masking. The safe template installs this extension category. Full-disk encryption such as LUKS or dm-crypt protects against stolen media at the operating-system layer and complements database-level controls.

Audit and Traceability

After an incident, you must be able to answer who did what and when. Pigsty provides layered logging:

Default baseline: all DDL is logged (log_statement: ddl), and statements taking longer than 100 ms are logged (log_min_duration_statement: 100). PostgreSQL 18 and later also record connection authorization events.

CRIT template: connection and disconnection events are recorded with log_connections and log_disconnections. PostgreSQL 18 can distinguish connection receipt, authentication, and authorization stages.

pgaudit extension: for fine-grained statement auditing such as object reads and writes or role-based audit classes, install pgaudit and add it to pg_libs for preloading. The safe template installs the extension, but loading and audit policy must be declared explicitly.

When INFRA logging is enabled and Vector is configured, PostgreSQL logs are sent to VictoriaLogs for centralized storage. The default retention is 15 days and can be adjusted for compliance. Logs and metrics support search, alerts, and incident reconstruction, but incident 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