postfixadmin: fix db owner

1 files changed, 38 insertions, 4 deletions

diff --git a/nixos/modules/services/mail/postfixadmin.nix b/nixos/modules/services/mail/postfixadmin.nix
index 96aa2408987..967e0e24055 100644
--- a/nixos/modules/services/mail/postfixadmin.nix
+++ b/nixos/modules/services/mail/postfixadmin.nix
@@ -125,14 +125,48 @@ in
 
     services.postgresql = mkIf localDB {
       enable = true;
-      ensureDatabases = [ cfg.database.dbname ];
       ensureUsers = [ {
         name = cfg.database.username;
-        ensurePermissions = {
-          "DATABASE ${cfg.database.username}" = "ALL PRIVILEGES";
-        };
       } ];
     };
+    # The postgresql module doesn't currently support concepts like
+    # objects owners and extensions; for now we tack on what's needed
+    # here.
+    systemd.services.postfixadmin-postgres = let pgsql = config.services.postgresql; in mkIf localDB {
+      after = [ "postgresql.service" ];
+      bindsTo = [ "postgresql.service" ];
+      wantedBy = [ "multi-user.target" ];
+      path = [
+        pgsql.package
+        pkgs.utillinux
+      ];
+      script = ''
+        set -eu
+
+        PSQL() {
+            psql --port=${toString pgsql.port} "$@"
+        }
+
+        PSQL -tAc "SELECT 1 FROM pg_database WHERE datname = '${cfg.database.dbname}'" | grep -q 1 || PSQL -tAc 'CREATE DATABASE "${cfg.database.dbname}" OWNER "${cfg.database.username}"'
+        current_owner=$(PSQL -tAc "SELECT pg_catalog.pg_get_userbyid(datdba) FROM pg_catalog.pg_database WHERE datname = '${cfg.database.dbname}'")
+        if [[ "$current_owner" != "${cfg.database.username}" ]]; then
+            PSQL -tAc 'ALTER DATABASE "${cfg.database.dbname}" OWNER TO "${cfg.database.username}"'
+            if [[ -e "${config.services.postgresql.dataDir}/.reassigning_${cfg.database.dbname}" ]]; then
+                echo "Reassigning ownership of database ${cfg.database.dbname} to user ${cfg.database.username} failed on last boot. Failing..."
+                exit 1
+            fi
+            touch "${config.services.postgresql.dataDir}/.reassigning_${cfg.database.dbname}"
+            PSQL "${cfg.database.dbname}" -tAc "REASSIGN OWNED BY \"$current_owner\" TO \"${cfg.database.username}\""
+            rm "${config.services.postgresql.dataDir}/.reassigning_${cfg.database.dbname}"
+        fi
+      '';
+
+      serviceConfig = {
+        User = pgsql.superUser;
+        Type = "oneshot";
+        RemainAfterExit = true;
+      };
+    };
 
     users.users.${user} = mkIf localDB {
       group = user;