systems/eldiron/databases/postgresql.nix

   1 { lib, pkgs, config, ... }:
   2 let
   3     cfg = config.myServices.databases.postgresql;
   4 in {
   5   options.myServices.databases = {
   6     postgresql = {
   7       enable = lib.mkOption {
   8         default = false;
   9         example = true;
  10         description = "Whether to enable postgresql database";
  11         type = lib.types.bool;
  12       };
  13       package = lib.mkOption {
  14         type = lib.types.package;
  15         default = pkgs.postgresql;
  16         description = ''
  17           Postgresql package to use.
  18           '';
  19       };
  20       ldapConfig = lib.mkOption {
  21         description = "LDAP configuration to allow PAM identification via LDAP";
  22         type = lib.types.submodule {
  23           options = {
  24             host = lib.mkOption { type = lib.types.str; };
  25             base = lib.mkOption { type = lib.types.str; };
  26             dn = lib.mkOption { type = lib.types.str; };
  27             password = lib.mkOption { type = lib.types.str; };
  28             filter = lib.mkOption { type = lib.types.str; };
  29           };
  30         };
  31       };
  32       replicationLdapConfig = lib.mkOption {
  33         description = "LDAP configuration to allow replication";
  34         type = lib.types.submodule {
  35           options = {
  36             host = lib.mkOption { type = lib.types.str; };
  37             base = lib.mkOption { type = lib.types.str; };
  38             dn = lib.mkOption { type = lib.types.str; };
  39             password = lib.mkOption { type = lib.types.str; };
  40           };
  41         };
  42       };
  43       authorizedHosts = lib.mkOption {
  44         default = {};
  45         description = "Hosts to allow connections from";
  46         type = lib.types.attrsOf (lib.types.listOf (lib.types.submodule {
  47           options = {
  48             method = lib.mkOption {
  49               default = "md5";
  50               type = lib.types.str;
  51             };
  52             username = lib.mkOption {
  53               default = "all";
  54               type = lib.types.str;
  55             };
  56             database = lib.mkOption {
  57               default = "all";
  58               type = lib.types.str;
  59             };
  60             ip4 = lib.mkOption {
  61               default = [];
  62               type = lib.types.listOf lib.types.str;
  63             };
  64             ip6 = lib.mkOption {
  65               default = [];
  66               type = lib.types.listOf lib.types.str;
  67             };
  68           };
  69         }));
  70       };
  71       replicationHosts = lib.mkOption {
  72         default = {};
  73         description = "Hosts to allow replication from";
  74         type = lib.types.attrsOf (lib.types.submodule {
  75           options = {
  76             ip4 = lib.mkOption {
  77               type = lib.types.listOf lib.types.str;
  78             };
  79             ip6 = lib.mkOption {
  80               type = lib.types.listOf lib.types.str;
  81             };
  82           };
  83         });
  84       };
  85       # Output variables
  86       socketsDir = lib.mkOption {
  87         type = lib.types.path;
  88         default = "/run/postgresql";
  89         description = ''
  90           The directory where Postgresql puts sockets.
  91           '';
  92         readOnly = true;
  93       };
  94     };
  95   };
  96
  97   config = lib.mkIf cfg.enable {
  98     networking.firewall.allowedTCPPorts = [ 5432 ];
  99
 100     security.acme.certs."postgresql" = {
 101       group = "postgres";
 102       domain = "db-1.immae.eu";
 103       postRun = ''
 104         systemctl reload postgresql.service
 105       '';
 106     };
 107
 108     systemd.services.postgresql.serviceConfig = {
 109       SupplementaryGroups = "keys";
 110       ExecStartPre = [ ("+" + (pkgs.writeShellScript "postgresql-fix-cert" ''
 111           # postgresql complains:
 112           # private key file "${config.security.acme.certs.postgresql.directory}/key.pem" must be owned by the database user or root
 113           cp -f "${config.security.acme.certs.postgresql.directory}/key.pem" "${config.services.postgresql.dataDir}/key.pem"
 114           chown postgres:postgres "${config.services.postgresql.dataDir}/key.pem"
 115           chmod go-r "${config.services.postgresql.dataDir}/key.pem"
 116       '')) ];
 117     };
 118     systemd.services.postgresql.postStart = lib.mkAfter ''
 119       # This line is already defined in 19.09
 120       PSQL="psql --port=5432"
 121
 122       ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
 123         $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
 124             | grep -q 1 \
 125             || $PSQL -tAc 'CREATE USER "${role}" WITH REPLICATION'
 126         '') cfg.replicationHosts)}
 127
 128       ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _:
 129         let
 130           sname = builtins.replaceStrings ["-"] ["_"] role;
 131         in
 132         ''
 133         $PSQL -tAc "SELECT 1 FROM pg_replication_slots WHERE slot_name='${sname}'" \
 134               | grep -q 1 \
 135               || $PSQL -tAc "SELECT * FROM pg_create_physical_replication_slot('${sname}')"
 136         '') cfg.replicationHosts)}
 137       '';
 138
 139     services.postgresql = {
 140       enable = true;
 141       package = cfg.package;
 142       enableTCPIP = true;
 143       checkConfig = false;
 144       logLinePrefix = "%h %q%u@%d "; # Default: '%m [%p] ', already stored independently by journald. %h needed for fail2ban
 145       settings = {
 146         max_connections = 300;
 147         wal_level = "logical";
 148         shared_buffers = "512MB";
 149         work_mem = "10MB";
 150         max_wal_size = "1GB";
 151         min_wal_size = "80MB";
 152         log_timezone = "Europe/Paris";
 153         datestyle = "iso, mdy";
 154         timezone = "Europe/Paris";
 155         lc_messages = "en_US.UTF-8";
 156         lc_monetary = "en_US.UTF-8";
 157         lc_numeric = "en_US.UTF-8";
 158         lc_time = "en_US.UTF-8";
 159         default_text_search_config = "pg_catalog.english";
 160         # this introduces a small delay before storing on disk, but
 161         # makes it order of magnitudes quicker
 162         synchronous_commit = "off";
 163         ssl = "on";
 164         ssl_cert_file = "${config.security.acme.certs.postgresql.directory}/fullchain.pem";
 165         ssl_key_file = "${config.services.postgresql.dataDir}/key.pem";
 166       };
 167       authentication = let
 168         hosts = builtins.concatStringsSep "\n" (
 169           lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v:
 170             map (ip6: "hostssl  ${v.database}   ${v.username}   ${ip6}  ${v.method}") v.ip6
 171             ++ map (ip4: "hostssl       ${v.database}   ${v.username}   ${ip4}/32               ${v.method}") v.ip4
 172           ) vs) cfg.authorizedHosts
 173         ));
 174         replication = builtins.concatStringsSep "\n" (
 175           lib.lists.flatten (lib.mapAttrsToList (k: v:
 176           map (ip6: "hostssl    replication     ${k}    ${ip6}/128      pam pamservice=postgresql_replication") v.ip6
 177           ++ map (ip4: "hostssl replication     ${k}    ${ip4}/32               pam pamservice=postgresql_replication") v.ip4
 178           ) cfg.replicationHosts
 179         ));
 180       in ''
 181         local   all     postgres                                ident
 182         local   all     all                                     md5
 183         ${hosts}
 184         hostssl all     all     all                             pam
 185         ${replication}
 186       '';
 187     };
 188
 189     secrets.keys = {
 190       "postgresql/pam" = {
 191         permissions = "0400";
 192         group = "postgres";
 193         user = "postgres";
 194         text =  with cfg.ldapConfig; ''
 195           host ${host}
 196           base ${base}
 197           binddn ${dn}
 198           bindpw ${password}
 199           pam_filter ${filter}
 200           ssl start_tls
 201         '';
 202       };
 203       "postgresql/pam_replication" = {
 204         permissions = "0400";
 205         group = "postgres";
 206         user = "postgres";
 207         text = with cfg.replicationLdapConfig; ''
 208           host ${host}
 209           base ${base}
 210           binddn ${dn}
 211           bindpw ${password}
 212           pam_login_attribute cn
 213           ssl start_tls
 214         '';
 215       };
 216     };
 217
 218     security.pam.services = let
 219       pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
 220     in {
 221       postgresql = {
 222         text = ''
 223           auth    required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
 224           account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
 225           '';
 226       };
 227       postgresql_replication = {
 228         text = ''
 229           auth    required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
 230           account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
 231           '';
 232       };
 233     };
 234   };
 235 }
 236