Merge pull request #200724 from JonathanLorimer/allow-configuration-of-roles

services.postgresql: Allow configuration of user roles in ensureUser

1 files changed, 172 insertions, 6 deletions

diff --git a/nixos/modules/services/databases/postgresql.nix b/nixos/modules/services/databases/postgresql.nix
index fe7ef48075a..6665e7a088f 100644
--- a/nixos/modules/services/databases/postgresql.nix
+++ b/nixos/modules/services/databases/postgresql.nix
@@ -146,6 +146,7 @@ in
                 Name of the user to ensure.
               '';
             };
+
             ensurePermissions = mkOption {
               type = types.attrsOf types.str;
               default = {};
@@ -167,6 +168,154 @@ in
                 }
               '';
             };
+
+            ensureClauses = mkOption {
+              description = lib.mdDoc ''
+                An attrset of clauses to grant to the user. Under the hood this uses the
+                [ALTER USER syntax](https://www.postgresql.org/docs/current/sql-alteruser.html) for each attrName where
+                the attrValue is true in the attrSet:
+                `ALTER USER user.name WITH attrName`
+              '';
+              example = literalExpression ''
+                {
+                  superuser = true;
+                  createrole = true;
+                  createdb = true;
+                }
+              '';
+              default = {};
+              defaultText = lib.literalMD ''
+                The default, `null`, means that the user created will have the default permissions assigned by PostgreSQL. Subsequent server starts will not set or unset the clause, so imperative changes are preserved.
+              '';
+              type = types.submodule {
+                options = let
+                  defaultText = lib.literalMD ''
+                    `null`: do not set. For newly created roles, use PostgreSQL's default. For existing roles, do not touch this clause.
+                  '';
+                in {
+                  superuser = mkOption {
+                    type = types.nullOr types.bool;
+                    description = lib.mdDoc ''
+                      Grants the user, created by the ensureUser attr, superuser permissions. From the postgres docs:
+
+                      A database superuser bypasses all permission checks,
+                      except the right to log in. This is a dangerous privilege
+                      and should not be used carelessly; it is best to do most
+                      of your work as a role that is not a superuser. To create
+                      a new database superuser, use CREATE ROLE name SUPERUSER.
+                      You must do this as a role that is already a superuser.
+
+                      More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
+                    '';
+                    default = null;
+                    inherit defaultText;
+                  };
+                  createrole = mkOption {
+                    type = types.nullOr types.bool;
+                    description = lib.mdDoc ''
+                      Grants the user, created by the ensureUser attr, createrole permissions. From the postgres docs:
+
+                      A role must be explicitly given permission to create more
+                      roles (except for superusers, since those bypass all
+                      permission checks). To create such a role, use CREATE
+                      ROLE name CREATEROLE. A role with CREATEROLE privilege
+                      can alter and drop other roles, too, as well as grant or
+                      revoke membership in them. However, to create, alter,
+                      drop, or change membership of a superuser role, superuser
+                      status is required; CREATEROLE is insufficient for that.
+
+                      More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
+                    '';
+                    default = null;
+                    inherit defaultText;
+                  };
+                  createdb = mkOption {
+                    type = types.nullOr types.bool;
+                    description = lib.mdDoc ''
+                      Grants the user, created by the ensureUser attr, createdb permissions. From the postgres docs:
+
+                      A role must be explicitly given permission to create
+                      databases (except for superusers, since those bypass all
+                      permission checks). To create such a role, use CREATE
+                      ROLE name CREATEDB.
+
+                      More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
+                    '';
+                    default = null;
+                    inherit defaultText;
+                  };
+                  "inherit" = mkOption {
+                    type = types.nullOr types.bool;
+                    description = lib.mdDoc ''
+                      Grants the user created inherit permissions. From the postgres docs:
+
+                      A role is given permission to inherit the privileges of
+                      roles it is a member of, by default. However, to create a
+                      role without the permission, use CREATE ROLE name
+                      NOINHERIT.
+
+                      More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
+                    '';
+                    default = null;
+                    inherit defaultText;
+                  };
+                  login = mkOption {
+                    type = types.nullOr types.bool;
+                    description = lib.mdDoc ''
+                      Grants the user, created by the ensureUser attr, login permissions. From the postgres docs:
+
+                      Only roles that have the LOGIN attribute can be used as
+                      the initial role name for a database connection. A role
+                      with the LOGIN attribute can be considered the same as a
+                      “database user”. To create a role with login privilege,
+                      use either:
+
+                      CREATE ROLE name LOGIN; CREATE USER name;
+
+                      (CREATE USER is equivalent to CREATE ROLE except that
+                      CREATE USER includes LOGIN by default, while CREATE ROLE
+                      does not.)
+
+                      More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
+                    '';
+                    default = null;
+                    inherit defaultText;
+                  };
+                  replication = mkOption {
+                    type = types.nullOr types.bool;
+                    description = lib.mdDoc ''
+                      Grants the user, created by the ensureUser attr, replication permissions. From the postgres docs:
+
+                      A role must explicitly be given permission to initiate
+                      streaming replication (except for superusers, since those
+                      bypass all permission checks). A role used for streaming
+                      replication must have LOGIN permission as well. To create
+                      such a role, use CREATE ROLE name REPLICATION LOGIN.
+
+                      More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
+                    '';
+                    default = null;
+                    inherit defaultText;
+                  };
+                  bypassrls = mkOption {
+                    type = types.nullOr types.bool;
+                    description = lib.mdDoc ''
+                      Grants the user, created by the ensureUser attr, replication permissions. From the postgres docs:
+
+                      A role must be explicitly given permission to bypass
+                      every row-level security (RLS) policy (except for
+                      superusers, since those bypass all permission checks). To
+                      create such a role, use CREATE ROLE name BYPASSRLS as a
+                      superuser.
+
+                      More information on postgres roles can be found [here](https://www.postgresql.org/docs/current/role-attributes.html)
+                    '';
+                    default = null;
+                    inherit defaultText;
+                  };
+                };
+              };
+            };
           };
         });
         default = [];
@@ -380,12 +529,29 @@ in
               $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}
+            ${
+              concatMapStrings
+              (user:
+                let
+                  userPermissions = concatStringsSep "\n"
+                    (mapAttrsToList
+                      (database: permission: ''$PSQL -tAc 'GRANT ${permission} ON ${database} TO "${user.name}"' '')
+                      user.ensurePermissions
+                    );
+
+                  filteredClauses = filterAttrs (name: value: value != null) user.ensureClauses;
+
+                  clauseSqlStatements = attrValues (mapAttrs (n: v: if v then n else "no${n}") filteredClauses);
+
+                  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}
+                ''
+              )
+              cfg.ensureUsers
+            }
           '';
 
         serviceConfig = mkMerge [