hosts/losurdo/postgresql.nix

   1 { pkgs, lib, config, hostName, ... }:
   2 let
   3   inherit (config) networking;
   4   inherit (config.services) postgresql;
   5   inherit (config.users) users;
   6 in
   7 {
   8 imports =
   9   map (eta: (import postgresql/openconcerto.nix) eta) [
  10     {db = "openconcerto1";}
  11     {db = "openconcerto2";}
  12     {db = "lbec";}
  13     {db = "lbm";}
  14   ];
  15 networking.nftables.ruleset = ''
  16   add rule inet filter net2fw tcp dport 5432 counter accept comment "PostgreSQL"
  17 '';
  18 users.groups.acme.members = [ users."postgres".name ];
  19 security.acme.certs."${networking.domain}" = {
  20   postRun = "systemctl reload postgresql";
  21 };
  22 systemd.services.postgresql = {
  23   wants = [ "acme-selfsigned-${networking.domain}.service" "acme-${networking.domain}.service"];
  24   after = [ "acme-selfsigned-${networking.domain}.service" ];
  25 };
  26 services.postgresql = {
  27   enable = true;
  28   package = pkgs.postgresql_9_6;
  29   enableTCPIP = true;
  30   settings = {
  31     # ZFS is Copy on Write (CoW). As a result, it’s not possible
  32     # to have a torn page because a page can’t be partially written
  33     # without reverting to the previous copy.
  34     full_page_writes = false;
  35     log_connections = true;
  36     log_disconnections = true;
  37     log_hostname = false;
  38     max_connections = 25;
  39     max_locks_per_transaction = 1024;
  40     password_encryption = "on"; # FIXME: replace md5 by scram-sha-256, which requires postfix >= 11
  41     ssl = true;
  42     ssl_cert_file = "/var/lib/acme/${networking.domain}/fullchain.pem";
  43     ssl_key_file = "/var/lib/acme/${networking.domain}/key.pem";
  44     unix_socket_permissions = "0770";
  45   };
  46   authentication = lib.mkForce ''
  47     # CONNECTION  DATABASE USER      AUTH  OPTIONS
  48     local         all      postgres  peer  map=admin
  49     local         samerole all       peer  map=user
  50     #local         all      backup    peer
  51   '';
  52   identMap = ''
  53     # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
  54     admin      postgres         postgres
  55     admin      root             postgres
  56     # Commented to prefer explicit auth
  57     # to handle revocation without droping the user yet
  58     #user       /^(.*)$          \1
  59   '';
  60 };
  61 fileSystems."/var/lib/postgresql" = {
  62   device = "${hostName}/var/postgresql";
  63   fsType = "zfs"; # with sync=always,
  64   # though loading OpenConcerto's initial SQL
  65   # takes 1m40s instead of 40s :\
  66 };
  67 services.syncoid.commands = {
  68   "${hostName}/var/postgresql" = {
  69     sendOptions = "raw";
  70     target = "backup@mermet.${networking.domain}:rpool/backup/${hostName}/var/postgresql";
  71   };
  72 };
  73 services.sanoid.datasets = {
  74   "${hostName}/var/postgresql" = {
  75     use_template = [ "local" ];
  76     daily = 31;
  77   };
  78 };
  79 systemd.services.postgresql = {
  80   # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
  81   postStart = ''
  82     set -eux
  83     # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
  84     $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
  85       -- Disallow access to the public schema
  86       -- of individual users' databases by other users
  87       REVOKE ALL ON DATABASE template0 FROM public;
  88       REVOKE ALL ON DATABASE template1 FROM public;
  89       REVOKE ALL ON SCHEMA public FROM public;
  90       GRANT  ALL ON SCHEMA public TO ${postgresql.superUser};
  91       REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
  92       GRANT  ALL ON ALL TABLES IN SCHEMA public TO ${postgresql.superUser};
  93
  94       -- Disallow access to database and user names for everyone
  95       REVOKE ALL ON pg_catalog.pg_user         FROM public;
  96       REVOKE ALL ON pg_catalog.pg_group        FROM public;
  97       REVOKE ALL ON pg_catalog.pg_authid       FROM public;
  98       REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
  99       REVOKE ALL ON pg_catalog.pg_settings     FROM public;
 100       -- The following must have SELECT reallowed if pg_dump is allowed
 101       REVOKE ALL ON pg_catalog.pg_roles        FROM public;
 102       REVOKE ALL ON pg_catalog.pg_database     FROM public;
 103       REVOKE ALL ON pg_catalog.pg_tablespace   FROM public;
 104     EOF
 105
 106     pg_createdb () {
 107       local db=$1
 108       local owner=''${owner:-$db}
 109       local template=''${template:-template1}
 110       $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
 111         $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
 112           CREATE ROLE $owner NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN PASSWORD '$pass';
 113           CREATE DATABASE $db WITH OWNER=$owner
 114            ''${encoding:+ENCODING='$encoding'}
 115            ''${lc_collate:+LC_COLLATE='$lc_collate'}
 116            ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
 117            ''${tablespace:+TABLESPACE='$tablespace'}
 118            ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
 119           ;
 120           REVOKE ALL ON DATABASE $db FROM public;
 121     EOF
 122         $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
 123           -- Grant all rights to the public schema in the new database to the main user
 124           GRANT ALL ON SCHEMA public TO $owner WITH GRANT OPTION;
 125     EOF
 126         $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
 127       }
 128     }
 129
 130     pg_adduser () {
 131       local db=$1
 132       local user=$2
 133       $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
 134       $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
 135         CREATE ROLE $user NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '$pass';
 136         GRANT USAGE ON SCHEMA public TO $user;
 137         GRANT CONNECT,TEMPORARY ON DATABASE $db TO $user;
 138     EOF
 139     }
 140   '';
 141 };
 142 }