Security and Compliance

Pigsty v4.0 provides Enterprise-grade PostgreSQL security configuration, covering multiple dimensions including identity authentication, access control, communication encryption, audit logging, data integrity, backup and recovery, etc.

This document uses China Level 3 MLPS (GB/T 22239-2019) and SOC 2 Type II security compliance requirements as reference, comparing and verifying Pigsty’s security capabilities item by item.

Each security dimension includes two parts:

• Default Configuration: Security compliance status when using conf/meta.yml and default parameters (Personal use)
• Available Configuration: Enhanced security status achievable by adjusting Pigsty parameters (Enterprise-grade configuration achievable)

Compliance Summary

Level 3 MLPS Core Requirements Comparison

SOC 2 Type II Control Points Comparison

Legend: ✅ Default met ⚠️ Requires additional configuration

Identity Authentication

MLPS Requirement: Users logging in should be identified and authenticated, with unique identity identification; two or more combined authentication techniques such as passwords, cryptographic technology, and biometric technology should be used.

SOC 2: CC6.1 - Logical and physical access control; user authentication mechanisms.

User Identity Identification

PostgreSQL implements user identity identification through the Role system, with each user having a unique role name.

Default Configuration: Pigsty presets a tiered role system:

pg_default_roles:
  - { name: dbrole_readonly  ,login: false ,comment: 'Global read-only role' }
  - { name: dbrole_offline   ,login: false ,comment: 'Restricted read-only role (offline queries)' }
  - { name: dbrole_readwrite ,login: false ,roles: [dbrole_readonly] ,comment: 'Global read-write role' }
  - { name: dbrole_admin     ,login: false ,roles: [pg_monitor,dbrole_readwrite] ,comment: 'Object management role' }
  - { name: postgres         ,superuser: true  ,comment: 'System superuser' }
  - { name: replicator       ,replication: true,roles: [pg_monitor,dbrole_readonly] ,comment: 'Replication user' }
  - { name: dbuser_dba       ,superuser: true  ,roles: [dbrole_admin] ,pgbouncer: true ,comment: 'Admin user' }
  - { name: dbuser_monitor   ,roles: [pg_monitor,dbrole_readonly] ,pgbouncer: true ,comment: 'Monitor user' }

Available Configuration: Users can define business users via pg_users, supporting account validity period, connection limits, etc.:

pg_users:
  - name: dbuser_app
    password: 'SecurePass123!'
    roles: [dbrole_readwrite]
    expire_in: 365           # Expires after 365 days
    connlimit: 100           # Maximum 100 connections
    comment: 'Application user'

Password Policy

Default Configuration:

• Password encryption uses SCRAM-SHA-256 algorithm, the most secure password hash algorithm currently supported by PostgreSQL
• Passwords automatically use SET log_statement TO 'none' when set to prevent plaintext leakage to logs
• Database superuser postgres has no password by default, only allows local Unix Socket access via ident authentication

Available Configuration:

• Enable passwordcheck extension to enforce password complexity:

  pg_libs: 'passwordcheck, pg_stat_statements, auto_explain'
  

• Use credcheck extension for richer password policies (length, complexity, history, etc.)

• Set user account validity period:

  pg_users:
    - { name: temp_user, password: 'xxx', expire_in: 30 }  # Expires after 30 days
    - { name: temp_user, password: 'xxx', expire_at: '2025-12-31' }  # Expires on specified date
  

Authentication Mechanisms

Default Configuration: Pigsty implements tiered authentication strategy based on source address:

pg_default_hba_rules:
  - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu local ident auth'}
  - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu local replication'}
  - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: pwd   ,title: 'replication user local password auth'}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replication user intranet password auth'}
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,title: 'replication user intranet access postgres'}
  - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor user local password auth'}
  - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: pwd   ,title: 'monitor user access from infra nodes'}
  - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin SSL+password auth'}
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: ssl   ,title: 'admin global SSL+password auth'}
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'readonly role local password auth'}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'readonly role intranet password auth'}
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'offline role intranet password auth'}

Supported authentication method aliases:

Available Configuration:

• Enable client certificate authentication for two-factor auth:

  pg_hba_rules:
    - {user: 'secure_user', db: all, addr: world, auth: cert, title: 'Certificate auth user'}
  

• Restrict specific user to access from specified IP only:

  pg_hba_rules:
    - {user: 'app_user', db: 'appdb', addr: '192.168.1.100/32', auth: ssl}
  

Access Control

MLPS Requirement: Management users should be granted minimum necessary privileges, implementing privilege separation for management users; access control policies should be configured by authorized entities.

SOC 2: CC6.3 - Role-based access control and least privilege principle.

Privilege Separation

Default Configuration: Pigsty implements clear separation of duties model:

Available Configuration:

• Fine-grained privilege control implemented via pg_default_privileges:

  pg_default_privileges:
    - 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 INSERT     ON TABLES    TO dbrole_readwrite
    - GRANT UPDATE     ON TABLES    TO dbrole_readwrite
    - GRANT DELETE     ON TABLES    TO dbrole_readwrite
    - GRANT TRUNCATE   ON TABLES    TO dbrole_admin
    - GRANT CREATE     ON SCHEMAS   TO dbrole_admin
  

Operating System Level Privileges

Default Configuration:

• Database superuser postgres sudo privileges are limit, only allowing execution of specific service management commands:
  • Start/stop/restart PostgreSQL related services
  • Load softdog kernel module (for watchdog)

%postgres ALL=NOPASSWD: /bin/systemctl stop postgres
%postgres ALL=NOPASSWD: /bin/systemctl start postgres
%postgres ALL=NOPASSWD: /bin/systemctl reload patroni
# ... other restricted commands

Available Configuration:

• pg_dbsu_sudo: none - Completely disable sudo privileges (strictest)
• pg_dbsu_sudo: all - Full sudo requiring password (balanced solution)
• pg_dbsu_sudo: nopass - Full sudo without password (not recommended)

Row Level Security (RLS)

PostgreSQL natively supports Row Level Security (RLS), can set user attributes via pg_users:

pg_users:
  - name: secure_user
    bypassrls: false  # Don't allow bypassing RLS
    roles: [dbrole_readwrite]

Combined with RLS policies in database, can implement fine-grained data access control.

Communication Security

MLPS Requirement: Cryptographic technology should be used to ensure data integrity and confidentiality during communication.

SOC 2: CC6.6 - Data transmission security; CC6.7 - Encryption controls.

SSL/TLS Encryption

Default Configuration:

• PostgreSQL server enables SSL by default, supports encrypted connections
• Admin users (${admin}) forced to use hostssl connections
• Automatically generates and distributes SSL certificates to all database nodes

# SSL configuration in patroni.yml
ssl: 'on'
ssl_cert_file: '/pg/cert/server.crt'
ssl_key_file: '/pg/cert/server.key'
ssl_ca_file: '/pg/cert/ca.crt'

Available Configuration:

• Enable Patroni REST API SSL encryption:

  patroni_ssl_enabled: true
  

• Enable PgBouncer client SSL:

  pgbouncer_sslmode: require  # or verify-ca, verify-full
  

• Force all connections to use SSL:

  pg_hba_rules:
    - {user: all, db: all, addr: world, auth: ssl, title: 'Force SSL'}
  

PKI Certificate Management

Default Configuration:

Pigsty uses self-built PKI system, automatically manages certificate lifecycle:

files/pki/
├── ca/           # CA root certificate
│   ├── ca.crt    # CA public key certificate
│   └── ca.key    # CA private key
├── csr/          # Certificate signing requests
├── pgsql/        # PostgreSQL cluster certificates
├── etcd/         # ETCD cluster certificates
├── infra/        # Infrastructure node certificates
└── minio/        # MinIO certificates

• Each PostgreSQL cluster shares one private key, each instance has independent certificate
• Certificates include correct SAN (Subject Alternative Name) configuration
• CA certificate automatically distributed to /etc/pki/ca.crt and /pg/cert/ca.crt

Available Configuration:

• Use externally CA-signed certificates: Place certificates in files/pki/ directory, set ca_create: false
• Adjust certificate validity: cert_validity: 365d (1 year)

ETCD Communication Security

ETCD as Patroni’s DCS (Distributed Configuration Store), uses mTLS (mutual TLS) authentication by default:

etcd3:
  hosts: '10.10.10.10:2379'
  protocol: https
  cacert: /pg/cert/ca.crt
  cert:   /pg/cert/server.crt
  key:    /pg/cert/server.key
  username: 'pg-meta'        # Cluster-specific account
  password: 'pg-meta'        # Default same as cluster name

Data Encryption

MLPS Requirement: Cryptographic technology should be used to ensure confidentiality of important data during storage.

SOC 2: CC6.1 - Data encryption storage.

Backup Encryption

Default Configuration:

• Local backup (pgbackrest_method: local) not encrypted by default
• Remote object storage backup supports AES-256-CBC encryption

Available Configuration:

Enable backup encryption (recommended for remote storage):

pgbackrest_method: minio
pgbackrest_repo:
  minio:
    type: s3
    s3_endpoint: sss.pigsty
    s3_bucket: pgsql
    s3_key: pgbackrest
    s3_key_secret: S3User.Backup
    cipher_type: aes-256-cbc
    cipher_pass: 'YourSecureBackupPassword!'  # Must modify!
    retention_full_type: time
    retention_full: 14

Transparent Data Encryption (TDE)

PostgreSQL community edition doesn’t support native TDE, but storage encryption can be implemented via:

• Filesystem-level encryption: Use LUKS/dm-crypt to encrypt storage volumes
• pgsodium extension: Supports column-level encryption

# Enable pgsodium column-level encryption
pg_libs: 'pgsodium, pg_stat_statements, auto_explain'

# Custom encryption key (64-bit hex)
pgsodium_key: 'a1b2c3d4e5f6...'  # Or use external key management script

Data Integrity Verification

Default Configuration:

• Data checksums enabled by default, can detect storage layer data corruption
• crit.yml template enforces data checksums
• Supports pg_rewind for failure recovery

pg_checksum: true  # Strongly recommend keeping enabled

Security Auditing

MLPS Requirement: Security auditing should be enabled, covering each user, auditing important user behaviors and security events.

SOC 2: CC7.2 - System monitoring and logging; CC7.3 - Security event detection.

Database Audit Logging

Default Configuration:

# oltp.yml template audit configuration
log_destination: csvlog
logging_collector: 'on'
log_directory: /pg/log/postgres
log_filename: 'postgresql-%a.log'    # Rotate by weekday
log_file_mode: '0640'                # Restrict log file permissions
log_rotation_age: '1d'
log_truncate_on_rotation: 'on'
log_checkpoints: 'on'
log_lock_waits: 'on'
log_replication_commands: 'on'
log_statement: ddl                   # Record all DDL
log_min_duration_statement: 100      # Record slow queries >100ms

Available Configuration (crit.yml critical business template):

# crit.yml provides more comprehensive auditing
log_connections: 'receipt,authentication,authorization'  # PG18 full connection audit
log_disconnections: 'on'             # Record disconnections
log_lock_failures: 'on'              # Record lock failures (PG18)
track_activity_query_size: 32768     # Full query recording

Enable pgaudit extension for fine-grained auditing:

pg_libs: 'pgaudit, pg_stat_statements, auto_explain'
pg_parameters:
  pgaudit.log: 'all'
  pgaudit.log_catalog: 'on'
  pgaudit.log_relation: 'on'

Performance and Execution Auditing

Default Configuration:

pg_libs: 'pg_stat_statements, auto_explain'

# auto_explain configuration
auto_explain.log_min_duration: 1s    # Record query plans >1s
auto_explain.log_analyze: 'on'
auto_explain.log_verbose: 'on'
auto_explain.log_timing: 'on'

# pg_stat_statements configuration
pg_stat_statements.max: 10000
pg_stat_statements.track: all

Centralized Log Management

Default Configuration:

• PostgreSQL logs: /pg/log/postgres/
• Patroni logs: /pg/log/patroni/
• PgBouncer logs: /pg/log/pgbouncer/
• pgBackRest logs: /pg/log/pgbackrest/

Available Configuration:

Send logs to VictoriaLogs for centralized storage via Vector:

# Logs automatically collected to VictoriaLogs
vlogs_enabled: true
vlogs_port: 9428
vlogs_options: >-
  -retentionPeriod=15d
  -retention.maxDiskSpaceUsageBytes=50GiB

Network Security

MLPS Requirement: Access control devices should be deployed at network boundaries to implement access control for data flows entering and leaving the network.

SOC 2: CC6.1 - Boundary protection and network security.

Firewall Configuration

Default Configuration:

node_firewall_mode: zone             # Enable zone firewall
node_firewall_intranet:              # Define intranet addresses
  - 10.0.0.0/8
  - 192.168.0.0/16
  - 172.16.0.0/12
node_firewall_public_port:           # Public ports
  - 22    # SSH
  - 80    # HTTP
  - 443   # HTTPS
  - 5432  # PostgreSQL (open cautiously)

Firewall rules:

• Intranet addresses automatically added to trusted zone
• Only specified ports open to public
• Supports firewalld (RHEL-based) and ufw (Debian-based)

Available Configuration:

• node_firewall_mode: off - Disable firewall (not recommended)
• node_firewall_mode: none - Don’t modify existing config
• Remove port 5432, only allow intranet database access

Service Access Control

Default Configuration:

Patroni REST API only allows access from following addresses:

# Automatically calculated whitelist
pg_allow_list = [admin_ip] + pg_cluster_members + groups["infra"]

Available Configuration:

Restrict PostgreSQL to listen on specific NIC only:

pg_listen: '${ip}'  # Only listen on host IP, not 0.0.0.0

SELinux

Default Configuration: SELinux set to permissive mode (log but don’t block)

Available Configuration:

node_selinux_mode: enforcing  # Enforcing mode (requires additional policy configuration)

Availability and Recovery

MLPS Requirement: Should provide data backup and recovery functions; should provide automatic failure recovery.

SOC 2: CC9.1 - Business continuity; A1.2 - Data backup and recovery.

High Availability Architecture

Default Configuration:

• Patroni automatic failure detection and switching (RTO < 30s)
• Asynchronous replication, max data loss 1MB (RPO)
• failsafe_mode: true prevents split-brain

Available Configuration:

Enable synchronous replication for RPO = 0:

pg_rpo: 0                    # Zero data loss
pg_conf: crit.yml            # Use critical business template
# crit.yml automatically enables synchronous_mode: true

Enable hardware watchdog:

patroni_watchdog_mode: automatic  # or required

Backup Recovery

Default Configuration:

pgbackrest_enabled: true
pgbackrest_method: local
pgbackrest_repo:
  local:
    path: /pg/backup
    retention_full_type: count
    retention_full: 2            # Retain 2 full backups

Available Configuration:

Off-site backup to object storage:

pgbackrest_method: minio
pgbackrest_repo:
  minio:
    type: s3
    s3_endpoint: sss.pigsty
    s3_bucket: pgsql
    cipher_type: aes-256-cbc     # Encrypt backups
    retention_full_type: time
    retention_full: 14           # Retain 14 days
    block: y                     # Block-level incremental backup
    bundle: y                    # Small file merging

Scheduled backup strategy:

node_crontab:
  - '00 01 * * * postgres /pg/bin/pg-backup full'   # Daily 1am full backup
  - '00 */4 * * * postgres /pg/bin/pg-backup diff'  # Every 4 hours differential backup

Intrusion Prevention

MLPS Requirement: Should follow minimal installation principle, only installing necessary components and applications; should be able to detect intrusion attempts on important nodes, providing alerts for serious intrusion events.

SOC 2: CC6.8 - Malware protection; CC7.1 - Intrusion detection.

Minimal Installation

Default Configuration:

• Only install necessary PostgreSQL components and extensions
• Precisely control installation content via pg_packages and pg_extensions
• Production systems don’t install development tools and debug symbols

pg_packages: [ pgsql-main, pgsql-common ]  # Minimal installation
pg_extensions: []                          # Add extensions as needed

Security Extensions

Pigsty provides the following security-related extensions, can be installed and enabled as needed:

Install all security extension packages:

pg_extensions: [ pg18-sec ]  # Install security extension group

Alerting and Monitoring

Default Configuration:

• VictoriaMetrics + Alertmanager provide monitoring and alerting
• Preset PostgreSQL alert rules
• Grafana visualization dashboards

Key security-related alerts:

• Excessive authentication failures
• Excessive replication lag
• Backup failures
• Disk space shortage
• Connection exhaustion