shithub Public

Watch 1 Fork 0 Star 0
YAML · 4182 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 - name: systemd unit override — pin DATA_DIR to block volume
65 copy:
66 dest: /etc/systemd/system/postgresql@16-main.service.d/datadir.conf
67 content: |
68 [Service]
69 Environment=PGDATA={{ shithub_data_root }}/pgdata
70 mode: "0644"
71 notify:
72 - daemon-reload
73 - restart postgres
74
75 - name: Start + enable postgres
76 systemd: { name: postgresql, state: started, enabled: yes }
77
78 - name: Roles — create shithub app role
79 community.postgresql.postgresql_user:
80 name: shithub
81 password: "{{ shithub_db_password }}"
82 role_attr_flags: LOGIN
83 state: present
84 become_user: postgres
85
86 - name: Roles — create shithub_hook (minimum-needed grants — S14 deferral)
87 community.postgresql.postgresql_user:
88 name: shithub_hook
89 password: "{{ shithub_hook_db_password }}"
90 role_attr_flags: LOGIN
91 state: present
92 become_user: postgres
93
94 - name: Database — create shithub
95 community.postgresql.postgresql_db:
96 name: shithub
97 owner: shithub
98 encoding: UTF8
99 state: present
100 become_user: postgres
101
102 - name: Extensions — pg_stat_statements + citext
103 community.postgresql.postgresql_ext:
104 name: "{{ item }}"
105 db: shithub
106 loop:
107 - pg_stat_statements
108 - citext
109 become_user: postgres
110
111 # Hook-role grant recipe: matches what cmd/shithubd/hook.go writes
112 # (push_events, jobs, domain_events, audit) and reads (users, repos,
113 # repo_collaborators). Update this BOTH HERE AND in the audit
114 # checklist if hook.go grows new touched tables.
115 - name: Hook role — grant exact write surface
116 community.postgresql.postgresql_privs:
117 db: shithub
118 role: shithub_hook
119 type: table
120 objs: "push_events,jobs,domain_events,auth_audit_log"
121 privs: INSERT
122 become_user: postgres
123
124 - name: Hook role — grant exact read surface
125 community.postgresql.postgresql_privs:
126 db: shithub
127 role: shithub_hook
128 type: table
129 objs: "users,repos,repo_collaborators,branch_protection_rules"
130 privs: SELECT
131 become_user: postgres
132
133 - name: Hook role — grant USAGE on schema (otherwise grants are inaccessible)
134 community.postgresql.postgresql_privs:
135 db: shithub
136 role: shithub_hook
137 type: schema
138 objs: public
139 privs: USAGE
140 become_user: postgres
141
142 - name: Hook role — grant sequence USAGE so INSERTs can claim ids
143 community.postgresql.postgresql_privs:
144 db: shithub
145 role: shithub_hook
146 type: sequence
147 objs: ALL_IN_SCHEMA
148 schema: public
149 privs: USAGE
150 become_user: postgres
151