-{ pkgs, lib, config, hostName, ... }:
+{ pkgs, config, hostName, host, ... }:
let
inherit (config.networking) domain;
inherit (config.services) postgresql;
inherit (config.users) users;
in
{
-imports =
- [];
+ imports =
+ [ ];
/*
- map (eta: (import postgresql/openconcerto.nix) eta) [
+ 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";
+ /*
+ 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" ];
+ };
+ services.postgresql = {
+ enable = true;
+ package = pkgs.postgresql_12;
+ #enableTCPIP = true;
+ settings = {
+ log_connections = true;
+ log_disconnections = true;
+ log_hostname = false;
+ 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";
+
+ # 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;
+ # 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 = "1GB";
+ # Used by the optimizer to estimate the size of the kernel's disk buffer cache.
+ effective_cache_size = "3GB";
+ # Setting a large value helps in tasks like
+ # VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.
+ maintenance_work_mem = "256MB";
+ # 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 = "20971kB";
+ 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
+ '';
};
- authentication = lib.mkForce ''
- # 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" = {
+ /*
+ services.syncoid.commands = {
+ "${hostName}/var/postgresql" = {
sendOptions = "raw";
target = "backup@mermet.${domain}:rpool/backup/${hostName}/var/postgresql";
+ };
+ };
+ */
+ services.sanoid.datasets = {
+ "${hostName}/var/lib/postgresql" = {
+ use_template = [ "snap" ];
+ daily = 31;
+ hourly = 0;
+ recursive = true;
+ };
};
-};
-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 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};
+ 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
+ -- 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 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_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 ENCRYPTED PASSWORD '$pass';
- GRANT USAGE ON SCHEMA public TO $user;
- GRANT CONNECT,TEMPORARY ON DATABASE $db TO $user;
- EOF
- }
- '';
-};
+ 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
+ }
+ '';
+ };
}