hosts/losurdo/postgresql.nix

   1 { pkgs, 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 }