hosts/losurdo/postgresql.nix

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