shithub Public

Watch 1 Fork 0 Star 0
YAML · 5846 bytes Raw Blame History
1 ---
2 # SPDX-License-Identifier: AGPL-3.0-or-later
3 #
4 # Postgres 16 self-hosted, data dir on the block volume. Tunes for
5 # WAL archiving + pg_stat_statements (S36 perf, S37 backups). The
6 # `shithub_hook` role is provisioned with the minimum-needed grants
7 # (S14 deferral, S37 amendment).
8
9 - name: Postgres — install pg16 + contrib (pg_stat_statements lives in contrib)
10 apt:
11 name:
12 - postgresql-16
13 - postgresql-contrib-16
14 - postgresql-client-16
15 state: present
16 update_cache: yes
17
18 - name: Stop postgres before relocating data dir
19 systemd: { name: postgresql, state: stopped }
20
21 - name: pgdata — create on block volume
22 file:
23 path: "{{ shithub_data_root }}/pgdata"
24 state: directory
25 owner: postgres
26 group: postgres
27 mode: "0700"
28
29 - name: pgdata — initdb on block volume if empty
30 shell: |
31 if [ ! -f "{{ shithub_data_root }}/pgdata/PG_VERSION" ]; then
32 sudo -u postgres /usr/lib/postgresql/16/bin/initdb \
33 -D "{{ shithub_data_root }}/pgdata"
34 fi
35 args:
36 creates: "{{ shithub_data_root }}/pgdata/PG_VERSION"
37
38 - name: postgresql.conf — render
39 template:
40 src: postgresql.conf.j2
41 dest: "{{ shithub_data_root }}/pgdata/postgresql.conf"
42 owner: postgres
43 group: postgres
44 mode: "0600"
45 notify: restart postgres
46
47 - name: pg_hba.conf — render
48 template:
49 src: pg_hba.conf.j2
50 dest: "{{ shithub_data_root }}/pgdata/pg_hba.conf"
51 owner: postgres
52 group: postgres
53 mode: "0600"
54 notify: restart postgres
55
56 - name: archive_command — install
57 copy:
58 src: "{{ playbook_dir }}/../postgres/archive_command.sh"
59 dest: /usr/local/bin/shithub-pg-archive
60 mode: "0755"
61 owner: root
62 group: root
63
64 # Drop-in conf.d include for archiving. The earlier `postgresql.conf —
65 # render` task above writes to /data/pgdata/postgresql.conf, but
66 # pg_ctlcluster (Debian's wrapper) ignores PGDATA= and uses
67 # /etc/postgresql/16/main/postgresql.conf as the live config — so the
68 # templated /data file is currently ignored at runtime. Until the
69 # data-dir-on-block-volume migration is sorted, the supported way to
70 # override settings on Debian is a conf.d drop-in.
71 - name: postgres conf.d — ensure dir
72 file:
73 path: /etc/postgresql/16/main/conf.d
74 state: directory
75 owner: postgres
76 group: postgres
77 mode: "0755"
78
79 - name: postgres conf.d — install shithub archive overrides
80 template:
81 src: 99_shithub_archive.conf
82 dest: /etc/postgresql/16/main/conf.d/99_shithub_archive.conf
83 owner: postgres
84 group: postgres
85 mode: "0644"
86 notify: restart postgres
87
88 - name: WAL archive — install verifier
89 copy:
90 src: "{{ playbook_dir }}/../postgres/verify-wal-archive.sh"
91 dest: /usr/local/bin/shithub-verify-wal-archive
92 mode: "0755"
93 owner: root
94 group: root
95
96 # Hourly health check. Silent on success; loud on the journal under
97 # `journalctl -t shithub-wal-archive` if archiving has stopped, the
98 # failed_count climbed, or segments aren't landing in Spaces. Same
99 # observability shape as the AIDE check.
100 - name: cron — hourly WAL-archive verifier
101 cron:
102 name: shithub-verify-wal-archive
103 job: /usr/local/bin/shithub-verify-wal-archive
104 minute: "47"
105
106 - name: systemd unit override — ensure drop-in dir exists
107 file:
108 path: /etc/systemd/system/postgresql@16-main.service.d
109 state: directory
110 mode: "0755"
111
112 - name: systemd unit override — pin DATA_DIR to block volume
113 copy:
114 dest: /etc/systemd/system/postgresql@16-main.service.d/datadir.conf
115 content: |
116 [Service]
117 Environment=PGDATA={{ shithub_data_root }}/pgdata
118 mode: "0644"
119 notify:
120 - daemon-reload
121 - restart postgres
122
123 - name: Start + enable postgres
124 systemd: { name: postgresql, state: started, enabled: yes }
125
126 - name: Roles — create shithub app role
127 community.postgresql.postgresql_user:
128 name: shithub
129 password: "{{ shithub_db_password }}"
130 role_attr_flags: LOGIN
131 state: present
132 become_user: postgres
133
134 - name: Roles — create shithub_hook (minimum-needed grants — S14 deferral)
135 community.postgresql.postgresql_user:
136 name: shithub_hook
137 password: "{{ shithub_hook_db_password }}"
138 role_attr_flags: LOGIN
139 state: present
140 become_user: postgres
141
142 - name: Database — create shithub
143 community.postgresql.postgresql_db:
144 name: shithub
145 owner: shithub
146 encoding: UTF8
147 state: present
148 become_user: postgres
149
150 - name: Extensions — pg_stat_statements + citext
151 community.postgresql.postgresql_ext:
152 name: "{{ item }}"
153 db: shithub
154 loop:
155 - pg_stat_statements
156 - citext
157 become_user: postgres
158
159 # Hook-role grant recipe: matches what cmd/shithubd/hook.go writes
160 # (push_events, jobs, domain_events, audit) and reads (users, repos,
161 # repo_collaborators). Update this BOTH HERE AND in the audit
162 # checklist if hook.go grows new touched tables.
163 - name: Hook role — grant exact write surface
164 community.postgresql.postgresql_privs:
165 db: shithub
166 role: shithub_hook
167 type: table
168 objs: "push_events,jobs,domain_events,auth_audit_log"
169 privs: INSERT
170 become_user: postgres
171
172 - name: Hook role — grant exact read surface
173 community.postgresql.postgresql_privs:
174 db: shithub
175 role: shithub_hook
176 type: table
177 objs: "users,repos,repo_collaborators,branch_protection_rules"
178 privs: SELECT
179 become_user: postgres
180
181 - name: Hook role — grant USAGE on schema (otherwise grants are inaccessible)
182 community.postgresql.postgresql_privs:
183 db: shithub
184 role: shithub_hook
185 type: schema
186 objs: public
187 privs: USAGE
188 become_user: postgres
189
190 - name: Hook role — grant sequence USAGE so INSERTs can claim ids
191 community.postgresql.postgresql_privs:
192 db: shithub
193 role: shithub_hook
194 type: sequence
195 objs: ALL_IN_SCHEMA
196 schema: public
197 privs: USAGE
198 become_user: postgres
199