hosts/mermet/postgresql.nix

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