{ pkgs, lib, config, hostName, ... }: let inherit (config.networking) domain; inherit (config.services) postgresql; inherit (config.users) users; in { imports = []; /* map (eta: (import postgresql/openconcerto.nix) eta) [ {db = "openconcerto1";} {db = "openconcerto2";} {db = "lbec";} {db = "lbm";} ]; */ /* networking.nftables.ruleset = '' add rule inet filter net2fw tcp dport 5432 counter accept comment "PostgreSQL" ''; */ users.groups.acme.members = [ users."postgres".name ]; security.acme.certs."${domain}" = { postRun = "systemctl reload postgresql"; }; systemd.services.postgresql = { wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service"]; after = [ "acme-selfsigned-${domain}.service" ]; }; services.postgresql = { enable = true; package = pkgs.postgresql_12; #enableTCPIP = true; settings = { # ZFS is Copy on Write (CoW). As a result, it’s not possible # to have a torn page because a page can’t be partially written # without reverting to the previous copy. full_page_writes = false; log_connections = true; log_disconnections = true; log_hostname = false; max_connections = 25; max_locks_per_transaction = 1024; password_encryption = "scram-sha-256"; # requires postfix >= 11 ssl = false; ssl_cert_file = "/var/lib/acme/${domain}/fullchain.pem"; ssl_key_file = "/var/lib/acme/${domain}/key.pem"; unix_socket_permissions = "0770"; }; # If one record is chosen and the auth fails, # subsequent records are not considered. authentication = '' # CONNECTION DATABASE USER AUTH OPTIONS local all postgres peer map=admin local samerole all peer map=user #local all backup peer ''; identMap = '' # MAPNAME SYSTEM-USERNAME PG-USERNAME admin postgres postgres admin root postgres # Commented to prefer explicit auth # to handle revocation without droping the user yet #user /^(.*)$ \1 ''; }; fileSystems."/var/lib/postgresql" = { device = "${hostName}/var/postgresql"; fsType = "zfs"; # with sync=always, # though loading OpenConcerto's initial SQL # takes 1m40s instead of 40s :\ }; /* services.syncoid.commands = { "${hostName}/var/postgresql" = { sendOptions = "raw"; target = "backup@mermet.${domain}:rpool/backup/${hostName}/var/postgresql"; }; }; */ services.sanoid.datasets = { "${hostName}/var/postgresql" = { use_template = [ "snap" ]; daily = 31; }; }; systemd.services.postgresql = { # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting postStart = '' set -eux # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <