X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;ds=sidebyside;f=systems%2Feldiron%2Fdatabases%2Fpostgresql.nix;h=721059a702097e70913b373810e36067cd5887d8;hb=1a64deeb894dc95e2645a75771732c6cc53a79ad;hpb=fa25ffd4583cc362075cd5e1b4130f33306103f0;p=perso%2FImmae%2FConfig%2FNix.git

diff --git a/systems/eldiron/databases/postgresql.nix b/systems/eldiron/databases/postgresql.nix
new file mode 100644
index 0000000..721059a
--- /dev/null
+++ b/systems/eldiron/databases/postgresql.nix
@@ -0,0 +1,236 @@
+{ 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"}
+          '';
+      };
+    };
+  };
+}
+