Security and Compliance

• 1: Seven-Layer Security Model
• 2: Authentication
• 3: Access Control
• 4: Encrypted Communication and Local CA
• 5: Data Security
• 6: Compliance Checklist

1 - Seven-Layer Security Model

Security is not a wall, but a city. Pigsty adopts a defense-in-depth strategy and builds multiple protections across seven layers. Even if one layer is breached, other layers still protect the system.

This layered approach addresses three core risks:

• Perimeter breach: reduce the chance that one breach compromises everything.
• Internal abuse: even if an internal account is compromised, least privilege limits damage.
• Unpredictable failures: hardware, software, and human errors all get multi-layer fallbacks.

Overview

L1 Physical and Media Security

When the physical layer falls, the only defense is the data itself.

Problems solved

• Silent data corruption from hardware faults
• Data leakage from stolen media

Pigsty support

• Data checksums: default pg_checksum: true, detects corruption from bad blocks/memory errors.
• Optional transparent encryption: pg_tde and similar extensions encrypt data at rest.

L2 Network Security

Control who can reach services to reduce attack surface.

Problems solved

• Unauthorized network access
• Plaintext traffic sniffing/tampering

Pigsty support

• Firewall zones: node_firewall_mode can enable zone, trust intranet, restrict public.
• Listen hardening: pg_listen limits bind addresses to avoid full exposure.
• TLS: HBA supports ssl/cert for encryption and identity checks.

L3 Perimeter Security

A unified ingress is the basis for audit, control, and blocking.

Problems solved

• Multiple entry points are hard to manage
• External systems lack a unified hardening point

Pigsty support

• HAProxy ingress: unified DB traffic entry for blocking/limiting/failover.
• Nginx gateway: unified HTTPS ingress for infrastructure services (nginx_sslmode).
• Centralized credentials: HAProxy and Grafana admin passwords are declared in config.

L4 Host Security

The foundation of DB security: least privilege, isolation, and hardening.

Problems solved

• Host compromise leads to total loss
• Admin privileges spread too widely

Pigsty support

• SELinux mode: node_selinux_mode can switch to enforcing.
• Least-privilege admin: node_admin_sudo supports limit to restrict sudo commands.
• Sensitive file permissions: CA private key directory 0700, private key files 0600.

L5 Application Security

Authentication is the first gate for DB security.

Problems solved

• Weak passwords or plaintext auth leak accounts
• Misconfigured rules allow privilege escalation

Pigsty support

• HBA layering: rules by source and role; internet admin must use ssl.
• SCRAM password hash: pg_pwd_enc: scram-sha-256 by default.
• Password strength checks: passwordcheck/credcheck optional.
• Certificate auth: auth: cert supports client certs.

L6 Data Security

Ensure data is available, recoverable, and accountable.

Problems solved

• Human errors and logic mistakes
• Data tampering or deletion after intrusion

Pigsty support

• pgBackRest backup: enabled by default, local and MinIO repos.
• Backup encryption: MinIO supports AES-256-CBC (cipher_type).
• PITR recovery: restore to any time point with WAL archive.
• Audit logs: templates enable DDL/connection/slow query logs, optional pgaudit.

L7 User Security

Least privilege is not a slogan, it is default behavior.

Problems solved

• Business accounts have excessive permissions
• Databases can “pierce” each other

Pigsty support

• Four-tier RBAC: dbrole_readonly/readwrite/admin/offline.
• Default privileges: objects automatically get correct grants.
• Database isolation: revokeconn: true blocks cross-DB access.
• Public privilege tightening: revoke CREATE on public schema.

Security Hardening Path

Pigsty provides a security hardening template: conf/ha/safe.yml. It bundles common hardening items:

• Enforce SSL and certificate auth
• Password strength and expiration policies
• Connection and disconnection logs
• Firewall and SELinux hardening

This path is a quick upgrade from default to compliance-ready.

Next

• 🔑 Authentication: HBA rules and password policy
• 👤 Access Control: role and privilege model
• 🔐 Encrypted Communication: TLS and certificate management
• 🔒 Data Security: backup and encryption
• ✅ Compliance Checklist: MLPS and SOC2 mapping

2 - Authentication

Authentication answers three core questions:

• Who you are: is the identity unique and recognizable?
• How you prove it: are passwords/certs strong enough?
• Where you come from: is the source controlled?

Pigsty uses HBA rules + password/certificates for authentication, with SCRAM as the default password hash.

Authentication Flow

flowchart LR
  C[Client] --> HBA[HBA Rules]
  HBA --> A1[Password SCRAM]
  HBA --> A2[Certificate Auth]
  HBA --> A3[Local ident/peer]
  A1 --> RBAC[Roles and Privileges]
  A2 --> RBAC
  A3 --> RBAC

HBA decides “who can come from where”, and the auth method decides “how identity is proven”.

HBA Layering Model

Pigsty default HBA rules are layered:

• Local uses ident/peer, the safest.
• Intranet uses scram password auth.
• Internet admin must use ssl.

This solves “same user, different auth strength by source”.

Key capabilities of HBA rules

• Order first: supports order sorting, smaller number means higher priority.
• Address aliases: local / localhost / intra / world, etc.
• Role conditions: primary/replica/offline for fine-grained control.

Password Authentication

Default password hash:

pg_pwd_enc: scram-sha-256

Problems solved

• Plaintext password storage risk
• Weak hashes cracked offline

Compatibility

For legacy clients you can use md5, but security is reduced.

Password Strength and Rotation

Pigsty can enable password strength checking extensions:

pg_libs: '$libdir/passwordcheck, pg_stat_statements, auto_explain'
pg_extensions: [ passwordcheck, credcheck ]

Use expire_in to control account expiry:

pg_users:
  - { name: dbuser_app, password: 'StrongPwd', expire_in: 365 }

Problems solved

• Weak or reused passwords
• Long-lived accounts without rotation

Certificate Authentication

Certificates mitigate the risk of “password phishing or copying”.

• HBA auth: cert requires client certs.
• Certificate CN usually matches the database username.
• Pigsty ships cert.yml to issue client certificates.

PgBouncer Authentication

PgBouncer uses separate HBA rules and TLS settings:

pgbouncer_sslmode: disable   # default off, set to require/verify-full
pgb_default_hba_rules: [...] # separate rules

This solves the problem of “pool entry and database entry being out of sync”.

Default Accounts and Risks

Default passwords must be changed in production.

Security Recommendations

• Use ssl/cert on all public entry points.
• Use scram for intranet users, avoid md5.
• Enable passwordcheck to enforce complexity.
• Rotate passwords regularly (expire_in).

Next

• 👤 Access Control: role and privilege model
• 🔐 Encrypted Communication: TLS and certificate management
• ✅ Compliance Checklist: MLPS and SOC2 mapping

3 - Access Control

Access control answers two core questions:

• What you can do: boundaries for read/write/DDL
• What data you can access: isolation across databases and schemas

Pigsty enforces least privilege with RBAC roles + default privileges.

Four-Tier Role Model

flowchart TB
    subgraph Admin["dbrole_admin (Admin)"]
        A1["Can run DDL / CREATE / ALTER"]
        A2["Inherits dbrole_readwrite"]
    end
    subgraph RW["dbrole_readwrite (Read-Write)"]
        RW1["Can INSERT/UPDATE/DELETE"]
        RW2["Inherits dbrole_readonly"]
    end
    subgraph RO["dbrole_readonly (Read-Only)"]
        RO1["Can SELECT all tables"]
    end
    subgraph Offline["dbrole_offline (Offline)"]
        OFF1["Only for offline instances"]
    end

    Admin --> RW --> RO

Problems solved

• Production accounts have excessive permissions
• DDL and DML are not separated, increasing risk

Default Roles and System Users

Pigsty provides four roles and four system users (from default source values):

This default role set covers most use cases.

Default Privilege Policy

Pigsty writes default privileges (pg_default_privileges) during initialization so new objects automatically get reasonable permissions.

Problems solved

• New objects lack grants and apps fail
• Accidental grants to PUBLIC expose the whole DB

Approach

• Read-only role: SELECT/EXECUTE
• Read-write role: INSERT/UPDATE/DELETE
• Admin role: DDL privileges

Object Ownership and DDL Convention

Default privileges only apply to objects created by admin roles.

That means:

• Run DDL as dbuser_dba / postgres
• Or business admins SET ROLE dbrole_admin before DDL

Otherwise, new objects fall outside the default privilege system and break least privilege.

Database Isolation

Database-level isolation uses revokeconn:

pg_databases:
  - { name: appdb, owner: dbuser_app, revokeconn: true }

Problems solved

• One account can “pierce” all databases
• Multi-tenant DBs lack boundaries

Public Privilege Tightening

Pigsty revokes CREATE on the public schema during init:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Problems solved

• Unauthorized users create objects
• “Shadow tables/functions” security risks

Offline Role Usage

dbrole_offline can only access offline instances (pg_role=offline or pg_offline_query=true).

Problems solved

• ETL/analysis affects production performance
• Personal accounts run risky queries on primary

Best Practices

• Use dbrole_readwrite or dbrole_readonly for business accounts.
• Run production DDL via admin roles.
• Enable revokeconn for multi-tenant isolation.
• Use dbrole_offline for reporting/ETL.

Next

• 🔑 Authentication: HBA rules and password policy
• 🔐 Encrypted Communication: TLS and certificate management
• ✅ Compliance Checklist: MLPS and SOC2 mapping

4 - Encrypted Communication and Local CA

Encrypted communication solves three problems:

• Eavesdropping: prevent plaintext traffic sniffing
• Tampering: prevent MITM modification
• Impersonation: prevent fake servers/clients

Pigsty uses a local CA + TLS to provide a unified trust root for databases and infrastructure components.

Role of the Local CA

Pigsty generates a self-signed CA on the admin node by default:

files/pki/ca/ca.key   # CA private key (must be protected)
files/pki/ca/ca.crt   # CA root certificate (distributable)

Default values in source:

• ca_create: true: auto-generate if CA not found.
• ca_cn: pigsty-ca: CA certificate CN fixed to pigsty-ca.
• Root cert validity about 100 years (self-signed).
• Server/client cert validity cert_validity: 7300d (20 years).

Certificate Coverage

The local CA issues certs for multiple components with a unified trust chain:

Problem solved: different components issuing their own certs create fragmented trust; a unified CA enables one distribution, many uses.

Trust Distribution

Pigsty distributes ca.crt to all nodes and adds it to system trust:

• Cert path: /etc/pki/ca.crt
• EL family: /etc/pki/ca-trust/source/anchors/
• Debian/Ubuntu: /usr/local/share/ca-certificates/

This allows system clients to trust Pigsty-issued certificates automatically.

Using an External CA

If you already have an enterprise CA, replace:

files/pki/ca/ca.key
files/pki/ca/ca.crt

Recommended:

ca_create: false

Problem solved: prevents accidental generation of a new self-signed CA and trust chain confusion.

Client Certificate Authentication

Certificate auth can replace or enhance password auth:

• Avoid password phishing or leakage
• Certificates can bind device and account

Pigsty ships cert.yml to issue client certificates:

./cert.yml -e cn=dbuser_dba
./cert.yml -e cn=dbuser_monitor

Generated by default at:

files/pki/misc/<cn>.key
files/pki/misc/<cn>.crt

Key Protection and Rotation

• CA private key is 0600 by default and stored in a 0700 directory.
• If the CA private key leaks, regenerate the CA and re-issue all certs.
• Rotate certificates after major upgrades or key incidents.

Next

• 🔑 Authentication: HBA and certificate auth
• 👤 Access Control: role and privilege model
• ✅ Compliance Checklist: compliance evidence and mapping

5 - Data Security

Data security focuses on three things: integrity, recoverability, confidentiality. Pigsty enables key capabilities by default and supports further hardening.

Data Integrity

Problems solved

• Silent corruption from bad disks or memory errors
• Accidental writes causing data pollution

Pigsty support

• Data checksums: default pg_checksum: true, enables data-checksums at init.
• Replica fallback: recover bad blocks from replicas (with HA).

Recoverability (Backup and PITR)

Problems solved

• Accidental deletion or modification
• Disaster-level data loss

Pigsty support

• pgBackRest enabled by default: pgbackrest_enabled: true.
• Local repository: keeps 2 full backups by default.
• Remote repository: MinIO support, object storage and multi-replica.
• PITR: recover to any point in time with WAL archive.

Data Confidentiality

Problems solved

• Backup theft leading to data leakage
• Media theft leaking plaintext data

Pigsty support

• Backup encryption: MinIO repo supports AES-256-CBC (cipher_type).
• Transparent encryption (optional): pg_tde and similar extensions for at-rest encryption.
• Key isolation: keep cipher_pass separate from CA private keys.

Audit and Traceability

Problems solved

• No accountability or audit trail
• Compliance audits lack evidence

Pigsty support

• Log collection: templates enable logging_collector by default.
• DDL audit: log_statement: ddl.
• Slow queries: log_min_duration_statement.
• Connection logs: log_connections (PG18+).
• Audit extensions: pgaudit, pgauditlogtofile optional.

Hardening Recommendations

• Enforce encryption and dedicated keys for remote backups.
• Drill PITR regularly and verify the recovery chain.
• Enable pgaudit for critical workloads.
• Pair with High Availability for “backup + replica” double safety.

Next

• 🔐 Encrypted Communication: certificate management and TLS
• ✅ Compliance Checklist: audit and compliance requirements
• ⏰ Backup & Recovery: PITR mechanics and practice

6 - Compliance Checklist

Compliance is not a switch, but a combination of configuration + process + evidence:

• Configuration: are security capabilities enabled (HBA/TLS/audit/backup)?
• Process: access management, change control, backup drills
• Evidence: logs, config snapshots, backup reports, monitoring alerts

This page uses SOC2 and MLPS Level 3 as entry points to map Pigsty’s security capabilities and compliance evidence.

Default Credentials Checklist (Must Change)

From source defaults:

Must change all defaults in production.

Evidence Preparation (Recommended)

SOC2 Perspective (Example Mapping)

SOC2 focuses on security, availability, confidentiality. Below is a conceptual mapping of common controls:

This is a conceptual mapping. SOC2 requires organizational policies and audit evidence.

MLPS Level 3 (GB/T 22239-2019) Mapping

MLPS Level 3 focuses on identity, access control, audit, data security, communication security, host security, and network boundary. Below is a mapping of key controls:

Tip: MLPS Level 3 is not only technical; it requires strong operations processes.

Compliance Hardening Snippets

# Enforce SSL / certificates
pg_hba_rules:
  - { user: '+dbrole_readonly', db: all, addr: intra, auth: ssl }
  - { user: dbuser_dba, db: all, addr: world, auth: cert }

# Password strength
pg_libs: '$libdir/passwordcheck, pg_stat_statements, auto_explain'
pg_extensions: [ passwordcheck, credcheck ]

# PgBouncer / Patroni TLS
pgbouncer_sslmode: require
patroni_ssl_enabled: true

# OS security
node_firewall_mode: zone
node_selinux_mode: enforcing

Compliance Checklist

Before Deployment

• Network segmentation and trusted CIDRs defined
• Certificate policy decided (self-signed / enterprise CA)
• Account and privilege tiering plan confirmed

After Deployment (Must)

• Change all default passwords
• Verify HBA rules meet expectations
• Enable and verify TLS
• Configure audit and log retention policies

Periodic Maintenance

• Permission audit and account cleanup
• Certificate rotation
• Backup and recovery drills

Next

• 🔑 Authentication: HBA and password policy
• 🔒 Data Security: backup and encryption
• ♾️ High Availability: business continuity