hosts/mermet/postgresql.nix

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