]> Git — Sourcephile - sourcephile-nix.git/blob - hosts/mermet/postgresql.nix
carotte: fix build
[sourcephile-nix.git] / hosts / mermet / postgresql.nix
1 { pkgs, config, host, ... }:
2 let
3 inherit (config.services) postgresql;
4 in
5 {
6 /*
7 networking.nftables.ruleset = ''
8 table inet filter {
9 chain input-net {
10 tcp dport postgresql counter accept comment "PostgreSQL"
11 }
12 }
13 '';
14 users.groups.acme.members = [ users."postgres".name ];
15 security.acme.certs."${domain}" = {
16 postRun = "systemctl reload postgresql";
17 };
18 systemd.services.postgresql = {
19 wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service"];
20 after = [ "acme-selfsigned-${domain}.service" ];
21 };
22 */
23 environment.systemPackages = [
24 pkgs.pg_top
25 ];
26 services.postgresql = {
27 enable = true;
28 package = pkgs.postgresql_12;
29 enableTCPIP = false;
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
36 settings = {
37 log_connections = true;
38 log_disconnections = true;
39 log_hostname = false;
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";
45
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;
55 wal_recycle = false;
56
57 max_connections = 50;
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.
71 wal_buffers = "16MB";
72 min_wal_size = "1GB";
73 max_wal_size = "4GB";
74 default_statistics_target = 100;
75 random_page_cost = "1.1";
76 effective_io_concurrency = 200;
77 work_mem = "5242kB";
78 huge_pages = false;
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;
83 };
84 # If one record is chosen and the auth fails,
85 # subsequent records are not considered.
86 authentication = ''
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
91 '';
92 identMap = ''
93 # MAPNAME SYSTEM-USERNAME PG-USERNAME
94 admin postgres postgres
95 admin root postgres
96 # Commented to prefer explicit auth
97 # to handle revocation without droping the user yet
98 #user /^(.*)$ \1
99 '';
100 };
101 services.sanoid.datasets = {
102 "rpool/var/lib/postgresql" = {
103 use_template = [ "snap" ];
104 monthly = 1;
105 daily = 31;
106 hourly = 0;
107 recursive = true;
108 };
109 };
110 systemd.services.postgresql = {
111 serviceConfig = {
112 MemoryAccounting = true;
113 MemoryHigh = "500M";
114 MemoryMax = "600M";
115 };
116 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
117 postStart = ''
118 set -eux
119 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
120 $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
121 -- Disallow access to the public schema
122 -- of individual users' databases by other users
123 REVOKE ALL ON DATABASE template0 FROM public;
124 REVOKE ALL ON DATABASE template1 FROM public;
125 REVOKE ALL ON SCHEMA public FROM public;
126 GRANT USAGE,CREATE ON SCHEMA public TO public;
127 GRANT ALL ON SCHEMA public TO "${postgresql.superUser}";
128 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
129 GRANT ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
130
131 -- Disallow access to database and user names for everyone
132 REVOKE ALL ON pg_catalog.pg_user FROM public;
133 REVOKE ALL ON pg_catalog.pg_group FROM public;
134 REVOKE ALL ON pg_catalog.pg_authid FROM public;
135 REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
136 REVOKE ALL ON pg_catalog.pg_settings FROM public;
137 -- The following must have SELECT reallowed if pg_dump is allowed
138 REVOKE ALL ON pg_catalog.pg_roles FROM public;
139 REVOKE ALL ON pg_catalog.pg_database FROM public;
140 REVOKE ALL ON pg_catalog.pg_tablespace FROM public;
141 EOF
142
143 pg_createdb () {
144 local db=$1
145 local owner=''${owner:-$db}
146 local template=''${template:-template1}
147 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
148 $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
149 CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+PASSWORD '$pass'};
150 CREATE DATABASE "$db" WITH OWNER="$owner"
151 ''${encoding:+ENCODING='$encoding'}
152 ''${lc_collate:+LC_COLLATE='$lc_collate'}
153 ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
154 ''${tablespace:+TABLESPACE='$tablespace'}
155 ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
156 ;
157 REVOKE ALL ON DATABASE "$db" FROM public;
158 EOF
159 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
160 -- Grant all rights to the public schema in the new database to the main user
161 GRANT ALL ON SCHEMA public TO "$owner" WITH GRANT OPTION;
162 EOF
163 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
164 }
165 }
166
167 pg_adduser () {
168 local db=$1
169 local user=$2
170 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
171 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
172 CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+ENCRYPTED PASSWORD '$pass'};
173 GRANT USAGE ON SCHEMA public TO "$user";
174 GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";
175 EOF
176 }
177 '';
178 };
179 }