add database setup; export everything from the flake as a module

database.nix is substantially copied from the ISL config repo, with a few
changes to make it more generic and usable by others

I also removed stuff in mattermost.nix that wasn't doing anything; I'll
detail that in comments

Change-Id: I0ff6ea69f293dc4070f277f30ae0fde5254cd87c

2 files changed, 104 insertions, 35 deletions

diff --git a/services/common/database.nix b/services/common/database.nix
new file mode 100644
index 0000000..d52916e
--- /dev/null
+++ b/services/common/database.nix
@@ -0,0 +1,86 @@
+{ lib, pkgs, ... }:
+
+{
+  imports = [ ];
+
+  services.postgresql = {
+    enable = true;
+
+    # At the time of this writing, nixpkgs defaults to postgresql_17, which
+    # also happens to be the current upstream version. In general, it's fairly
+    # typical for the stable version of NixOS to lag one major version behind
+    # upstream.
+    #
+    # Specifying this explicitly rather than leaving it at the default does
+    # also mean that we won't automatically get new major versions as nixpkgs
+    # rolls them out; that's important for stability, because databases are
+    # only visible to the version that created them, and need to be migrated
+    # when there's a new one. At some point, we'll probably need to come up
+    # with some form of automation to relieve our users from having to
+    # understand the operational considerations.
+    package = pkgs.postgresql_17;
+
+    # This is the default, but we specify it explicitly so it doesn't become
+    # a point of confusion.
+    enableTCPIP = false;
+
+    # The mkForce here is so that we can be more restrictive than the nixpkgs
+    # defaults, which would otherwise be appended to anything specified below.
+    #
+    # The use of "peer" as the auth method on the first line means that that
+    # line only allows logging in as the database user with the same name as
+    # your Unix user. This allows us to avoid managing passwords for
+    # individual services.
+    #
+    # There are no rows here matching TCP/IP connections, which means it is
+    # not possible to log in via TCP/IP, even were TCP otherwise enabled.
+    # This is intentional.
+    authentication = lib.mkForce ''
+      local all all peer
+    '';
+
+    # This only ever matters at the very beginning, when PostgreSQL is first
+    # installed, but we specify it here for the sake of documenting how it was
+    # done.
+    initdbArgs = [
+      # ICU will get us better internationalization defaults than libc will.
+      # In particular, it makes sure that the default encoding is UTF8, and
+      # that everything else is set up to work well with that.
+      "--locale-provider=icu"
+      "--icu-locale=en"
+
+      # It would be possible to configure locale settings in more detail, but
+      # the vast majority of that stuff can also be specified under the
+      # "settings" option, which is preferable because, unlike specifying it
+      # here, changes to it later will actually do something.
+    ];
+
+    # Settings configured here will apply to the entire PostgreSQL server and
+    # all databases within it. There is a whole other family of settings
+    # which are done inside the database, and managed as mutable state. We
+    # will preferentially put things here rather than doing it as mutable
+    # state, whenever possible. It is normal and expected that there are many
+    # cases where it's not possible; that's the nature of databases.
+    settings = {
+      # Nothing here yet, just a placeholder so it's easy to find when we
+      # need it. :)
+    };
+  };
+
+  services.postgresqlBackup = {
+    enable = true;
+
+    # Daily backups. Putting this in the middle of the night should hopefully
+    # avoid it happening in the middle of anyone's manual changes.
+    startAt = "02:15:00";
+
+    # Some services have sizable databases that we don't necessarily want to
+    # back up, so we turn off the default behavior of backing up everything,
+    # and instead require all services to explicitly add themselves to
+    # `services.postgresqlBackup.databases`.
+    backupAll = false;
+
+    compression = "gzip";
+    compressionLevel = 9;
+  };
+}
diff --git a/services/mattermost/mattermost.nix b/services/mattermost/default.nix
index 132fdeb..f16f721 100644
--- a/services/mattermost/mattermost.nix
+++ b/services/mattermost/default.nix
@@ -1,16 +1,23 @@
 { config, pkgs, lib, ... }:
 
-let
-  mattermostPassword = ""; # Change to a strong password
-in {
-  networking.firewall.allowedTCPPorts = [ 80 443 8065 ];
-
-  services.mattermost.database.peerAuth = true;
-
+{
   systemd.services.mattermost = {
     description = "Mattermost server";
     after = [ "network.target" "postgresql.service" ];
     wantedBy = [ "multi-user.target" ];
+
+    preStart = ''
+      mkdir -p /var/lib/mattermost/bin
+      mkdir -p /var/lib/mattermost/client
+      mkdir -p /var/lib/mattermost/config
+      mkdir -p /var/lib/mattermost/templates
+      cp -r ${pkgs.mattermost}/client/* /var/lib/mattermost/client/
+      cp -r ${pkgs.mattermost}/bin/*  /var/lib/mattermost/bin/
+      cp -r ${pkgs.mattermost}/config/*  /var/lib/mattermost/config/
+      cp -r ${pkgs.mattermost}/templates/*  /var/lib/mattermost/templates/
+      chown -R mattermost:mattermost /var/lib/mattermost
+    '';
+
     serviceConfig = {
       User = "mattermost";
       Group = "mattermost";
@@ -18,7 +25,7 @@ in {
       ExecStart = "${pkgs.mattermost}/bin/mattermost";
       Environment = [
         "MM_SQLSETTINGS_DRIVERNAME=postgres"
-        "MM_SQLSETTINGS_DATASOURCE=postgres://mattermost:QwErAsDf@localhost:5432/mattermost?sslmode=disable&connect_timeout=10"
+        "MM_SQLSETTINGS_DATASOURCE=postgres://mattermost@localhost:5432/mattermost?sslmode=disable&connect_timeout=10"
         "MM_SERVICESETTINGS_SITEURL=http://islmm"
         "MM_SERVICESETTINGS_LISTENADDRESS=:8065"
         "MM_SERVICESETTINGS_ENABLEUSERCREATION=true"
@@ -26,17 +33,6 @@ in {
       ];
       Restart = "always";
     };
-    preStart = ''
-      mkdir -p /var/lib/mattermost/bin
-      mkdir -p /var/lib/mattermost/client
-      mkdir -p /var/lib/mattermost/config
-      mkdir -p /var/lib/mattermost/templates
-      cp -r ${pkgs.mattermost}/client/* /var/lib/mattermost/client/
-      cp -r ${pkgs.mattermost}/bin/*  /var/lib/mattermost/bin/
-      cp -r ${pkgs.mattermost}/config/*  /var/lib/mattermost/config/
-      cp -r ${pkgs.mattermost}/templates/*  /var/lib/mattermost/templates/
-      chown -R mattermost:mattermost /var/lib/mattermost
-    '';
   };
 
   users.users.mattermost = {
@@ -50,6 +46,7 @@ in {
 
   services.postgresql = {
     enable = true;
+
     ensureDatabases = [ "mattermost" ];
     ensureUsers = [
       {
@@ -57,21 +54,7 @@ in {
         ensureDBOwnership = true;
       }
     ];
-    initialScript = pkgs.writeText "init-mattermost.sql" ''
-      DO $$
-      BEGIN
-        IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'mattermost') THEN
-          CREATE ROLE mattermost LOGIN PASSWORD 'QwErAsDf';
-        END IF;
-      END
-      $$;
-      ALTER DATABASE mattermost OWNER TO mattermost;
-    '';
   };
-  services.postgresql.authentication = ''
-    local   all             postgres                                peer
-    local   all             mattermost                              md5
-    host    all             all             127.0.0.1/32            md5
-    host    all             all             ::1/128                 md5
-  '';
+
+  services.postgresqlBackup.databases = [ "mattermost" ];
 }