pyrox.dev
lol
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_15";
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/15";
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 example = ''
110 map-name-0 system-username-0 database-username-0
111 map-name-1 system-username-1 database-username-1
112 '';
113 description = lib.mdDoc ''
114 Defines the mapping from system users to database users.
115
116 See the [auth doc](https://postgresql.org/docs/current/auth-username-maps.html).
117 '';
118 };
119
120 initdbArgs = mkOption {
121 type = with types; listOf str;
122 default = [];
123 example = [ "--data-checksums" "--allow-group-access" ];
124 description = lib.mdDoc ''
125 Additional arguments passed to `initdb` during data dir
126 initialisation.
127 '';
128 };
129
130 initialScript = mkOption {
131 type = types.nullOr types.path;
132 default = null;
133 example = literalExpression ''
134 pkgs.writeText "init-sql-script" '''
135 alter user postgres with password 'myPassword';
136 ''';'';
137
138 description = lib.mdDoc ''
139 A file containing SQL statements to execute on first startup.
140 '';
141 };
142
143 ensureDatabases = mkOption {
144 type = types.listOf types.str;
145 default = [];
146 description = lib.mdDoc ''
147 Ensures that the specified databases exist.
148 This option will never delete existing databases, especially not when the value of this
149 option is changed. This means that databases created once through this option or
150 otherwise have to be removed manually.
151 '';
152 example = [
153 "gitea"
154 "nextcloud"
155 ];
156 };
157
158 ensureUsers = mkOption {
159 type = types.listOf (types.submodule {
160 options = {
161 name = mkOption {
162 type = types.str;
163 description = lib.mdDoc ''
164 Name of the user to ensure.
165 '';
166 };
167
168 ensurePermissions = mkOption {
169 type = types.attrsOf types.str;
170 default = {};
171 visible = false; # This option has been deprecated.
172 description = lib.mdDoc ''
173 This option is DEPRECATED and should not be used in nixpkgs anymore,
174 use `ensureDBOwnership` instead. It can also break with newer
175 versions of PostgreSQL (≥ 15).
176
177 Permissions to ensure for the user, specified as an attribute set.
178 The attribute names specify the database and tables to grant the permissions for.
179 The attribute values specify the permissions to grant. You may specify one or
180 multiple comma-separated SQL privileges here.
181
182 For more information on how to specify the target
183 and on which privileges exist, see the
184 [GRANT syntax](https://www.postgresql.org/docs/current/sql-grant.html).
185 The attributes are used as `GRANT ''${attrValue} ON ''${attrName}`.
186 '';
187 example = literalExpression ''
188 {
189 "DATABASE \"nextcloud\"" = "ALL PRIVILEGES";
190 "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES";
191 }
192 '';
193 };
194
195 ensureDBOwnership = mkOption {
196 type = types.bool;
197 default = false;
198 description = mdDoc ''
199 Grants the user ownership to a database with the same name.
200 This database must be defined manually in
201 [](#opt-services.postgresql.ensureDatabases).
202 '';
203 };
204
205 ensureClauses = mkOption {
206 description = lib.mdDoc ''
207 An attrset of clauses to grant to the user. Under the hood this uses the
208 [ALTER USER syntax](https://www.postgresql.org/docs/current/sql-alteruser.html) for each attrName where
209 the attrValue is true in the attrSet:
210 `ALTER USER user.name WITH attrName`
211 '';
212 example = literalExpression ''
213 {
214 superuser = true;
215 createrole = true;
216 createdb = true;
217 }
218 '';
219 default = {};
220 defaultText = lib.literalMD ''
221 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.
222 '';
223 type = types.submodule {
224 options = let
225 defaultText = lib.literalMD ''
226 `null`: do not set. For newly created roles, use PostgreSQL's default. For existing roles, do not touch this clause.
227 '';
228 in {
229 superuser = mkOption {
230 type = types.nullOr types.bool;
231 description = lib.mdDoc ''
232 Grants the user, created by the ensureUser attr, superuser permissions. From the postgres docs:
233
234 A database superuser bypasses all permission checks,
235 except the right to log in. This is a dangerous privilege
236 and should not be used carelessly; it is best to do most
237 of your work as a role that is not a superuser. To create
238 a new database superuser, use CREATE ROLE name SUPERUSER.
239 You must do this as a role that is already a superuser.
240
241 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
242 '';
243 default = null;
244 inherit defaultText;
245 };
246 createrole = mkOption {
247 type = types.nullOr types.bool;
248 description = lib.mdDoc ''
249 Grants the user, created by the ensureUser attr, createrole permissions. From the postgres docs:
250
251 A role must be explicitly given permission to create more
252 roles (except for superusers, since those bypass all
253 permission checks). To create such a role, use CREATE
254 ROLE name CREATEROLE. A role with CREATEROLE privilege
255 can alter and drop other roles, too, as well as grant or
256 revoke membership in them. However, to create, alter,
257 drop, or change membership of a superuser role, superuser
258 status is required; CREATEROLE is insufficient for that.
259
260 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
261 '';
262 default = null;
263 inherit defaultText;
264 };
265 createdb = mkOption {
266 type = types.nullOr types.bool;
267 description = lib.mdDoc ''
268 Grants the user, created by the ensureUser attr, createdb permissions. From the postgres docs:
269
270 A role must be explicitly given permission to create
271 databases (except for superusers, since those bypass all
272 permission checks). To create such a role, use CREATE
273 ROLE name CREATEDB.
274
275 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
276 '';
277 default = null;
278 inherit defaultText;
279 };
280 "inherit" = mkOption {
281 type = types.nullOr types.bool;
282 description = lib.mdDoc ''
283 Grants the user created inherit permissions. From the postgres docs:
284
285 A role is given permission to inherit the privileges of
286 roles it is a member of, by default. However, to create a
287 role without the permission, use CREATE ROLE name
288 NOINHERIT.
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 login = mkOption {
296 type = types.nullOr types.bool;
297 description = lib.mdDoc ''
298 Grants the user, created by the ensureUser attr, login permissions. From the postgres docs:
299
300 Only roles that have the LOGIN attribute can be used as
301 the initial role name for a database connection. A role
302 with the LOGIN attribute can be considered the same as a
303 “database user”. To create a role with login privilege,
304 use either:
305
306 CREATE ROLE name LOGIN; CREATE USER name;
307
308 (CREATE USER is equivalent to CREATE ROLE except that
309 CREATE USER includes LOGIN by default, while CREATE ROLE
310 does not.)
311
312 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
313 '';
314 default = null;
315 inherit defaultText;
316 };
317 replication = mkOption {
318 type = types.nullOr types.bool;
319 description = lib.mdDoc ''
320 Grants the user, created by the ensureUser attr, replication permissions. From the postgres docs:
321
322 A role must explicitly be given permission to initiate
323 streaming replication (except for superusers, since those
324 bypass all permission checks). A role used for streaming
325 replication must have LOGIN permission as well. To create
326 such a role, use CREATE ROLE name REPLICATION LOGIN.
327
328 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
329 '';
330 default = null;
331 inherit defaultText;
332 };
333 bypassrls = mkOption {
334 type = types.nullOr types.bool;
335 description = lib.mdDoc ''
336 Grants the user, created by the ensureUser attr, replication permissions. From the postgres docs:
337
338 A role must be explicitly given permission to bypass
339 every row-level security (RLS) policy (except for
340 superusers, since those bypass all permission checks). To
341 create such a role, use CREATE ROLE name BYPASSRLS as a
342 superuser.
343
344 More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
345 '';
346 default = null;
347 inherit defaultText;
348 };
349 };
350 };
351 };
352 };
353 });
354 default = [];
355 description = lib.mdDoc ''
356 Ensures that the specified users exist.
357 The PostgreSQL users will be identified using peer authentication. This authenticates the Unix user with the
358 same name only, and that without the need for a password.
359 This option will never delete existing users or remove DB ownership of databases
360 once granted with `ensureDBOwnership = true;`. This means that this must be
361 cleaned up manually when changing after changing the config in here.
362 '';
363 example = literalExpression ''
364 [
365 {
366 name = "nextcloud";
367 }
368 {
369 name = "superuser";
370 ensureDBOwnership = true;
371 }
372 ]
373 '';
374 };
375
376 enableTCPIP = mkOption {
377 type = types.bool;
378 default = false;
379 description = lib.mdDoc ''
380 Whether PostgreSQL should listen on all network interfaces.
381 If disabled, the database can only be accessed via its Unix
382 domain socket or via TCP connections to localhost.
383 '';
384 };
385
386 logLinePrefix = mkOption {
387 type = types.str;
388 default = "[%p] ";
389 example = "%m [%p] ";
390 description = lib.mdDoc ''
391 A printf-style string that is output at the beginning of each log line.
392 Upstream default is `'%m [%p] '`, i.e. it includes the timestamp. We do
393 not include the timestamp, because journal has it anyway.
394 '';
395 };
396
397 extraPlugins = mkOption {
398 type = types.listOf types.path;
399 default = [];
400 example = literalExpression "with pkgs.postgresql_15.pkgs; [ postgis pg_repack ]";
401 description = lib.mdDoc ''
402 List of PostgreSQL plugins. PostgreSQL version for each plugin should
403 match version for `services.postgresql.package` value.
404 '';
405 };
406
407 settings = mkOption {
408 type = with types; attrsOf (oneOf [ bool float int str ]);
409 default = {};
410 description = lib.mdDoc ''
411 PostgreSQL configuration. Refer to
412 <https://www.postgresql.org/docs/15/config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE>
413 for an overview of `postgresql.conf`.
414
415 ::: {.note}
416 String values will automatically be enclosed in single quotes. Single quotes will be
417 escaped with two single quotes as described by the upstream documentation linked above.
418 :::
419 '';
420 example = literalExpression ''
421 {
422 log_connections = true;
423 log_statement = "all";
424 logging_collector = true;
425 log_disconnections = true;
426 log_destination = lib.mkForce "syslog";
427 }
428 '';
429 };
430
431 recoveryConfig = mkOption {
432 type = types.nullOr types.lines;
433 default = null;
434 description = lib.mdDoc ''
435 Contents of the {file}`recovery.conf` file.
436 '';
437 };
438
439 superUser = mkOption {
440 type = types.str;
441 default = "postgres";
442 internal = true;
443 readOnly = true;
444 description = lib.mdDoc ''
445 PostgreSQL superuser account to use for various operations. Internal since changing
446 this value would lead to breakage while setting up databases.
447 '';
448 };
449 };
450
451 };
452
453
454 ###### implementation
455
456 config = mkIf cfg.enable {
457
458 assertions = map ({ name, ensureDBOwnership, ... }: {
459 assertion = ensureDBOwnership -> builtins.elem name cfg.ensureDatabases;
460 message = ''
461 For each database user defined with `services.postgresql.ensureUsers` and
462 `ensureDBOwnership = true;`, a database with the same name must be defined
463 in `services.postgresql.ensureDatabases`.
464
465 Offender: ${name} has not been found among databases.
466 '';
467 }) cfg.ensureUsers;
468 # `ensurePermissions` is now deprecated, let's avoid it.
469 warnings = lib.optional (any ({ ensurePermissions, ... }: ensurePermissions != {}) cfg.ensureUsers) "
470 `services.postgresql.ensureUsers.*.ensurePermissions` is used in your expressions,
471 this option is known to be broken with newer PostgreSQL versions,
472 consider migrating to `services.postgresql.ensureUsers.*.ensureDBOwnership` or
473 consult the release notes or manual for more migration guidelines.
474
475 This option will be removed in NixOS 24.05 unless it sees significant
476 maintenance improvements.
477 ";
478
479 services.postgresql.settings =
480 {
481 hba_file = "${pkgs.writeText "pg_hba.conf" cfg.authentication}";
482 ident_file = "${pkgs.writeText "pg_ident.conf" cfg.identMap}";
483 log_destination = "stderr";
484 log_line_prefix = cfg.logLinePrefix;
485 listen_addresses = if cfg.enableTCPIP then "*" else "localhost";
486 port = cfg.port;
487 jit = mkDefault (if cfg.enableJIT then "on" else "off");
488 };
489
490 services.postgresql.package = let
491 mkThrow = ver: throw "postgresql_${ver} was removed, please upgrade your postgresql version.";
492 base = if versionAtLeast config.system.stateVersion "23.11" then pkgs.postgresql_15
493 else if versionAtLeast config.system.stateVersion "22.05" then pkgs.postgresql_14
494 else if versionAtLeast config.system.stateVersion "21.11" then pkgs.postgresql_13
495 else if versionAtLeast config.system.stateVersion "20.03" then mkThrow "11"
496 else if versionAtLeast config.system.stateVersion "17.09" then mkThrow "9_6"
497 else mkThrow "9_5";
498 in
499 # Note: when changing the default, make it conditional on
500 # ‘system.stateVersion’ to maintain compatibility with existing
501 # systems!
502 mkDefault (if cfg.enableJIT then base.withJIT else base);
503
504 services.postgresql.dataDir = mkDefault "/var/lib/postgresql/${cfg.package.psqlSchema}";
505
506 services.postgresql.authentication = mkMerge [
507 (mkBefore "# Generated file; do not edit!")
508 (mkAfter
509 ''
510 # default value of services.postgresql.authentication
511 local all all peer
512 host all all 127.0.0.1/32 md5
513 host all all ::1/128 md5
514 '')
515 ];
516
517 users.users.postgres =
518 { name = "postgres";
519 uid = config.ids.uids.postgres;
520 group = "postgres";
521 description = "PostgreSQL server user";
522 home = "${cfg.dataDir}";
523 useDefaultShell = true;
524 };
525
526 users.groups.postgres.gid = config.ids.gids.postgres;
527
528 environment.systemPackages = [ postgresql ];
529
530 environment.pathsToLink = [
531 "/share/postgresql"
532 ];
533
534 system.checks = lib.optional (cfg.checkConfig && pkgs.stdenv.hostPlatform == pkgs.stdenv.buildPlatform) configFileCheck;
535
536 systemd.services.postgresql =
537 { description = "PostgreSQL Server";
538
539 wantedBy = [ "multi-user.target" ];
540 after = [ "network.target" ];
541
542 environment.PGDATA = cfg.dataDir;
543
544 path = [ postgresql ];
545
546 preStart =
547 ''
548 if ! test -e ${cfg.dataDir}/PG_VERSION; then
549 # Cleanup the data directory.
550 rm -f ${cfg.dataDir}/*.conf
551
552 # Initialise the database.
553 initdb -U ${cfg.superUser} ${concatStringsSep " " cfg.initdbArgs}
554
555 # See postStart!
556 touch "${cfg.dataDir}/.first_startup"
557 fi
558
559 ln -sfn "${configFile}/postgresql.conf" "${cfg.dataDir}/postgresql.conf"
560 ${optionalString (cfg.recoveryConfig != null) ''
561 ln -sfn "${pkgs.writeText "recovery.conf" cfg.recoveryConfig}" \
562 "${cfg.dataDir}/recovery.conf"
563 ''}
564 '';
565
566 # Wait for PostgreSQL to be ready to accept connections.
567 postStart =
568 ''
569 PSQL="psql --port=${toString cfg.port}"
570
571 while ! $PSQL -d postgres -c "" 2> /dev/null; do
572 if ! kill -0 "$MAINPID"; then exit 1; fi
573 sleep 0.1
574 done
575
576 if test -e "${cfg.dataDir}/.first_startup"; then
577 ${optionalString (cfg.initialScript != null) ''
578 $PSQL -f "${cfg.initialScript}" -d postgres
579 ''}
580 rm -f "${cfg.dataDir}/.first_startup"
581 fi
582 '' + optionalString (cfg.ensureDatabases != []) ''
583 ${concatMapStrings (database: ''
584 $PSQL -tAc "SELECT 1 FROM pg_database WHERE datname = '${database}'" | grep -q 1 || $PSQL -tAc 'CREATE DATABASE "${database}"'
585 '') cfg.ensureDatabases}
586 '' + ''
587 ${
588 concatMapStrings
589 (user:
590 let
591 userPermissions = concatStringsSep "\n"
592 (mapAttrsToList
593 (database: permission: ''$PSQL -tAc 'GRANT ${permission} ON ${database} TO "${user.name}"' '')
594 user.ensurePermissions
595 );
596 dbOwnershipStmt = optionalString
597 user.ensureDBOwnership
598 ''$PSQL -tAc 'ALTER DATABASE "${user.name}" OWNER TO "${user.name}";' '';
599
600 filteredClauses = filterAttrs (name: value: value != null) user.ensureClauses;
601
602 clauseSqlStatements = attrValues (mapAttrs (n: v: if v then n else "no${n}") filteredClauses);
603
604 userClauses = ''$PSQL -tAc 'ALTER ROLE "${user.name}" ${concatStringsSep " " clauseSqlStatements}' '';
605 in ''
606 $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${user.name}'" | grep -q 1 || $PSQL -tAc 'CREATE USER "${user.name}"'
607 ${userPermissions}
608 ${userClauses}
609
610 ${dbOwnershipStmt}
611 ''
612 )
613 cfg.ensureUsers
614 }
615 '';
616
617 serviceConfig = mkMerge [
618 { ExecReload = "${pkgs.coreutils}/bin/kill -HUP $MAINPID";
619 User = "postgres";
620 Group = "postgres";
621 RuntimeDirectory = "postgresql";
622 Type = if versionAtLeast cfg.package.version "9.6"
623 then "notify"
624 else "simple";
625
626 # Shut down Postgres using SIGINT ("Fast Shutdown mode"). See
627 # https://www.postgresql.org/docs/current/server-shutdown.html
628 KillSignal = "SIGINT";
629 KillMode = "mixed";
630
631 # Give Postgres a decent amount of time to clean up after
632 # receiving systemd's SIGINT.
633 TimeoutSec = 120;
634
635 ExecStart = "${postgresql}/bin/postgres";
636 }
637 (mkIf (cfg.dataDir == "/var/lib/postgresql/${cfg.package.psqlSchema}") {
638 StateDirectory = "postgresql postgresql/${cfg.package.psqlSchema}";
639 StateDirectoryMode = if groupAccessAvailable then "0750" else "0700";
640 })
641 ];
642
643 unitConfig.RequiresMountsFor = "${cfg.dataDir}";
644 };
645
646 };
647
648 meta.doc = ./postgresql.md;
649 meta.maintainers = with lib.maintainers; [ thoughtpolice danbst ];
650}