hosts/nan2gua1/postgresql.nix

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