plausible: first review fix iteration

* Most significant is probably the patching necessary to run plausible
  with postgres without superuser privilege. This change includes:
  * updating ecto_sql to 3.6 where `CREATE DATABASE` is only executed if
    it doesn't exist[1].
  * patching a migration to only modify the `users.email` column (to use
    `citext` rather than creating the extension. `plausible-postgres`
    takes care of that).
* Correctly declare dependencies in systemd.
* A few minor fixes.

[1] https://github.com/elixir-ecto/ecto_sql/commit/051baf669ef6ec92639ccc8b113d00b98047e927

6 files changed, 73 insertions, 35 deletions

diff --git a/nixos/modules/services/web-apps/plausible.nix b/nixos/modules/services/web-apps/plausible.nix
index 429a4fa6205..a07f683e28c 100644
--- a/nixos/modules/services/web-apps/plausible.nix
+++ b/nixos/modules/services/web-apps/plausible.nix
@@ -5,10 +5,10 @@ with lib;
 let
   cfg = config.services.plausible;
 
+  # FIXME consider using LoadCredential as soon as it actually works.
   envSecrets = ''
     export ADMIN_USER_PWD="$(<${cfg.adminUser.passwordFile})"
     export SECRET_KEY_BASE="$(<${cfg.server.secretKeybaseFile})"
-    export RELEASE_TMP=/var/lib/plausible/tmp
     ${optionalString (cfg.mail.smtp.passwordFile != null) ''
       export SMTP_USER_PWD="$(<${cfg.mail.smtp.passwordFile})"
     ''}
@@ -51,9 +51,7 @@ in {
           default = "http://localhost:8123/default";
           type = types.str;
           description = ''
-            The URL to be used to connect to <package>postgres</package>. The format
-            is described in <link xlink:href="https://hexdocs.pm/ecto/Ecto.Repo.html#module-urls">
-            the elixir docs</link>.
+            The URL to be used to connect to <package>clickhouse</package>.
           '';
         };
       };
@@ -110,7 +108,7 @@ in {
 
     mail = {
       email = mkOption {
-        default = "	hello@plausible.local";
+        default = "hello@plausible.local";
         type = types.str;
         description = ''
           The email id to use for as <emphasis>from</emphasis> address of all communications
@@ -146,7 +144,7 @@ in {
             The path to the file with the password in case SMTP auth is enabled.
           '';
         };
-        enableSSL = mkEnableOption "";
+        enableSSL = mkEnableOption "SSL when connecting to the SMTP server";
         retries = mkOption {
           type = types.ints.unsigned;
           default = 2;
@@ -162,7 +160,7 @@ in {
     assertions = [
       { assertion = cfg.adminUser.activate -> cfg.database.postgres.setup;
         message = ''
-          Unable to automatically activate the admin-user if no local DB-managed for
+          Unable to automatically activate the admin-user if no locally DB-managed for
           postgres (`services.plausible.database.postgres.setup') is enabled!
         '';
       }
@@ -181,10 +179,13 @@ in {
         plausible = {
           inherit (pkgs.plausible.meta) description;
           documentation = [ "https://plausible.io/docs/self-hosting" ];
-          wantedBy = [ "multi-user.target" ]
-            ++ optional cfg.database.clickhouse.setup "clickhouse.service"
-            ++ optional cfg.database.postgres.setup "postgresql.service";
+          wantedBy = [ "multi-user.target" ];
           after = optional cfg.database.postgres.setup "plausible-postgres.service";
+          requires = optional cfg.database.clickhouse.setup "clickhouse.service"
+            ++ optionals cfg.database.postgres.setup [
+              "postgresql.service"
+              "plausible-postgres.service"
+            ];
 
           environment = {
             # NixOS specific option to avoid that it's trying to write into its store-path.
@@ -196,6 +197,8 @@ in {
             PORT = toString cfg.server.port;
             DISABLE_REGISTRATION = boolToString cfg.server.disableRegistration;
 
+            RELEASE_TMP = "/var/lib/plausible/tmp";
+
             ADMIN_USER_NAME = cfg.adminUser.name;
             ADMIN_USER_EMAIL = cfg.adminUser.email;
 
@@ -210,8 +213,11 @@ in {
             SMTP_HOST_PORT = toString cfg.mail.smtp.hostPort;
             SMTP_RETRIES = toString cfg.mail.smtp.retries;
             SMTP_HOST_SSL_ENABLED = boolToString cfg.mail.smtp.enableSSL;
-            ${if cfg.mail.smtp.user != null then "SMTP_USER_NAME" else null} = cfg.mail.smtp.user;
-          };
+
+            SELFHOST = "true";
+          } // (optionalAttrs (cfg.mail.smtp.user != null) {
+            SMTP_USER_NAME = cfg.mail.smtp.user;
+          });
 
           path = [ pkgs.plausible ]
             ++ optional cfg.database.postgres.setup config.services.postgresql.package;
@@ -239,26 +245,23 @@ in {
         };
       }
       (mkIf cfg.database.postgres.setup {
-        # Unfortunately `plausible' requires super-user permissions in postgresql, so this
-        # has to be done imperatively here.
+        # `plausible' requires the `citext'-extension.
         plausible-postgres = {
           after = [ "postgresql.service" ];
           bindsTo = [ "postgresql.service" ];
           requiredBy = [ "plausible.service" ];
           partOf = [ "plausible.service" ];
           serviceConfig.Type = "oneshot";
+          unitConfig.ConditionPathExists = "!/var/lib/plausible/.db-setup";
           script = ''
-            if [ ! -e /var/lib/plausible/.db-setup ]; then
-              mkdir -p /var/lib/plausible/
-              PSQL() {
-                /run/wrappers/bin/sudo -Hu postgres ${config.services.postgresql.package}/bin/psql --port=5432 "$@"
-              }
-              PSQL -tAc "CREATE EXTENSION IF NOT EXISTS citext;"
-              PSQL -tAc "CREATE ROLE plausible WITH LOGIN;"
-              PSQL -tAc "CREATE DATABASE plausible WITH OWNER plausible;"
-              PSQL -tAc "ALTER USER plausible WITH SUPERUSER;"
-              touch /var/lib/plausible/.db-setup
-            fi
+            mkdir -p /var/lib/plausible/
+            PSQL() {
+              /run/wrappers/bin/sudo -Hu postgres ${config.services.postgresql.package}/bin/psql --port=5432 "$@"
+            }
+            PSQL -tAc "CREATE ROLE plausible WITH LOGIN;"
+            PSQL -tAc "CREATE DATABASE plausible WITH OWNER plausible;"
+            PSQL -d plausible -tAc "CREATE EXTENSION IF NOT EXISTS citext;"
+            touch /var/lib/plausible/.db-setup
           '';
         };
       })
diff --git a/nixos/modules/services/web-apps/plausible.xml b/nixos/modules/services/web-apps/plausible.xml
index d1bcc22b231..92a571b9fbd 100644
--- a/nixos/modules/services/web-apps/plausible.xml
+++ b/nixos/modules/services/web-apps/plausible.xml
@@ -12,7 +12,7 @@
   <title>Basic Usage</title>
   <para>
    At first, a secret key is needed to be generated. This can be done with e.g.
-   <prompt>$ openssl rand -base64 64</prompt>
+   <screen><prompt>$ </prompt>openssl rand -base64 64</screen>
   </para>
   <para>
    After that, <package>plausible</package> can be deployed like this:
diff --git a/pkgs/servers/web-apps/plausible/default.nix b/pkgs/servers/web-apps/plausible/default.nix
index 0264d30e9ff..91c683b5ddb 100644
--- a/pkgs/servers/web-apps/plausible/default.nix
+++ b/pkgs/servers/web-apps/plausible/default.nix
@@ -14,10 +14,12 @@ let
     sha256 = "03lm1f29gwwixnhgjish5bhi3m73qyp71ns2sczdnwnbhrw61zps";
   };
 
-  mixDeps = beamPackages.fetchMixDeps {
+  # TODO consider using `mix2nix` as soon as it supports git dependencies.
+  mixFodDeps = beamPackages.fetchMixDeps {
     pname = "${pname}-deps";
     inherit src version;
-    sha256 = "sha256-66zSdYmis3UnbdLkPi649RbPbMPI5gVeFlaMekOy5CQ=";
+    sha256 = "sha256-pv/zXcku+ZgxV1804kIfDZN0jave2qG3rgZwm4yGA6I=";
+    patches = [ ./ecto_sql-fix.patch ];
   };
 
   yarnDeps = mkYarnModules {
@@ -28,15 +30,15 @@ let
     yarnLock = ./yarn.lock;
     preBuild = ''
       mkdir -p tmp/deps
-      cp -r ${mixDeps}/phoenix tmp/deps/phoenix
-      cp -r ${mixDeps}/phoenix_html tmp/deps/phoenix_html
+      cp -r ${mixFodDeps}/phoenix tmp/deps/phoenix
+      cp -r ${mixFodDeps}/phoenix_html tmp/deps/phoenix_html
     '';
     postBuild = ''
       echo 'module.exports = {}' > $out/node_modules/flatpickr/dist/postcss.config.js
     '';
   };
 in beamPackages.mixRelease {
-  inherit pname version src mixDeps;
+  inherit pname version src mixFodDeps;
 
   nativeBuildInputs = [ nodejs ];
 
@@ -47,6 +49,10 @@ in beamPackages.mixRelease {
       url = "https://github.com/Ma27/analytics/commit/f2ee5892a6c3e1a861d69ed30cac43e05e9cd36f.patch";
       sha256 = "sha256-JvJ7xlGw+tHtWje+jiQChVC4KTyqqdq2q+MIcOv/k1o=";
     })
+
+    # CREATE EXTENSION requires super-user privileges. To avoid that, we just skip
+    # the responsible SQL statement here and take care of it in the module.
+    ./skip-create-ext.patch
   ];
 
   passthru = {
diff --git a/pkgs/servers/web-apps/plausible/ecto_sql-fix.patch b/pkgs/servers/web-apps/plausible/ecto_sql-fix.patch
new file mode 100644
index 00000000000..13c3f4ffc3c
--- /dev/null
+++ b/pkgs/servers/web-apps/plausible/ecto_sql-fix.patch
@@ -0,0 +1,13 @@
+diff --git a/mix.exs b/mix.exs
+index f6e3b9a..67687d1 100644
+--- a/mix.exs
++++ b/mix.exs
+@@ -52,7 +52,7 @@ defmodule Plausible.MixProject do
+     [
+       {:bcrypt_elixir, "~> 2.0"},
+       {:cors_plug, "~> 1.5"},
+-      {:ecto_sql, "~> 3.0"},
++      {:ecto_sql, "~> 3.6"},
+       {:elixir_uuid, "~> 1.2"},
+       {:gettext, "~> 0.11"},
+       {:jason, "~> 1.0"},
diff --git a/pkgs/servers/web-apps/plausible/skip-create-ext.patch b/pkgs/servers/web-apps/plausible/skip-create-ext.patch
new file mode 100644
index 00000000000..ad9defadf9a
--- /dev/null
+++ b/pkgs/servers/web-apps/plausible/skip-create-ext.patch
@@ -0,0 +1,16 @@
+diff --git a/priv/repo/migrations/20190430140411_use_citext_for_email.exs b/priv/repo/migrations/20190430140411_use_citext_for_email.exs
+index ecb6f80..f3bf12f 100644
+--- a/priv/repo/migrations/20190430140411_use_citext_for_email.exs
++++ b/priv/repo/migrations/20190430140411_use_citext_for_email.exs
+@@ -2,10 +2,6 @@ defmodule Plausible.Repo.Migrations.UseCitextForEmail do
+   use Ecto.Migration
+ 
+   def change do
+-    execute "CREATE EXTENSION IF NOT EXISTS citext;"
+-
+-    alter table(:users) do
+-      modify :email, :citext, null: false
+-    end
++    execute "ALTER TABLE users ALTER COLUMN email TYPE citext;"
+   end
+ end
diff --git a/pkgs/servers/web-apps/plausible/update.sh b/pkgs/servers/web-apps/plausible/update.sh
index 8861cd50a72..0807f11ede9 100755
--- a/pkgs/servers/web-apps/plausible/update.sh
+++ b/pkgs/servers/web-apps/plausible/update.sh
@@ -45,17 +45,17 @@ fake_hash="$(nix-instantiate --eval -A lib.fakeSha256 | xargs echo)"
 sed -i "$dir/default.nix" \
   -e 's,version = ".*",version = "'"$nix_version"'",' \
   -e '/^  src = fetchFromGitHub/,+4{;s/sha256 = "\(.*\)"/sha256 = "'"$tarball_hash"'"/}' \
-  -e '/^  mixDeps =/,+3{;s/sha256 = "\(.*\)"/sha256 = "'"$fake_hash"'"/}'
+  -e '/^  mixFodDeps =/,+3{;s/sha256 = "\(.*\)"/sha256 = "'"$fake_hash"'"/}'
 
-mix_hash="$(nix-build -A plausible.mixDeps 2>&1 | tail -n3 | grep 'got:' | cut -d: -f2- | xargs echo)"
+mix_hash="$(nix-build -A plausible.mixFodDeps 2>&1 | tail -n3 | grep 'got:' | cut -d: -f2- | xargs echo)"
 
-sed -i "$dir/default.nix" -e '/^  mixDeps =/,+3{;s/sha256 = "\(.*\)"/sha256 = "'"$mix_hash"'"/}'
+sed -i "$dir/default.nix" -e '/^  mixFodDeps =/,+3{;s/sha256 = "\(.*\)"/sha256 = "'"$mix_hash"'"/}'
 
 tmp_setup_dir="$(mktemp -d)"
 trap "rm -rf $tmp_setup_dir" EXIT
 
 cp -r $tarball_path/* $tmp_setup_dir/
-cp -r "$(nix-build -A plausible.mixDeps)" "$tmp_setup_dir/deps"
+cp -r "$(nix-build -A plausible.mixFodDeps)" "$tmp_setup_dir/deps"
 chmod -R a+rwx "$tmp_setup_dir"
 
 pushd $tmp_setup_dir/assets