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