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

Before examining individual security features, answer two more fundamental questions: Where is the root of trust? and How many defensive layers exist? The first determines what deserves the strongest protection. The second determines what remains when one layer fails.

Trust Boundaries

Pigsty is an Ansible-based declarative deployment system. Like other control-plane systems, its admin node is the control plane and the node that requires the strongest protection.

These roles hold different capabilities; they do not form a simple linear hierarchy. Three assets are especially important:

1. The pigsty.yml inventory contains component passwords and credentials. Strictly control access to the admin node and to the configuration repository when Git is used.
2. The CA private key, files/pki/ca/ca.key, is the trust anchor for the deployment. Anyone holding it can issue an arbitrary trusted certificate. The file uses mode 0600 inside a 0700 directory; keep an offline backup.
3. The administration user’s SSH private key lets the admin node manage every enrolled node with passwordless sudo. It is effectively root access to the managed fleet.

Pigsty’s security policy states this boundary explicitly: an attack that requires admin-node access, or possession of both pigsty.yml and the CA private key, is not treated as a product vulnerability. These are high-trust control-plane assets by design and must be protected accordingly.

Seven Defensive Layers

Defense in depth does not ask one mechanism to solve every problem. It combines controls so that one failure does not remove all protection. Pigsty’s security capabilities can be summarized as seven layers:

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

Network Boundaries

Pigsty enables a firewall during node provisioning (node_firewall_mode defaults to zone), using firewalld or ufw according to the operating system. Intranet CIDRs (10.0.0.0/8, 172.16.0.0/12, and 192.168.0.0/16, defined by node_firewall_intranet) enter the trusted zone. Public networks can reach only ports declared in node_firewall_public_port, which defaults to 22 for SSH and 80/443 for web traffic.

The default demo inventory, pigsty.yml, also exposes port 5432 for local evaluation. Remove it in production. If direct database access is required, restrict sources to explicit CIDRs with security groups, host firewalls, and HBA.

PostgreSQL listens on all addresses by default (pg_listen: 0.0.0.0). The effective access boundary is the combination of listen addresses, firewall rules, and HBA. Stricter environments can constrain the listener:

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

The default firewall does not expose Grafana, VictoriaMetrics, or other web infrastructure directly to public networks. External web access normally enters through the Nginx portal. Database traffic enters through HAProxy service ports. Fewer entry points are easier to harden and audit.

Host Security

The central host-level rule is: each OS user receives only the privileges required for its job.

• The database superuser postgres (pg_dbsu) has no password by default and can enter the database only through local ident authentication. pg_dbsu_sudo defaults to limit, allowing passwordless systemctl operations for database services and log viewing rather than unrestricted root access.
• The administration user (node_admin_username, default dba) is used by operators and playbooks and receives passwordless sudo (nopass) by default. Security-sensitive environments can set node_admin_sudo to all, which requires a sudo password, or limit, which restricts the command set.
• node_selinux_mode defaults SELinux to permissive: violations are logged but not blocked, providing a baseline before moving to enforcing.

Pigsty does not manage the SSH server configuration. Disabling password login, restricting remote root login, and similar operating-system hardening belong in your host security baseline.

Hardening Levels

Security does not have to jump to its final state in one step. Pigsty provides an upgrade path in which each level builds on the previous one:

Level 1: default baseline. Out-of-the-box controls include SCRAM passwords, data checksums, a local CA and component certificates, layered HBA, a four-tier role model, default backups, and firewall zones. This level suits development, testing, and evaluation on a trusted intranet. Production still requires credential review, network-boundary review, and client verification.

Level 2: randomized credentials. Default passwords are documented publicly and must be changed in every network-exposed deployment. Add -g when generating configuration to randomize built-in parameters and example credentials recognized by the configuration wizard:

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

This option does not replace the pgBackRest cipher_pass, every MinIO example credential in ha/safe, or user-defined values. See the Default Credentials Checklist for the complete scope.

Level 3: policy hardening with the ha/safe template. conf/ha/safe.yml combines several controls into a starting point for further customization:

• TLS and certificate authentication: the main TCP HBA rules use ssl, public administrator access uses a client certificate, PgBouncer uses require, and the Patroni API uses HTTPS. Local ident and selected localhost password rules remain.
• Password policy: passwordcheck is preloaded explicitly, and built-in users declare expire_in. Example passwords in the template still require review and replacement.
• Reduced attack surface: listen addresses are limited to ${ip},${vip},${lo}, and public connection-pool access by monitoring and administration accounts is denied explicitly.
• Backup encryption: pgBackRest uses a MinIO repository with AES-256-CBC. pgBR.${pg_cluster} is a predictable example value and must be replaced.
• Security extensions: passwordcheck, credcheck, pgaudit, pgsodium, anonymizer, and related extensions are installed. Installation does not preload, create, or configure an extension.

Level 4: database hardening with the crit.yml parameter template. The safe template selects the CRIT parameter template for consistency-first workloads. Compared with the general oltp template, it:

• forces data checksums regardless of pg_checksum;
• enables strict synchronous replication (synchronous_mode_strict), blocking writes that require synchronous acknowledgment when no synchronous replica is available;
• logs connection and disconnection events; PostgreSQL 18 also separates connection receipt, authentication, and authorization stages;
• configures watchdog as automatic, which activates only when a usable device exists.

Strict synchronous mode targets preservation of acknowledged transactions, but still depends on synchronous_commit, synchronous replica state, and failover eligibility. Validate RPO with failure exercises on the target topology.

You can also select individual controls instead of adopting the complete 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

PostgreSQL uses pg_hba.conf for Host-Based Authentication: who may connect, from where, to which database, and how they must prove their identity.

The mechanism is powerful, but expensive to maintain manually across a cluster. Primary and replica instances may require different rules, and every instance stores its own configuration in the data directory. Without a common declaration and refresh process, rules can drift between instances.

Pigsty applies the same declarative configuration model here: HBA rules are part of the inventory and are rendered and distributed consistently by playbooks.

HBA as Code

Cluster HBA policy combines two parameter groups: the global defaults in pg_default_hba_rules and cluster-specific additions in pg_hba_rules. The PgBouncer connection pool has two independent counterparts: pgb_default_hba_rules and pgb_hba_rules.

A rule can use either of two forms. The recommended alias form keeps one semantic rule on one 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' }

The raw form supplies a literal pg_hba.conf line for cases the aliases cannot express.

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

• order: render order. HBA uses first-match semantics, so order is priority. By convention, 0-99 is reserved for high-priority user rules, 100-999 for defaults, and rules without order come last.
• role: instance-role filter. common and default apply to every instance; primary, replica, offline, standby, and delayed apply only to matching instances. A role: offline rule is also rendered on instances marked with pg_offline_query. The same declaration therefore produces the appropriate rules for each instance role without maintaining primary and replica files manually.

After editing the declaration, apply it with the wrapper script. The rules are rendered again and reloaded:

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

pg_hba_rules appends rules; it does not automatically narrow broader defaults. To establish a stricter boundary, review pg_default_hba_rules as well, then inspect the generated pg_hba.conf on every instance.

Address and Authentication Aliases

The alias form gives common cases semantic names. Values in addr expand into concrete address blocks:

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

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

Do not confuse transport enforcement with server verification: auth: ssl requires TLS but does not require the client to verify the server identity. Security-sensitive clients should also use sslmode=verify-full with a trusted CA; see Encrypted Communication.

Default Rules Explained

Pigsty’s default HBA policy follows a simple rule: the farther the source, the stronger the requirement. These are the PostgreSQL defaults 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 is most trusted: postgres can enter only through a local Unix socket with ident. No password is required, but remote login is impossible. This is why dbsu has no password by default.
• The intranet comes next: replication and application accounts use SCRAM password authentication on the intranet. Remote monitoring and administration access primarily originates from INFRA nodes.
• Public sources are strictest: only the administrator may connect from any address by default, and the connection requires both a password and TLS.

PgBouncer defaults are more restrictive: public access for monitoring and administration accounts is explicitly denied, while application users are limited to localhost and intranet sources.

The default +dbrole_offline rule does not set role and therefore applies to every instance. To restrict offline users to pg_role: offline or instances with pg_offline_query: true, add role: offline explicitly to the corresponding HBA rule.

This default policy favors usability: application accounts can connect from the intranet with password authentication. The ha/safe template changes the main TCP rules to ssl and requires administrators outside the intranet to present a client certificate (cert); local ident and selected localhost password rules remain.

Password Policy

Pigsty uses PostgreSQL’s recommended scram-sha-256 password storage by default (pg_pwd_enc). Downgrade to md5 only for legacy client compatibility.

Before executing ALTER USER ... PASSWORD, the password workflow temporarily disables statement logging (SET log_statement TO 'none') to keep passwords out of PostgreSQL logs. Plaintext passwords still appear in the inventory, and rendered user SQL is written to /pg/tmp/pg-user-<name>.sql with mode 0640. The related Ansible tasks do not use no_log consistently. Restrict access to the admin node, configuration repository, and automation output, and avoid --diff on tasks containing credentials.

Password strength is not enforced by default. If required, preload passwordcheck or the more configurable credcheck:

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

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

Declare account lifetime with expire_in (days after creation) or expire_at (absolute date), then combine it with the 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, use auth: cert in HBA to require client certificate authentication. The client must present a certificate signed by the local CA whose CN matches the database user name. When the HBA 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 certificate and key are stored in files/pki/misc/<cn>.crt and files/pki/misc/<cn>.key. Deliver the private key through a controlled channel. The client should still use verify-full to authenticate the database server; see Encrypted Communication.

Connection Pool and Component APIs

The database is not the only authenticated entry point.

The PgBouncer connection pool uses an independent HBA policy and user list. pgbouncer_auth_query is disabled by default, so only users declared with pgbouncer: true are written to userlist.txt and can authenticate through the pool. Re-evaluate the login scope before enabling dynamic authentication queries.

The Patroni REST API carries high-availability control operations such as restart, switchover, and configuration reload. Write operations require HTTP Basic authentication (patroni_username and patroni_password) and are restricted by source-address allowlists. When patroni_ssl_enabled is enabled, the API uses HTTPS throughout.

Credentials for Grafana, the HAProxy administration interface, MinIO, etcd, and other components are also declared in the inventory. See the Default Credentials Checklist for the full list and update guidance.

Next

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

3 - Access Control

Authentication answers “Who are you?” Authorization answers “What may you do?”

Privilege failures rarely result from a lack of mechanisms—PostgreSQL GRANT and REVOKE are sufficiently precise. The usual problem is the absence of conventions that are applied by default: an application account is made the owner at launch, temporary superuser access is not revoked after troubleshooting, or grants are missed when new tables are created and cause failures in production.

Pigsty provides an out-of-the-box access control model as a starting point: four role tiers, default privileges, and database isolation. It reduces per-database manual grants, but operators must still assign roles according to business boundaries and review effective privileges regularly.

Role System

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

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

The role system is itself declarative (pg_default_roles) and can be customized. This parameter is a complete list. Preserve all required system users and default roles when changing it, and check references from HBA rules, default privileges, and scripts at the same time.

Default Privileges

Roles answer “Who receives a privilege?” The other half of the problem is: How do newly created objects receive the correct privileges 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 receives query and function execution privileges, the read-write role adds DML, and the administrator role adds the supporting privileges required for object management.

Ownership Convention

Default privileges have an often-missed prerequisite: they apply only to objects created by identities for which those defaults were configured. Pigsty configures default privileges 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. Platform administration and release workflows can use dbuser_dba or first execute SET ROLE dbrole_admin. Objects created directly by other users do not enter this default privilege model unless ALTER DEFAULT PRIVILEGES is also configured for those users.

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

Database Isolation

PostgreSQL grants CONNECT on databases to PUBLIC by default. If HBA also permits a connection, a login role may enter a database it does not own. This default is particularly important to tighten when several 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 }

When enabled, CONNECT is revoked from PUBLIC and granted explicitly to the replication, monitoring, and administration users and to the database owner. The owner receives GRANT OPTION and can decide who else may connect. Without additional grants or inherited roles, the app_a account 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 can no longer create objects freely in public databases or schemas, reducing risks from unsafe search_path settings and object shadowing. PostgreSQL 15 tightened the default CREATE privilege on the public schema; Pigsty applies the same boundary consistently across all supported major versions.

Offline Role and Instance Isolation

dbrole_offline provides an independent set of read-only privileges for ETL, reporting, and ad hoc queries. The role controls object privileges only; it does not automatically restrict which instance a user may connect to.

In the current default HBA rules, the intranet rule for +dbrole_offline does not set role and therefore applies to every instance. To restrict it to a dedicated pg_role: offline instance, or to a regular replica marked with pg_offline_query: true, modify that rule in 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' }

Defining pg_default_hba_rules replaces the entire default list; the example rule cannot be used alone. Expensive queries are limited to offline instances only when HBA filters by instance role and the user does not inherit another role allowed by broader rules. Resource isolation should also use a dedicated service endpoint, connection limits, and query resource controls.

Beyond the Database

Least privilege also applies at the host level:

• The postgres superuser has no password and can log in only through local ident. Its sudo access defaults to a restricted set of database service and log commands (pg_dbsu_sudo: limit).
• The monitoring user dbuser_monitor holds pg_monitor, the read-only role, and privileges on the dedicated monitor schema; it cannot write business tables by default.
• The replication user replicator receives only the directory function privileges required for backup and recovery instead of broad 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 three separate protections: transport encryption, server authentication, and client authentication. Each must be configured independently. Enabling server-side TLS does not mean the client verifies the server identity, nor does it mean the server requires a client certificate.

The main operational cost of TLS is not the encryption algorithm but certificate issuance, distribution, trust, and rotation. Without centralized management, internal services often encrypt traffic while skipping certificate verification—or remain on plaintext connections.

Pigsty brings PKI under declarative management. During deployment it creates a local self-signed CA, issues certificates for managed components, and distributes trust so TLS is ready for use after installation.

Local CA

During the first deployment, Pigsty checks for a CA on the admin node and creates one when required:

• ca_create controls CA behavior. Existing files are reused unchanged for idempotency; otherwise a new CA is created. If set to false and the CA files are missing, deployment fails instead of silently creating a new trust root.
• ca_cn sets the CA certificate CN, which defaults to pigsty-ca. The key is RSA 4096.
• The root CA is valid for 100 years, while component certificates default to 20 years (cert_validity: 7300d). The browser-facing Nginx certificate is an exception and currently defaults to 397 days.

Long default lifetimes reduce the initial maintenance burden for private infrastructure; they do not remove the need for production rotation. Organizations with an established certificate policy should shorten lifetimes and monitor expiration.

Trust Distribution

Issuing a certificate is only half of PKI. Every node must trust it. When a node is managed, Pigsty distributes the CA certificate to /etc/pki/ca.crt and links it into the operating system 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 certificates signed by the Pigsty CA. The CA certificate is also published as ca.crt at the site root of the Nginx portal for browsers and external clients.

PostgreSQL libpq clients require special attention: by default they look for ~/.postgresql/root.crt and use sslmode=prefer, so they do not directly use the operating system trust store to verify the 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 validates both the certificate chain and the connection host name. The DNS name or IP address used by the client must therefore appear in the server certificate SAN. External clients must install ca.crt or specify it with sslrootcert.

Certificate Matrix

The local CA issues certificates for the following components and places them under one trust chain:

The encryption-state column reflects deliberate defaults:

• Enabled at deployment: PostgreSQL accepts SSL connections; etcd uses TLS for client and peer traffic.
• Encrypted by default: MinIO backup traffic and Nginx web traffic use HTTPS.
• Disabled by default, available on demand: TLS for the Patroni REST API and PgBouncer is disabled by default, but certificates are already present. Enable it through the corresponding parameters; both are enabled in the ha/safe template.

Keep three states distinct: server-side SSL support does not force clients to use SSL, and neither state proves that the client verifies the server identity. HBA rules enforce encryption with auth: ssl or cert. Client sslmode and trust settings control server verification. The default rules require TLS only for administrator connections from arbitrary sources. The safe template changes the main TCP rules to ssl or cert while retaining local ident and selected localhost password rules.

Client Certificates

The built-in cert.yml playbook issues client certificates. The certificate CN represents the database user name for HBA cert authentication:

./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

Results are stored in files/pki/misc/<cn>.key and files/pki/misc/<cn>.crt. Deliver private keys through a controlled channel and make them readable only by the corresponding user. The client certificate lets the server authenticate the client; the client must still use verify-full to authenticate the database server.

Using an Enterprise CA

If the organization already operates a PKI, Pigsty can issue certificates from that CA, or from an intermediate signed by the enterprise root. Place the certificate and private key at the expected paths; playbooks do not regenerate a CA when one already exists:

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 will then fail explicitly if the files are missing instead of creating an unexpected self-signed CA and breaking the existing trust chain.

Key Protection and Rotation

• The CA private key exists only on the admin node. Together with pigsty.yml, it is one of the highest-trust assets in the deployment; see Trust Boundaries. Keep an offline backup.
• If the CA private key is compromised, establish a new trust root and reissue every component and client certificate. Plan an overlap period in which both old and new CAs are trusted to avoid interrupting all connections at once.
• Component certificate sources are stored under files/pki/<component>/ on the admin node; node certificates are deployment copies. Deleting only a node copy restores the same certificate rather than issuing a new one. To rotate, update or remove the corresponding source on the admin node, rerun the relevant playbook, then 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 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

6 - Compliance

Compliance is not a product you can buy. It is a state that must be demonstrated continuously through three elements:

• Configuration: whether security controls are enabled. Pigsty directly provides this part.
• Process: access approval, change management, recovery exercises, and related procedures. The organization must establish these.
• Evidence: records showing that configuration and process remain effective. Pigsty’s inventory, runtime logs, and monitoring system can provide part of this evidence.

This page begins with a pre-launch hardening checklist and then maps Pigsty security capabilities to common compliance frameworks. The mappings support architecture and gap analysis; they are not an MLPS assessment conclusion, a SOC 2 audit opinion, or legal advice.

Default Credentials Checklist

Pigsty default credentials are public in the documentation and source code. They are intended only for demonstrations and local development. Change every applicable default before any production or network-exposed deployment goes live:

Use -g while generating configuration 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, so protect terminal history and automation logs as sensitive data. After generation, inspect the configuration and replace pgBackRest cipher_pass, MinIO example values in ha/safe that were not covered, and all custom credentials.

Launch Hardening Checklist

Before deployment:

• Define the network boundary: do not expose database ports publicly, and remove the demo firewall exception for 5432
• Select a certificate policy: use the built-in CA or integrate enterprise PKI; see Using an Enterprise CA
• Plan client verification: configure database clients with sslmode=verify-full and a trusted CA
• Design the account model: assign application accounts through the four-tier roles and declare expire_in
• Plan the backup repository, retention, encryption passphrase, and off-site copies
• Decide whether to use the ha/safe template and the CRIT parameter template

After deployment:

• Confirm that credentials covered by configure -g and uncovered backup, MinIO, and custom credentials have all been changed
• Review the effective HBA rules in /pg/data/pg_hba.conf against the declaration and intended boundary
• Query effective users, roles, default privileges, and database CONNECT grants, and compare them with the inventory
• Run one full backup and a 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

Declarative configuration provides a stable starting point for audit evidence. Retain runtime state as well to show that the configuration was applied and remains effective.

MLPS Level 3 Mapping

The following maps database-related Pigsty capabilities to controls in the “secure computing environment” section of GB/T 22239-2019 Level 3:

MLPS also covers physical security, communication networks, and management systems beyond the scope of a database distribution. Pigsty can support database-related technical controls in a secure computing environment; facilities, network devices, and governance must be addressed in the overall system.

SOC 2 Mapping

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

Supply Chain and Vulnerability Response

Compliance reviews increasingly cover the software supply chain. Pigsty provides the following distribution and response controls:

Package integrity: RPM and DEB packages in the Pigsty repositories (repo.pigsty.io and repo.pigsty.cc) are GPG-signed. The public-key fingerprint is 9592 A7BC 7A68 2E73 3337 6E09 E793 5D8D B9BD 8B20 (B9BD8B20) and can be verified before trust is established. Repository definitions written during deployment and the local repository on the INFRA node do not enforce signature verification for every package by default; review package-manager repository trust and signature settings in production.

Vulnerability response: report security issues privately through GitHub private vulnerability reporting or email, as documented in SECURITY.md. The project targets acknowledgment within three business days and an initial assessment within seven days.

Version support: security fixes ship with the latest stable release. Staying current is the standard way to receive them. Users who must remain on a version for longer can obtain extended support through subscription services.

Next

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