pyrox.dev
1{ config, lib, pkgs, ... }:
2
3with lib;
4
5let
6
7 cfg = config.services.postgresql;
8
9 postgresql =
10 if cfg.extraPlugins == []
11 then cfg.package
12 else cfg.package.withPackages (_: cfg.extraPlugins);
13
14 toStr = value:
15 if true == value then "yes"
16 else if false == value then "no"
17 else if isString value then "'${lib.replaceStrings ["'"] ["''"] value}'"
18 else toString value;
19
20 # The main PostgreSQL configuration file.
21 configFile = pkgs.writeTextDir "postgresql.conf" (concatStringsSep "\n" (mapAttrsToList (n: v: "${n} = ${toStr v}") cfg.settings));
22
23 configFileCheck = pkgs.runCommand "postgresql-configfile-check" {} ''
24 ${cfg.package}/bin/postgres -D${configFile} -C config_file >/dev/null
25 touch $out
26 '';
27
28 groupAccessAvailable = versionAtLeast postgresql.version "11.0";
29
30in
31
32{
33 imports = [
34 (mkRemovedOptionModule [ "services" "postgresql" "extraConfig" ] "Use services.postgresql.settings instead.")
35 ];
36
37 ###### interface
38
39 options = {
40
41 services.postgresql = {
42
43 enable = mkEnableOption (lib.mdDoc "PostgreSQL Server");
44
45 package = mkOption {
46 type = types.package;
47 example = literalExpression "pkgs.postgresql_11";
48 description = lib.mdDoc ''
49 PostgreSQL package to use.
50 '';
51 };
52
53 port = mkOption {
54 type = types.int;
55 default = 5432;
56 description = lib.mdDoc ''
57 The port on which PostgreSQL listens.
58 '';
59 };
60
61 checkConfig = mkOption {
62 type = types.bool;
63 default = true;
64 description = lib.mdDoc "Check the syntax of the configuration file at compile time";
65 };
66
67 dataDir = mkOption {
68 type = types.path;
69 defaultText = literalExpression ''"/var/lib/postgresql/''${config.services.postgresql.package.psqlSchema}"'';
70 example = "/var/lib/postgresql/11";
71 description = lib.mdDoc ''
72 The data directory for PostgreSQL. If left as the default value
73 this directory will automatically be created before the PostgreSQL server starts, otherwise
74 the sysadmin is responsible for ensuring the directory exists with appropriate ownership
75 and permissions.
76 '';
77 };
78
79 authentication = mkOption {
80 type = types.lines;
81 default = "";
82 description = lib.mdDoc ''
83 Defines how users authenticate themselves to the server. See the
84 [PostgreSQL documentation for pg_hba.conf](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html)
85 for details on the expected format of this option. By default,
86 peer based authentication will be used for users connecting
87 via the Unix socket, and md5 password authentication will be
88 used for users connecting via TCP. Any added rules will be
89 inserted above the default rules. If you'd like to replace the
90 default rules entirely, you can use `lib.mkForce` in your
91 module.
92 '';
93 };
94
95 identMap = mkOption {
96 type = types.lines;
97 default = "";
98 description = lib.mdDoc ''
99 Defines the mapping from system users to database users.
100
101 The general form is:
102
103 map-name system-username database-username
104 '';
105 };
106
107 initdbArgs = mkOption {
108 type = with types; listOf str;
109 default = [];
110 example = [ "--data-checksums" "--allow-group-access" ];
111 description = lib.mdDoc ''
112 Additional arguments passed to `initdb` during data dir
113 initialisation.
114 '';
115 };
116
117 initialScript = mkOption {
118 type = types.nullOr types.path;
119 default = null;
120 description = lib.mdDoc ''
121 A file containing SQL statements to execute on first startup.
122 '';
123 };
124
125 ensureDatabases = mkOption {
126 type = types.listOf types.str;
127 default = [];
128 description = lib.mdDoc ''
129 Ensures that the specified databases exist.
130 This option will never delete existing databases, especially not when the value of this
131 option is changed. This means that databases created once through this option or
132 otherwise have to be removed manually.
133 '';
134 example = [
135 "gitea"
136 "nextcloud"
137 ];
138 };
139
140 ensureUsers = mkOption {
141 type = types.listOf (types.submodule {
142 options = {
143 name = mkOption {
144 type = types.str;
145 description = lib.mdDoc ''
146 Name of the user to ensure.
147 '';
148 };
149 ensurePermissions = mkOption {
150 type = types.attrsOf types.str;
151 default = {};
152 description = lib.mdDoc ''
153 Permissions to ensure for the user, specified as an attribute set.
154 The attribute names specify the database and tables to grant the permissions for.
155 The attribute values specify the permissions to grant. You may specify one or
156 multiple comma-separated SQL privileges here.
157
158 For more information on how to specify the target
159 and on which privileges exist, see the
160 [GRANT syntax](https://www.postgresql.org/docs/current/sql-grant.html).
161 The attributes are used as `GRANT ''${attrValue} ON ''${attrName}`.
162 '';
163 example = literalExpression ''
164 {
165 "DATABASE \"nextcloud\"" = "ALL PRIVILEGES";
166 "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES";
167 }
168 '';
169 };
170 };
171 });
172 default = [];
173 description = lib.mdDoc ''
174 Ensures that the specified users exist and have at least the ensured permissions.
175 The PostgreSQL users will be identified using peer authentication. This authenticates the Unix user with the
176 same name only, and that without the need for a password.
177 This option will never delete existing users or remove permissions, especially not when the value of this
178 option is changed. This means that users created and permissions assigned once through this option or
179 otherwise have to be removed manually.
180 '';
181 example = literalExpression ''
182 [
183 {
184 name = "nextcloud";
185 ensurePermissions = {
186 "DATABASE nextcloud" = "ALL PRIVILEGES";
187 };
188 }
189 {
190 name = "superuser";
191 ensurePermissions = {
192 "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES";
193 };
194 }
195 ]
196 '';
197 };
198
199 enableTCPIP = mkOption {
200 type = types.bool;
201 default = false;
202 description = lib.mdDoc ''
203 Whether PostgreSQL should listen on all network interfaces.
204 If disabled, the database can only be accessed via its Unix
205 domain socket or via TCP connections to localhost.
206 '';
207 };
208
209 logLinePrefix = mkOption {
210 type = types.str;
211 default = "[%p] ";
212 example = "%m [%p] ";
213 description = lib.mdDoc ''
214 A printf-style string that is output at the beginning of each log line.
215 Upstream default is `'%m [%p] '`, i.e. it includes the timestamp. We do
216 not include the timestamp, because journal has it anyway.
217 '';
218 };
219
220 extraPlugins = mkOption {
221 type = types.listOf types.path;
222 default = [];
223 example = literalExpression "with pkgs.postgresql_11.pkgs; [ postgis pg_repack ]";
224 description = lib.mdDoc ''
225 List of PostgreSQL plugins. PostgreSQL version for each plugin should
226 match version for `services.postgresql.package` value.
227 '';
228 };
229
230 settings = mkOption {
231 type = with types; attrsOf (oneOf [ bool float int str ]);
232 default = {};
233 description = lib.mdDoc ''
234 PostgreSQL configuration. Refer to
235 <https://www.postgresql.org/docs/11/config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE>
236 for an overview of `postgresql.conf`.
237
238 ::: {.note}
239 String values will automatically be enclosed in single quotes. Single quotes will be
240 escaped with two single quotes as described by the upstream documentation linked above.
241 :::
242 '';
243 example = literalExpression ''
244 {
245 log_connections = true;
246 log_statement = "all";
247 logging_collector = true
248 log_disconnections = true
249 log_destination = lib.mkForce "syslog";
250 }
251 '';
252 };
253
254 recoveryConfig = mkOption {
255 type = types.nullOr types.lines;
256 default = null;
257 description = lib.mdDoc ''
258 Contents of the {file}`recovery.conf` file.
259 '';
260 };
261
262 superUser = mkOption {
263 type = types.str;
264 default = "postgres";
265 internal = true;
266 readOnly = true;
267 description = lib.mdDoc ''
268 PostgreSQL superuser account to use for various operations. Internal since changing
269 this value would lead to breakage while setting up databases.
270 '';
271 };
272 };
273
274 };
275
276
277 ###### implementation
278
279 config = mkIf cfg.enable {
280
281 services.postgresql.settings =
282 {
283 hba_file = "${pkgs.writeText "pg_hba.conf" cfg.authentication}";
284 ident_file = "${pkgs.writeText "pg_ident.conf" cfg.identMap}";
285 log_destination = "stderr";
286 log_line_prefix = cfg.logLinePrefix;
287 listen_addresses = if cfg.enableTCPIP then "*" else "localhost";
288 port = cfg.port;
289 };
290
291 services.postgresql.package = let
292 mkThrow = ver: throw "postgresql_${ver} was removed, please upgrade your postgresql version.";
293 in
294 # Note: when changing the default, make it conditional on
295 # ‘system.stateVersion’ to maintain compatibility with existing
296 # systems!
297 mkDefault (if versionAtLeast config.system.stateVersion "22.05" then pkgs.postgresql_14
298 else if versionAtLeast config.system.stateVersion "21.11" then pkgs.postgresql_13
299 else if versionAtLeast config.system.stateVersion "20.03" then pkgs.postgresql_11
300 else if versionAtLeast config.system.stateVersion "17.09" then mkThrow "9_6"
301 else mkThrow "9_5");
302
303 services.postgresql.dataDir = mkDefault "/var/lib/postgresql/${cfg.package.psqlSchema}";
304
305 services.postgresql.authentication = mkAfter
306 ''
307 # Generated file; do not edit!
308 local all all peer
309 host all all 127.0.0.1/32 md5
310 host all all ::1/128 md5
311 '';
312
313 users.users.postgres =
314 { name = "postgres";
315 uid = config.ids.uids.postgres;
316 group = "postgres";
317 description = "PostgreSQL server user";
318 home = "${cfg.dataDir}";
319 useDefaultShell = true;
320 };
321
322 users.groups.postgres.gid = config.ids.gids.postgres;
323
324 environment.systemPackages = [ postgresql ];
325
326 environment.pathsToLink = [
327 "/share/postgresql"
328 ];
329
330 system.extraDependencies = lib.optional (cfg.checkConfig && pkgs.stdenv.hostPlatform == pkgs.stdenv.buildPlatform) configFileCheck;
331
332 systemd.services.postgresql =
333 { description = "PostgreSQL Server";
334
335 wantedBy = [ "multi-user.target" ];
336 after = [ "network.target" ];
337
338 environment.PGDATA = cfg.dataDir;
339
340 path = [ postgresql ];
341
342 preStart =
343 ''
344 if ! test -e ${cfg.dataDir}/PG_VERSION; then
345 # Cleanup the data directory.
346 rm -f ${cfg.dataDir}/*.conf
347
348 # Initialise the database.
349 initdb -U ${cfg.superUser} ${concatStringsSep " " cfg.initdbArgs}
350
351 # See postStart!
352 touch "${cfg.dataDir}/.first_startup"
353 fi
354
355 ln -sfn "${configFile}/postgresql.conf" "${cfg.dataDir}/postgresql.conf"
356 ${optionalString (cfg.recoveryConfig != null) ''
357 ln -sfn "${pkgs.writeText "recovery.conf" cfg.recoveryConfig}" \
358 "${cfg.dataDir}/recovery.conf"
359 ''}
360 '';
361
362 # Wait for PostgreSQL to be ready to accept connections.
363 postStart =
364 ''
365 PSQL="psql --port=${toString cfg.port}"
366
367 while ! $PSQL -d postgres -c "" 2> /dev/null; do
368 if ! kill -0 "$MAINPID"; then exit 1; fi
369 sleep 0.1
370 done
371
372 if test -e "${cfg.dataDir}/.first_startup"; then
373 ${optionalString (cfg.initialScript != null) ''
374 $PSQL -f "${cfg.initialScript}" -d postgres
375 ''}
376 rm -f "${cfg.dataDir}/.first_startup"
377 fi
378 '' + optionalString (cfg.ensureDatabases != []) ''
379 ${concatMapStrings (database: ''
380 $PSQL -tAc "SELECT 1 FROM pg_database WHERE datname = '${database}'" | grep -q 1 || $PSQL -tAc 'CREATE DATABASE "${database}"'
381 '') cfg.ensureDatabases}
382 '' + ''
383 ${concatMapStrings (user: ''
384 $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${user.name}'" | grep -q 1 || $PSQL -tAc 'CREATE USER "${user.name}"'
385 ${concatStringsSep "\n" (mapAttrsToList (database: permission: ''
386 $PSQL -tAc 'GRANT ${permission} ON ${database} TO "${user.name}"'
387 '') user.ensurePermissions)}
388 '') cfg.ensureUsers}
389 '';
390
391 serviceConfig = mkMerge [
392 { ExecReload = "${pkgs.coreutils}/bin/kill -HUP $MAINPID";
393 User = "postgres";
394 Group = "postgres";
395 RuntimeDirectory = "postgresql";
396 Type = if versionAtLeast cfg.package.version "9.6"
397 then "notify"
398 else "simple";
399
400 # Shut down Postgres using SIGINT ("Fast Shutdown mode"). See
401 # http://www.postgresql.org/docs/current/static/server-shutdown.html
402 KillSignal = "SIGINT";
403 KillMode = "mixed";
404
405 # Give Postgres a decent amount of time to clean up after
406 # receiving systemd's SIGINT.
407 TimeoutSec = 120;
408
409 ExecStart = "${postgresql}/bin/postgres";
410 }
411 (mkIf (cfg.dataDir == "/var/lib/postgresql/${cfg.package.psqlSchema}") {
412 StateDirectory = "postgresql postgresql/${cfg.package.psqlSchema}";
413 StateDirectoryMode = if groupAccessAvailable then "0750" else "0700";
414 })
415 ];
416
417 unitConfig.RequiresMountsFor = "${cfg.dataDir}";
418 };
419
420 };
421
422 meta.doc = ./postgresql.xml;
423 meta.maintainers = with lib.maintainers; [ thoughtpolice danbst ];
424}