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