hosts/mermet/postgresql.nix

   1 { pkgs, config, ... }:
   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       # ZFS is Copy on Write (CoW). As a result, it’s not possible
  29       # to have a torn page because a page can’t be partially written
  30       # without reverting to the previous copy.
  31       full_page_writes = false;
  32       log_connections = true;
  33       log_disconnections = true;
  34       log_hostname = false;
  35       max_connections = 25;
  36       max_locks_per_transaction = 1024;
  37       password_encryption = "scram-sha-256"; # requires postfix >= 11
  38       unix_socket_permissions = "0770";
  39     };
  40     # If one record is chosen and the auth fails,
  41     # subsequent records are not considered.
  42     authentication = ''
  43       # CONNECTION  DATABASE USER      AUTH  OPTIONS
  44       local         all      postgres  peer  map=admin
  45       local         samerole all       peer  map=user
  46       #local         all      backup    peer
  47     '';
  48     identMap = ''
  49       # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
  50       admin      postgres         postgres
  51       admin      root             postgres
  52       # Commented to prefer explicit auth
  53       # to handle revocation without droping the user yet
  54       #user       /^(.*)$          \1
  55     '';
  56   };
  57   fileSystems."/var/lib/postgresql" = {
  58     device = "rpool/var/postgresql";
  59     fsType = "zfs"; # with sync=always,
  60     # though loading OpenConcerto's initial SQL
  61     # takes 1m40s instead of 40s :\
  62   };
  63   services.sanoid.datasets = {
  64     "rpool/var/postgresql" = {
  65       use_template = [ "snap" ];
  66       daily = 31;
  67     };
  68   };
  69   systemd.services.postgresql = {
  70     # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
  71     postStart = ''
  72       set -eux
  73       # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
  74       $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
  75         -- Disallow access to the public schema
  76         -- of individual users' databases by other users
  77         REVOKE ALL ON DATABASE template0 FROM public;
  78         REVOKE ALL ON DATABASE template1 FROM public;
  79         REVOKE ALL ON SCHEMA public FROM public;
  80         GRANT USAGE,CREATE ON SCHEMA public TO public;
  81         GRANT  ALL ON SCHEMA public TO "${postgresql.superUser}";
  82         REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
  83         GRANT  ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
  84
  85         -- Disallow access to database and user names for everyone
  86         REVOKE ALL ON pg_catalog.pg_user         FROM public;
  87         REVOKE ALL ON pg_catalog.pg_group        FROM public;
  88         REVOKE ALL ON pg_catalog.pg_authid       FROM public;
  89         REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
  90         REVOKE ALL ON pg_catalog.pg_settings     FROM public;
  91         -- The following must have SELECT reallowed if pg_dump is allowed
  92         REVOKE ALL ON pg_catalog.pg_roles        FROM public;
  93         REVOKE ALL ON pg_catalog.pg_database     FROM public;
  94         REVOKE ALL ON pg_catalog.pg_tablespace   FROM public;
  95       EOF
  96     '';
  97   };
  98 }