nixos/postgresql: added new options to mimic mysql module

1 files changed, 89 insertions, 2 deletions

diff --git a/nixos/modules/services/databases/postgresql.nix b/nixos/modules/services/databases/postgresql.nix
index aeab445a998..5dd6392f994 100644
--- a/nixos/modules/services/databases/postgresql.nix
+++ b/nixos/modules/services/databases/postgresql.nix
@@ -105,6 +105,80 @@ in
         '';
       };
 
+      ensureDatabases = mkOption {
+        type = types.listOf types.str;
+        default = [];
+        description = ''
+          Ensures that the specified databases exist.
+          This option will never delete existing databases, especially not when the value of this
+          option is changed. This means that databases created once through this option or
+          otherwise have to be removed manually.
+        '';
+        example = [
+          "gitea"
+          "nextcloud"
+        ];
+      };
+
+      ensureUsers = mkOption {
+        type = types.listOf (types.submodule {
+          options = {
+            name = mkOption {
+              type = types.str;
+              description = ''
+                Name of the user to ensure.
+              '';
+            };
+            ensurePermissions = mkOption {
+              type = types.attrsOf types.str;
+              default = {};
+              description = ''
+                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
+                <link xlink:href="https://www.postgresql.org/docs/current/sql-grant.html">GRANT syntax</link>.
+                The attributes are used as <code>GRANT ''${attrName} ON ''${attrValue}</code>.
+              '';
+              example = literalExample ''
+                {
+                  "DATABASE nextcloud" = "ALL PRIVILEGES";
+                  "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES";
+                }
+              '';
+            };
+          };
+        });
+        default = [];
+        description = ''
+          Ensures that the specified users exist and have at least the ensured permissions.
+          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.
+        '';
+        example = literalExample ''
+          [
+            {
+              name = "nextcloud";
+              ensurePermissions = {
+                "DATABASE nextcloud" = "ALL PRIVILEGES";
+              };
+            }
+            {
+              name = "superuser";
+              ensurePermissions = {
+                "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES";
+              };
+            }
+          ]
+        '';
+      };
+
       enableTCPIP = mkOption {
         type = types.bool;
         default = false;
@@ -255,17 +329,30 @@ in
         # Wait for PostgreSQL to be ready to accept connections.
         postStart =
           ''
-            while ! ${pkgs.sudo}/bin/sudo -u ${cfg.superUser} psql --port=${toString cfg.port} -d postgres -c "" 2> /dev/null; do
+            PSQL="${pkgs.sudo}/bin/sudo -u ${cfg.superUser} psql --port=${toString cfg.port}"
+
+            while ! $PSQL -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) ''
-                ${pkgs.sudo}/bin/sudo -u ${cfg.superUser} psql -f "${cfg.initialScript}" --port=${toString cfg.port} -d postgres
+                $PSQL -f "${cfg.initialScript}" -d postgres
               ''}
               rm -f "${cfg.dataDir}/.first_startup"
             fi
+          '' + optionalString (cfg.ensureDatabases != []) ''
+            ${concatMapStrings (database: ''
+              $PSQL -tAc "SELECT 1 FROM pg_database WHERE datname = '${database}'" | grep -q 1 || $PSQL -tAc "CREATE DATABASE ${database}"
+            '') cfg.ensureDatabases}
+          '' + ''
+            ${concatMapStrings (user: ''
+              $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${user.name}'" | grep -q 1 || $PSQL -tAc "CREATE USER ${user.name}"
+              ${concatStringsSep "\n" (mapAttrsToList (database: permission: ''
+                $PSQL -tAc "GRANT ${permission} ON ${database} TO ${user.name}"
+              '') user.ensurePermissions)}
+            '') cfg.ensureUsers}
           '';
 
         unitConfig.RequiresMountsFor = "${cfg.dataDir}";