pyrox.dev
1{ config, lib, pkgs, ... }:
2
3with lib;
4
5let
6
7 cfg = config.services.postgresql;
8
9 postgresql =
10 let
11 # ensure that
12 # services.postgresql = {
13 # enableJIT = true;
14 # package = pkgs.postgresql_<major>;
15 # };
16 # works.
17 base = if cfg.enableJIT && !cfg.package.jitSupport then cfg.package.withJIT else cfg.package;
18 in
19 if cfg.extraPlugins == []
20 then base
21 else base.withPackages (_: cfg.extraPlugins);
22
23 toStr = value:
24 if true == value then "yes"
25 else if false == value then "no"
26 else if isString value then "'${lib.replaceStrings ["'"] ["''"] value}'"
27 else toString value;
28
29 # The main PostgreSQL configuration file.
30 configFile = pkgs.writeTextDir "postgresql.conf" (concatStringsSep "\n" (mapAttrsToList (n: v: "${n} = ${toStr v}") cfg.settings));
31
32 configFileCheck = pkgs.runCommand "postgresql-configfile-check" {} ''
33 ${cfg.package}/bin/postgres -D${configFile} -C config_file >/dev/null
34 touch $out
35 '';
36
37 groupAccessAvailable = versionAtLeast postgresql.version "11.0";
38
39in
40
41{
42 imports = [
43 (mkRemovedOptionModule [ "services" "postgresql" "extraConfig" ] "Use services.postgresql.settings instead.")
44 ];
45
46 ###### interface
47
48 options = {
49
50 services.postgresql = {
51
52 enable = mkEnableOption (lib.mdDoc "PostgreSQL Server");
53
54 enableJIT = mkEnableOption (lib.mdDoc "JIT support");
55
56 package = mkOption {
57 type = types.package;
58 example = literalExpression "pkgs.postgresql_11";
59 description = lib.mdDoc ''
60 PostgreSQL package to use.
61 '';
62 };
63
64 port = mkOption {
65 type = types.port;
66 default = 5432;
67 description = lib.mdDoc ''
68 The port on which PostgreSQL listens.
69 '';
70 };
71
72 checkConfig = mkOption {
73 type = types.bool;
74 default = true;
75 description = lib.mdDoc "Check the syntax of the configuration file at compile time";
76 };
77
78 dataDir = mkOption {
79 type = types.path;
80 defaultText = literalExpression ''"/var/lib/postgresql/''${config.services.postgresql.package.psqlSchema}"'';
81 example = "/var/lib/postgresql/11";
82 description = lib.mdDoc ''
83 The data directory for PostgreSQL. If left as the default value
84 this directory will automatically be created before the PostgreSQL server starts, otherwise
85 the sysadmin is responsible for ensuring the directory exists with appropriate ownership
86 and permissions.
87 '';
88 };
89
90 authentication = mkOption {
91 type = types.lines;
92 default = "";
93 description = lib.mdDoc ''
94 Defines how users authenticate themselves to the server. See the
95 [PostgreSQL documentation for pg_hba.conf](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html)
96 for details on the expected format of this option. By default,
97 peer based authentication will be used for users connecting
98 via the Unix socket, and md5 password authentication will be
99 used for users connecting via TCP. Any added rules will be
100 inserted above the default rules. If you'd like to replace the
101 default rules entirely, you can use `lib.mkForce` in your
102 module.
103 '';
104 };
105
106 identMap = mkOption {
107 type = types.lines;
108 default = "";
109 description = lib.mdDoc ''
110 Defines the mapping from system users to database users.
111
112 The general form is:
113
114 map-name system-username database-username
115 '';
116 };
117
118 initdbArgs = mkOption {
119 type = with types; listOf str;
120 default = [];
121 example = [ "--data-checksums" "--allow-group-access" ];
122 description = lib.mdDoc ''
123 Additional arguments passed to `initdb` during data dir
124 initialisation.
125 '';
126 };
127
128 initialScript = mkOption {
129 type = types.nullOr types.path;
130 default = null;
131 description = lib.mdDoc ''
132 A file containing SQL statements to execute on first startup.
133 '';
134 };
135
136 ensureDatabases = mkOption {
137 type = types.listOf types.str;
138 default = [];
139 description = lib.mdDoc ''
140 Ensures that the specified databases exist.
141 This option will never delete existing databases, especially not when the value of this
142 option is changed. This means that databases created once through this option or
143 otherwise have to be removed manually.
144 '';
145 example = [
146 "gitea"
147 "nextcloud"
148 ];
149 };
150
151 ensureUsers = mkOption {
152 type = types.listOf (types.submodule {
153 options = {
154 name = mkOption {
155 type = types.str;
156 description = lib.mdDoc ''
157 Name of the user to ensure.
158 '';
159 };
160
161 ensurePermissions = mkOption {
162 type = types.attrsOf types.str;
163 default = {};
164 description = lib.mdDoc ''
165 Permissions to ensure for the user, specified as an attribute set.
166 The attribute names specify the database and tables to grant the permissions for.
167 The attribute values specify the permissions to grant. You may specify one or
168 multiple comma-separated SQL privileges here.
169
170 For more information on how to specify the target
171 and on which privileges exist, see the
172 [GRANT syntax](https://www.postgresql.org/docs/current/sql-grant.html).
173 The attributes are used as `GRANT ''${attrValue} ON ''${attrName}`.
174 '';
175 example = literalExpression ''
176 {
177 "DATABASE \"nextcloud\"" = "ALL PRIVILEGES";
178 "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES";
179 }
180 '';
181 };
182
183 ensureClauses = mkOption {
184 description = lib.mdDoc ''
185 An attrset of clauses to grant to the user. Under the hood this uses the
186 [ALTER USER syntax](https://www.postgresql.org/docs/current/sql-alteruser.html) for each attrName where
187 the attrValue is true in the attrSet:
188 `ALTER USER user.name WITH attrName`
189 '';
190 example = literalExpression ''
191 {
192 superuser = true;
193 createrole = true;
194 createdb = true;
195 }
196 '';
197 default = {};
198 defaultText = lib.literalMD ''
199 The default, `null`, means that the user created will have the default permissions assigned by PostgreSQL. Subsequent server starts will not set or unset the clause, so imperative changes are preserved.
200 '';
201 type = types.submodule {
202 options = let
203 defaultText = lib.literalMD ''
204 `null`: do not set. For newly created roles, use PostgreSQL's default. For existing roles, do not touch this clause.
205 '';
206 in {
207 superuser = mkOption {
208 type = types.nullOr types.bool;
209 description = lib.mdDoc ''
210 Grants the user, created by the ensureUser attr, superuser permissions. From the postgres docs:
211
212 A database superuser bypasses all permission checks,
213 except the right to log in. This is a dangerous privilege
214 and should not be used carelessly; it is best to do most
215 of your work as a role that is not a superuser. To create
216 a new database superuser, use CREATE ROLE name SUPERUSER.
217 You must do this as a role that is already a superuser.
218
219 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
220 '';
221 default = null;
222 inherit defaultText;
223 };
224 createrole = mkOption {
225 type = types.nullOr types.bool;
226 description = lib.mdDoc ''
227 Grants the user, created by the ensureUser attr, createrole permissions. From the postgres docs:
228
229 A role must be explicitly given permission to create more
230 roles (except for superusers, since those bypass all
231 permission checks). To create such a role, use CREATE
232 ROLE name CREATEROLE. A role with CREATEROLE privilege
233 can alter and drop other roles, too, as well as grant or
234 revoke membership in them. However, to create, alter,
235 drop, or change membership of a superuser role, superuser
236 status is required; CREATEROLE is insufficient for that.
237
238 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
239 '';
240 default = null;
241 inherit defaultText;
242 };
243 createdb = mkOption {
244 type = types.nullOr types.bool;
245 description = lib.mdDoc ''
246 Grants the user, created by the ensureUser attr, createdb permissions. From the postgres docs:
247
248 A role must be explicitly given permission to create
249 databases (except for superusers, since those bypass all
250 permission checks). To create such a role, use CREATE
251 ROLE name CREATEDB.
252
253 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
254 '';
255 default = null;
256 inherit defaultText;
257 };
258 "inherit" = mkOption {
259 type = types.nullOr types.bool;
260 description = lib.mdDoc ''
261 Grants the user created inherit permissions. From the postgres docs:
262
263 A role is given permission to inherit the privileges of
264 roles it is a member of, by default. However, to create a
265 role without the permission, use CREATE ROLE name
266 NOINHERIT.
267
268 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
269 '';
270 default = null;
271 inherit defaultText;
272 };
273 login = mkOption {
274 type = types.nullOr types.bool;
275 description = lib.mdDoc ''
276 Grants the user, created by the ensureUser attr, login permissions. From the postgres docs:
277
278 Only roles that have the LOGIN attribute can be used as
279 the initial role name for a database connection. A role
280 with the LOGIN attribute can be considered the same as a
281 “database user”. To create a role with login privilege,
282 use either:
283
284 CREATE ROLE name LOGIN; CREATE USER name;
285
286 (CREATE USER is equivalent to CREATE ROLE except that
287 CREATE USER includes LOGIN by default, while CREATE ROLE
288 does not.)
289
290 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
291 '';
292 default = null;
293 inherit defaultText;
294 };
295 replication = mkOption {
296 type = types.nullOr types.bool;
297 description = lib.mdDoc ''
298 Grants the user, created by the ensureUser attr, replication permissions. From the postgres docs:
299
300 A role must explicitly be given permission to initiate
301 streaming replication (except for superusers, since those
302 bypass all permission checks). A role used for streaming
303 replication must have LOGIN permission as well. To create
304 such a role, use CREATE ROLE name REPLICATION LOGIN.
305
306 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
307 '';
308 default = null;
309 inherit defaultText;
310 };
311 bypassrls = mkOption {
312 type = types.nullOr types.bool;
313 description = lib.mdDoc ''
314 Grants the user, created by the ensureUser attr, replication permissions. From the postgres docs:
315
316 A role must be explicitly given permission to bypass
317 every row-level security (RLS) policy (except for
318 superusers, since those bypass all permission checks). To
319 create such a role, use CREATE ROLE name BYPASSRLS as a
320 superuser.
321
322 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
323 '';
324 default = null;
325 inherit defaultText;
326 };
327 };
328 };
329 };
330 };
331 });
332 default = [];
333 description = lib.mdDoc ''
334 Ensures that the specified users exist and have at least the ensured permissions.
335 The PostgreSQL users will be identified using peer authentication. This authenticates the Unix user with the
336 same name only, and that without the need for a password.
337 This option will never delete existing users or remove permissions, especially not when the value of this
338 option is changed. This means that users created and permissions assigned once through this option or
339 otherwise have to be removed manually.
340 '';
341 example = literalExpression ''
342 [
343 {
344 name = "nextcloud";
345 ensurePermissions = {
346 "DATABASE nextcloud" = "ALL PRIVILEGES";
347 };
348 }
349 {
350 name = "superuser";
351 ensurePermissions = {
352 "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES";
353 };
354 }
355 ]
356 '';
357 };
358
359 enableTCPIP = mkOption {
360 type = types.bool;
361 default = false;
362 description = lib.mdDoc ''
363 Whether PostgreSQL should listen on all network interfaces.
364 If disabled, the database can only be accessed via its Unix
365 domain socket or via TCP connections to localhost.
366 '';
367 };
368
369 logLinePrefix = mkOption {
370 type = types.str;
371 default = "[%p] ";
372 example = "%m [%p] ";
373 description = lib.mdDoc ''
374 A printf-style string that is output at the beginning of each log line.
375 Upstream default is `'%m [%p] '`, i.e. it includes the timestamp. We do
376 not include the timestamp, because journal has it anyway.
377 '';
378 };
379
380 extraPlugins = mkOption {
381 type = types.listOf types.path;
382 default = [];
383 example = literalExpression "with pkgs.postgresql_11.pkgs; [ postgis pg_repack ]";
384 description = lib.mdDoc ''
385 List of PostgreSQL plugins. PostgreSQL version for each plugin should
386 match version for `services.postgresql.package` value.
387 '';
388 };
389
390 settings = mkOption {
391 type = with types; attrsOf (oneOf [ bool float int str ]);
392 default = {};
393 description = lib.mdDoc ''
394 PostgreSQL configuration. Refer to
395 <https://www.postgresql.org/docs/11/config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE>
396 for an overview of `postgresql.conf`.
397
398 ::: {.note}
399 String values will automatically be enclosed in single quotes. Single quotes will be
400 escaped with two single quotes as described by the upstream documentation linked above.
401 :::
402 '';
403 example = literalExpression ''
404 {
405 log_connections = true;
406 log_statement = "all";
407 logging_collector = true
408 log_disconnections = true
409 log_destination = lib.mkForce "syslog";
410 }
411 '';
412 };
413
414 recoveryConfig = mkOption {
415 type = types.nullOr types.lines;
416 default = null;
417 description = lib.mdDoc ''
418 Contents of the {file}`recovery.conf` file.
419 '';
420 };
421
422 superUser = mkOption {
423 type = types.str;
424 default = "postgres";
425 internal = true;
426 readOnly = true;
427 description = lib.mdDoc ''
428 PostgreSQL superuser account to use for various operations. Internal since changing
429 this value would lead to breakage while setting up databases.
430 '';
431 };
432 };
433
434 };
435
436
437 ###### implementation
438
439 config = mkIf cfg.enable {
440
441 services.postgresql.settings =
442 {
443 hba_file = "${pkgs.writeText "pg_hba.conf" cfg.authentication}";
444 ident_file = "${pkgs.writeText "pg_ident.conf" cfg.identMap}";
445 log_destination = "stderr";
446 log_line_prefix = cfg.logLinePrefix;
447 listen_addresses = if cfg.enableTCPIP then "*" else "localhost";
448 port = cfg.port;
449 jit = mkDefault (if cfg.enableJIT then "on" else "off");
450 };
451
452 services.postgresql.package = let
453 mkThrow = ver: throw "postgresql_${ver} was removed, please upgrade your postgresql version.";
454 base = if versionAtLeast config.system.stateVersion "22.05" then pkgs.postgresql_14
455 else if versionAtLeast config.system.stateVersion "21.11" then pkgs.postgresql_13
456 else if versionAtLeast config.system.stateVersion "20.03" then pkgs.postgresql_11
457 else if versionAtLeast config.system.stateVersion "17.09" then mkThrow "9_6"
458 else mkThrow "9_5";
459 in
460 # Note: when changing the default, make it conditional on
461 # ‘system.stateVersion’ to maintain compatibility with existing
462 # systems!
463 mkDefault (if cfg.enableJIT then base.withJIT else base);
464
465 services.postgresql.dataDir = mkDefault "/var/lib/postgresql/${cfg.package.psqlSchema}";
466
467 services.postgresql.authentication = mkAfter
468 ''
469 # Generated file; do not edit!
470 local all all peer
471 host all all 127.0.0.1/32 md5
472 host all all ::1/128 md5
473 '';
474
475 users.users.postgres =
476 { name = "postgres";
477 uid = config.ids.uids.postgres;
478 group = "postgres";
479 description = "PostgreSQL server user";
480 home = "${cfg.dataDir}";
481 useDefaultShell = true;
482 };
483
484 users.groups.postgres.gid = config.ids.gids.postgres;
485
486 environment.systemPackages = [ postgresql ];
487
488 environment.pathsToLink = [
489 "/share/postgresql"
490 ];
491
492 system.checks = lib.optional (cfg.checkConfig && pkgs.stdenv.hostPlatform == pkgs.stdenv.buildPlatform) configFileCheck;
493
494 systemd.services.postgresql =
495 { description = "PostgreSQL Server";
496
497 wantedBy = [ "multi-user.target" ];
498 after = [ "network.target" ];
499
500 environment.PGDATA = cfg.dataDir;
501
502 path = [ postgresql ];
503
504 preStart =
505 ''
506 if ! test -e ${cfg.dataDir}/PG_VERSION; then
507 # Cleanup the data directory.
508 rm -f ${cfg.dataDir}/*.conf
509
510 # Initialise the database.
511 initdb -U ${cfg.superUser} ${concatStringsSep " " cfg.initdbArgs}
512
513 # See postStart!
514 touch "${cfg.dataDir}/.first_startup"
515 fi
516
517 ln -sfn "${configFile}/postgresql.conf" "${cfg.dataDir}/postgresql.conf"
518 ${optionalString (cfg.recoveryConfig != null) ''
519 ln -sfn "${pkgs.writeText "recovery.conf" cfg.recoveryConfig}" \
520 "${cfg.dataDir}/recovery.conf"
521 ''}
522 '';
523
524 # Wait for PostgreSQL to be ready to accept connections.
525 postStart =
526 ''
527 PSQL="psql --port=${toString cfg.port}"
528
529 while ! $PSQL -d postgres -c "" 2> /dev/null; do
530 if ! kill -0 "$MAINPID"; then exit 1; fi
531 sleep 0.1
532 done
533
534 if test -e "${cfg.dataDir}/.first_startup"; then
535 ${optionalString (cfg.initialScript != null) ''
536 $PSQL -f "${cfg.initialScript}" -d postgres
537 ''}
538 rm -f "${cfg.dataDir}/.first_startup"
539 fi
540 '' + optionalString (cfg.ensureDatabases != []) ''
541 ${concatMapStrings (database: ''
542 $PSQL -tAc "SELECT 1 FROM pg_database WHERE datname = '${database}'" | grep -q 1 || $PSQL -tAc 'CREATE DATABASE "${database}"'
543 '') cfg.ensureDatabases}
544 '' + ''
545 ${
546 concatMapStrings
547 (user:
548 let
549 userPermissions = concatStringsSep "\n"
550 (mapAttrsToList
551 (database: permission: ''$PSQL -tAc 'GRANT ${permission} ON ${database} TO "${user.name}"' '')
552 user.ensurePermissions
553 );
554
555 filteredClauses = filterAttrs (name: value: value != null) user.ensureClauses;
556
557 clauseSqlStatements = attrValues (mapAttrs (n: v: if v then n else "no${n}") filteredClauses);
558
559 userClauses = ''$PSQL -tAc 'ALTER ROLE "${user.name}" ${concatStringsSep " " clauseSqlStatements}' '';
560 in ''
561 $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${user.name}'" | grep -q 1 || $PSQL -tAc 'CREATE USER "${user.name}"'
562 ${userPermissions}
563 ${userClauses}
564 ''
565 )
566 cfg.ensureUsers
567 }
568 '';
569
570 serviceConfig = mkMerge [
571 { ExecReload = "${pkgs.coreutils}/bin/kill -HUP $MAINPID";
572 User = "postgres";
573 Group = "postgres";
574 RuntimeDirectory = "postgresql";
575 Type = if versionAtLeast cfg.package.version "9.6"
576 then "notify"
577 else "simple";
578
579 # Shut down Postgres using SIGINT ("Fast Shutdown mode"). See
580 # http://www.postgresql.org/docs/current/static/server-shutdown.html
581 KillSignal = "SIGINT";
582 KillMode = "mixed";
583
584 # Give Postgres a decent amount of time to clean up after
585 # receiving systemd's SIGINT.
586 TimeoutSec = 120;
587
588 ExecStart = "${postgresql}/bin/postgres";
589 }
590 (mkIf (cfg.dataDir == "/var/lib/postgresql/${cfg.package.psqlSchema}") {
591 StateDirectory = "postgresql postgresql/${cfg.package.psqlSchema}";
592 StateDirectoryMode = if groupAccessAvailable then "0750" else "0700";
593 })
594 ];
595
596 unitConfig.RequiresMountsFor = "${cfg.dataDir}";
597 };
598
599 };
600
601 meta.doc = ./postgresql.md;
602 meta.maintainers = with lib.maintainers; [ thoughtpolice danbst ];
603}