Security and Compliance

Pigsty’s Security Philosophy

Secure by Default: Out-of-the-box security configuration—basic protection without additional setup.

Progressive Configuration: Enterprise users can gradually enhance security measures through configuration.

Defense in Depth: Multiple security layers—even if one layer is breached, others remain protective.

Least Privilege: Grant users only the minimum permissions needed to complete tasks, reducing risk.

Default Security Configuration

Pigsty enables these security features by default:

Enhanced Configuration

Additional configuration enables higher security levels:

If you only have one minute, remember this diagram:

flowchart TB
    subgraph L1["Layer 1: Network Security"]
        L1A["Firewall + SSL/TLS Encryption + HAProxy Proxy"]
        L1B["Who can connect? Is the connection encrypted?"]
    end

    subgraph L2["Layer 2: Authentication"]
        L2A["HBA Rules + SCRAM-SHA-256 Passwords + Certificate Auth"]
        L2B["Who are you? How do you prove it?"]
    end

    subgraph L3["Layer 3: Access Control"]
        L3A["Role System + Object Permissions + Database Isolation"]
        L3B["What can you do? What data can you access?"]
    end

    subgraph L4["Layer 4: Data Security"]
        L4A["Data Checksums + Backup Encryption + Audit Logs"]
        L4B["Is data intact? Are operations logged?"]
    end

    L1 --> L2 --> L3 --> L4

Core Value: Enterprise-grade security configuration out of the box, best practices enabled by default, additional configuration achieves SOC 2 compliance.

Contents

Why Security Matters

The Cost of Data Breaches

flowchart LR
    Breach["Data Breach"]

    subgraph Direct["Direct Losses"]
        D1["Regulatory Fines<br/>GDPR up to 4% global revenue"]
        D2["Legal Costs"]
        D3["Customer Compensation"]
    end

    subgraph Indirect["Indirect Losses"]
        I1["Brand Reputation Damage"]
        I2["Customer Trust Loss"]
        I3["Business Disruption"]
    end

    subgraph Compliance["Compliance Risk"]
        C1["Liability"]
        C2["SOC 2: Certification Revocation"]
        C3["Industry Access: Banned from Operating"]
    end

    Breach --> Direct
    Breach --> Indirect
    Breach --> Compliance

Default Users and Passwords

Pigsty creates these system users by default:

# pigsty.yml - Change default passwords
pg_admin_password: 'YourSecurePassword123!'
pg_monitor_password: 'AnotherSecurePass456!'
pg_replication_password: 'ReplicationPass789!'

Important: After production deployment, immediately change these default passwords!

Role and Permission System

Pigsty provides a four-tier role system out of the box:

flowchart TB
    subgraph Admin["dbrole_admin (Admin)"]
        A1["Inherits dbrole_readwrite"]
        A2["Can CREATE/DROP/ALTER objects (DDL)"]
        A3["For: Business admins, apps needing table creation"]
    end

    subgraph RW["dbrole_readwrite (Read-Write)"]
        RW1["Inherits dbrole_readonly"]
        RW2["Can INSERT/UPDATE/DELETE"]
        RW3["For: Production business accounts"]
    end

    subgraph RO["dbrole_readonly (Read-Only)"]
        RO1["Can SELECT all tables"]
        RO2["For: Reporting, data analysis"]
    end

    subgraph Offline["dbrole_offline (Offline)"]
        OFF1["Can only access offline instances"]
        OFF2["For: ETL, personal analysis, slow queries"]
    end

    Admin --> |inherits| RW
    RW --> |inherits| RO

Creating Business Users

pg_users:
  # Read-only user - for reporting
  - name: dbuser_report
    password: ReportUser123
    roles: [dbrole_readonly]
    pgbouncer: true

  # Read-write user - for production
  - name: dbuser_app
    password: AppUser456
    roles: [dbrole_readwrite]
    pgbouncer: true

  # Admin user - for DDL operations
  - name: dbuser_admin
    password: AdminUser789
    roles: [dbrole_admin]
    pgbouncer: true

HBA Access Control

HBA (Host-Based Authentication) controls “who can connect from where”:

flowchart LR
    subgraph Sources["Connection Sources"]
        S1["Local Socket"]
        S2["localhost"]
        S3["Intranet CIDR"]
        S4["Admin Nodes"]
        S5["External"]
    end

    subgraph Auth["Auth Methods"]
        A1["ident/peer<br/>OS user mapping, most secure"]
        A2["scram-sha-256<br/>Password auth"]
        A3["scram-sha-256 + SSL<br/>Enforce SSL"]
    end

    S1 --> A1
    S2 --> A2
    S3 --> A2
    S4 --> A3
    S5 --> A3

    Note["Rules matched in order<br/>First matching rule applies"]

Custom HBA Rules

pg_hba_rules:
  # Allow app servers from intranet
  - {user: dbuser_app, db: mydb, addr: '10.10.10.0/24', auth: scram-sha-256}

  # Force SSL for certain users
  - {user: admin, db: all, addr: world, auth: ssl}

  # Require certificate auth (highest security)
  - {user: secure_user, db: all, addr: world, auth: cert}

Encrypted Communication

SSL/TLS Architecture

sequenceDiagram
    participant Client as Client
    participant Server as PostgreSQL

    Client->>Server: 1. ClientHello
    Server->>Client: 2. ServerHello
    Server->>Client: 3. Server Certificate
    Client->>Server: 4. Client Key
    Client->>Server: 5. Encrypted Channel Established
    Server->>Client: 5. Encrypted Channel Established

    rect rgb(200, 255, 200)
        Note over Client,Server: Encrypted Data Transfer
        Client->>Server: 6. Application Data (encrypted)
        Server->>Client: 6. Application Data (encrypted)
    end

    Note over Client,Server: Prevents eavesdropping, tampering, verifies server identity

Local CA

Pigsty automatically generates a local CA and issues certificates:

/etc/pki/
├── ca.crt              # CA certificate (public)
├── ca.key              # CA private key (keep secret!)
└── server.crt/key      # Server certificate/key

Important: Securely back up ca.key—if lost, all certificates must be reissued!

Compliance Mapping

SOC 2 Type II

Legend: Yes = Default satisfaction · Partial = Needs additional config

Security Checklist

Before Deployment

• Prepare strong passwords (use password manager)
• Plan network partitions (intranet/external CIDRs)
• Decide SSL strategy (self-signed/external CA)

After Deployment (Required)

• Change all default passwords
• Verify HBA rules match expectations
• Test SSL connections work
• Configure auth failure alerts
• Securely back up CA private key

Regular Maintenance

• Audit user permissions
• Check for expired accounts
• Update certificates (if needed)
• Review audit logs

Quick Config Examples

Production Security Configuration

# pigsty.yml - Production security config example
all:
  vars:
    # Change default passwords (required!)
    pg_admin_password: 'SecureDBAPassword2024!'
    pg_monitor_password: 'SecureMonitorPass2024!'
    pg_replication_password: 'SecureReplPass2024!'

    # Enable password strength check
    pg_libs: 'passwordcheck, pg_stat_statements, auto_explain'

    # Custom HBA rules
    pg_hba_rules:
      # App servers
      - {user: app, db: appdb, addr: '10.10.10.0/24', auth: scram-sha-256}
      # Admin enforce SSL
      - {user: dbuser_dba, db: all, addr: world, auth: ssl}

Financial-Grade Security Configuration

# Financial-grade config - enable certificate auth
pg_hba_rules:
  # Trading system uses certificate auth
  - {user: trade_user, db: trade, addr: world, auth: cert}
  # Other systems use SSL + password
  - {user: all, db: all, addr: world, auth: ssl}

# Enable backup encryption
pgbackrest_repo:
  minio:
    cipher_type: aes-256-cbc
    cipher_pass: 'YourBackupEncryptionKey'

Next Steps

Deep dive into security configuration details:

• Security Overview: Overall security architecture and checklist
• Authentication: HBA rules and password policies
• Access Control: Role system and permission model
• Encrypted Communication: SSL/TLS and certificate management
• Compliance Checklist: Detailed SOC2 mapping

Related topics:

• High Availability: Business continuity assurance
• Backup & Recovery: Data recovery capabilities
• Observability: Security event monitoring