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