nixops/modules/databases/postgresql.nix

   1 { lib, pkgs, config, myconfig, mylibs, ... }:
   2 let
   3     cfg = config.services.myDatabases;
   4 in {
   5   options.services.myDatabases = {
   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     };
  14   };
  15
  16   config = lib.mkIf cfg.enable {
  17     nixpkgs.overlays = [ (self: super: rec {
  18       postgresql = self.postgresql_11_custom;
  19     }) ];
  20
  21     networking.firewall.allowedTCPPorts = [ 5432 ];
  22
  23     security.acme.certs."postgresql" = config.services.myCertificates.certConfig // {
  24       user = "postgres";
  25       group = "postgres";
  26       plugins = [ "fullchain.pem" "key.pem" "account_key.json" ];
  27       domain = "db-1.immae.eu";
  28       postRun = ''
  29         systemctl reload postgresql.service
  30       '';
  31     };
  32
  33     system.activationScripts.postgresql = ''
  34       install -m 0755 -o postgres -g postgres -d ${myconfig.env.databases.postgresql.socket}
  35       '';
  36
  37     systemd.services.postgresql.serviceConfig.SupplementaryGroups = "keys";
  38     services.postgresql = rec {
  39       enable = cfg.postgresql.enable;
  40       package = pkgs.postgresql;
  41       enableTCPIP = true;
  42       extraConfig = ''
  43         max_connections = 100
  44         wal_level = logical
  45         shared_buffers = 512MB
  46         work_mem = 10MB
  47         max_wal_size = 1GB
  48         min_wal_size = 80MB
  49         log_timezone = 'Europe/Paris'
  50         datestyle = 'iso, mdy'
  51         timezone = 'Europe/Paris'
  52         lc_messages = 'en_US.UTF-8'
  53         lc_monetary = 'en_US.UTF-8'
  54         lc_numeric = 'en_US.UTF-8'
  55         lc_time = 'en_US.UTF-8'
  56         default_text_search_config = 'pg_catalog.english'
  57         ssl = on
  58         ssl_cert_file = '/var/lib/acme/postgresql/fullchain.pem'
  59         ssl_key_file = '/var/lib/acme/postgresql/key.pem'
  60         '';
  61       authentication = ''
  62         local   all     postgres                                ident
  63         local   all     all                                     md5
  64         hostssl all     all     188.165.209.148/32              md5
  65         hostssl all     all     178.33.252.96/32                md5
  66         hostssl all     all     all                             pam
  67         hostssl replication     backup-1        2001:41d0:302:1100::9:e5a9/128  pam pamservice=postgresql_replication
  68         hostssl replication     backup-1        54.37.151.137/32                pam pamservice=postgresql_replication
  69       '';
  70     };
  71
  72     secrets.keys = [
  73       {
  74         dest = "postgresql/pam";
  75         permissions = "0400";
  76         group = "postgres";
  77         user = "postgres";
  78         text =  with myconfig.env.databases.postgresql.pam; ''
  79           host ${myconfig.env.ldap.host}
  80           base ${myconfig.env.ldap.base}
  81           binddn ${dn}
  82           bindpw ${password}
  83           pam_filter ${filter}
  84           ssl start_tls
  85         '';
  86       }
  87       {
  88         dest = "postgresql/pam_replication";
  89         permissions = "0400";
  90         group = "postgres";
  91         user = "postgres";
  92         text = ''
  93           host ${myconfig.env.ldap.host}
  94           base ${myconfig.env.ldap.base}
  95           binddn ${myconfig.env.ldap.host_dn}
  96           bindpw ${myconfig.env.ldap.password}
  97           pam_login_attribute cn
  98           ssl start_tls
  99         '';
 100       }
 101     ];
 102
 103     security.pam.services = let
 104       pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
 105     in [
 106       {
 107         name = "postgresql";
 108         text = ''
 109           auth    required ${pam_ldap} config=/var/secrets/postgresql/pam
 110           account required ${pam_ldap} config=/var/secrets/postgresql/pam
 111           '';
 112       }
 113       {
 114         name = "postgresql_replication";
 115         text = ''
 116           auth    required ${pam_ldap} config=/var/secrets/postgresql/pam_replication
 117           account required ${pam_ldap} config=/var/secrets/postgresql/pam_replication
 118           '';
 119       }
 120     ];
 121   };
 122 }
 123