aboutsummaryrefslogtreecommitdiff
path: root/systems/eldiron/databases/postgresql.nix
diff options
context:
space:
mode:
Diffstat (limited to 'systems/eldiron/databases/postgresql.nix')
-rw-r--r--systems/eldiron/databases/postgresql.nix236
1 files changed, 236 insertions, 0 deletions
diff --git a/systems/eldiron/databases/postgresql.nix b/systems/eldiron/databases/postgresql.nix
new file mode 100644
index 0000000..721059a
--- /dev/null
+++ b/systems/eldiron/databases/postgresql.nix
@@ -0,0 +1,236 @@
1{ lib, pkgs, config, ... }:
2let
3 cfg = config.myServices.databases.postgresql;
4in {
5 options.myServices.databases = {
6 postgresql = {
7 enable = lib.mkOption {
8 default = false;
9 example = true;
10 description = "Whether to enable postgresql database";
11 type = lib.types.bool;
12 };
13 package = lib.mkOption {
14 type = lib.types.package;
15 default = pkgs.postgresql;
16 description = ''
17 Postgresql package to use.
18 '';
19 };
20 ldapConfig = lib.mkOption {
21 description = "LDAP configuration to allow PAM identification via LDAP";
22 type = lib.types.submodule {
23 options = {
24 host = lib.mkOption { type = lib.types.str; };
25 base = lib.mkOption { type = lib.types.str; };
26 dn = lib.mkOption { type = lib.types.str; };
27 password = lib.mkOption { type = lib.types.str; };
28 filter = lib.mkOption { type = lib.types.str; };
29 };
30 };
31 };
32 replicationLdapConfig = lib.mkOption {
33 description = "LDAP configuration to allow replication";
34 type = lib.types.submodule {
35 options = {
36 host = lib.mkOption { type = lib.types.str; };
37 base = lib.mkOption { type = lib.types.str; };
38 dn = lib.mkOption { type = lib.types.str; };
39 password = lib.mkOption { type = lib.types.str; };
40 };
41 };
42 };
43 authorizedHosts = lib.mkOption {
44 default = {};
45 description = "Hosts to allow connections from";
46 type = lib.types.attrsOf (lib.types.listOf (lib.types.submodule {
47 options = {
48 method = lib.mkOption {
49 default = "md5";
50 type = lib.types.str;
51 };
52 username = lib.mkOption {
53 default = "all";
54 type = lib.types.str;
55 };
56 database = lib.mkOption {
57 default = "all";
58 type = lib.types.str;
59 };
60 ip4 = lib.mkOption {
61 default = [];
62 type = lib.types.listOf lib.types.str;
63 };
64 ip6 = lib.mkOption {
65 default = [];
66 type = lib.types.listOf lib.types.str;
67 };
68 };
69 }));
70 };
71 replicationHosts = lib.mkOption {
72 default = {};
73 description = "Hosts to allow replication from";
74 type = lib.types.attrsOf (lib.types.submodule {
75 options = {
76 ip4 = lib.mkOption {
77 type = lib.types.listOf lib.types.str;
78 };
79 ip6 = lib.mkOption {
80 type = lib.types.listOf lib.types.str;
81 };
82 };
83 });
84 };
85 # Output variables
86 socketsDir = lib.mkOption {
87 type = lib.types.path;
88 default = "/run/postgresql";
89 description = ''
90 The directory where Postgresql puts sockets.
91 '';
92 readOnly = true;
93 };
94 };
95 };
96
97 config = lib.mkIf cfg.enable {
98 networking.firewall.allowedTCPPorts = [ 5432 ];
99
100 security.acme.certs."postgresql" = {
101 group = "postgres";
102 domain = "db-1.immae.eu";
103 postRun = ''
104 systemctl reload postgresql.service
105 '';
106 };
107
108 systemd.services.postgresql.serviceConfig = {
109 SupplementaryGroups = "keys";
110 ExecStartPre = [ ("+" + (pkgs.writeShellScript "postgresql-fix-cert" ''
111 # postgresql complains:
112 # private key file "${config.security.acme.certs.postgresql.directory}/key.pem" must be owned by the database user or root
113 cp -f "${config.security.acme.certs.postgresql.directory}/key.pem" "${config.services.postgresql.dataDir}/key.pem"
114 chown postgres:postgres "${config.services.postgresql.dataDir}/key.pem"
115 chmod go-r "${config.services.postgresql.dataDir}/key.pem"
116 '')) ];
117 };
118 systemd.services.postgresql.postStart = lib.mkAfter ''
119 # This line is already defined in 19.09
120 PSQL="psql --port=5432"
121
122 ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
123 $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
124 | grep -q 1 \
125 || $PSQL -tAc 'CREATE USER "${role}" WITH REPLICATION'
126 '') cfg.replicationHosts)}
127
128 ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _:
129 let
130 sname = builtins.replaceStrings ["-"] ["_"] role;
131 in
132 ''
133 $PSQL -tAc "SELECT 1 FROM pg_replication_slots WHERE slot_name='${sname}'" \
134 | grep -q 1 \
135 || $PSQL -tAc "SELECT * FROM pg_create_physical_replication_slot('${sname}')"
136 '') cfg.replicationHosts)}
137 '';
138
139 services.postgresql = {
140 enable = true;
141 package = cfg.package;
142 enableTCPIP = true;
143 checkConfig = false;
144 logLinePrefix = "%h %q%u@%d "; # Default: '%m [%p] ', already stored independently by journald. %h needed for fail2ban
145 settings = {
146 max_connections = 300;
147 wal_level = "logical";
148 shared_buffers = "512MB";
149 work_mem = "10MB";
150 max_wal_size = "1GB";
151 min_wal_size = "80MB";
152 log_timezone = "Europe/Paris";
153 datestyle = "iso, mdy";
154 timezone = "Europe/Paris";
155 lc_messages = "en_US.UTF-8";
156 lc_monetary = "en_US.UTF-8";
157 lc_numeric = "en_US.UTF-8";
158 lc_time = "en_US.UTF-8";
159 default_text_search_config = "pg_catalog.english";
160 # this introduces a small delay before storing on disk, but
161 # makes it order of magnitudes quicker
162 synchronous_commit = "off";
163 ssl = "on";
164 ssl_cert_file = "${config.security.acme.certs.postgresql.directory}/fullchain.pem";
165 ssl_key_file = "${config.services.postgresql.dataDir}/key.pem";
166 };
167 authentication = let
168 hosts = builtins.concatStringsSep "\n" (
169 lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v:
170 map (ip6: "hostssl ${v.database} ${v.username} ${ip6} ${v.method}") v.ip6
171 ++ map (ip4: "hostssl ${v.database} ${v.username} ${ip4}/32 ${v.method}") v.ip4
172 ) vs) cfg.authorizedHosts
173 ));
174 replication = builtins.concatStringsSep "\n" (
175 lib.lists.flatten (lib.mapAttrsToList (k: v:
176 map (ip6: "hostssl replication ${k} ${ip6}/128 pam pamservice=postgresql_replication") v.ip6
177 ++ map (ip4: "hostssl replication ${k} ${ip4}/32 pam pamservice=postgresql_replication") v.ip4
178 ) cfg.replicationHosts
179 ));
180 in ''
181 local all postgres ident
182 local all all md5
183 ${hosts}
184 hostssl all all all pam
185 ${replication}
186 '';
187 };
188
189 secrets.keys = {
190 "postgresql/pam" = {
191 permissions = "0400";
192 group = "postgres";
193 user = "postgres";
194 text = with cfg.ldapConfig; ''
195 host ${host}
196 base ${base}
197 binddn ${dn}
198 bindpw ${password}
199 pam_filter ${filter}
200 ssl start_tls
201 '';
202 };
203 "postgresql/pam_replication" = {
204 permissions = "0400";
205 group = "postgres";
206 user = "postgres";
207 text = with cfg.replicationLdapConfig; ''
208 host ${host}
209 base ${base}
210 binddn ${dn}
211 bindpw ${password}
212 pam_login_attribute cn
213 ssl start_tls
214 '';
215 };
216 };
217
218 security.pam.services = let
219 pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
220 in {
221 postgresql = {
222 text = ''
223 auth required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
224 account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
225 '';
226 };
227 postgresql_replication = {
228 text = ''
229 auth required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
230 account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
231 '';
232 };
233 };
234 };
235}
236
generated by cgit v1.2.3 (git 2.25.1) at 2025-02-16 11:26:28 +0000