Skip to content

Security

Security considerations and best-practices
Security considerations and best-practices

Pigsty already provides a secure-by-default database authentication and access control model.

It is strong enough for most common scenarios, as long as you follow the security best-practices below.

Confidentiality

Files

Secure your pigsty config file
  • 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
Secure your CA private key
  • 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

Passwords

DO NOT USE THE DEFAULT PASSWORDs

Always change these default passwords in serious deployment

Change MinIO credentials and pgbackrest references

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

Enforce strong pg password with the passwordcheck extension
  • add $lib/passwordcheck to pg_libs to enforce password policy.
  • stronger version: passwordcheck_cracklib
Encrypt remote backup with an encryption algorithm
  • check pgbackrest_repo definition repo_cipher_type
  • cipher_type: aes-256-cbc by default
Use advanced password encryption method for PostgreSQL
  • use pg_pwd_enc default scram-sha-256 instead of legacy md5
  • default behavior is scram-sha-256, md5 is deprecated
Add an expiration date to business user passwords

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

Don't print password to logs
SET log_statement TO 'none';
ALTER USER "{{ user.name }}" PASSWORD '{{ user.password }}';
SET log_statement TO DEFAULT;

IP Addresses

Bind specific IP addresses for postgres/pgbouncer/patroni
  • 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.
Do not expose any port to the Internet; except 80/443, the infra portal
  • 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.
Limit postgres client access with HBA
  • There’s a security enhance config template: safe.yml
Limit patroni admin access from the infra/admin node

Network Traffic

Access Nginx with SSL and domain names
Secure Patroni REST API with SSL
  • 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.
Secure Pgbouncer Client Traffic with SSL
  • pgbouncer_sslmode is disable by default
  • Since it has a significant performance impact.

Integrity

Consistency

Use consistency-first mode for PostgreSQL
  • Use crit.yml templates for pg_conf will trade some availability for the best consistency.
Use node crit tuned template for better consistency

  • set node_tune to crit to reduce dirty page ratio.

  • Enable data checksum to detect silent data corruption.

  • pg_checksum is enabled by default in v3.7.0

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

Audit

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

Fat Finger

DO NOT RE-RUN install.yml PLAYBOOK

Running install.yml again will nuke (overwrite) the entire deployment!

RE-RUN pgsql.yml WITH CAUTION

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

Use pg_safeguard to avoid fat fingers

Availability

Redundancy

Use enough nodes for serious production deployment
  • 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
  • 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
  • Use enough etcd members and use odd numbers (1,3,5,7).
  • Check ETCD Configuration for details.

Fault Tolerance

Trade off between availability and consistency for PostgreSQL
  • pg_rpo : trade-off between Availability and Consistency
  • pg_rto : trade-off between failure chance and impact

Access

Use VIP, DNS, HAProxy instead of fixed IP
  • 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.