{ 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 - <<EOF -- Disallow access to the public schema -- of individual users' databases by other users REVOKE ALL ON DATABASE template0 FROM public; REVOKE ALL ON DATABASE template1 FROM public; REVOKE ALL ON SCHEMA public FROM public; GRANT USAGE,CREATE ON SCHEMA public TO public; GRANT ALL ON SCHEMA public TO "${postgresql.superUser}"; REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public; GRANT ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}"; -- Disallow access to database and user names for everyone REVOKE ALL ON pg_catalog.pg_user FROM public; REVOKE ALL ON pg_catalog.pg_group FROM public; REVOKE ALL ON pg_catalog.pg_authid FROM public; REVOKE ALL ON pg_catalog.pg_auth_members FROM public; REVOKE ALL ON pg_catalog.pg_settings FROM public; -- The following must have SELECT reallowed if pg_dump is allowed REVOKE ALL ON pg_catalog.pg_roles FROM public; REVOKE ALL ON pg_catalog.pg_database FROM public; REVOKE ALL ON pg_catalog.pg_tablespace FROM public; EOF pg_createdb () { local db=$1 local owner=''${owner:-$db} local template=''${template:-template1} $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || { $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ''${pass:+LOGIN PASSWORD '$pass'}; CREATE DATABASE "$db" WITH OWNER="$owner" ''${encoding:+ENCODING='$encoding'} ''${lc_collate:+LC_COLLATE='$lc_collate'} ''${lc_ctype:+LC_CTYPE='$lc_ctype'} ''${tablespace:+TABLESPACE='$tablespace'} ''${connection_limit:+CONNECTION LIMIT=$connection_limit} ; REVOKE ALL ON DATABASE "$db" FROM public; EOF $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF -- Grant all rights to the public schema in the new database to the main user GRANT ALL ON SCHEMA public TO "$owner" WITH GRANT OPTION; EOF $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - } } pg_adduser () { local db=$1 local user=$2 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 || $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ''${pass:+LOGIN ENCRYPTED PASSWORD '$pass'}; GRANT USAGE ON SCHEMA public TO "$user"; GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user"; EOF } ''; }; }