# Security > Security considerations and best-practices --- Pigsty already provides a secure-by-default database [authentication](/docs/pgsql/hba/) and [access control](/docs/pgsql/acl) 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 - [`grafana_admin_password`](/docs/infra/param#grafana_admin_password): `pigsty` - [`pg_admin_password`](/docs/pgsql/param#pg_admin_password): `DBUser.DBA` - [`pg_monitor_password`](/docs/pgsql/param#pg_monitor_password): `DBUser.Monitor` - [`pg_replication_password`](/docs/pgsql/param#pg_replication_password): `DBUser.Replicator` - [`patroni_password`](/docs/pgsql/param#patroni_password): `Patroni.API` - [`haproxy_admin_password`](/docs/node/param#haproxy_admin_password): `pigsty` - [`minio_secret_key`](/docs/minio/param#minio_secret_key): `minioadmin` **Change MinIO credentials and pgbackrest references** If you are using MinIO as backup storage, also change these credentials: - Change the password for [`minio_users`.`[pgbackrest]`.`secret_key`](/docs/minio/param#minio_users) - Change pgbackrest references: [`pgbackrest_repo`.`minio`.`s3_key_secret`](/docs/pgsql/param#pgbackrest_repo) **Enforce strong pg password with the passwordcheck extension** - add `$lib/passwordcheck` to [`pg_libs`](/docs/pgsql/param#pg_libs) to enforce password policy. - stronger version: `passwordcheck_cracklib` **Encrypt remote backup with an encryption algorithm** - check [`pgbackrest_repo`](/docs/pgsql/param#pgbackrest_repo) definition `repo_cipher_type` - `cipher_type: aes-256-cbc` by default **Use advanced password encryption method for PostgreSQL** - use [`pg_pwd_enc`](/docs/pgsql/param#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. ```yaml - { 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** ```bash 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`](/docs/pgsql/param#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`](/docs/redis/param#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`](https://github.com/pgsty/pigsty/blob/v3.7.0/conf/ha/safe.yml) **Limit patroni admin access from the infra/admin node** - This is restricted by default with [`restapi.allowlist`](https://github.com/pgsty/pigsty/blob/v3.7.0/roles/pgsql/templates/oltp.yml#L108) ------ ### Network Traffic **Access Nginx with SSL and domain names** - Nginx SSL is controlled by [`nginx_sslmode`](/docs/infra/param#nginx_sslmode), which is `enable` by default. - Nginx Domain names are specified by [`infra_portal..domain`](/docs/infra/param#infra_portal). **Secure Patroni REST API with SSL** - [`patroni_ssl_enabled`](/docs/pgsql/param#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`](/docs/pgsql/param#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`](/docs/pgsql/param#pg_conf) will trade some availability for the best consistency. **Use node crit tuned template for better consistency** - set [`node_tune`](/docs/node/param#node_tune) to `crit` to reduce dirty page ratio. - Enable data checksum to detect silent data corruption. - [`pg_checksum`](/docs/pgsql/param#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](/docs/etcd/config/) for details. ### Fault Tolerance **Trade off between availability and consistency for PostgreSQL** - [`pg_rpo`](/docs/pgsql/param#pg_rpo) : **trade-off between Availability and Consistency** - [`pg_rto`](/docs/pgsql/param#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.