-
Notifications
You must be signed in to change notification settings - Fork 270
/
safe.yml
189 lines (172 loc) · 12.3 KB
/
safe.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
---
#==============================================================#
# File : safe.yml
# Desc : Pigsty 3-node security enhance template
# Ctime : 2020-05-22
# Mtime : 2024-12-16
# Docs : https://pigsty.io/docs/conf/safe
# License : AGPLv3 @ https://pigsty.io/docs/about/license
# Copyright : 2018-2025 Ruohang Feng / Vonng ([email protected])
#==============================================================#
#===== SECURITY ENHANCEMENT CONFIG TEMPLATE WITH 3 NODES ======#
# * 3 infra nodes, 3 etcd nodes, single minio node
# * 3-instance pgsql cluster with an extra delayed instance
# * crit.yml templates, no data loss, checksum enforced
# * enforce ssl on postgres & pgbouncer, use postgres by default
# * enforce an expiration date for all users (20 years by default)
# * enforce strong password policy with passwordcheck extension
# * enforce changing default password for all users
# * log connections and disconnections
# * restrict listen ip address for postgres/patroni/pgbouncer
all:
children:
infra: # infra cluster for proxy, monitor, alert, etc
hosts: # 1 for common usage, 3 nodes for production
10.10.10.10: { infra_seq: 1 } # identity required
10.10.10.11: { infra_seq: 2, repo_enabled: false }
10.10.10.12: { infra_seq: 3, repo_enabled: false }
vars: { patroni_watchdog_mode: off }
minio: # minio cluster, s3 compatible object storage
hosts: { 10.10.10.10: { minio_seq: 1 } }
vars: { minio_cluster: minio }
etcd: # dcs service for postgres/patroni ha consensus
hosts: # 1 node for testing, 3 or 5 for production
10.10.10.10: { etcd_seq: 1 } # etcd_seq required
10.10.10.11: { etcd_seq: 2 } # assign from 1 ~ n
10.10.10.12: { etcd_seq: 3 } # odd number please
vars: # cluster level parameter override roles/etcd
etcd_cluster: etcd # mark etcd cluster name etcd
etcd_safeguard: false # safeguard against purging
etcd_clean: true # purge etcd during init process
pg-meta: # 3 instance postgres cluster `pg-meta`
hosts:
10.10.10.10: { pg_seq: 1, pg_role: primary }
10.10.10.11: { pg_seq: 2, pg_role: replica }
10.10.10.12: { pg_seq: 3, pg_role: replica , pg_offline_query: true }
vars:
pg_cluster: pg-meta
pg_conf: crit.yml
pg_users:
- { 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 }
pg_databases:
- { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [ pigsty ] ,extensions: [ { name: vector } ] }
pg_services:
- { name: standby , ip: "*" ,port: 5435 , dest: default ,selector: "[]" , backup: "[? pg_role == `primary`]" }
pg_listen: '${ip},${vip},${lo}'
pg_vip_enabled: true
pg_vip_address: 10.10.10.2/24
pg_vip_interface: eth1
# OPTIONAL delayed cluster for pg-meta
pg-meta-delay: # delayed instance for pg-meta (1 hour ago)
hosts: { 10.10.10.13: { pg_seq: 1, pg_role: primary, pg_upstream: 10.10.10.10, pg_delay: 1h } }
vars: { pg_cluster: pg-meta-delay }
####################################################################
# Parameters #
####################################################################
vars: # global variables
version: v3.2.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default|china|europe
node_tune: oltp # node tuning specs: oltp,olap,tiny,crit
pg_conf: oltp.yml # pgsql tuning specs: {oltp,olap,tiny,crit}.yml
patroni_ssl_enabled: true # secure patroni RestAPI communications with SSL?
pgbouncer_sslmode: require # pgbouncer client ssl mode: disable|allow|prefer|require|verify-ca|verify-full, disable by default
pg_default_service_dest: postgres # default service destination to postgres instead of pgbouncer
pgbackrest_method: minio # pgbackrest repo method: local,minio,[user-defined...]
#----------------------------------#
# Credentials
#----------------------------------#
#grafana_admin_username: admin
grafana_admin_password: You.Have2Use-A_VeryStrongPassword
#pg_admin_username: dbuser_dba
pg_admin_password: PessWorb.Should8eStrong-eNough
#pg_monitor_username: dbuser_monitor
pg_monitor_password: MekeSuerYour.PassWordI5secured
#pg_replication_username: replicator
pg_replication_password: doNotUseThis-PasswordFor.AnythingElse
#patroni_username: postgres
patroni_password: don.t-forget-to-change-thEs3-password
#haproxy_admin_username: admin
haproxy_admin_password: GneratePasswordWith-pwgen-s-16-1
#----------------------------------#
# MinIO Related Options
#----------------------------------#
minio_users: # and configure `pgbackrest_repo` & `minio_users` accordingly
- { access_key: dba , secret_key: S3User.DBA.Strong.Password, policy: consoleAdmin }
- { access_key: pgbackrest , secret_key: Min10.bAckup ,policy: readwrite }
pgbackrest_repo: # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
local: # default pgbackrest repo with local posix fs
path: /pg/backup # local backup directory, `/pg/backup` by default
retention_full_type: count # retention full backups by count
retention_full: 2 # keep 2, at most 3 full backup when using local fs repo
minio: # optional minio repo for pgbackrest
s3_key: pgbackrest # <-------- CHANGE THIS, SAME AS `minio_users` access_key
s3_key_secret: Min10.bAckup # <-------- CHANGE THIS, SAME AS `minio_users` secret_key
cipher_pass: 'pgBR.${pg_cluster}' # <-------- CHANGE THIS, you can use cluster name as part of password
type: s3 # minio is s3-compatible, so s3 is used
s3_endpoint: sss.pigsty # minio endpoint domain name, `sss.pigsty` by default
s3_region: us-east-1 # minio region, us-east-1 by default, useless for minio
s3_bucket: pgsql # minio bucket name, `pgsql` by default
s3_uri_style: path # use path style uri for minio rather than host style
path: /pgbackrest # minio backup path, default is `/pgbackrest`
storage_port: 9000 # minio port, 9000 by default
storage_ca_file: /etc/pki/ca.crt # minio ca file path, `/etc/pki/ca.crt` by default
bundle: y # bundle small files into a single file
cipher_type: aes-256-cbc # enable AES encryption for remote backup repo
retention_full_type: time # retention full backup by time on minio repo
retention_full: 14 # keep full backup for last 14 days
#----------------------------------#
# Access Control
#----------------------------------#
# add passwordcheck extension to enforce strong password policy
pg_libs: '$libdir/passwordcheck, pg_stat_statements, auto_explain'
pg_extensions:
- passwordcheck, supautils, pgsodium, pg_vault, pg_session_jwt, anonymizer, pgsmcrypto, pgauditlogtofile, pgaudit #, pgaudit17, pgaudit16, pgaudit15, pgaudit14
- pg_auth_mon, credcheck, pgcryptokey, pg_jobmon, logerrors, login_hook, set_user, pgextwlist, pg_auditor, sslutils, noset #pg_tde #pg_snakeoil
pg_default_roles: # default roles and users in postgres cluster
- { name: dbrole_readonly ,login: false ,comment: role for global read-only access }
- { name: dbrole_offline ,login: false ,comment: role for restricted read-only access }
- { name: dbrole_readwrite ,login: false ,roles: [ dbrole_readonly ] ,comment: role for global read-write access }
- { name: dbrole_admin ,login: false ,roles: [ pg_monitor, dbrole_readwrite ] ,comment: role for object creation }
- { 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 }
pg_default_hba_rules: # postgres host-based auth rules by default
- { user: '${dbsu}' ,db: all ,addr: local ,auth: ident ,title: 'dbsu access via local os user ident' }
- { user: '${dbsu}' ,db: replication ,addr: local ,auth: ident ,title: 'dbsu replication from local os ident' }
- { user: '${repl}' ,db: replication ,addr: localhost ,auth: ssl ,title: 'replicator replication from localhost' }
- { user: '${repl}' ,db: replication ,addr: intra ,auth: ssl ,title: 'replicator replication from intranet' }
- { user: '${repl}' ,db: postgres ,addr: intra ,auth: ssl ,title: 'replicator postgres db from intranet' }
- { user: '${monitor}' ,db: all ,addr: localhost ,auth: pwd ,title: 'monitor from localhost with password' }
- { user: '${monitor}' ,db: all ,addr: infra ,auth: ssl ,title: 'monitor from infra host with password' }
- { user: '${admin}' ,db: all ,addr: infra ,auth: ssl ,title: 'admin @ infra nodes with pwd & ssl' }
- { user: '${admin}' ,db: all ,addr: world ,auth: cert ,title: 'admin @ everywhere with ssl & cert' }
- { user: '+dbrole_readonly',db: all ,addr: localhost ,auth: ssl ,title: 'pgbouncer read/write via local socket' }
- { user: '+dbrole_readonly',db: all ,addr: intra ,auth: ssl ,title: 'read/write biz user via password' }
- { user: '+dbrole_offline' ,db: all ,addr: intra ,auth: ssl ,title: 'allow etl offline tasks from intranet' }
pgb_default_hba_rules: # pgbouncer host-based authentication rules
- { user: '${dbsu}' ,db: pgbouncer ,addr: local ,auth: peer ,title: 'dbsu local admin access with os ident' }
- { user: 'all' ,db: all ,addr: localhost ,auth: pwd ,title: 'allow all user local access with pwd' }
- { user: '${monitor}' ,db: pgbouncer ,addr: intra ,auth: ssl ,title: 'monitor access via intranet with pwd' }
- { user: '${monitor}' ,db: all ,addr: world ,auth: deny ,title: 'reject all other monitor access addr' }
- { user: '${admin}' ,db: all ,addr: intra ,auth: ssl ,title: 'admin access via intranet with pwd' }
- { user: '${admin}' ,db: all ,addr: world ,auth: deny ,title: 'reject all other admin access addr' }
- { user: 'all' ,db: all ,addr: intra ,auth: ssl ,title: 'allow all user intra access with pwd' }
#----------------------------------#
# Repo, Node, Packages
#----------------------------------#
# if you wish to customize your own repo, change these settings:
repo_modules: infra,node,pgsql # install upstream repo during repo bootstrap
repo_remove: true # remove existing repo on admin node during repo bootstrap
node_repo_modules: local # install the local module in repo_upstream for all nodes
node_repo_remove: true # remove existing node repo for node managed by pigsty
repo_packages: [ # default packages to be downloaded
node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-common #,docker
]
repo_extra_packages: [ # default postgres packages to be downloaded
pg17-main, pg17-sec # replace with the following line if you want all extensions
#pg17-core ,pg17-time ,pg17-gis ,pg17-rag ,pg17-fts ,pg17-olap ,pg17-feat ,pg17-lang ,pg17-type ,pg17-func ,pg17-admin ,pg17-stat ,pg17-sec ,pg17-fdw ,pg17-sim ,pg17-etl
]
...