summaryrefslogtreecommitdiff
path: root/hosts/surtr/postgresql
diff options
context:
space:
mode:
authorGregor Kleen <gkleen@yggdrasil.li>2022-11-21 18:58:56 +0100
committerGregor Kleen <gkleen@yggdrasil.li>2022-11-21 18:58:56 +0100
commitba86ae504d8ea9796e43c1b061aa070761cd1323 (patch)
tree4a675b01270402b20f6e442f9d87ce4dc3bb600b /hosts/surtr/postgresql
parent3705bb3ef68b56892ec840c23683d5728136b5fa (diff)
downloadnixos-ba86ae504d8ea9796e43c1b061aa070761cd1323.tar
nixos-ba86ae504d8ea9796e43c1b061aa070761cd1323.tar.gz
nixos-ba86ae504d8ea9796e43c1b061aa070761cd1323.tar.bz2
nixos-ba86ae504d8ea9796e43c1b061aa070761cd1323.tar.xz
nixos-ba86ae504d8ea9796e43c1b061aa070761cd1323.zip
pgbackrest
Diffstat (limited to 'hosts/surtr/postgresql')
-rw-r--r--hosts/surtr/postgresql/default.nix239
-rw-r--r--hosts/surtr/postgresql/pgbackrest.crt13
-rw-r--r--hosts/surtr/postgresql/pgbackrest.key26
3 files changed, 278 insertions, 0 deletions
diff --git a/hosts/surtr/postgresql/default.nix b/hosts/surtr/postgresql/default.nix
new file mode 100644
index 00000000..9cf494ae
--- /dev/null
+++ b/hosts/surtr/postgresql/default.nix
@@ -0,0 +1,239 @@
1{ pkgs, sources, config, flake, ... }:
2let
3 versioning = sources.psql-versioning.src;
4in {
5 config = {
6 services.postgresql = {
7 enable = true;
8 package = pkgs.postgresql_14;
9 };
10
11 services.pgbackrest = {
12 enable = true;
13 settings = {
14 "surtr" = {
15 pg1-path = config.services.postgresql.dataDir;
16
17 repo1-path = "/var/lib/pgbackrest";
18 repo1-retention-full-type = "time";
19 repo1-retention-full = 7;
20 repo1-retention-archive = 2;
21
22 repo2-host-type = "tls";
23 repo2-host = "pgbackrest.vidhar.yggdrasil";
24 repo2-host-ca-file = toString ../../vidhar/pgbackrest/ca/ca.crt;
25 repo2-host-cert-file = toString ./pgbackrest.crt;
26 repo2-host-key-file = config.sops.secrets."pgbackrest.key".path;
27 repo2-retention-full-type = "time";
28 repo2-retention-full = 14;
29 repo2-retention-archive = 7;
30 };
31
32 "global" = {
33 compress-type = "zst";
34 compress-level = 9;
35
36 archive-async = true;
37 spool-path = "/var/spool/pgbackrest";
38 };
39
40 "global:server" = {
41 tls-server-address = "2a03:4000:52:ada:1::";
42 tls-server-ca-file = toString ../../vidhar/pgbackrest/ca/ca.crt;
43 tls-server-cert-file = toString ./pgbackrest.crt;
44 tls-server-key-file = config.sops.secrets."pgbackrest.key".path;
45 tls-server-auth = ["vidhar.yggdrasil=surtr"];
46 };
47
48 "global:archive-push" = {
49 process-max = 2;
50 };
51 "global:archive-get" = {
52 process-max = 2;
53 };
54 };
55
56 tlsServer.enable = true;
57
58 backups."surtr-daily" = {
59 stanza = "surtr";
60 repo = "1";
61 timerConfig.OnCalendar = "daily";
62 };
63 };
64
65 sops.secrets."pgbackrest.key" = {
66 format = "binary";
67 sopsFile = ./pgbackrest.key;
68 owner = "postgres";
69 group = "postgres";
70 mode = "0400";
71 };
72
73 systemd.tmpfiles.rules = [
74 "d /var/lib/pgbackrest 0750 postgres postgres - -"
75 "d /var/spool/pgbackrest 0750 postgres postgres - -"
76 ];
77
78 systemd.services.migrate-postgresql = {
79 after = [ "postgresql.service" ];
80 bindsTo = [ "postgresql.service" ];
81 wantedBy = [ "postgresql.service" ];
82
83 serviceConfig = {
84 Type = "oneshot";
85 inherit (config.systemd.services.postgresql.serviceConfig) User Group;
86 RemainAfterExit = true;
87 };
88
89 path = [ config.services.postgresql.package ];
90 script = ''
91 psql postgres postgres -eXf ${pkgs.writeText "schema.sql" ''
92 CREATE DATABASE "matrix-synapse" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
93 CREATE DATABASE "email" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
94 CREATE DATABASE "etebase" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
95 ''}
96
97 psql matrix-synapse postgres -eXf ${pkgs.writeText "matrix-synapse.sql" ''
98 \i ${versioning + "/install.versioning.sql"}
99
100 BEGIN;
101 SELECT _v.register_patch('000-matrix-users', null, null);
102
103 CREATE USER "matrix-synapse";
104 GRANT ALL PRIVILEGES ON DATABASE "matrix-synapse" TO "matrix-synapse";
105 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "matrix-synapse";
106 COMMIT;
107 ''}
108
109 psql email postgres -eXf ${pkgs.writeText "email.sql" ''
110 \i ${versioning + "/install.versioning.sql"}
111
112 BEGIN;
113 SELECT _v.register_patch('000-users', null, null);
114
115 CREATE USER "postfix";
116 GRANT CONNECT ON DATABASE "email" TO "postfix";
117 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix";
118 CREATE USER "dovecot2";
119 GRANT CONNECT ON DATABASE "email" TO "dovecot2";
120 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "dovecot2";
121 COMMIT;
122
123 BEGIN;
124 SELECT _v.register_patch('001-spm', null, null);
125
126 CREATE USER "spm";
127 GRANT CONNECT ON DATABASE "email" TO "spm";
128 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to "spm";
129 COMMIT;
130
131 BEGIN;
132 SELECT _v.register_patch('000-base', null, null);
133
134 CREATE TABLE mailbox (
135 id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
136 mailbox text NOT NULL CONSTRAINT mailbox_non_empty CHECK (mailbox <> '''),
137 quota_bytes bigint CONSTRAINT quota_bytes_positive CHECK (CASE WHEN quota_bytes IS NOT NULL THEN quota_bytes > 0 ELSE true END),
138 CONSTRAINT mailbox_unique UNIQUE (mailbox)
139 );
140 CREATE TABLE mailbox_mapping (
141 id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
142 local text CONSTRAINT local_non_empty CHECK (local IS DISTINCT FROM '''),
143 domain text NOT NULL CONSTRAINT domain_non_empty CHECK (domain <> '''),
144 mailbox uuid REFERENCES mailbox(id),
145 CONSTRAINT local_domain_unique UNIQUE (local, domain)
146 );
147 CREATE UNIQUE INDEX domain_unique ON mailbox_mapping (domain) WHERE local IS NULL;
148
149 CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping;
150 CREATE VIEW virtual_mailbox_mapping (mailbox, lookup) AS SELECT mailbox.mailbox as mailbox, (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping INNER JOIN mailbox on mailbox.id = mailbox_mapping.mailbox;
151
152 CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox;
153 COMMIT;
154
155 BEGIN;
156 SELECT _v.register_patch('001-lmtp-mapping', ARRAY['000-base'], null);
157
158 CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox.mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule, local, domain FROM mailbox INNER JOIN mailbox_mapping ON mailbox.id = mailbox_mapping.mailbox;
159 COMMIT;
160
161 BEGIN;
162 SELECT _v.register_patch('002-citext', ARRAY['000-base'], null);
163
164 DROP VIEW virtual_mailbox_domain;
165 DROP VIEW virtual_mailbox_mapping;
166 DROP VIEW imap_user;
167 DROP VIEW lmtp_mapping;
168
169 CREATE EXTENSION citext;
170
171 ALTER TABLE mailbox ALTER mailbox TYPE citext;
172 ALTER TABLE mailbox_mapping ALTER local TYPE citext;
173 ALTER TABLE mailbox_mapping ALTER domain TYPE citext;
174
175 CREATE VIEW mailbox_quota_rule (id, mailbox, quota_rule) AS SELECT id, mailbox, (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox;
176
177 CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping;
178 CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping;
179 CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", quota_rule FROM mailbox_quota_rule;
180 CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox;
181 COMMIT;
182
183 BEGIN;
184 SELECT _v.register_patch('003-extensions', ARRAY['000-base', '002-citext'], null);
185
186 ALTER TABLE mailbox_mapping ADD COLUMN extension citext CHECK (CASE WHEN extension IS NOT NULL THEN extension NOT LIKE '%+%' ELSE true END);
187
188 DROP VIEW virtual_mailbox_mapping;
189 DROP VIEW lmtp_mapping;
190
191 CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || (CASE WHEN extension IS NULL THEN ''' ELSE '+' || extension END) || '@' || domain AS lookup FROM mailbox_mapping WHERE mailbox IS NOT NULL;
192 CREATE VIEW virtual_mailbox_access (lookup, action) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || (CASE WHEN extension IS NULL THEN ''' ELSE '+' || extension END) || '@' || domain AS lookup, CASE WHEN mailbox IS NULL THEN 'REJECT' ELSE 'DUNNO' END AS action FROM mailbox_mapping;
193 CREATE VIEW lmtp_mapping ("user", quota_rule, local, extension, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, extension, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox;
194 COMMIT;
195
196 BEGIN;
197 SELECT _v.register_patch('004-cascade', ARRAY['000-base'], null);
198
199 ALTER TABLE mailbox_mapping DROP CONSTRAINT mailbox_mapping_mailbox_fkey;
200 ALTER TABLE mailbox_mapping ADD CONSTRAINT mailbox_mapping_mailbox_fkey FOREIGN KEY (mailbox) REFERENCES mailbox(id) ON DELETE CASCADE ON UPDATE RESTRICT;
201 COMMIT;
202
203 BEGIN;
204 SELECT _v.register_patch('005-spm', ARRAY['000-base', '002-citext', '003-extensions'], null);
205
206 GRANT INSERT ON "mailbox_mapping" TO "spm";
207 COMMIT;
208
209 BEGIN;
210 SELECT _v.register_patch('006-spm-mailbox', ARRAY['000-base'], null);
211
212 GRANT SELECT ON ALL TABLES IN SCHEMA public TO "spm";
213 COMMIT;
214
215 BEGIN;
216 SELECT _v.register_patch('007-ccert-sender-policy', ARRAY['000-base'], null);
217
218 CREATE USER "postfix-ccert-sender-policy";
219 GRANT CONNECT ON DATABASE "email" TO "postfix-ccert-sender-policy";
220 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix-ccert-sender-policy";
221 GRANT SELECT ON ALL TABLES IN SCHEMA public TO "postfix-ccert-sender-policy";
222 COMMIT;
223 ''}
224
225 psql etebase postgres -eXf ${pkgs.writeText "etebase.sql" ''
226 \i ${versioning + "/install.versioning.sql"}
227
228 BEGIN;
229 SELECT _v.register_patch('000-user', null, null);
230
231 CREATE USER "etebase";
232 GRANT ALL PRIVILEGES ON DATABASE "etebase" TO "etebase";
233 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "etebase";
234 COMMIT;
235 ''}
236 '';
237 };
238 };
239}
diff --git a/hosts/surtr/postgresql/pgbackrest.crt b/hosts/surtr/postgresql/pgbackrest.crt
new file mode 100644
index 00000000..b4dc4d97
--- /dev/null
+++ b/hosts/surtr/postgresql/pgbackrest.crt
@@ -0,0 +1,13 @@
1-----BEGIN CERTIFICATE-----
2MIIB7zCCAW+gAwIBAgIPQAAAAGN7p/Q5SZ7JU43JMAUGAytlcTAfMR0wGwYDVQQD
3DBRwZ2JhY2tyZXN0LnlnZ2RyYXNpbDAeFw0yMjExMjExNjI2MTFaFw0zMjExMjEx
4NjMxMTFaMBoxGDAWBgNVBAMMD3N1cnRyLnlnZ2RyYXNpbDAqMAUGAytlcAMhABIl
5okEGkov33jgsrF0QA4CKQILbIWkZ2tn+UUhXxxyDo4HGMIHDMB8GA1UdIwQYMBaA
6FO+/yfEkwcLr+vNPIsyCW86UwJ3aMB0GA1UdDgQWBBQnVeShLYsqF35OmmzLJEV5
7dfenhjAOBgNVHQ8BAf8EBAMCBeAwDAYDVR0TAQH/BAIwADAdBgNVHSUEFjAUBggr
8BgEFBQcDAgYIKwYBBQUHAwEwRAYDVR0RBD0wO4IdcGdiYWNrcmVzdC5zdXJ0ci55
9Z2dkcmFzaWwubGmCGnBnYmFja3Jlc3Quc3VydHIueWdnZHJhc2lsMAUGAytlcQNz
10AJqqMDWN1Ym5XANRKWcCh09j0Rej3V64XZlOOP7qFF9Gh4QJXeCvDMjX4LOeRUmi
11lB8iosdRN9MSANI4kfwYBnzgn3BNMrvMI4faEOuVnd6X2ulsJdNbJNQzB3hRVsNf
12b+QNBV+PpTUgR4k9e1XWX+wwAA==
13-----END CERTIFICATE-----
diff --git a/hosts/surtr/postgresql/pgbackrest.key b/hosts/surtr/postgresql/pgbackrest.key
new file mode 100644
index 00000000..bc2af12d
--- /dev/null
+++ b/hosts/surtr/postgresql/pgbackrest.key
@@ -0,0 +1,26 @@
1{
2 "data": "ENC[AES256_GCM,data:Bg4fIAqIGLF1P1P583vQnHhjzrD8fdnS5tA/7SuSdBRJjVaRzB0bieEv+2i9WxgaStG9TTUSmClCVUsbR5gy7MoV6Br4AL17Y++R6wPpJbQJvtMMDJB2xg+THU/Ex61dendcWqPYh73Wn4U9uBE/wC1eVrShXRM=,iv:YG/foZwVcrzi6hdk7Vk0sYZ92LMbmiKg1SbAgPaeUNM=,tag:lAcoxUfQXB4vvc6XnIcA/g==,type:str]",
3 "sops": {
4 "kms": null,
5 "gcp_kms": null,
6 "azure_kv": null,
7 "hc_vault": null,
8 "age": null,
9 "lastmodified": "2022-11-21T14:30:27Z",
10 "mac": "ENC[AES256_GCM,data:Dsfc1XrGl4abSnDqRl/IwC11bVy+kHz1RaI0V/nkkaJ3fM/qTXPVc5mMoWCiPn1nz5BTABQRSnrf79qHc0wpZ1WUpn07yOf7JejJ/T/bUC7D8BuoVdWRh1og+NzWCEIwaGXg0Eo04yli+GXisdM3YVM9g3BrxYrSInjnNZFyB+Q=,iv:T5QprwIhB8ZWwmmfWVtxkXqbMB1onW+wX7GPIFMn+z0=,tag:zMi77nMepajhg2Djgz8rBA==,type:str]",
11 "pgp": [
12 {
13 "created_at": "2022-11-21T14:30:27Z",
14 "enc": "-----BEGIN PGP MESSAGE-----\n\nhF4DXxoViZlp6dISAQdAi3pfg9DA+1v5r5sEijbkdwmOopWh05IuhRJxuy1btyAw\nuo0iV7VpngK8tFcBHnmhx3QsxIJo/gU+xrOwczW3RoSGrWo9tV2FantQPRp6f1aS\n0lwBEJSxmTApD/YDu3M6WhxN49/ZVEXG+KQ/mOdoBo0ITGKa6No0btMolzJ0bCJU\n+/avVdlDdZzfXo9XP0iJUoqh+1yMn+XdnD5deGac8a/QGvXZkxsYQ8KpK9sONA==\n=QyKr\n-----END PGP MESSAGE-----\n",
15 "fp": "30D3453B8CD02FE2A3E7C78C0FB536FB87AE8F51"
16 },
17 {
18 "created_at": "2022-11-21T14:30:27Z",
19 "enc": "-----BEGIN PGP MESSAGE-----\n\nhF4DyFKFNkTVG5oSAQdAYU2U/anEJ8JSiG7NBppmsFeogXN3ynOEdq2tHXf+mUww\nIS7kW1pqcGMjnf7RQNuL91Wek5GEk4T498IFadiYDImAfIdS5jeX2w7UvxWLX5OZ\n0lwBlnxOwkYRWZzAhB6jHthmk2zEc+0JKuFolXhrwXqsFwFGoLTO9fctJrV7ry0u\naM9DqXru+/cEUZJDSq5GYDQaxTjyaFMVwLVdfxrtFwc8YMlqU8vVoWTqLaUVYA==\n=Tg80\n-----END PGP MESSAGE-----\n",
20 "fp": "7ED22F4AA7BB55728B643DC5471B7D88E4EF66F8"
21 }
22 ],
23 "unencrypted_suffix": "_unencrypted",
24 "version": "3.7.3"
25 }
26} \ No newline at end of file