path: root/systems/eldiron/databases/postgresql.nix

{ lib, pkgs, config, ... }:
let
    cfg = config.myServices.databases.postgresql;
in {
  options.myServices.databases = {
    postgresql = {
      enable = lib.mkOption {
        default = false;
        example = true;
        description = "Whether to enable postgresql database";
        type = lib.types.bool;
      };
      package = lib.mkOption {
        type = lib.types.package;
        default = pkgs.postgresql;
        description = ''
          Postgresql package to use.
          '';
      };
      ldapConfig = lib.mkOption {
        description = "LDAP configuration to allow PAM identification via LDAP";
        type = lib.types.submodule {
          options = {
            host = lib.mkOption { type = lib.types.str; };
            base = lib.mkOption { type = lib.types.str; };
            dn = lib.mkOption { type = lib.types.str; };
            password = lib.mkOption { type = lib.types.str; };
            filter = lib.mkOption { type = lib.types.str; };
          };
        };
      };
      replicationLdapConfig = lib.mkOption {
        description = "LDAP configuration to allow replication";
        type = lib.types.submodule {
          options = {
            host = lib.mkOption { type = lib.types.str; };
            base = lib.mkOption { type = lib.types.str; };
            dn = lib.mkOption { type = lib.types.str; };
            password = lib.mkOption { type = lib.types.str; };
          };
        };
      };
      authorizedHosts = lib.mkOption {
        default = {};
        description = "Hosts to allow connections from";
        type = lib.types.attrsOf (lib.types.listOf (lib.types.submodule {
          options = {
            method = lib.mkOption {
              default = "md5";
              type = lib.types.str;
            };
            username = lib.mkOption {
              default = "all";
              type = lib.types.str;
            };
            database = lib.mkOption {
              default = "all";
              type = lib.types.str;
            };
            ip4 = lib.mkOption {
              default = [];
              type = lib.types.listOf lib.types.str;
            };
            ip6 = lib.mkOption {
              default = [];
              type = lib.types.listOf lib.types.str;
            };
          };
        }));
      };
      replicationHosts = lib.mkOption {
        default = {};
        description = "Hosts to allow replication from";
        type = lib.types.attrsOf (lib.types.submodule {
          options = {
            ip4 = lib.mkOption {
              type = lib.types.listOf lib.types.str;
            };
            ip6 = lib.mkOption {
              type = lib.types.listOf lib.types.str;
            };
          };
        });
      };
      # Output variables
      socketsDir = lib.mkOption {
        type = lib.types.path;
        default = "/run/postgresql";
        description = ''
          The directory where Postgresql puts sockets.
          '';
        readOnly = true;
      };
    };
  };

  config = lib.mkIf cfg.enable {
    networking.firewall.allowedTCPPorts = [ 5432 ];

    security.acme.certs."postgresql" = {
      group = "postgres";
      domain = "db-1.immae.eu";
      postRun = ''
        systemctl reload postgresql.service
      '';
    };

    systemd.services.postgresql.serviceConfig = {
      SupplementaryGroups = "keys";
      ExecStartPre = [ ("+" + (pkgs.writeShellScript "postgresql-fix-cert" ''
          # postgresql complains:
          # private key file "${config.security.acme.certs.postgresql.directory}/key.pem" must be owned by the database user or root
          cp -f "${config.security.acme.certs.postgresql.directory}/key.pem" "${config.services.postgresql.dataDir}/key.pem"
          chown postgres:postgres "${config.services.postgresql.dataDir}/key.pem"
          chmod go-r "${config.services.postgresql.dataDir}/key.pem"
      '')) ];
    };
    systemd.services.postgresql.postStart = lib.mkAfter ''
      # This line is already defined in 19.09
      PSQL="psql --port=5432"

      ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
        $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
            | grep -q 1 \
            || $PSQL -tAc 'CREATE USER "${role}" WITH REPLICATION'
        '') cfg.replicationHosts)}

      ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _:
        let
          sname = builtins.replaceStrings ["-"] ["_"] role;
        in
        ''
        $PSQL -tAc "SELECT 1 FROM pg_replication_slots WHERE slot_name='${sname}'" \
              | grep -q 1 \
              || $PSQL -tAc "SELECT * FROM pg_create_physical_replication_slot('${sname}')"
        '') cfg.replicationHosts)}
      '';

    services.postgresql = {
      enable = true;
      package = cfg.package;
      enableTCPIP = true;
      checkConfig = false;
      logLinePrefix = "%h %q%u@%d "; # Default: '%m [%p] ', already stored independently by journald. %h needed for fail2ban
      settings = {
        max_connections = 300;
        wal_level = "logical";
        shared_buffers = "512MB";
        work_mem = "10MB";
        max_wal_size = "1GB";
        min_wal_size = "80MB";
        log_timezone = "Europe/Paris";
        datestyle = "iso, mdy";
        timezone = "Europe/Paris";
        lc_messages = "en_US.UTF-8";
        lc_monetary = "en_US.UTF-8";
        lc_numeric = "en_US.UTF-8";
        lc_time = "en_US.UTF-8";
        default_text_search_config = "pg_catalog.english";
        # this introduces a small delay before storing on disk, but
        # makes it order of magnitudes quicker
        synchronous_commit = "off";
        ssl = "on";
        ssl_cert_file = "${config.security.acme.certs.postgresql.directory}/fullchain.pem";
        ssl_key_file = "${config.services.postgresql.dataDir}/key.pem";
      };
      authentication = let
        hosts = builtins.concatStringsSep "\n" (
          lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v:
            map (ip6: "hostssl	${v.database}	${v.username}	${ip6}	${v.method}") v.ip6
            ++ map (ip4: "hostssl	${v.database}	${v.username}	${ip4}/32		${v.method}") v.ip4
          ) vs) cfg.authorizedHosts
        ));
        replication = builtins.concatStringsSep "\n" (
          lib.lists.flatten (lib.mapAttrsToList (k: v:
          map (ip6: "hostssl	replication	${k}	${ip6}/128	pam pamservice=postgresql_replication") v.ip6
          ++ map (ip4: "hostssl	replication	${k}	${ip4}/32		pam pamservice=postgresql_replication") v.ip4
          ) cfg.replicationHosts
        ));
      in ''
        local	all	postgres				ident
        local	all	all					md5
        ${hosts}
        hostssl	all	all	all				pam
        ${replication}
      '';
    };

    secrets.keys = {
      "postgresql/pam" = {
        permissions = "0400";
        group = "postgres";
        user = "postgres";
        text =  with cfg.ldapConfig; ''
          host ${host}
          base ${base}
          binddn ${dn}
          bindpw ${password}
          pam_filter ${filter}
          ssl start_tls
        '';
      };
      "postgresql/pam_replication" = {
        permissions = "0400";
        group = "postgres";
        user = "postgres";
        text = with cfg.replicationLdapConfig; ''
          host ${host}
          base ${base}
          binddn ${dn}
          bindpw ${password}
          pam_login_attribute cn
          ssl start_tls
        '';
      };
    };

    security.pam.services = let
      pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
    in {
      postgresql = {
        text = ''
          auth    required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
          account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
          '';
      };
      postgresql_replication = {
        text = ''
          auth    required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
          account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
          '';
      };
    };
  };
}