modules/private/databases/postgresql.nix

   1 { lib, pkgs, config, myconfig,  ... }:
   2 let
   3     cfg = config.myServices.databases.postgresql;
   4 in {
   5   options.myServices.databases = {
   6     postgresql = {
   7       enable = lib.mkOption {
   8         default = cfg.enable;
   9         example = true;
  10         description = "Whether to enable postgresql database";
  11         type = lib.types.bool;
  12       };
  13       # Output variables
  14       socketsDir = lib.mkOption {
  15         type = lib.types.path;
  16         default = "/run/postgresql";
  17         description = ''
  18           The directory where Postgresql puts sockets.
  19           '';
  20         readOnly = true;
  21       };
  22       systemdRuntimeDirectory = lib.mkOption {
  23         type = lib.types.str;
  24         # Use ReadWritePaths= instead if socketsDir is outside of /run
  25         default = assert lib.strings.hasPrefix "/run/" cfg.socketsDir;
  26           lib.strings.removePrefix "/run/" cfg.socketsDir;
  27         description = ''
  28         Adjusted Postgresql sockets directory for systemd
  29         '';
  30         readOnly = true;
  31       };
  32     };
  33   };
  34
  35   config = lib.mkIf cfg.enable {
  36     nixpkgs.overlays = [ (self: super: rec {
  37       postgresql = self.postgresql_11_custom;
  38     }) ];
  39
  40     networking.firewall.allowedTCPPorts = [ 5432 ];
  41
  42     security.acme.certs."postgresql" = config.myServices.databasesCerts // {
  43       user = "postgres";
  44       group = "postgres";
  45       plugins = [ "fullchain.pem" "key.pem" "account_key.json" ];
  46       domain = "db-1.immae.eu";
  47       postRun = ''
  48         systemctl reload postgresql.service
  49       '';
  50     };
  51
  52     systemd.services.postgresql.serviceConfig = {
  53       SupplementaryGroups = "keys";
  54       RuntimeDirectory = cfg.systemdRuntimeDirectory;
  55     };
  56     services.postgresql = rec {
  57       enable = true;
  58       package = pkgs.postgresql;
  59       enableTCPIP = true;
  60       extraConfig = ''
  61         max_connections = 100
  62         wal_level = logical
  63         shared_buffers = 512MB
  64         work_mem = 10MB
  65         max_wal_size = 1GB
  66         min_wal_size = 80MB
  67         log_timezone = 'Europe/Paris'
  68         datestyle = 'iso, mdy'
  69         timezone = 'Europe/Paris'
  70         lc_messages = 'en_US.UTF-8'
  71         lc_monetary = 'en_US.UTF-8'
  72         lc_numeric = 'en_US.UTF-8'
  73         lc_time = 'en_US.UTF-8'
  74         default_text_search_config = 'pg_catalog.english'
  75         ssl = on
  76         ssl_cert_file = '${config.security.acme.directory}/postgresql/fullchain.pem'
  77         ssl_key_file = '${config.security.acme.directory}/postgresql/key.pem'
  78         '';
  79       authentication = ''
  80         local   all     postgres                                ident
  81         local   all     all                                     md5
  82         hostssl all     all     188.165.209.148/32              md5
  83         hostssl all     all     178.33.252.96/32                md5
  84         hostssl all     all     all                             pam
  85         hostssl replication     backup-1        2001:41d0:302:1100::9:e5a9/128  pam pamservice=postgresql_replication
  86         hostssl replication     backup-1        54.37.151.137/32                pam pamservice=postgresql_replication
  87       '';
  88     };
  89
  90     secrets.keys = [
  91       {
  92         dest = "postgresql/pam";
  93         permissions = "0400";
  94         group = "postgres";
  95         user = "postgres";
  96         text =  with myconfig.env.databases.postgresql.pam; ''
  97           host ${myconfig.env.ldap.host}
  98           base ${myconfig.env.ldap.base}
  99           binddn ${dn}
 100           bindpw ${password}
 101           pam_filter ${filter}
 102           ssl start_tls
 103         '';
 104       }
 105       {
 106         dest = "postgresql/pam_replication";
 107         permissions = "0400";
 108         group = "postgres";
 109         user = "postgres";
 110         text = ''
 111           host ${myconfig.env.ldap.host}
 112           base ${myconfig.env.ldap.base}
 113           binddn ${myconfig.env.ldap.host_dn}
 114           bindpw ${myconfig.env.ldap.password}
 115           pam_login_attribute cn
 116           ssl start_tls
 117         '';
 118       }
 119     ];
 120
 121     security.pam.services = let
 122       pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
 123     in [
 124       {
 125         name = "postgresql";
 126         text = ''
 127           auth    required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
 128           account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
 129           '';
 130       }
 131       {
 132         name = "postgresql_replication";
 133         text = ''
 134           auth    required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication
 135           account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication
 136           '';
 137       }
 138     ];
 139   };
 140 }
 141