pyrox.dev
1# PostgreSQL {#module-postgresql}
2
3<!-- FIXME: render nicely -->
4<!-- FIXME: source can be added automatically -->
5
6*Source:* {file}`modules/services/databases/postgresql.nix`
7
8*Upstream documentation:* <https://www.postgresql.org/docs/>
9
10<!-- FIXME: more stuff, like maintainer? -->
11
12PostgreSQL is an advanced, free, relational database.
13<!-- MORE -->
14
15## Configuring {#module-services-postgres-configuring}
16
17To enable PostgreSQL, add the following to your {file}`configuration.nix`:
18```nix
19{
20 services.postgresql.enable = true;
21 services.postgresql.package = pkgs.postgresql_15;
22}
23```
24Note that you are required to specify the desired version of PostgreSQL (e.g. `pkgs.postgresql_15`). Since upgrading your PostgreSQL version requires a database dump and reload (see below), NixOS cannot provide a default value for [](#opt-services.postgresql.package) such as the most recent release of PostgreSQL.
25
26<!--
27After running {command}`nixos-rebuild`, you can verify
28whether PostgreSQL works by running {command}`psql`:
29
30```ShellSession
31$ psql
32psql (9.2.9)
33Type "help" for help.
34
35alice=>
36```
37-->
38
39By default, PostgreSQL stores its databases in {file}`/var/lib/postgresql/$psqlSchema`. You can override this using [](#opt-services.postgresql.dataDir), e.g.
40```nix
41{
42 services.postgresql.dataDir = "/data/postgresql";
43}
44```
45
46## Initializing {#module-services-postgres-initializing}
47
48As of NixOS 24.05,
49`services.postgresql.ensureUsers.*.ensurePermissions` has been
50removed, after a change to default permissions in PostgreSQL 15
51invalidated most of its previous use cases:
52
53- In psql < 15, `ALL PRIVILEGES` used to include `CREATE TABLE`, where
54 in psql >= 15 that would be a separate permission
55- psql >= 15 instead gives only the database owner create permissions
56- Even on psql < 15 (or databases migrated to >= 15), it is
57 recommended to manually assign permissions along these lines
58 - https://www.postgresql.org/docs/release/15.0/
59 - https://www.postgresql.org/docs/15/ddl-schemas.html#DDL-SCHEMAS-PRIV
60
61### Assigning ownership {#module-services-postgres-initializing-ownership}
62
63Usually, the database owner should be a database user of the same
64name. This can be done with
65`services.postgresql.ensureUsers.*.ensureDBOwnership = true;`.
66
67If the database user name equals the connecting system user name,
68postgres by default will accept a passwordless connection via unix
69domain socket. This makes it possible to run many postgres-backed
70services without creating any database secrets at all.
71
72### Assigning extra permissions {#module-services-postgres-initializing-extra-permissions}
73
74For many cases, it will be enough to have the database user be the
75owner. Until `services.postgresql.ensureUsers.*.ensurePermissions` has
76been re-thought, if more users need access to the database, please use
77one of the following approaches:
78
79**WARNING:** `services.postgresql.initialScript` is not recommended
80for `ensurePermissions` replacement, as that is *only run on first
81start of PostgreSQL*.
82
83**NOTE:** all of these methods may be obsoleted, when `ensure*` is
84reworked, but it is expected that they will stay viable for running
85database migrations.
86
87**NOTE:** please make sure that any added migrations are idempotent (re-runnable).
88
89#### as superuser {#module-services-postgres-initializing-extra-permissions-superuser}
90
91**Advantage:** compatible with postgres < 15, because it's run
92as the database superuser `postgres`.
93
94##### in database `postStart` {#module-services-postgres-initializing-extra-permissions-superuser-post-start}
95
96**Disadvantage:** need to take care of ordering yourself. In this
97example, `mkAfter` ensures that permissions are assigned after any
98databases from `ensureDatabases` and `extraUser1` from `ensureUsers`
99are already created.
100
101```nix
102 {
103 systemd.services.postgresql.postStart = lib.mkAfter ''
104 $PSQL service1 -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"'
105 $PSQL service1 -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"'
106 # ....
107 '';
108 }
109```
110
111##### in intermediate oneshot service {#module-services-postgres-initializing-extra-permissions-superuser-oneshot}
112
113```nix
114 {
115 systemd.services."migrate-service1-db1" = {
116 serviceConfig.Type = "oneshot";
117 requiredBy = "service1.service";
118 before = "service1.service";
119 after = "postgresql.service";
120 serviceConfig.User = "postgres";
121 environment.PSQL = "psql --port=${toString services.postgresql.settings.port}";
122 path = [ postgresql ];
123 script = ''
124 $PSQL service1 -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"'
125 $PSQL service1 -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"'
126 # ....
127 '';
128 };
129 }
130```
131
132#### as service user {#module-services-postgres-initializing-extra-permissions-service-user}
133
134**Advantage:** re-uses systemd's dependency ordering;
135
136**Disadvantage:** relies on service user having grant permission. To be combined with `ensureDBOwnership`.
137
138##### in service `preStart` {#module-services-postgres-initializing-extra-permissions-service-user-pre-start}
139
140```nix
141 {
142 environment.PSQL = "psql --port=${toString services.postgresql.settings.port}";
143 path = [ postgresql ];
144 systemd.services."service1".preStart = ''
145 $PSQL -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"'
146 $PSQL -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"'
147 # ....
148 '';
149 }
150```
151
152##### in intermediate oneshot service {#module-services-postgres-initializing-extra-permissions-service-user-oneshot}
153
154```nix
155 {
156 systemd.services."migrate-service1-db1" = {
157 serviceConfig.Type = "oneshot";
158 requiredBy = "service1.service";
159 before = "service1.service";
160 after = "postgresql.service";
161 serviceConfig.User = "service1";
162 environment.PSQL = "psql --port=${toString services.postgresql.settings.port}";
163 path = [ postgresql ];
164 script = ''
165 $PSQL -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"'
166 $PSQL -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"'
167 # ....
168 '';
169 };
170 }
171```
172
173## Authentication {#module-services-postgres-authentication}
174
175Local connections are made through unix sockets by default and support [peer authentication](https://www.postgresql.org/docs/current/auth-peer.html).
176This allows system users to login with database roles of the same name.
177For example, the `postgres` system user is allowed to login with the database role `postgres`.
178
179System users and database roles might not always match.
180In this case, to allow access for a service, you can create a [user name map](https://www.postgresql.org/docs/current/auth-username-maps.html) between system roles and an existing database role.
181
182### User Mapping {#module-services-postgres-authentication-user-mapping}
183
184Assume that your app creates a role `admin` and you want the `root` user to be able to login with it.
185You can then use [](#opt-services.postgresql.identMap) to define the map and [](#opt-services.postgresql.authentication) to enable it:
186
187```nix
188services.postgresql = {
189 identMap = ''
190 admin root admin
191 '';
192 authentication = ''
193 local all admin peer map=admin
194 '';
195}
196```
197
198::: {.warning}
199To avoid conflicts with other modules, you should never apply a map to `all` roles.
200Because PostgreSQL will stop on the first matching line in `pg_hba.conf`, a line matching all roles would lock out other services.
201Each module should only manage user maps for the database roles that belong to this module.
202Best practice is to name the map after the database role it manages to avoid name conflicts.
203:::
204
205## Upgrading {#module-services-postgres-upgrading}
206
207::: {.note}
208The steps below demonstrate how to upgrade from an older version to `pkgs.postgresql_13`.
209These instructions are also applicable to other versions.
210:::
211
212Major PostgreSQL upgrades require a downtime and a few imperative steps to be called. This is the case because
213each major version has some internal changes in the databases' state. Because of that,
214NixOS places the state into {file}`/var/lib/postgresql/<version>` where each `version`
215can be obtained like this:
216```
217$ nix-instantiate --eval -A postgresql_13.psqlSchema
218"13"
219```
220For an upgrade, a script like this can be used to simplify the process:
221```nix
222{ config, lib, pkgs, ... }:
223{
224 environment.systemPackages = [
225 (let
226 # XXX specify the postgresql package you'd like to upgrade to.
227 # Do not forget to list the extensions you need.
228 newPostgres = pkgs.postgresql_13.withPackages (pp: [
229 # pp.plv8
230 ]);
231 cfg = config.services.postgresql;
232 in pkgs.writeScriptBin "upgrade-pg-cluster" ''
233 set -eux
234 # XXX it's perhaps advisable to stop all services that depend on postgresql
235 systemctl stop postgresql
236
237 export NEWDATA="/var/lib/postgresql/${newPostgres.psqlSchema}"
238 export NEWBIN="${newPostgres}/bin"
239
240 export OLDDATA="${cfg.dataDir}"
241 export OLDBIN="${cfg.finalPackage}/bin"
242
243 install -d -m 0700 -o postgres -g postgres "$NEWDATA"
244 cd "$NEWDATA"
245 sudo -u postgres "$NEWBIN/initdb" -D "$NEWDATA" ${lib.escapeShellArgs cfg.initdbArgs}
246
247 sudo -u postgres "$NEWBIN/pg_upgrade" \
248 --old-datadir "$OLDDATA" --new-datadir "$NEWDATA" \
249 --old-bindir "$OLDBIN" --new-bindir "$NEWBIN" \
250 "$@"
251 '')
252 ];
253}
254```
255
256The upgrade process is:
257
258 1. Add the above to your {file}`configuration.nix` and rebuild. Alternatively, add that into a separate file and reference it in the `imports` list.
259 2. Login as root (`sudo su -`).
260 3. Run `upgrade-pg-cluster`. This will stop the old postgresql cluster, initialize a new one and migrate the old one to the new one. You may supply arguments like `--jobs 4` and `--link` to speedup the migration process. See <https://www.postgresql.org/docs/current/pgupgrade.html> for details.
261 4. Change the postgresql package in NixOS configuration to the one you were upgrading to via [](#opt-services.postgresql.package). Rebuild NixOS. This should start the new postgres version using the upgraded data directory and all services you stopped during the upgrade.
262 5. After the upgrade it's advisable to analyze the new cluster:
263
264 - For PostgreSQL ≥ 14, use the `vacuumdb` command printed by the upgrades script.
265 - For PostgreSQL < 14, run (as `su -l postgres` in the [](#opt-services.postgresql.dataDir), in this example {file}`/var/lib/postgresql/13`):
266
267 ```
268 $ ./analyze_new_cluster.sh
269 ```
270
271 ::: {.warning}
272 The next step removes the old state-directory!
273 :::
274
275 ```
276 $ ./delete_old_cluster.sh
277 ```
278
279## Versioning and End-of-Life {#module-services-postgres-versioning}
280
281PostgreSQL's versioning policy is described [here](https://www.postgresql.org/support/versioning/). TLDR:
282
283- Each major version is supported for 5 years.
284- Every three months there will be a new minor release, containing bug and security fixes.
285- For criticial/security fixes there could be more minor releases inbetween. This happens *very* infrequently.
286- After five years, a final minor version is released. This usually happens in early November.
287- After that a version is considered end-of-life (EOL).
288- Around February each year is the first time an EOL-release will not have received regular updates anymore.
289
290Technically, we'd not want to have EOL'ed packages in a stable NixOS release, which is to be supported until one month after the previous release. Thus, with NixOS' release schedule in May and November, the oldest PostgreSQL version in nixpkgs would have to be supported until December. It could be argued that a soon-to-be-EOL-ed version should thus be removed in May for the .05 release already. But since new security vulnerabilities are first disclosed in February of the following year, we agreed on keeping the oldest PostgreSQL major version around one more cycle in [#310580](https://github.com/NixOS/nixpkgs/pull/310580#discussion_r1597284693).
291
292Thus:
293- In September/October the new major version will be released and added to nixos-unstable.
294- In November the last minor version for the oldest major will be released.
295- Both the current stable .05 release and nixos-unstable should be updated to the latest minor that will usually be released in November.
296 - This is relevant for people who need to use this major for as long as possible. In that case its desirable to be able to pin nixpkgs to a commit that still has it, at the latest minor available.
297- In November, before branch-off for the .11 release and after the update to the latest minor, the EOL-ed major will be removed from nixos-unstable.
298
299This leaves a small gap of a couple of weeks after the latest minor release and the end of our support window for the .05 release, in which there could be an emergency release to other major versions of PostgreSQL - but not the oldest major we have in that branch. In that case: If we can't trivially patch the issue, we will mark the package/version as insecure **immediately**.
300
301## Options {#module-services-postgres-options}
302
303A complete list of options for the PostgreSQL module may be found [here](#opt-services.postgresql.enable).
304
305## Plugins {#module-services-postgres-plugins}
306
307The collection of plugins for each PostgreSQL version can be accessed with `.pkgs`. For example, for the `pkgs.postgresql_15` package, its plugin collection is accessed by `pkgs.postgresql_15.pkgs`:
308```ShellSession
309$ nix repl '<nixpkgs>'
310
311Loading '<nixpkgs>'...
312Added 10574 variables.
313
314nix-repl> postgresql_15.pkgs.<TAB><TAB>
315postgresql_15.pkgs.cstore_fdw postgresql_15.pkgs.pg_repack
316postgresql_15.pkgs.pg_auto_failover postgresql_15.pkgs.pg_safeupdate
317postgresql_15.pkgs.pg_bigm postgresql_15.pkgs.pg_similarity
318postgresql_15.pkgs.pg_cron postgresql_15.pkgs.pg_topn
319postgresql_15.pkgs.pg_hll postgresql_15.pkgs.pgjwt
320postgresql_15.pkgs.pg_partman postgresql_15.pkgs.pgroonga
321...
322```
323
324To add plugins via NixOS configuration, set `services.postgresql.extensions`:
325```nix
326{
327 services.postgresql.package = pkgs.postgresql_17;
328 services.postgresql.extensions = ps: with ps; [
329 pg_repack
330 postgis
331 ];
332}
333```
334
335You can build a custom `postgresql-with-plugins` (to be used outside of NixOS) using the function `.withPackages`. For example, creating a custom PostgreSQL package in an overlay can look like this:
336```nix
337self: super: {
338 postgresql_custom = self.postgresql_17.withPackages (ps: [
339 ps.pg_repack
340 ps.postgis
341 ]);
342}
343```
344
345Here's a recipe on how to override a particular plugin through an overlay:
346```nix
347self: super: {
348 postgresql_15 = super.postgresql_15// {
349 pkgs = super.postgresql_15.pkgs // {
350 pg_repack = super.postgresql_15.pkgs.pg_repack.overrideAttrs (_: {
351 name = "pg_repack-v20181024";
352 src = self.fetchzip {
353 url = "https://github.com/reorg/pg_repack/archive/923fa2f3c709a506e111cc963034bf2fd127aa00.tar.gz";
354 sha256 = "17k6hq9xaax87yz79j773qyigm4fwk8z4zh5cyp6z0sxnwfqxxw5";
355 };
356 });
357 };
358 };
359}
360```
361
362## Procedural Languages {#module-services-postgres-pls}
363
364PostgreSQL ships the additional procedural languages PL/Perl, PL/Python and PL/Tcl as extensions.
365They are packaged as plugins and can be made available in the same way as external extensions:
366```nix
367{
368 services.postgresql.extensions = ps: with ps; [
369 plperl
370 plpython3
371 pltcl
372 ];
373}
374```
375
376Each procedural language plugin provides a `.withPackages` helper to make language specific packages available at run-time.
377
378For example, to make `python3Packages.base58` available:
379```nix
380{
381 services.postgresql.extensions = pgps: with pgps; [
382 (plpython3.withPackages (pyps: with pyps; [ base58 ]))
383 ];
384}
385```
386
387This currently works for:
388- `plperl` by re-using `perl.withPackages`
389- `plpython3` by re-using `python3.withPackages`
390- `plr` by exposing `rPackages`
391- `pltcl` by exposing `tclPackages`
392
393## JIT (Just-In-Time compilation) {#module-services-postgres-jit}
394
395[JIT](https://www.postgresql.org/docs/current/jit-reason.html)-support in the PostgreSQL package
396is disabled by default because of the ~600MiB closure-size increase from the LLVM dependency. It
397can be optionally enabled in PostgreSQL with the following config option:
398
399```nix
400{
401 services.postgresql.enableJIT = true;
402}
403```
404
405This makes sure that the [`jit`](https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-JIT)-setting
406is set to `on` and a PostgreSQL package with JIT enabled is used. Further tweaking of the JIT compiler, e.g. setting a different
407query cost threshold via [`jit_above_cost`](https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-JIT-ABOVE-COST)
408can be done manually via [`services.postgresql.settings`](#opt-services.postgresql.settings).
409
410The attribute-names of JIT-enabled PostgreSQL packages are suffixed with `_jit`, i.e. for each `pkgs.postgresql`
411(and `pkgs.postgresql_<major>`) in `nixpkgs` there's also a `pkgs.postgresql_jit` (and `pkgs.postgresql_<major>_jit`).
412Alternatively, a JIT-enabled variant can be derived from a given `postgresql` package via `postgresql.withJIT`.
413This is also useful if it's not clear which attribute from `nixpkgs` was originally used (e.g. when working with
414[`config.services.postgresql.package`](#opt-services.postgresql.package) or if the package was modified via an
415overlay) since all modifications are propagated to `withJIT`. I.e.
416
417```nix
418with import <nixpkgs> {
419 overlays = [
420 (self: super: {
421 postgresql = super.postgresql.overrideAttrs (_: { pname = "foobar"; });
422 })
423 ];
424};
425postgresql.withJIT.pname
426```
427
428evaluates to `"foobar"`.
429
430## Service hardening {#module-services-postgres-hardening}
431
432The service created by the [`postgresql`-module](#opt-services.postgresql.enable) uses
433several common hardening options from `systemd`, most notably:
434
435* Memory pages must not be both writable and executable (this only applies to non-JIT setups).
436* A system call filter (see {manpage}`systemd.exec(5)` for details on `@system-service`).
437* A stricter default UMask (`0027`).
438* Only sockets of type `AF_INET`/`AF_INET6`/`AF_NETLINK`/`AF_UNIX` allowed.
439* Restricted filesystem access (private `/tmp`, most of the file-system hierarchy is mounted read-only, only process directories in `/proc` that are owned by the same user).
440 * When using [`TABLESPACE`](https://www.postgresql.org/docs/current/manage-ag-tablespaces.html)s, make sure to add the filesystem paths to `ReadWritePaths` like this:
441 ```nix
442 {
443 systemd.services.postgresql.serviceConfig.ReadWritePaths = [
444 "/path/to/tablespace/location"
445 ];
446 }
447 ```
448
449The NixOS module also contains necessary adjustments for extensions from `nixpkgs`,
450if these are enabled. If an extension or a postgresql feature from `nixpkgs` breaks
451with hardening, it's considered a bug.
452
453When using extensions that are not packaged in `nixpkgs`, hardening adjustments may
454become necessary.
455
456## Notable differences to upstream {#module-services-postgres-upstream-deviation}
457
458- To avoid circular dependencies between default and -dev outputs, the output of the `pg_config` system view has been removed.