hosts/mermet/postgresql.nix

   1 { pkgs, config, host, ... }:
   2 let
   3   inherit (config.services) postgresql;
   4 in
   5 {
   6   /*
   7     networking.nftables.ruleset = ''
   8     table inet filter {
   9     chain input-net {
  10       tcp dport postgresql counter accept comment "PostgreSQL"
  11     }
  12     }
  13     '';
  14     users.groups.acme.members = [ users."postgres".name ];
  15     security.acme.certs."${domain}" = {
  16     postRun = "systemctl reload postgresql";
  17     };
  18     systemd.services.postgresql = {
  19     wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service"];
  20     after = [ "acme-selfsigned-${domain}.service" ];
  21     };
  22   */
  23   environment.systemPackages = [
  24     pkgs.pg_top
  25   ];
  26   services.postgresql = {
  27     enable = true;
  28     package = pkgs.postgresql_12;
  29     enableTCPIP = false;
  30     # zfs set recordsize=16k
  31     # zfs set sync=disabled
  32     # zfs set logbias=latency
  33     # zfs set primarycache=all
  34     # zfs set redundant_metadata=most
  35     # DOC: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
  36     settings = {
  37       log_connections = true;
  38       log_disconnections = true;
  39       log_hostname = false;
  40       max_locks_per_transaction = 1024;
  41       password_encryption = "scram-sha-256"; # requires postfix >= 11
  42       # Synchronous commit doesn't introduce the risk of corruption, which is really bad,
  43       # just some risk of data loss.
  44       unix_socket_permissions = "0770";
  45
  46       # ZFS is Copy on Write (CoW). As a result, it’s not possible
  47       # to have a torn page because a page can’t be partially written
  48       # without reverting to the previous copy.
  49       # https://vadosware.io/post/everything-ive-seen-on-optimizing-postgres-on-zfs-on-linux/#setting-full_page_writesoff
  50       full_page_writes = false;
  51       # Avoid zero-filling of new files used for Postgres’s Write Ahead Log.
  52       # The reasoning here is that since ZFS overwrites these anyway,
  53       # you might as well get yourself a new inode.
  54       wal_init_zero = false;
  55       wal_recycle = false;
  56
  57       max_connections = 50;
  58       # The proper size for the POSTGRESQL shared buffer cache is the largest
  59       # useful size that does not adversely affect other activity.
  60       # Ideally we should limit ZFS's ARC's max size
  61       # to a percentage of physical memory
  62       # minus the shared_buffer cache for PostgreSQL
  63       # minus the kernel's memory overhead.
  64       shared_buffers = "512MB";
  65       # Used by the optimizer to estimate the size of the kernel's disk buffer cache.
  66       effective_cache_size = "1536MB";
  67       # Setting a large value helps in tasks like
  68       # VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.
  69       maintenance_work_mem = "128MB";
  70       # Bigger value for wal_buffers in case of lot of concurrent connection gives better performance.
  71       wal_buffers = "16MB";
  72       min_wal_size = "1GB";
  73       max_wal_size = "4GB";
  74       default_statistics_target = 100;
  75       random_page_cost = "1.1";
  76       effective_io_concurrency = 200;
  77       work_mem = "5242kB";
  78       huge_pages = false;
  79       max_worker_processes = host.CPUs;
  80       max_parallel_workers_per_gather = host.CPUs / 2;
  81       max_parallel_workers = host.CPUs;
  82       max_parallel_maintenance_workers = host.CPUs / 2;
  83     };
  84     # If one record is chosen and the auth fails,
  85     # subsequent records are not considered.
  86     authentication = ''
  87       # CONNECTION  DATABASE USER      AUTH  OPTIONS
  88       local         all      postgres  peer  map=admin
  89       local         samerole all       peer  map=user
  90       #local         all      backup    peer
  91     '';
  92     identMap = ''
  93       # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
  94       admin      postgres         postgres
  95       admin      root             postgres
  96       # Commented to prefer explicit auth
  97       # to handle revocation without droping the user yet
  98       #user       /^(.*)$          \1
  99     '';
 100   };
 101   services.sanoid.datasets = {
 102     "rpool/var/lib/postgresql" = {
 103       use_template = [ "snap" ];
 104       monthly = 1;
 105       daily = 31;
 106       hourly = 0;
 107       recursive = true;
 108     };
 109   };
 110   systemd.services.postgresql = {
 111     serviceConfig = {
 112       CPUAccounting = true;
 113       #CPUWeight = "idle";
 114       CPUQuota = "75%";
 115       MemoryAccounting = true;
 116       MemoryHigh = "500M";
 117       MemoryMax = "600M";
 118     };
 119     # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
 120     postStart = ''
 121       set -eux
 122       # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
 123       $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
 124         -- Disallow access to the public schema
 125         -- of individual users' databases by other users
 126         REVOKE ALL ON DATABASE template0 FROM public;
 127         REVOKE ALL ON DATABASE template1 FROM public;
 128         REVOKE ALL ON SCHEMA public FROM public;
 129         GRANT  USAGE,CREATE ON SCHEMA public TO public;
 130         GRANT  ALL ON SCHEMA public TO "${postgresql.superUser}";
 131         REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
 132         GRANT  ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
 133
 134         -- Disallow access to database and user names for everyone
 135         REVOKE ALL ON pg_catalog.pg_user         FROM public;
 136         REVOKE ALL ON pg_catalog.pg_group        FROM public;
 137         REVOKE ALL ON pg_catalog.pg_authid       FROM public;
 138         REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
 139         REVOKE ALL ON pg_catalog.pg_settings     FROM public;
 140         -- The following must have SELECT reallowed if pg_dump is allowed
 141         REVOKE ALL ON pg_catalog.pg_roles        FROM public;
 142         REVOKE ALL ON pg_catalog.pg_database     FROM public;
 143         REVOKE ALL ON pg_catalog.pg_tablespace   FROM public;
 144       EOF
 145
 146       pg_createdb () {
 147         local db=$1
 148         local owner=''${owner:-$db}
 149         local template=''${template:-template1}
 150         $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
 151           $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
 152             CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+PASSWORD '$pass'};
 153             CREATE DATABASE "$db" WITH OWNER="$owner"
 154              ''${encoding:+ENCODING='$encoding'}
 155              ''${lc_collate:+LC_COLLATE='$lc_collate'}
 156              ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
 157              ''${tablespace:+TABLESPACE='$tablespace'}
 158              ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
 159             ;
 160             REVOKE ALL ON DATABASE "$db" FROM public;
 161       EOF
 162           $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
 163             -- Grant all rights to the public schema in the new database to the main user
 164             GRANT ALL ON SCHEMA public TO "$owner" WITH GRANT OPTION;
 165       EOF
 166           $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
 167         }
 168       }
 169
 170       pg_adduser () {
 171         local db=$1
 172         local user=$2
 173         $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
 174         $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
 175           CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+ENCRYPTED PASSWORD '$pass'};
 176           GRANT USAGE ON SCHEMA public TO "$user";
 177           GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";
 178       EOF
 179       }
 180     '';
 181   };
 182 }