1 { pkgs, config, hostName, ... }:
3 inherit (config.networking) domain;
4 inherit (config.services) postgresql;
5 inherit (config.users) users;
11 map (eta: (import postgresql/openconcerto.nix) eta) [
12 {db = "openconcerto1";}
13 {db = "openconcerto2";}
19 networking.nftables.ruleset = ''
22 tcp dport postgresql counter accept comment "postgresql"
27 users.groups.acme.members = [ users."postgres".name ];
28 security.acme.certs."${domain}" = {
29 postRun = "systemctl reload postgresql";
31 systemd.services.postgresql = {
32 wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service" ];
33 after = [ "acme-selfsigned-${domain}.service" ];
35 services.postgresql = {
37 package = pkgs.postgresql_12;
40 # ZFS is Copy on Write (CoW). As a result, it’s not possible
41 # to have a torn page because a page can’t be partially written
42 # without reverting to the previous copy.
43 full_page_writes = false;
44 log_connections = true;
45 log_disconnections = true;
48 max_locks_per_transaction = 1024;
49 password_encryption = "scram-sha-256"; # requires postfix >= 11
51 ssl_cert_file = "/var/lib/acme/${domain}/fullchain.pem";
52 ssl_key_file = "/var/lib/acme/${domain}/key.pem";
53 unix_socket_permissions = "0770";
55 # If one record is chosen and the auth fails,
56 # subsequent records are not considered.
58 # CONNECTION DATABASE USER AUTH OPTIONS
59 local all postgres peer map=admin
60 local samerole all peer map=user
61 #local all backup peer
64 # MAPNAME SYSTEM-USERNAME PG-USERNAME
65 admin postgres postgres
67 # Commented to prefer explicit auth
68 # to handle revocation without droping the user yet
72 fileSystems."/var/lib/postgresql" = {
73 device = "${hostName}/var/postgresql";
74 fsType = "zfs"; # with sync=always,
75 # though loading OpenConcerto's initial SQL
76 # takes 1m40s instead of 40s :\
79 services.syncoid.commands = {
80 "${hostName}/var/postgresql" = {
82 target = "backup@mermet.${domain}:rpool/backup/${hostName}/var/postgresql";
86 services.sanoid.datasets = {
87 "${hostName}/var/postgresql" = {
88 use_template = [ "snap" ];
92 systemd.services.postgresql = {
93 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
96 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
97 $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
98 -- Disallow access to the public schema
99 -- of individual users' databases by other users
100 REVOKE ALL ON DATABASE template0 FROM public;
101 REVOKE ALL ON DATABASE template1 FROM public;
102 REVOKE ALL ON SCHEMA public FROM public;
103 GRANT USAGE,CREATE ON SCHEMA public TO public;
104 GRANT ALL ON SCHEMA public TO "${postgresql.superUser}";
105 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
106 GRANT ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
108 -- Disallow access to database and user names for everyone
109 REVOKE ALL ON pg_catalog.pg_user FROM public;
110 REVOKE ALL ON pg_catalog.pg_group FROM public;
111 REVOKE ALL ON pg_catalog.pg_authid FROM public;
112 REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
113 REVOKE ALL ON pg_catalog.pg_settings FROM public;
114 -- The following must have SELECT reallowed if pg_dump is allowed
115 REVOKE ALL ON pg_catalog.pg_roles FROM public;
116 REVOKE ALL ON pg_catalog.pg_database FROM public;
117 REVOKE ALL ON pg_catalog.pg_tablespace FROM public;
122 local owner=''${owner:-$db}
123 local template=''${template:-template1}
124 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
125 $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
126 CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+PASSWORD '$pass'};
127 CREATE DATABASE "$db" WITH OWNER="$owner"
128 ''${encoding:+ENCODING='$encoding'}
129 ''${lc_collate:+LC_COLLATE='$lc_collate'}
130 ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
131 ''${tablespace:+TABLESPACE='$tablespace'}
132 ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
134 REVOKE ALL ON DATABASE "$db" FROM public;
136 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
137 -- Grant all rights to the public schema in the new database to the main user
138 GRANT ALL ON SCHEMA public TO "$owner" WITH GRANT OPTION;
140 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
147 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
148 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
149 CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+ENCRYPTED PASSWORD '$pass'};
150 GRANT USAGE ON SCHEMA public TO "$user";
151 GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";