nixos/postgresql: drop ensurePermissions option

...effectively what was planned already in #266270, but it was too late
because the branches were restricted and didn't allow any breaking
changes anymore.

It also suffers from the same issue that we already had when discussing
this the last time[1] when `ensureDBOwnership` was ultimately introduced
as band-aid fix: newly created users don't get CREATE permission on
the `public` schema anymore (since psql 15), even with `ALL PRIVILEGES`.

If one's use-case is more sophisticated than having a single owner, it's
questionable anyways if this module is the correct tool since
permissions aren't dropped on a change to this option or a removal which
is pretty surprising in the context of NixOS.

[1] https://github.com/NixOS/nixpkgs/pull/266270

2 files changed, 5 insertions, 43 deletions

diff --git a/nixos/doc/manual/release-notes/rl-2405.section.md b/nixos/doc/manual/release-notes/rl-2405.section.md
index bbe59b002003..e2569f4d937c 100644
--- a/nixos/doc/manual/release-notes/rl-2405.section.md
+++ b/nixos/doc/manual/release-notes/rl-2405.section.md
@@ -91,6 +91,11 @@ The pre-existing [services.ankisyncd](#opt-services.ankisyncd.enable) has been m
   [v0.31](https://github.com/derailed/k9s/releases/tag/v0.31.0) for details. It is recommended
   to back up your current configuration and let k9s recreate the new base configuration.
 
+- The option `services.postgresql.ensureUsers._.ensurePermissions` has been removed as it's
+  not declarative and is broken with newer postgresql versions. Consider using
+  [](#opt-services.postgresql.ensureUsers._.ensureDBOwnership)
+  instead or a tool that's more suited for managing the data inside a postgresql database.
+
 - `idris2` was updated to v0.7.0. This version introduces breaking changes. Check out the [changelog](https://github.com/idris-lang/Idris2/blob/v0.7.0/CHANGELOG.md#v070) for details.
 
 - `neo4j` has been updated to 5, you may want to read the [release notes for Neo4j 5](https://neo4j.com/release-notes/database/neo4j-5/)
diff --git a/nixos/modules/services/databases/postgresql.nix b/nixos/modules/services/databases/postgresql.nix
index ed5915735730..c4e76c82ba5c 100644
--- a/nixos/modules/services/databases/postgresql.nix
+++ b/nixos/modules/services/databases/postgresql.nix
@@ -161,33 +161,6 @@ 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
-                multiple comma-separated SQL privileges here.
-
-                For more information on how to specify the target
-                and on which privileges exist, see the
-                [GRANT syntax](https://www.postgresql.org/docs/current/sql-grant.html).
-                The attributes are used as `GRANT ''${attrValue} ON ''${attrName}`.
-              '';
-              example = literalExpression ''
-                {
-                  "DATABASE \"nextcloud\"" = "ALL PRIVILEGES";
-                  "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES";
-                }
-              '';
-            };
-
             ensureDBOwnership = mkOption {
               type = types.bool;
               default = false;
@@ -460,16 +433,6 @@ in
         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.ensureUsers.*.ensurePermissions` is used in your expressions,
-      this option is known to be broken with newer PostgreSQL versions,
-      consider migrating to `services.postgresql.ensureUsers.*.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 =
       {
@@ -583,11 +546,6 @@ in
               concatMapStrings
               (user:
               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}";' '';
@@ -599,7 +557,6 @@ in
                   userClauses = ''$PSQL -tAc 'ALTER ROLE "${user.name}" ${concatStringsSep " " clauseSqlStatements}' '';
                 in ''
                   $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${user.name}'" | grep -q 1 || $PSQL -tAc 'CREATE USER "${user.name}"'
-                  ${userPermissions}
                   ${userClauses}
 
                   ${dbOwnershipStmt}