1 { pkgs, config, host, ... }:
3 inherit (config.services) postgresql;
7 networking.nftables.ruleset = ''
10 tcp dport postgresql counter accept comment "PostgreSQL"
14 users.groups.acme.members = [ users."postgres".name ];
15 security.acme.certs."${domain}" = {
16 postRun = "systemctl reload postgresql";
18 systemd.services.postgresql = {
19 wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service"];
20 after = [ "acme-selfsigned-${domain}.service" ];
23 environment.systemPackages = [
26 services.postgresql = {
28 package = pkgs.postgresql_12;
30 # zfs set recordsize=16k
31 # zfs set sync=disabled
32 # zfs set logbias=latency
33 # zfs set primarycache=all
34 # zfs set redundant_metadata=most
35 # DOC: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
37 log_connections = true;
38 log_disconnections = true;
40 max_locks_per_transaction = 1024;
41 password_encryption = "scram-sha-256"; # requires postfix >= 11
42 # Synchronous commit doesn't introduce the risk of corruption, which is really bad,
43 # just some risk of data loss.
44 unix_socket_permissions = "0770";
46 # ZFS is Copy on Write (CoW). As a result, it’s not possible
47 # to have a torn page because a page can’t be partially written
48 # without reverting to the previous copy.
49 # https://vadosware.io/post/everything-ive-seen-on-optimizing-postgres-on-zfs-on-linux/#setting-full_page_writesoff
50 full_page_writes = false;
51 # Avoid zero-filling of new files used for Postgres’s Write Ahead Log.
52 # The reasoning here is that since ZFS overwrites these anyway,
53 # you might as well get yourself a new inode.
54 wal_init_zero = false;
58 # The proper size for the POSTGRESQL shared buffer cache is the largest
59 # useful size that does not adversely affect other activity.
60 # Ideally we should limit ZFS's ARC's max size
61 # to a percentage of physical memory
62 # minus the shared_buffer cache for PostgreSQL
63 # minus the kernel's memory overhead.
64 shared_buffers = "512MB";
65 # Used by the optimizer to estimate the size of the kernel's disk buffer cache.
66 effective_cache_size = "1536MB";
67 # Setting a large value helps in tasks like
68 # VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.
69 maintenance_work_mem = "128MB";
70 # Bigger value for wal_buffers in case of lot of concurrent connection gives better performance.
74 default_statistics_target = 100;
75 random_page_cost = "1.1";
76 effective_io_concurrency = 200;
79 max_worker_processes = host.CPUs;
80 max_parallel_workers_per_gather = host.CPUs / 2;
81 max_parallel_workers = host.CPUs;
82 max_parallel_maintenance_workers = host.CPUs / 2;
84 # If one record is chosen and the auth fails,
85 # subsequent records are not considered.
87 # CONNECTION DATABASE USER AUTH OPTIONS
88 local all postgres peer map=admin
89 local samerole all peer map=user
90 #local all backup peer
93 # MAPNAME SYSTEM-USERNAME PG-USERNAME
94 admin postgres postgres
96 # Commented to prefer explicit auth
97 # to handle revocation without droping the user yet
101 services.sanoid.datasets = {
102 "rpool/var/lib/postgresql" = {
103 use_template = [ "snap" ];
109 systemd.services.postgresql = {
110 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
113 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
114 $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
115 -- Disallow access to the public schema
116 -- of individual users' databases by other users
117 REVOKE ALL ON DATABASE template0 FROM public;
118 REVOKE ALL ON DATABASE template1 FROM public;
119 REVOKE ALL ON SCHEMA public FROM public;
120 GRANT USAGE,CREATE ON SCHEMA public TO public;
121 GRANT ALL ON SCHEMA public TO "${postgresql.superUser}";
122 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
123 GRANT ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
125 -- Disallow access to database and user names for everyone
126 REVOKE ALL ON pg_catalog.pg_user FROM public;
127 REVOKE ALL ON pg_catalog.pg_group FROM public;
128 REVOKE ALL ON pg_catalog.pg_authid FROM public;
129 REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
130 REVOKE ALL ON pg_catalog.pg_settings FROM public;
131 -- The following must have SELECT reallowed if pg_dump is allowed
132 REVOKE ALL ON pg_catalog.pg_roles FROM public;
133 REVOKE ALL ON pg_catalog.pg_database FROM public;
134 REVOKE ALL ON pg_catalog.pg_tablespace FROM public;
139 local owner=''${owner:-$db}
140 local template=''${template:-template1}
141 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
142 $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
143 CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+PASSWORD '$pass'};
144 CREATE DATABASE "$db" WITH OWNER="$owner"
145 ''${encoding:+ENCODING='$encoding'}
146 ''${lc_collate:+LC_COLLATE='$lc_collate'}
147 ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
148 ''${tablespace:+TABLESPACE='$tablespace'}
149 ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
151 REVOKE ALL ON DATABASE "$db" FROM public;
153 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
154 -- Grant all rights to the public schema in the new database to the main user
155 GRANT ALL ON SCHEMA public TO "$owner" WITH GRANT OPTION;
157 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
164 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
165 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
166 CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+ENCRYPTED PASSWORD '$pass'};
167 GRANT USAGE ON SCHEMA public TO "$user";
168 GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";