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

1 files changed, 86 insertions, 0 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;
+  };
+}