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