{ pkgs, config, host, ... }:
let
inherit (config.services) postgresql;
in
{
/*
networking.nftables.ruleset = ''
table inet filter {
chain input-net {
tcp dport postgresql 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" ];
};
*/
environment.systemPackages = [
pkgs.pg_top
];
services.postgresql = {
enable = true;
package = pkgs.postgresql_13;
enableTCPIP = false;
# zfs set recordsize=16k
# zfs set sync=disabled
# zfs set logbias=latency
# zfs set primarycache=all
# zfs set redundant_metadata=most
# DOC: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
settings = {
log_connections = true;
log_disconnections = true;
log_hostname = false;
max_locks_per_transaction = 1024;
password_encryption = "scram-sha-256"; # requires postfix >= 11
# Synchronous commit doesn't introduce the risk of corruption, which is really bad,
# just some risk of data loss.
unix_socket_permissions = "0770";
# 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.
# https://vadosware.io/post/everything-ive-seen-on-optimizing-postgres-on-zfs-on-linux/#setting-full_page_writesoff
full_page_writes = false;
# Avoid zero-filling of new files used for Postgres’s Write Ahead Log.
# The reasoning here is that since ZFS overwrites these anyway,
# you might as well get yourself a new inode.
wal_init_zero = false;
wal_recycle = false;
max_connections = 50;
# The proper size for the POSTGRESQL shared buffer cache is the largest
# useful size that does not adversely affect other activity.
# Ideally we should limit ZFS's ARC's max size
# to a percentage of physical memory
# minus the shared_buffer cache for PostgreSQL
# minus the kernel's memory overhead.
shared_buffers = "512MB";
# Used by the optimizer to estimate the size of the kernel's disk buffer cache.
effective_cache_size = "1536MB";
# Setting a large value helps in tasks like
# VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.
maintenance_work_mem = "128MB";
# Bigger value for wal_buffers in case of lot of concurrent connection gives better performance.
wal_buffers = "16MB";
min_wal_size = "1GB";
max_wal_size = "4GB";
default_statistics_target = 100;
random_page_cost = "1.1";
effective_io_concurrency = 200;
work_mem = "5242kB";
huge_pages = false;
max_worker_processes = host.CPUs;
max_parallel_workers_per_gather = host.CPUs / 2;
max_parallel_workers = host.CPUs;
max_parallel_maintenance_workers = host.CPUs / 2;
};
# 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
'';
};
services.sanoid.datasets = {
"rpool/var/lib/postgresql" = {
use_template = [ "snap" ];
monthly = 1;
daily = 31;
hourly = 0;
recursive = true;
};
};
systemd.services.postgresql = {
serviceConfig = {
CPUAccounting = true;
#CPUWeight = "idle";
CPUQuota = "75%";
MemoryAccounting = true;
MemoryHigh = "500M";
MemoryMax = "600M";
};
# 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 LOGIN ''${pass:+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 LOGIN ''${pass:+ENCRYPTED PASSWORD '$pass'};
GRANT USAGE ON SCHEMA public TO "$user";
GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";
EOF
}
'';
};
}