我的书签
添加书签
移除书签PostgreSQL Auth
Authentication is about identity verification and block/allow list
Pigsty uses md5 password authentication by default and provides access control based on the PostgreSQL HBA mechanism.
HBA(Host Based Authentication)can be treated as an IP blocklist and allowlist.
Config: HBA
In Pigsty, the HBA of all instances is generated from the config file, and HBA rules vary depending on the instance’s role (pg_role). The following variables control pigsty’s HBAs.
- pg_hba_rules: Environmentally uniform HBA rules
- pg_hba_rules_extra: HBA rules for a specific instance or cluster
- pgbouncer_hba_rules: HBA rules used for connection pooling
- pgbouncer_hba_rules_extra: HBA rules for a specific instance or cluster connection pooling
Each variable is an array consisting of the following rules.
1. - title: allow intranet admin password access
2. role: common
3. rules:
4. - host all +dbrole_admin 10.0.0.0/8 md5
5. - host all +dbrole_admin 172.16.0.0/12 md5
6. - host all +dbrole_admin 192.168.0.0/16 md5
Role-Based HBA
The HBA rule set with role = common is installed to all instances,(role: primary) are only installed to instances with pg_role = primary.
As a special case, the HBA rule for the role: offline will be installed to instances with pg_role == 'offline' as well as to instances with pg_offline_query == true.
The rendering priority rules for HBA are:
hard_coded_rulesGlobal hard-coded rulespg_hba_rules_extra.commonCluster common rulespg_hba_rules_extra.pg_roleCluster role rulespg_hba_rules.pg_roleGlobal role rulespg_hba_rules.offlineCluster offline rulespg_hba_rules_extra.offlineGlobal offline rulespg_hba_rules.commonGlobal common rules
Default HBA Rules
Under the default config, the primary and replica will use the following HBA rules:
- Superuser access with local OS auth.
- Other users can access it with a password from local.
- Replica users can access via password from the LAN segment.
- Monitor users can access it locally.
- Everyone can access it with a password on the meta node.
- Admin users can access via password from the LAN.
- Everyone can access the intranet with a password.
- Read and write users (production business users) can be accessed locally (Connection Pool).
- On the replica: read-only users (individuals) can access from the local (Connection Pool).
- On instances with
pg_role == 'offline'or withpg_offline_query == true, HBA rules that allow access todbrole_offlinegrouped users are added.
Default HBA rule information
1. #==============================================================#
2. # Default HBA
3. #==============================================================#
4. # allow local su with ident"
5. local all postgres ident
6. local replication postgres ident
8. # allow local user password access
9. local all all md5
11. # allow local/intranet replication with password
12. local replication replicator md5
13. host replication replicator 127.0.0.1/32 md5
14. host all replicator 10.0.0.0/8 md5
15. host all replicator 172.16.0.0/12 md5
16. host all replicator 192.168.0.0/16 md5
17. host replication replicator 10.0.0.0/8 md5
18. host replication replicator 172.16.0.0/12 md5
19. host replication replicator 192.168.0.0/16 md5
21. # allow local role monitor with password
22. local all dbuser_monitor md5
23. host all dbuser_monitor 127.0.0.1/32 md5
25. #==============================================================#
26. # Extra HBA
27. #==============================================================#
28. # add extra hba rules here
30. #==============================================================#
31. # primary HBA
32. #==============================================================#
34. #==============================================================#
35. # special HBA for instance marked with 'pg_offline_query = true'
36. #==============================================================#
38. #==============================================================#
39. # Common HBA
40. #==============================================================#
41. # allow meta node password access
42. host all all 10.10.10.10/32 md5
44. # allow intranet admin password access
45. host all +dbrole_admin 10.0.0.0/8 md5
46. host all +dbrole_admin 172.16.0.0/12 md5
47. host all +dbrole_admin 192.168.0.0/16 md5
49. # allow intranet password access
50. host all all 10.0.0.0/8 md5
51. host all all 172.16.0.0/12 md5
52. host all all 192.168.0.0/16 md5
54. # allow local read/write (local production user via pgbouncer)
55. local all +dbrole_readonly md5
56. host all +dbrole_readonly 127.0.0.1/32 md5
58. #==============================================================#
59. # Ad Hoc HBA
60. #===========================================================
Change HBA Rules
Users can modify and apply the new HBA rules through a playbook after the cluster/instance is created and running.
1. ./pgsql.yml -t pg_hba # Specify the target cluster with -l
2. bin/reloadhba <cluster> # Reload the HBA rules
When the database cluster directory is destroyed and rebuilt, the new copy will have the same HBA rules as the cluster primary. You can use the above command to perform HBA repair for a specific instance.
Pgbouncer HBA
In Pigsty, Pgbouncer also uses HBA for access control. The usage is the same as Postgres HBA:
- pgbouncer_hba_rules: HBA rules used by the connection pool
- pgbouncer_hba_rules_extra: Instance- or cluster-specific connection pooling HBA rules
The default Pgbouncer HBA rules allow password access from local and intranet.
1. pgbouncer_hba_rules: # pgbouncer host-based authentication rules
2. - title: local password access
3. role: common
4. rules:
5. - local all all md5
6. - host all all 127.0.0.1/32 md5
8. - title: intranet password access
9. role: common
10. rules:
11. - host all all 10.0.0.0/8 md5
12. - host all all 172.16.0.0/12 md5
13. - host all all 192.168.0.0/16 md5
Last modified 2022-06-04: fii en docs batch 2 (61bf601)
