X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=modules%2Fprivate%2Fdatabases%2Fpostgresql.nix;h=c442a63c01b727913b41f5e275c130fd918497db;hb=258dd18bac4bf5dd03cf1098ffa35cb954f9e015;hp=26242a8e02a2a25b00c321f3dae922ea5c7d0159;hpb=182ae57f53731be220075bc87aff4d47a35563b8;p=perso%2FImmae%2FConfig%2FNix.git diff --git a/modules/private/databases/postgresql.nix b/modules/private/databases/postgresql.nix index 26242a8..c442a63 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,18 +149,32 @@ 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 = '/var/lib/acme/postgresql/fullchain.pem' - ssl_key_file = '/var/lib/acme/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} ''; }; @@ -93,9 +184,9 @@ in { 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} @@ -107,11 +198,11 @@ in { 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 ''; @@ -120,22 +211,20 @@ in { 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 ''; - } - { - 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 ''; - } - ]; + }; + }; }; }