hosts/mermet/postgresql.nix

   1 { pkgs, lib, config, hostName, ... }:
   2 let
   3   inherit (config.networking) domain;
   4   inherit (config.services) postgresql;
   5   inherit (config.users) users;
   6 in
   7 {
   8 /*
   9 networking.nftables.ruleset = ''
  10   add rule inet filter net2fw tcp dport 5432 counter accept comment "PostgreSQL"
  11 '';
  12 users.groups.acme.members = [ users."postgres".name ];
  13 security.acme.certs."${domain}" = {
  14   postRun = "systemctl reload postgresql";
  15 };
  16 systemd.services.postgresql = {
  17   wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service"];
  18   after = [ "acme-selfsigned-${domain}.service" ];
  19 };
  20 */
  21 services.postgresql = {
  22   enable = true;
  23   package = pkgs.postgresql_12;
  24   enableTCPIP = false;
  25   settings = {
  26     # ZFS is Copy on Write (CoW). As a result, it’s not possible
  27     # to have a torn page because a page can’t be partially written
  28     # without reverting to the previous copy.
  29     full_page_writes = false;
  30     log_connections = true;
  31     log_disconnections = true;
  32     log_hostname = false;
  33     max_connections = 25;
  34     max_locks_per_transaction = 1024;
  35     password_encryption = "scram-sha-256"; # requires postfix >= 11
  36     unix_socket_permissions = "0770";
  37   };
  38   # If one record is chosen and the auth fails,
  39   # subsequent records are not considered.
  40   authentication = ''
  41     # CONNECTION  DATABASE USER      AUTH  OPTIONS
  42     local         all      postgres  peer  map=admin
  43     local         samerole all       peer  map=user
  44     #local         all      backup    peer
  45   '';
  46   identMap = ''
  47     # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
  48     admin      postgres         postgres
  49     admin      root             postgres
  50     # Commented to prefer explicit auth
  51     # to handle revocation without droping the user yet
  52     #user       /^(.*)$          \1
  53   '';
  54 };
  55 fileSystems."/var/lib/postgresql" = {
  56   device = "rpool/var/postgresql";
  57   fsType = "zfs"; # with sync=always,
  58   # though loading OpenConcerto's initial SQL
  59   # takes 1m40s instead of 40s :\
  60 };
  61 services.sanoid.datasets = {
  62   "rpool/var/postgresql" = {
  63     use_template = [ "snap" ];
  64     daily = 31;
  65   };
  66 };
  67 systemd.services.postgresql = {
  68   # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
  69   postStart = ''
  70     set -eux
  71     # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
  72     $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
  73       -- Disallow access to the public schema
  74       -- of individual users' databases by other users
  75       REVOKE ALL ON DATABASE template0 FROM public;
  76       REVOKE ALL ON DATABASE template1 FROM public;
  77       REVOKE ALL ON SCHEMA public FROM public;
  78       GRANT USAGE,CREATE ON SCHEMA public TO public;
  79       GRANT  ALL ON SCHEMA public TO "${postgresql.superUser}";
  80       REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
  81       GRANT  ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
  82
  83       -- Disallow access to database and user names for everyone
  84       REVOKE ALL ON pg_catalog.pg_user         FROM public;
  85       REVOKE ALL ON pg_catalog.pg_group        FROM public;
  86       REVOKE ALL ON pg_catalog.pg_authid       FROM public;
  87       REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
  88       REVOKE ALL ON pg_catalog.pg_settings     FROM public;
  89       -- The following must have SELECT reallowed if pg_dump is allowed
  90       REVOKE ALL ON pg_catalog.pg_roles        FROM public;
  91       REVOKE ALL ON pg_catalog.pg_database     FROM public;
  92       REVOKE ALL ON pg_catalog.pg_tablespace   FROM public;
  93     EOF
  94   '';
  95 };
  96 }