{ lib, config, pkgs, ... }: {
  systemd.services.postgresql.after = [
    "docker-network-taiga.service" # Needed to listen in 172.20.0.1
  ];

  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" "jinx" ];

    ensureUsers = [
      {
        name = "clicks_grafana";
      }
      {
        name = "matrix-synapse";
      }
      {
        name = "keycloak";
        ensureDBOwnership = true;
      }
      {
        name = "vaultwarden";
        ensureDBOwnership = true;
      }
      {
        name = "privatebin";
        ensureDBOwnership = true;
      }
      {
        name = "nextcloud";
        ensureDBOwnership = true;
      }
      {
        name = "taiga";
        ensureDBOwnership = true;
      }
      {
        name = "taiga";
        ensureDBOwnership = true;
      }
      {
        name = "jinx";
        ensureDBOwnership = true;
      }
    ] ++ (map (name: ({
      inherit name;
    })) [ "minion" "coded" "pineafan" ]);

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

  systemd.services.postgresql.restartTriggers = [
    config.systemd.services.postgresql.postStart
  ];
  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;
      }
      {
        user = "jinx";
        passwordFile = config.sops.secrets.clicks_jinx_db_password.path;
      }
    ] [
      (map (userData: ''
        $PSQL -tAc "ALTER USER ${userData.user} PASSWORD '$(cat ${userData.passwordFile})';"
      ''))
      (lib.concatStringsSep "\n")
    ]))
    ''
      $PSQL -tAc 'ALTER DATABASE synapse OWNER TO "matrix-synapse";'
      # matrix-synapse is done manually, because the database does not have the same name as the user

      $PSQL -tAc 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "clicks_grafana"'
      $PSQL -tAc 'GRANT USAGE ON SCHEMA public TO "clicks_grafana"'
      # grafana is done manually, because it needs read permission in lots of places

      $PSQL -tAc 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "coded"'
      $PSQL -tAc 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "minion"'
      $PSQL -tAc 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "pineafan"'
      # leadership is done manually, because we need owner-level permissions in lots of places but cannot specify ourselves as the database owners (as there may only be 1)
    ''
  ];

  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"
    "clicks_jinx_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
  ];
}