nixos/keycloak: Add support for MySQL and external DBs with SSL

- Add support for using MySQL as an option to PostgreSQL.
- Enable connecting to external DBs with SSL
- Add a database port config option

4 files changed, 229 insertions, 86 deletions

diff --git a/nixos/modules/services/web-apps/keycloak.nix b/nixos/modules/services/web-apps/keycloak.nix
index 9c6a5ca305c..bbb0c8d0483 100644
--- a/nixos/modules/services/web-apps/keycloak.nix
+++ b/nixos/modules/services/web-apps/keycloak.nix
@@ -97,11 +97,59 @@ in
       '';
     };
 
+    databaseType = lib.mkOption {
+      type = lib.types.enum [ "mysql" "postgresql" ];
+      default = "postgresql";
+      example = "mysql";
+      description = ''
+        The type of database Keycloak should connect to.
+      '';
+    };
+
     databaseHost = lib.mkOption {
       type = lib.types.str;
       default = "localhost";
       description = ''
-        Hostname of the PostgreSQL database to connect to.
+        Hostname of the database to connect to.
+      '';
+    };
+
+    databasePort =
+      let
+        dbPorts = {
+          postgresql = 5432;
+          mysql = 3306;
+        };
+      in
+        lib.mkOption {
+          type = lib.types.port;
+          default = dbPorts.${cfg.databaseType};
+          description = ''
+            Port of the database to connect to.
+          '';
+        };
+
+    databaseUseSSL = lib.mkOption {
+      type = lib.types.bool;
+      default = cfg.databaseHost != "localhost";
+      description = ''
+        Whether the database connection should be secured by SSL /
+        TLS.
+      '';
+    };
+
+    databaseCaCert = lib.mkOption {
+      type = lib.types.nullOr lib.types.path;
+      default = null;
+      description = ''
+        The SSL / TLS CA certificate that verifies the identity of the
+        database server.
+
+        Required when PostgreSQL is used and SSL is turned on.
+
+        For MySQL, if left at <literal>null</literal>, the default
+        Java keystore is used, which should suffice if the server
+        certificate is issued by an official CA.
       '';
     };
 
@@ -208,6 +256,12 @@ in
     let
       # We only want to create a database if we're actually going to connect to it.
       databaseActuallyCreateLocally = cfg.databaseCreateLocally && cfg.databaseHost == "localhost";
+      createLocalPostgreSQL = databaseActuallyCreateLocally && cfg.databaseType == "postgresql";
+      createLocalMySQL = databaseActuallyCreateLocally && cfg.databaseType == "mysql";
+
+      mySqlCaKeystore = pkgs.runCommandNoCC "mysql-ca-keystore" {} ''
+        ${pkgs.jre}/bin/keytool -importcert -trustcacerts -alias MySQLCACert -file ${cfg.databaseCaCert} -keystore $out -storepass notsosecretpassword -noprompt
+      '';
 
       keycloakConfig' = builtins.foldl' lib.recursiveUpdate {
         "interface=public".inet-address = cfg.bindAddress;
@@ -220,19 +274,52 @@ in
             };
           };
         };
-        "subsystem=datasources"."jdbc-driver=postgresql" = {
-          driver-module-name = "org.postgresql";
-          driver-name = "postgresql";
-          driver-xa-datasource-class-name = "org.postgresql.xa.PGXADataSource";
-        };
         "subsystem=datasources"."data-source=KeycloakDS" = {
-          connection-url = "jdbc:postgresql://${cfg.databaseHost}/keycloak";
-          driver-name = "postgresql";
           max-pool-size = "20";
           user-name = if databaseActuallyCreateLocally then "keycloak" else cfg.databaseUsername;
           password = "@db-password@";
         };
       } [
+        (lib.optionalAttrs (cfg.databaseType == "postgresql") {
+          "subsystem=datasources" = {
+            "jdbc-driver=postgresql" = {
+              driver-module-name = "org.postgresql";
+              driver-name = "postgresql";
+              driver-xa-datasource-class-name = "org.postgresql.xa.PGXADataSource";
+            };
+            "data-source=KeycloakDS" = {
+              connection-url = "jdbc:postgresql://${cfg.databaseHost}:${builtins.toString cfg.databasePort}/keycloak";
+              driver-name = "postgresql";
+              "connection-properties=ssl".value = lib.boolToString cfg.databaseUseSSL;
+            } // (lib.optionalAttrs (cfg.databaseCaCert != null) {
+              "connection-properties=sslrootcert".value = cfg.databaseCaCert;
+              "connection-properties=sslmode".value = "verify-ca";
+            });
+          };
+        })
+        (lib.optionalAttrs (cfg.databaseType == "mysql") {
+          "subsystem=datasources" = {
+            "jdbc-driver=mysql" = {
+              driver-module-name = "com.mysql";
+              driver-name = "mysql";
+              driver-class-name = "com.mysql.jdbc.Driver";
+            };
+            "data-source=KeycloakDS" = {
+              connection-url = "jdbc:mysql://${cfg.databaseHost}:${builtins.toString cfg.databasePort}/keycloak";
+              driver-name = "mysql";
+              "connection-properties=useSSL".value = lib.boolToString cfg.databaseUseSSL;
+              "connection-properties=requireSSL".value = lib.boolToString cfg.databaseUseSSL;
+              "connection-properties=verifyServerCertificate".value = lib.boolToString cfg.databaseUseSSL;
+              "connection-properties=characterEncoding".value = "UTF-8";
+              valid-connection-checker-class-name = "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker";
+              validate-on-match = true;
+              exception-sorter-class-name = "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter";
+            } // (lib.optionalAttrs (cfg.databaseCaCert != null) {
+              "connection-properties=trustCertificateKeyStoreUrl".value = "file:${mySqlCaKeystore}";
+              "connection-properties=trustCertificateKeyStorePassword".value = "notsosecretpassword";
+            });
+          };
+        })
         (lib.optionalAttrs (cfg.certificatePrivateKeyBundle != null) {
           "socket-binding-group=standard-sockets"."socket-binding=https".port = cfg.httpsPort;
           "core-service=management"."security-realm=UndertowRealm"."server-identity=ssl" = {
@@ -444,7 +531,7 @@ in
 
       jbossCliScript = pkgs.writeText "jboss-cli-script" (mkJbossScript keycloakConfig');
 
-      keycloakConfig = pkgs.runCommand "keycloak-config" {} ''
+      keycloakConfig = pkgs.runCommandNoCC "keycloak-config" {} ''
         export JBOSS_BASE_DIR="$(pwd -P)";
         export JBOSS_MODULEPATH="${cfg.package}/modules";
         export JBOSS_LOG_DIR="$JBOSS_BASE_DIR/log";
@@ -475,9 +562,16 @@ in
     in
       lib.mkIf cfg.enable {
 
+        assertions = [
+          {
+            assertion = (cfg.databaseUseSSL && cfg.databaseType == "postgresql") -> (cfg.databaseCaCert != null);
+            message = ''A CA certificate must be specified (in 'services.keycloak.databaseCaCert') when PostgreSQL is used with SSL'';
+          }
+        ];
+
         environment.systemPackages = [ cfg.package ];
 
-        systemd.services.keycloakDatabaseInit = lib.mkIf databaseActuallyCreateLocally {
+        systemd.services.keycloakPostgreSQLInit = lib.mkIf createLocalPostgreSQL {
           after = [ "postgresql.service" ];
           before = [ "keycloak.service" ];
           bindsTo = [ "postgresql.service" ];
@@ -498,71 +592,100 @@ in
           '';
         };
 
-        systemd.services.keycloak = {
-          after = lib.optionals databaseActuallyCreateLocally [
-            "keycloakDatabaseInit.service" "postgresql.service"
-          ];
-          bindsTo = lib.optionals databaseActuallyCreateLocally [
-            "keycloakDatabaseInit.service" "postgresql.service"
-          ];
-          wantedBy = [ "multi-user.target" ];
-          environment = {
-            JBOSS_LOG_DIR = "/var/log/keycloak";
-            JBOSS_BASE_DIR = "/run/keycloak";
-            JBOSS_MODULEPATH = "${cfg.package}/modules";
-          };
+        systemd.services.keycloakMySQLInit = lib.mkIf createLocalMySQL {
+          after = [ "mysql.service" ];
+          before = [ "keycloak.service" ];
+          bindsTo = [ "mysql.service" ];
           serviceConfig = {
-            ExecStartPre = let
-              startPreFullPrivileges = ''
-                set -eu
-
-                install -T -m 0400 -o keycloak -g keycloak '${cfg.databasePasswordFile}' /run/keycloak/secrets/db_password
-              '' + lib.optionalString (cfg.certificatePrivateKeyBundle != null) ''
-                install -T -m 0400 -o keycloak -g keycloak '${cfg.certificatePrivateKeyBundle}' /run/keycloak/secrets/ssl_cert_pk_bundle
-              '';
-              startPre = ''
-                set -eu
-
-                install -m 0600 ${cfg.package}/standalone/configuration/*.properties /run/keycloak/configuration
-                install -T -m 0600 ${keycloakConfig} /run/keycloak/configuration/standalone.xml
-
-                db_password="$(</run/keycloak/secrets/db_password)"
-                ${pkgs.replace}/bin/replace-literal -fe '@db-password@' "$db_password" /run/keycloak/configuration/standalone.xml
-
-                export JAVA_OPTS=-Djboss.server.config.user.dir=/run/keycloak/configuration
-                ${cfg.package}/bin/add-user-keycloak.sh -u admin -p '${cfg.initialAdminPassword}'
-              '' + lib.optionalString (cfg.certificatePrivateKeyBundle != null) ''
-                pushd /run/keycloak/ssl/
-                cat /run/keycloak/secrets/ssl_cert_pk_bundle <(echo) /etc/ssl/certs/ca-certificates.crt > allcerts.pem
-                ${pkgs.openssl}/bin/openssl pkcs12 -export -in /run/keycloak/secrets/ssl_cert_pk_bundle -chain \
-                                                   -name "${cfg.frontendUrl}" -out certificate_private_key_bundle.p12 \
-                                                   -CAfile allcerts.pem -passout pass:notsosecretpassword
-                popd
-              '';
-            in [
-              "+${pkgs.writeShellScript "keycloak-start-pre-full-privileges" startPreFullPrivileges}"
-              "${pkgs.writeShellScript "keycloak-start-pre" startPre}"
-            ];
-            ExecStart = "${cfg.package}/bin/standalone.sh";
-            User = "keycloak";
-            Group = "keycloak";
-            DynamicUser = true;
-            RuntimeDirectory = map (p: "keycloak/" + p) [
-              "secrets"
-              "configuration"
-              "deployments"
-              "data"
-              "ssl"
-              "log"
-              "tmp"
-            ];
-            RuntimeDirectoryMode = 0700;
-            LogsDirectory = "keycloak";
-            AmbientCapabilities = "CAP_NET_BIND_SERVICE";
+            Type = "oneshot";
+            RemainAfterExit = true;
+            User = config.services.mysql.user;
+            Group = config.services.mysql.group;
           };
+          script = ''
+            set -eu
+
+            db_password="$(<'${cfg.databasePasswordFile}')"
+            ( echo "CREATE USER IF NOT EXISTS 'keycloak'@'localhost' IDENTIFIED BY '$db_password';"
+              echo "CREATE DATABASE keycloak CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
+              echo "GRANT ALL PRIVILEGES ON keycloak.* TO 'keycloak'@'localhost';"
+            ) | ${config.services.mysql.package}/bin/mysql -N
+          '';
         };
 
-        services.postgresql.enable = lib.mkDefault databaseActuallyCreateLocally;
+        systemd.services.keycloak =
+          let
+            databaseServices =
+              if createLocalPostgreSQL then [
+                "keycloakPostgreSQLInit.service" "postgresql.service"
+              ]
+              else if createLocalMySQL then [
+                "keycloakMySQLInit.service" "mysql.service"
+              ]
+              else [ ];
+          in {
+            after = databaseServices;
+            bindsTo = databaseServices;
+            wantedBy = [ "multi-user.target" ];
+            environment = {
+              JBOSS_LOG_DIR = "/var/log/keycloak";
+              JBOSS_BASE_DIR = "/run/keycloak";
+              JBOSS_MODULEPATH = "${cfg.package}/modules";
+            };
+            serviceConfig = {
+              ExecStartPre = let
+                startPreFullPrivileges = ''
+                  set -eu
+
+                  install -T -m 0400 -o keycloak -g keycloak '${cfg.databasePasswordFile}' /run/keycloak/secrets/db_password
+                '' + lib.optionalString (cfg.certificatePrivateKeyBundle != null) ''
+                  install -T -m 0400 -o keycloak -g keycloak '${cfg.certificatePrivateKeyBundle}' /run/keycloak/secrets/ssl_cert_pk_bundle
+                '';
+                startPre = ''
+                  set -eu
+
+                  install -m 0600 ${cfg.package}/standalone/configuration/*.properties /run/keycloak/configuration
+                  install -T -m 0600 ${keycloakConfig} /run/keycloak/configuration/standalone.xml
+
+                  db_password="$(</run/keycloak/secrets/db_password)"
+                  ${pkgs.replace}/bin/replace-literal -fe '@db-password@' "$db_password" /run/keycloak/configuration/standalone.xml
+
+                  export JAVA_OPTS=-Djboss.server.config.user.dir=/run/keycloak/configuration
+                  ${cfg.package}/bin/add-user-keycloak.sh -u admin -p '${cfg.initialAdminPassword}'
+                '' + lib.optionalString (cfg.certificatePrivateKeyBundle != null) ''
+                  pushd /run/keycloak/ssl/
+                  cat /run/keycloak/secrets/ssl_cert_pk_bundle <(echo) /etc/ssl/certs/ca-certificates.crt > allcerts.pem
+                  ${pkgs.openssl}/bin/openssl pkcs12 -export -in /run/keycloak/secrets/ssl_cert_pk_bundle -chain \
+                                                     -name "${cfg.frontendUrl}" -out certificate_private_key_bundle.p12 \
+                                                     -CAfile allcerts.pem -passout pass:notsosecretpassword
+                  popd
+                '';
+              in [
+                "+${pkgs.writeShellScript "keycloak-start-pre-full-privileges" startPreFullPrivileges}"
+                "${pkgs.writeShellScript "keycloak-start-pre" startPre}"
+              ];
+              ExecStart = "${cfg.package}/bin/standalone.sh";
+              User = "keycloak";
+              Group = "keycloak";
+              DynamicUser = true;
+              RuntimeDirectory = map (p: "keycloak/" + p) [
+                "secrets"
+                "configuration"
+                "deployments"
+                "data"
+                "ssl"
+                "log"
+                "tmp"
+              ];
+              RuntimeDirectoryMode = 0700;
+              LogsDirectory = "keycloak";
+              AmbientCapabilities = "CAP_NET_BIND_SERVICE";
+            };
+          };
+
+        services.postgresql.enable = lib.mkDefault createLocalPostgreSQL;
+        services.mysql.enable = lib.mkDefault createLocalMySQL;
+        services.mysql.package = lib.mkIf createLocalMySQL pkgs.mysql;
       };
 
   meta.doc = ./keycloak.xml;
diff --git a/nixos/modules/services/web-apps/keycloak.xml b/nixos/modules/services/web-apps/keycloak.xml
index 6b97d48e0bd..ca5e223eee4 100644
--- a/nixos/modules/services/web-apps/keycloak.xml
+++ b/nixos/modules/services/web-apps/keycloak.xml
@@ -37,16 +37,31 @@
    <section xml:id="module-services-keycloak-database">
      <title>Database access</title>
      <para>
-       <productname>Keycloak</productname> depends on
-       <productname>PostgreSQL</productname> and will automatically
-       enable it and create a database and role unless configured not
-       to, either by changing <xref linkend="opt-services.keycloak.databaseHost" />
-       from its default of <literal>localhost</literal> or setting
-       <xref linkend="opt-services.keycloak.databaseCreateLocally" />
+       <productname>Keycloak</productname> can be used with either
+       <productname>PostgreSQL</productname> or
+       <productname>MySQL</productname>. Which one is used can be
+       configured in <xref
+       linkend="opt-services.keycloak.databaseType" />. The selected
+       database will automatically be enabled and a database and role
+       created unless <xref
+       linkend="opt-services.keycloak.databaseHost" /> is changed from
+       its default of <literal>localhost</literal> or <xref
+       linkend="opt-services.keycloak.databaseCreateLocally" /> is set
        to <literal>false</literal>.
      </para>
 
      <para>
+       External database access can also be configured by setting
+       <xref linkend="opt-services.keycloak.databaseHost" />, <xref
+       linkend="opt-services.keycloak.databaseUsername" />, <xref
+       linkend="opt-services.keycloak.databaseUseSSL" /> and <xref
+       linkend="opt-services.keycloak.databaseCaCert" /> as
+       appropriate. Note that you need to manually create a database
+       called <literal>keycloak</literal> and allow the configured
+       database user full access to it.
+     </para>
+
+     <para>
        <xref linkend="opt-services.keycloak.databasePasswordFile" />
        must be set to the path to a file containing the password used
        to log in to the database. If <xref linkend="opt-services.keycloak.databaseHost" />
diff --git a/nixos/tests/all-tests.nix b/nixos/tests/all-tests.nix
index 5a10f60fc9a..d49357cb463 100644
--- a/nixos/tests/all-tests.nix
+++ b/nixos/tests/all-tests.nix
@@ -175,7 +175,7 @@ in
   kernel-latest = handleTest ./kernel-latest.nix {};
   kernel-lts = handleTest ./kernel-lts.nix {};
   kernel-testing = handleTest ./kernel-testing.nix {};
-  keycloak = handleTest ./keycloak.nix {};
+  keycloak = discoverTests (import ./keycloak.nix);
   keymap = handleTest ./keymap.nix {};
   knot = handleTest ./knot.nix {};
   krb5 = discoverTests (import ./krb5 {});
diff --git a/nixos/tests/keycloak.nix b/nixos/tests/keycloak.nix
index e5e31b038e9..f448a0f7095 100644
--- a/nixos/tests/keycloak.nix
+++ b/nixos/tests/keycloak.nix
@@ -2,12 +2,12 @@
 # OIDC client and a user, and simulates the user logging in to the
 # client using their Keycloak login.
 
-import ./make-test-python.nix (
-  { pkgs, ... }:
-  let
-    frontendUrl = "http://keycloak/auth";
-    initialAdminPassword = "h4IhoJFnt2iQIR9";
-  in
+let
+  frontendUrl = "http://keycloak/auth";
+  initialAdminPassword = "h4IhoJFnt2iQIR9";
+
+  keycloakTest = import ./make-test-python.nix (
+    { pkgs, databaseType, ... }:
     {
       name = "keycloak";
       meta = with pkgs.stdenv.lib.maintainers; {
@@ -19,7 +19,7 @@ import ./make-test-python.nix (
           virtualisation.memorySize = 1024;
           services.keycloak = {
             enable = true;
-            inherit frontendUrl initialAdminPassword;
+            inherit frontendUrl databaseType initialAdminPassword;
             databasePasswordFile = pkgs.writeText "dbPassword" "wzf6vOCbPp6cqTH";
           };
           environment.systemPackages = with pkgs; [
@@ -136,4 +136,9 @@ import ./make-test-python.nix (
           )
         '';
     }
-)
+  );
+in
+{
+  postgres = keycloakTest { databaseType = "postgresql"; };
+  mysql = keycloakTest { databaseType = "mysql"; };
+}