# PostgreSQL {#module-postgresql}

<!-- FIXME: render nicely -->
<!-- FIXME: source can be added automatically -->

*Source:* {file}`modules/services/databases/postgresql.nix`

*Upstream documentation:* <https://www.postgresql.org/docs/>

<!-- FIXME: more stuff, like maintainer? -->

PostgreSQL is an advanced, free, relational database.
<!-- MORE -->

## Configuring {#module-services-postgres-configuring}

To enable PostgreSQL, add the following to your {file}`configuration.nix`:
```nix
{
  services.postgresql.enable = true;
  services.postgresql.package = pkgs.postgresql_15;
}
```

The default PostgreSQL version is approximately the latest major version available on the NixOS release matching your [`system.stateVersion`](#opt-system.stateVersion).
This is because PostgreSQL upgrades require a manual migration process (see below).
Hence, upgrades must happen by setting [`services.postgresql.package`](#opt-services.postgresql.package) explicitly.

<!--
After running {command}`nixos-rebuild`, you can verify
whether PostgreSQL works by running {command}`psql`:

```ShellSession
$ psql
psql (9.2.9)
Type "help" for help.

alice=>
```
-->

By default, PostgreSQL stores its databases in {file}`/var/lib/postgresql/$psqlSchema`. You can override this using [](#opt-services.postgresql.dataDir), e.g.
```nix
{ services.postgresql.dataDir = "/data/postgresql"; }
```

## Initializing {#module-services-postgres-initializing}

As of NixOS 24.05,
`services.postgresql.ensureUsers.*.ensurePermissions` has been
removed, after a change to default permissions in PostgreSQL 15
invalidated most of its previous use cases:

- In psql < 15, `ALL PRIVILEGES` used to include `CREATE TABLE`, where
  in psql >= 15 that would be a separate permission
- psql >= 15 instead gives only the database owner create permissions
- Even on psql < 15 (or databases migrated to >= 15), it is
  recommended to manually assign permissions along these lines
  - <https://www.postgresql.org/docs/release/15.0/>
  - <https://www.postgresql.org/docs/15/ddl-schemas.html#DDL-SCHEMAS-PRIV>

### Assigning ownership {#module-services-postgres-initializing-ownership}

Usually, the database owner should be a database user of the same
name. This can be done with
`services.postgresql.ensureUsers.*.ensureDBOwnership = true;`.

If the database user name equals the connecting system user name,
postgres by default will accept a passwordless connection via unix
domain socket. This makes it possible to run many postgres-backed
services without creating any database secrets at all.

### Assigning extra permissions {#module-services-postgres-initializing-extra-permissions}

For many cases, it will be enough to have the database user be the
owner. Until `services.postgresql.ensureUsers.*.ensurePermissions` has
been re-thought, if more users need access to the database, please use
one of the following approaches:

**WARNING:** `services.postgresql.initialScript` is not recommended
for `ensurePermissions` replacement, as that is *only run on first
start of PostgreSQL*.

**NOTE:** all of these methods may be obsoleted, when `ensure*` is
reworked, but it is expected that they will stay viable for running
database migrations.

**NOTE:** please make sure that any added migrations are idempotent (re-runnable).

#### in database's setup `postStart` {#module-services-postgres-initializing-extra-permissions-superuser-post-start}

`ensureUsers` is run in `postgresql-setup`, so this is where `postStart` must be added to:

```nix
{
  systemd.services.postgresql-setup.postStart = ''
    psql service1 -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"'
    psql service1 -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"'
    # ....
  '';
}
```

#### in intermediate oneshot service {#module-services-postgres-initializing-extra-permissions-superuser-oneshot}

Make sure to run this service after `postgresql.target`, not `postgresql.service`.

They differ in two aspects:
- `postgresql.target` includes `postgresql-setup`, so users managed via `ensureUsers` are already created.