modules/private/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       systemdRuntimeDirectory = lib.mkOption {
  95         type = lib.types.str;
  96         # Use ReadWritePaths= instead if socketsDir is outside of /run
  97         default = assert lib.strings.hasPrefix "/run/" cfg.socketsDir;
  98           lib.strings.removePrefix "/run/" cfg.socketsDir;
  99         description = ''
 100         Adjusted Postgresql sockets directory for systemd
 101         '';
 102         readOnly = true;
 103       };
 104     };
 105   };
 106
 107   config = lib.mkIf cfg.enable {
 108     networking.firewall.allowedTCPPorts = [ 5432 ];
 109
 110     security.acme2.certs."postgresql" = config.myServices.databasesCerts // {
 111       user = "postgres";
 112       group = "postgres";
 113       plugins = [ "fullchain.pem" "key.pem" "account_key.json" "account_reg.json" ];
 114       domain = "db-1.immae.eu";
 115       postRun = ''
 116         systemctl reload postgresql.service
 117       '';
 118     };
 119
 120     systemd.services.postgresql.serviceConfig = {
 121       SupplementaryGroups = "keys";
 122       RuntimeDirectory = cfg.systemdRuntimeDirectory;
 123     };
 124     systemd.services.postgresql.postStart = lib.mkAfter ''
 125       # This line is already defined in 19.09
 126       PSQL="${pkgs.sudo}/bin/sudo -u postgres psql --port=5432"
 127
 128       ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
 129         $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
 130             | grep -q 1 \
 131             || $PSQL -tAc 'CREATE USER "${role}" WITH REPLICATION'
 132         '') cfg.replicationHosts)}
 133
 134       ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _:
 135         let
 136           sname = builtins.replaceStrings ["-"] ["_"] role;
 137         in
 138         ''
 139         $PSQL -tAc "SELECT 1 FROM pg_replication_slots WHERE slot_name='${sname}'" \
 140               | grep -q 1 \
 141               || $PSQL -tAc "SELECT * FROM pg_create_physical_replication_slot('${sname}')"
 142         '') cfg.replicationHosts)}
 143       '';
 144
 145     services.postgresql = {
 146       enable = true;
 147       package = cfg.package;
 148       enableTCPIP = true;
 149       extraConfig = ''
 150         max_connections = 100
 151         wal_level = logical
 152         shared_buffers = 512MB
 153         work_mem = 10MB
 154         max_wal_size = 1GB
 155         min_wal_size = 80MB
 156         log_timezone = 'Europe/Paris'
 157         datestyle = 'iso, mdy'
 158         timezone = 'Europe/Paris'
 159         lc_messages = 'en_US.UTF-8'
 160         lc_monetary = 'en_US.UTF-8'
 161         lc_numeric = 'en_US.UTF-8'
 162         lc_time = 'en_US.UTF-8'
 163         default_text_search_config = 'pg_catalog.english'
 164         # this introduces a small delay before storing on disk, but
 165         # makes it order of magnitudes quicker
 166         synchronous_commit = off
 167         ssl = on
 168         ssl_cert_file = '${config.security.acme2.certs.postgresql.directory}/fullchain.pem'
 169         ssl_key_file = '${config.security.acme2.certs.postgresql.directory}/key.pem'
 170         '';
 171       authentication = let
 172         hosts = builtins.concatStringsSep "\n" (
 173           lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v:
 174             map (ip6: "hostssl  ${v.database}   ${v.username}   ${ip6}/128      ${v.method}") v.ip6
 175             ++ map (ip4: "hostssl       ${v.database}   ${v.username}   ${ip4}/32               ${v.method}") v.ip4
 176           ) vs) cfg.authorizedHosts
 177         ));
 178         replication = builtins.concatStringsSep "\n" (
 179           lib.lists.flatten (lib.mapAttrsToList (k: v:
 180           map (ip6: "hostssl    replication     ${k}    ${ip6}/128      pam pamservice=postgresql_replication") v.ip6
 181           ++ map (ip4: "hostssl replication     ${k}    ${ip4}/32               pam pamservice=postgresql_replication") v.ip4
 182           ) cfg.replicationHosts
 183         ));
 184       in ''
 185         local   all     postgres                                ident
 186         local   all     all                                     md5
 187         ${hosts}
 188         hostssl all     all     all                             pam
 189         ${replication}
 190       '';
 191     };
 192
 193     secrets.keys = [
 194       {
 195         dest = "postgresql/pam";
 196         permissions = "0400";
 197         group = "postgres";
 198         user = "postgres";
 199         text =  with cfg.ldapConfig; ''
 200           host ${host}
 201           base ${base}
 202           binddn ${dn}
 203           bindpw ${password}
 204           pam_filter ${filter}
 205           ssl start_tls
 206         '';
 207       }
 208       {
 209         dest = "postgresql/pam_replication";
 210         permissions = "0400";
 211         group = "postgres";
 212         user = "postgres";
 213         text = with cfg.replicationLdapConfig; ''
 214           host ${host}
 215           base ${base}
 216           binddn ${dn}
 217           bindpw ${password}
 218           pam_login_attribute cn
 219           ssl start_tls
 220         '';
 221       }
 222     ];
 223
 224     security.pam.services = let
 225       pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
 226     in [
 227       {
 228         name = "postgresql";
 229         text = ''
 230           auth    required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
 231           account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
 232           '';
 233       }
 234       {
 235         name = "postgresql_replication";
 236         text = ''
 237           auth    required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication
 238           account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication
 239           '';
 240       }
 241     ];
 242   };
 243 }
 244