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