{ lib, config, pkgs, ... }: {
  services.postgresql = {
    enable = true;

    package = pkgs.postgresql;
    settings = {
      listen_addresses = lib.mkForce "standard, 172.20.0.1";
      log_connections = true;
      logging_collector = true;
      log_disconnections = true;
      log_destination = lib.mkForce "syslog";
    };

    ensureDatabases =
      [ "vaultwarden" "gerrit" "privatebin" "keycloak" "nextcloud" "synapse" "taiga" ];

    ensureUsers = [
      {
        name = "clicks_grafana";
        ensurePermissions = {
          "ALL TABLES IN SCHEMA public" = "SELECT";
          "SCHEMA public" = "USAGE";
        };
      }
      {
        name = "matrix-synapse";
        ensurePermissions = { "DATABASE synapse" = "ALL PRIVILEGES"; };
      }
      {
        name = "keycloak";
        ensurePermissions = { "DATABASE keycloak" = "ALL PRIVILEGES"; };
      }
      {
        name = "vaultwarden";
        ensurePermissions = { "DATABASE vaultwarden" = "ALL PRIVILEGES"; };
      }
      {
        name = "privatebin";
        ensurePermissions = { "DATABASE privatebin" = "ALL PRIVILEGES"; };
      }
      {
        name = "nextcloud";
        ensurePermissions = { "DATABASE nextcloud" = "ALL PRIVILEGES"; };
      }
      {
        name = "taiga";
        ensurePermissions = { "DATABASE taiga" = "ALL PRIVILEGES"; };
      }
    ] ++ (map (name: ({
      inherit name;
      ensurePermissions = { "ALL TABLES IN SCHEMA public" = "ALL PRIVILEGES"; };
    })) [ "minion" "coded" "pineafan" ]);

    #                 method database user address auth-method
    authentication = "host   all      all  samenet scram-sha-256";
  };

  systemd.services.postgresql.postStart = lib.mkMerge [
    (let
      database = "synapse";
      cfg = config.services.postgresql;
    in lib.mkBefore (''
      PSQL="psql --port=${toString cfg.port}"

      while ! $PSQL -d postgres -c "" 2> /dev/null; do
          if ! kill -0 "$MAINPID"; then exit 1; fi
          sleep 0.1
      done

      $PSQL -tAc "SELECT 1 FROM pg_database WHERE datname = '${database}'" | grep -q 1 || $PSQL -tAc 'CREATE DATABASE "${database}" WITH LC_CTYPE="C" LC_COLLATE="C" TEMPLATE="template0"'
    '') # synapse needs C collation, so we can't use ensureDatabases for it
    )
    (lib.mkAfter (lib.pipe [
      {
        user = "clicks_grafana";
        passwordFile = config.sops.secrets.clicks_grafana_db_password.path;
      }
      {
        user = "keycloak";
        passwordFile = config.sops.secrets.clicks_keycloak_db_password.path;
      }
      {
        user = "vaultwarden";
        passwordFile = config.sops.secrets.clicks_vaultwarden_db_password.path;
      }
      {
        user = "privatebin";
        passwordFile = config.sops.secrets.clicks_privatebin_db_password.path;
      }
      {
        user = "nextcloud";
        passwordFile = config.sops.secrets.clicks_nextcloud_db_password.path;
      }
      {
        user = "taiga";
        passwordFile = config.sops.secrets.clicks_taiga_db_password.path;
      }
    ] [
      (map (userData: ''
        $PSQL -tAc "ALTER USER ${userData.user} PASSWORD '$(cat ${userData.passwordFile})';"
      ''))
      (lib.concatStringsSep "\n")
    ]))
  ];

  sops.secrets = lib.pipe [
    "clicks_grafana_db_password"
    "clicks_keycloak_db_password"
    "clicks_vaultwarden_db_password"
    "clicks_privatebin_db_password"
    "clicks_nextcloud_db_password"
    "clicks_taiga_db_password"
  ] [
    (map (name: {
      inherit name;
      value = {
        mode = "0400";
        owner = config.services.postgresql.superUser;
        group =
          config.users.users.${config.services.postgresql.superUser}.group;
        sopsFile = ../../secrets/postgres.json;
        format = "json";
      };
    }))
    builtins.listToAttrs
  ];
}