Security

• pigsty.yml hold very sensitive information like passwords
• Limit access to admin/infra nodes to admin/DBA users only
• Limit access to repo, if you are managing pigsty config with GitOps

• Generated at ~/pigsty/files/pki/ca/ca.key by default
• Secure & backup it in a safe place, DON'T THROW IT AWAY!
• Also consider secure other private keys for various certs

If you are using MinIO as backup storage, also change these credentials:

• Change the password for minio_users.[pgbackrest].secret_key
• Change pgbackrest references: pgbackrest_repo.minio.s3_key_secret

• add $lib/passwordcheck to pg_libs to enforce password policy.
• stronger version: passwordcheck_cracklib

• check pgbackrest_repo definition repo_cipher_type
• cipher_type: aes-256-cbc by default

• use pg_pwd_enc default scram-sha-256 instead of legacy md5
• default behavior is scram-sha-256, md5 is deprecated

You can set an expiry date for each user for compliance purposes.

- { name: dbuser_meta , password: Pleas3-ChangeThisPwd ,expire_in: 7300 ,pgbouncer: true ,roles: [ dbrole_admin ]    ,comment: pigsty admin user }
- { name: dbuser_view , password: Make.3ure-Compl1ance  ,expire_in: 7300 ,pgbouncer: true ,roles: [ dbrole_readonly ] ,comment: read-only viewer for meta database }
- { name: postgres     ,superuser: true  ,expire_in: 7300                        ,comment: system superuser }
- { name: replicator ,replication: true  ,expire_in: 7300 ,roles: [pg_monitor, dbrole_readonly]   ,comment: system replicator }
- { name: dbuser_dba   ,superuser: true  ,expire_in: 7300 ,roles: [dbrole_admin]  ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user }
- { name: dbuser_monitor ,roles: [pg_monitor] ,expire_in: 7300 ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }

Don't forget to refresh these expiry dates periodically with pgsql-user.yml playbook

• The default pg_listen address is 0.0.0.0, which is all IPv4 addresses.
• Consider using pg_listen: '${ip},${vip},${lo}' to bind to specific addresses for better security.

• Grafana/Prometheus are bind to all IP address by default for convenience.
• You can modify their bind configuration to listen on localhost/intranet IP and expose by Nginx.
• Redis server are bind to all IP address by default for convenience. You can change redis_bind_address to listen on intranet IP.
• You can also implement it with the security group or firewall rules.

• There's a security enhance config template: security.yml

• This is restricted by default with restapi.allowlist

• Nginx SSL is controlled by nginx_sslmode, which is enable by default.
• Nginx Domain names are specified by infra_portal..domain.

• patroni_ssl_enabled is disabled by default
• Since it affects health checks and API invocation.
• Note this is a global option, and you have to decide before deployment.

• pgbouncer_sslmode is disable by default
• Since it has a significant performance impact.

• Use crit.yml templates for pg_conf will trade some availability for the best consistency.

• set node_tune to crit to reduce dirty page ratio.

• Enable data checksum to detect silent data corruption.

• pg_checksum is disabled by default, and enabled for crit.yml by default

• This can be enabled later, which requires a full cluster scan/stop.

• Enable log_connections and log_disconnections after the pg cluster bootstrap.
• Audit incoming sessions; this is enabled in crit.yml by default.

It will OVERWRITE existing PostgreSQL by default before v3.5.

Use pg_safeguard to avoid fat fingers

• You need at least three nodes (tolerate one node failure) to achieve production-grade high availability.
• If you only have two nodes, you can tolerate the failure of the specific standby node.
• If you have one node, use an external S3/MinIO for cold backup & wal archive storage.

• Use multiple infra nodes in serious production deployment (e.g., 1~3)
• Usually, 2 ~ 3 is enough for a large production deployment.

• Use enough etcd members and use odd numbers (1,3,5,7).
• Check ETCD Configuration for details.

• pg_rpo : trade-off between Availability and Consistency
• pg_rto : trade-off between failure chance and impact

• Do not access the database directly via a fixed IP address; use VIP, DNS, HAProxy, or their combination.
• Haproxy will handle the traffic control for the clients in case of failover/switchover.