]> Git — Sourcephile - sourcephile-nix.git/blob - hosts/losurdo/postgresql.nix
nix: update inputs
[sourcephile-nix.git] / hosts / losurdo / postgresql.nix
1 { pkgs, config, hostName, ... }:
2 let
3 inherit (config.networking) domain;
4 inherit (config.services) postgresql;
5 inherit (config.users) users;
6 in
7 {
8 imports =
9 [ ];
10 /*
11 map (eta: (import postgresql/openconcerto.nix) eta) [
12 {db = "openconcerto1";}
13 {db = "openconcerto2";}
14 {db = "lbec";}
15 {db = "lbm";}
16 ];
17 */
18 /*
19 networking.nftables.ruleset = ''
20 table inet filter {
21 chain input-net {
22 tcp dport postgresql counter accept comment "postgresql"
23 }
24 }
25 '';
26 */
27 users.groups.acme.members = [ users."postgres".name ];
28 security.acme.certs."${domain}" = {
29 postRun = "systemctl reload postgresql";
30 };
31 systemd.services.postgresql = {
32 wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service" ];
33 after = [ "acme-selfsigned-${domain}.service" ];
34 };
35 services.postgresql = {
36 enable = true;
37 package = pkgs.postgresql_12;
38 #enableTCPIP = true;
39 settings = {
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;
46 log_hostname = false;
47 max_connections = 25;
48 max_locks_per_transaction = 1024;
49 password_encryption = "scram-sha-256"; # requires postfix >= 11
50 ssl = false;
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";
54 };
55 # If one record is chosen and the auth fails,
56 # subsequent records are not considered.
57 authentication = ''
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
62 '';
63 identMap = ''
64 # MAPNAME SYSTEM-USERNAME PG-USERNAME
65 admin postgres postgres
66 admin root postgres
67 # Commented to prefer explicit auth
68 # to handle revocation without droping the user yet
69 #user /^(.*)$ \1
70 '';
71 };
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 :\
77 };
78 /*
79 services.syncoid.commands = {
80 "${hostName}/var/postgresql" = {
81 sendOptions = "raw";
82 target = "backup@mermet.${domain}:rpool/backup/${hostName}/var/postgresql";
83 };
84 };
85 */
86 services.sanoid.datasets = {
87 "${hostName}/var/postgresql" = {
88 use_template = [ "snap" ];
89 daily = 31;
90 };
91 };
92 systemd.services.postgresql = {
93 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
94 postStart = ''
95 set -eux
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}";
107
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;
118 EOF
119
120 pg_createdb () {
121 local db=$1
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 ''${pass:+LOGIN 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}
133 ;
134 REVOKE ALL ON DATABASE "$db" FROM public;
135 EOF
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;
139 EOF
140 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
141 }
142 }
143
144 pg_adduser () {
145 local db=$1
146 local user=$2
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 ''${pass:+LOGIN ENCRYPTED PASSWORD '$pass'};
150 GRANT USAGE ON SCHEMA public TO "$user";
151 GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";
152 EOF
153 }
154 '';
155 };
156 }