X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=modules%2Fprivate%2Fdatabases%2Fpostgresql.nix;h=a6c4cc998fdb138ccf53671992a916794b6eff95;hb=4c4652aabf2cb3ac8b40f2856eca07a1df9c27e0;hp=8c36d84e0ccbab15e68b555c4d46008861fd5ffc;hpb=9ade8f6eb774dc7d19d82a070199b5024786b819;p=perso%2FImmae%2FConfig%2FNix.git diff --git a/modules/private/databases/postgresql.nix b/modules/private/databases/postgresql.nix index 8c36d84..a6c4cc9 100644 --- a/modules/private/databases/postgresql.nix +++ b/modules/private/databases/postgresql.nix @@ -1,15 +1,87 @@ -{ lib, pkgs, config, myconfig, ... }: +{ lib, pkgs, config, ... }: let cfg = config.myServices.databases.postgresql; in { options.myServices.databases = { postgresql = { enable = lib.mkOption { - default = cfg.enable; + 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; @@ -19,30 +91,15 @@ in { ''; readOnly = true; }; - systemdRuntimeDirectory = lib.mkOption { - type = lib.types.str; - # Use ReadWritePaths= instead if socketsDir is outside of /run - default = assert lib.strings.hasPrefix "/run/" cfg.socketsDir; - lib.strings.removePrefix "/run/" cfg.socketsDir; - description = '' - Adjusted Postgresql sockets directory for systemd - ''; - readOnly = true; - }; }; }; config = lib.mkIf cfg.enable { - nixpkgs.overlays = [ (self: super: rec { - postgresql = self.postgresql_11_custom; - }) ]; - networking.firewall.allowedTCPPorts = [ 5432 ]; security.acme.certs."postgresql" = config.myServices.databasesCerts // { user = "postgres"; group = "postgres"; - plugins = [ "fullchain.pem" "key.pem" "account_key.json" ]; domain = "db-1.immae.eu"; postRun = '' systemctl reload postgresql.service @@ -51,11 +108,31 @@ in { systemd.services.postgresql.serviceConfig = { SupplementaryGroups = "keys"; - RuntimeDirectory = cfg.systemdRuntimeDirectory; }; - services.postgresql = rec { + systemd.services.postgresql.postStart = lib.mkAfter '' + # This line is already defined in 19.09 + PSQL="${pkgs.sudo}/bin/sudo -u postgres 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 = pkgs.postgresql; + package = cfg.package; enableTCPIP = true; extraConfig = '' max_connections = 100 @@ -72,70 +149,80 @@ in { 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.directory}/postgresql/fullchain.pem' - ssl_key_file = '${config.security.acme.directory}/postgresql/key.pem' + ssl_cert_file = '${config.security.acme.certs.postgresql.directory}/fullchain.pem' + ssl_key_file = '${config.security.acme.certs.postgresql.directory}/key.pem' ''; - authentication = '' + authentication = let + hosts = builtins.concatStringsSep "\n" ( + lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v: + map (ip6: "hostssl ${v.database} ${v.username} ${ip6}/128 ${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 - hostssl all all 188.165.209.148/32 md5 - hostssl all all 178.33.252.96/32 md5 + ${hosts} hostssl all all all pam - hostssl replication backup-1 2001:41d0:302:1100::9:e5a9/128 pam pamservice=postgresql_replication - hostssl replication backup-1 54.37.151.137/32 pam pamservice=postgresql_replication + ${replication} ''; }; - secrets.keys = [ - { - dest = "postgresql/pam"; + secrets.keys = { + "postgresql/pam" = { permissions = "0400"; group = "postgres"; user = "postgres"; - text = with myconfig.env.databases.postgresql.pam; '' - host ${myconfig.env.ldap.host} - base ${myconfig.env.ldap.base} + text = with cfg.ldapConfig; '' + host ${host} + base ${base} binddn ${dn} bindpw ${password} pam_filter ${filter} ssl start_tls ''; - } - { - dest = "postgresql/pam_replication"; + }; + "postgresql/pam_replication" = { permissions = "0400"; group = "postgres"; user = "postgres"; - text = '' - host ${myconfig.env.ldap.host} - base ${myconfig.env.ldap.base} - binddn ${myconfig.env.ldap.host_dn} - bindpw ${myconfig.env.ldap.password} + 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 [ - { - name = "postgresql"; + in { + postgresql = { text = '' - auth required ${pam_ldap} config=${config.secrets.location}/postgresql/pam - account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam + auth required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"} + account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"} ''; - } - { - name = "postgresql_replication"; + }; + postgresql_replication = { text = '' - auth required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication - account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication + auth required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"} + account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"} ''; - } - ]; + }; + }; }; }