Cleanup PostgreSQL for state version 17.09 (#25753)

* postgresql service: make 9.6 the default version for 17.09

* postgresql service: change default superuser for 17.09

Change the default superuser from `root` to `postgres` for state
version 17.09

* postgresql service: change default data directory for 17.09

The new directory includes the schema version of the database.
This makes upgrades easier and is more consistent with other distros.

* updated nixos release notes

1 files changed, 15 insertions, 5 deletions

diff --git a/nixos/modules/services/databases/postgresql.nix b/nixos/modules/services/databases/postgresql.nix
index 24ef4637ec98..d06e03a52978 100644
--- a/nixos/modules/services/databases/postgresql.nix
+++ b/nixos/modules/services/databases/postgresql.nix
@@ -38,6 +38,10 @@ let
 
   pre84 = versionOlder (builtins.parseDrvName postgresql.name).version "8.4";
 
+  # NixOS traditionally used `root` as superuser, most other distros use `postgres`. From 17.09
+  # we also try to follow this standard
+  superuser = (if versionAtLeast config.system.stateVersion "17.09" then "postgres" else "root");
+
 in
 
 {
@@ -74,7 +78,7 @@ in
 
       dataDir = mkOption {
         type = types.path;
-        default = "/var/db/postgresql";
+        example = "/var/lib/postgresql/9.6";
         description = ''
           Data directory for PostgreSQL.
         '';
@@ -160,7 +164,13 @@ in
       # Note: when changing the default, make it conditional on
       # ‘system.stateVersion’ to maintain compatibility with existing
       # systems!
-      mkDefault (if versionAtLeast config.system.stateVersion "16.03" then pkgs.postgresql95 else pkgs.postgresql94);
+      mkDefault (if versionAtLeast config.system.stateVersion "17.09" then pkgs.postgresql96
+            else if versionAtLeast config.system.stateVersion "16.03" then pkgs.postgresql95
+            else pkgs.postgresql94);
+
+    services.postgresql.dataDir =
+      mkDefault (if versionAtLeast config.system.stateVersion "17.09" then "/var/lib/postgresql/${config.services.postgresql.package.psqlSchema}"
+                 else "/var/db/postgresql");
 
     services.postgresql.authentication = mkAfter
       ''
@@ -205,7 +215,7 @@ in
           ''
             # Initialise the database.
             if ! test -e ${cfg.dataDir}/PG_VERSION; then
-              initdb -U root
+              initdb -U ${superuser}
               # See postStart!
               touch "${cfg.dataDir}/.first_startup"
             fi
@@ -237,14 +247,14 @@ in
         # Wait for PostgreSQL to be ready to accept connections.
         postStart =
           ''
-            while ! psql --port=${toString cfg.port} postgres -c "" 2> /dev/null; do
+            while ! ${pkgs.sudo}/bin/sudo -u ${superuser} psql --port=${toString cfg.port} -d postgres -c "" 2> /dev/null; do
                 if ! kill -0 "$MAINPID"; then exit 1; fi
                 sleep 0.1
             done
 
             if test -e "${cfg.dataDir}/.first_startup"; then
               ${optionalString (cfg.initialScript != null) ''
-                psql -f "${cfg.initialScript}" --port=${toString cfg.port} postgres
+                ${pkgs.sudo}/bin/sudo -u ${superuser} psql -f "${cfg.initialScript}" --port=${toString cfg.port} -d postgres
               ''}
               rm -f "${cfg.dataDir}/.first_startup"
             fi