diff options
Diffstat (limited to 'nixos/modules/services/databases')
| -rw-r--r-- | nixos/modules/services/databases/postgresql.md | 119 | ||||
| -rw-r--r-- | nixos/modules/services/databases/postgresql.nix | 58 |
2 files changed, 166 insertions, 11 deletions
diff --git a/nixos/modules/services/databases/postgresql.md b/nixos/modules/services/databases/postgresql.md index d65d9616e2f..e5e0b7efec2 100644 --- a/nixos/modules/services/databases/postgresql.md +++ b/nixos/modules/services/databases/postgresql.md @@ -39,6 +39,125 @@ By default, PostgreSQL stores its databases in {file}`/var/lib/postgresql/$psqlS services.postgresql.dataDir = "/data/postgresql"; ``` +## Initializing {#module-services-postgres-initializing} + +As of NixOS 23.11, +`services.postgresql.ensureUsers.*.ensurePermissions` has been +deprecated, 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). + +#### as superuser {#module-services-postgres-initializing-extra-permissions-superuser} + +**Advantage:** compatible with postgres < 15, because it's run +as the database superuser `postgres`. + +##### in database `postStart` {#module-services-postgres-initializing-extra-permissions-superuser-post-start} + +**Disadvantage:** need to take care of ordering yourself. In this +example, `mkAfter` ensures that permissions are assigned after any +databases from `ensureDatabases` and `extraUser1` from `ensureUsers` +are already created. + +```nix + systemd.services.postgresql.postStart = lib.mkAfter '' + $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} + +```nix + systemd.services."migrate-service1-db1" = { + serviceConfig.Type = "oneshot"; + requiredBy = "service1.service"; + before = "service1.service"; + after = "postgresql.service"; + serviceConfig.User = "postgres"; + environment.PSQL = "psql --port=${toString services.postgresql.port}"; + path = [ postgresql ]; + script = '' + $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"' + # .... + ''; + }; +``` + +#### as service user {#module-services-postgres-initializing-extra-permissions-service-user} + +**Advantage:** re-uses systemd's dependency ordering; + +**Disadvantage:** relies on service user having grant permission. To be combined with `ensureDBOwnership`. + +##### in service `preStart` {#module-services-postgres-initializing-extra-permissions-service-user-pre-start} + +```nix + environment.PSQL = "psql --port=${toString services.postgresql.port}"; + path = [ postgresql ]; + systemd.services."service1".preStart = '' + $PSQL -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"' + $PSQL -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"' + # .... + ''; +``` + +##### in intermediate oneshot service {#module-services-postgres-initializing-extra-permissions-service-user-oneshot} + +```nix + systemd.services."migrate-service1-db1" = { + serviceConfig.Type = "oneshot"; + requiredBy = "service1.service"; + before = "service1.service"; + after = "postgresql.service"; + serviceConfig.User = "service1"; + environment.PSQL = "psql --port=${toString services.postgresql.port}"; + path = [ postgresql ]; + script = '' + $PSQL -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"' + $PSQL -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"' + # .... + ''; + }; +``` + ## Upgrading {#module-services-postgres-upgrading} ::: {.note} diff --git a/nixos/modules/services/databases/postgresql.nix b/nixos/modules/services/databases/postgresql.nix index af4db5c9611..a9067d5974a 100644 --- a/nixos/modules/services/databases/postgresql.nix +++ b/nixos/modules/services/databases/postgresql.nix @@ -168,7 +168,12 @@ in ensurePermissions = mkOption { type = types.attrsOf types.str; default = {}; + visible = false; # This option has been deprecated. description = lib.mdDoc '' + This option is DEPRECATED and should not be used in nixpkgs anymore, + use `ensureDBOwnership` instead. It can also break with newer + versions of PostgreSQL (≥ 15). + Permissions to ensure for the user, specified as an attribute set. The attribute names specify the database and tables to grant the permissions for. The attribute values specify the permissions to grant. You may specify one or @@ -187,6 +192,16 @@ in ''; }; + ensureDBOwnership = mkOption { + type = types.bool; + default = false; + description = mdDoc '' + Grants the user ownership to a database with the same name. + This database must be defined manually in + [](#opt-services.postgresql.ensureDatabases). + ''; + }; + ensureClauses = mkOption { description = lib.mdDoc '' An attrset of clauses to grant to the user. Under the hood this uses the @@ -338,26 +353,21 @@ in }); default = []; description = lib.mdDoc '' - Ensures that the specified users exist and have at least the ensured permissions. + Ensures that the specified users exist. The PostgreSQL users will be identified using peer authentication. This authenticates the Unix user with the same name only, and that without the need for a password. - This option will never delete existing users or remove permissions, especially not when the value of this - option is changed. This means that users created and permissions assigned once through this option or - otherwise have to be removed manually. + This option will never delete existing users or remove DB ownership of databases + once granted with `ensureDBOwnership = true;`. This means that this must be + cleaned up manually when changing after changing the config in here. ''; example = literalExpression '' [ { name = "nextcloud"; - ensurePermissions = { - "DATABASE nextcloud" = "ALL PRIVILEGES"; - }; } { name = "superuser"; - ensurePermissions = { - "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES"; - }; + ensureDBOwnership = true; } ] ''; @@ -445,6 +455,27 @@ in config = mkIf cfg.enable { + assertions = map ({ name, ensureDBOwnership, ... }: { + assertion = ensureDBOwnership -> builtins.elem name cfg.ensureDatabases; + message = '' + For each database user defined with `services.postgresql.ensureUsers` and + `ensureDBOwnership = true;`, a database with the same name must be defined + in `services.postgresql.ensureDatabases`. + + Offender: ${name} has not been found among databases. + ''; + }) cfg.ensureUsers; + # `ensurePermissions` is now deprecated, let's avoid it. + warnings = lib.optional (any ({ ensurePermissions, ... }: ensurePermissions != {}) cfg.ensureUsers) " + `services.postgresql.*.ensurePermissions` is used in your expressions, + this option is known to be broken with newer PostgreSQL versions, + consider migrating to `services.postgresql.*.ensureDBOwnership` or + consult the release notes or manual for more migration guidelines. + + This option will be removed in NixOS 24.05 unless it sees significant + maintenance improvements. + "; + services.postgresql.settings = { hba_file = "${pkgs.writeText "pg_hba.conf" cfg.authentication}"; @@ -556,12 +587,15 @@ in ${ concatMapStrings (user: - let + let userPermissions = concatStringsSep "\n" (mapAttrsToList (database: permission: ''$PSQL -tAc 'GRANT ${permission} ON ${database} TO "${user.name}"' '') user.ensurePermissions ); + dbOwnershipStmt = optionalString + user.ensureDBOwnership + ''$PSQL -tAc 'ALTER DATABASE "${user.name}" OWNER TO "${user.name}";' ''; filteredClauses = filterAttrs (name: value: value != null) user.ensureClauses; @@ -572,6 +606,8 @@ in $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${user.name}'" | grep -q 1 || $PSQL -tAc 'CREATE USER "${user.name}"' ${userPermissions} ${userClauses} + + ${dbOwnershipStmt} '' ) cfg.ensureUsers |