Security

Security considerations and best-practices in Pigsty
Module:
Categories:
Tags:

Pigsty already provides a secure-by-default authentication and access control model, which is sufficient for most scenarios.

pigsty-acl.jpg

But if you want to further strengthen the security of the system, the following suggestions are for your reference:

Confidentiality

Important Files

Secure your pigsty config inventory

  • pigsty.yml has highly sensitive information, including passwords, certificates, and keys.
  • You should limit access to admin/infra nodes, only accessible by the admin/dba users
  • Limit access to the git repo, if you are using git to manage your pigsty source.

Secure your CA private key and other certs

  • These files are very important, and will be generated under files/pki under pigsty source dir by default.
  • You should secure & backup them in a safe place periodically.

Passwords

Always change these passwords, DO NOT USE THE DEFAULT VALUES:

Please change MinIO user secret key and pgbackrest_repo references

If you are using remote backup method, secure backup with distinct passwords

  • Use aes-256-cbc for pgbackrest_repo.*.cipher_type
  • When setting a password, you can use ${pg_cluster} placeholder as part of the password to avoid using the same password.

Use advanced password encryption method for PostgreSQL

  • use pg_pwd_enc default scram-sha-256 instead of legacy md5

Enforce a strong pg password with the passwordcheck extension.

  • add $lib/passwordcheck to pg_libs to enforce password policy.

Encrypt remote backup with an encryption algorithm

Add an expiration date to biz user passwords.

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

  • Don’t forget to refresh these passwords periodically.

    - { 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 }

Do not log changing password statement into postgres log.

SET log_statement TO 'none';
ALTER USER "{{ user.name }}" PASSWORD '{{ user.password }}';
SET log_statement TO DEFAULT;

IP Addresses

Bind to specific IP addresses rather than all 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: security.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 disabled by default, and enabled for crit.yml by default
    • This can be enabled later, which requires a full cluster scan/stop.

Audit

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

Availability

  • 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.

  • 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.

  • 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

  • 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 even numbers (1,3,5,7).


Last modified 2024-02-29: update content (34b2b75)