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