]> Git — Sourcephile - sourcephile-nix.git/blob - hosts/losurdo/postgresql.nix
nix: update nixpkgs
[sourcephile-nix.git] / hosts / losurdo / postgresql.nix
1 { pkgs, lib, 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 add rule inet filter net2fw tcp dport 5432 counter accept comment "PostgreSQL"
21 '';
22 */
23 users.groups.acme.members = [ users."postgres".name ];
24 security.acme.certs."${domain}" = {
25 postRun = "systemctl reload postgresql";
26 };
27 systemd.services.postgresql = {
28 wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service"];
29 after = [ "acme-selfsigned-${domain}.service" ];
30 };
31 services.postgresql = {
32 enable = true;
33 package = pkgs.postgresql_12;
34 #enableTCPIP = true;
35 settings = {
36 # ZFS is Copy on Write (CoW). As a result, it’s not possible
37 # to have a torn page because a page can’t be partially written
38 # without reverting to the previous copy.
39 full_page_writes = false;
40 log_connections = true;
41 log_disconnections = true;
42 log_hostname = false;
43 max_connections = 25;
44 max_locks_per_transaction = 1024;
45 password_encryption = "scram-sha-256"; # requires postfix >= 11
46 ssl = false;
47 ssl_cert_file = "/var/lib/acme/${domain}/fullchain.pem";
48 ssl_key_file = "/var/lib/acme/${domain}/key.pem";
49 unix_socket_permissions = "0770";
50 };
51 # If one record is chosen and the auth fails,
52 # subsequent records are not considered.
53 authentication = ''
54 # CONNECTION DATABASE USER AUTH OPTIONS
55 local all postgres peer map=admin
56 local samerole all peer map=user
57 #local all backup peer
58 '';
59 identMap = ''
60 # MAPNAME SYSTEM-USERNAME PG-USERNAME
61 admin postgres postgres
62 admin root postgres
63 # Commented to prefer explicit auth
64 # to handle revocation without droping the user yet
65 #user /^(.*)$ \1
66 '';
67 };
68 fileSystems."/var/lib/postgresql" = {
69 device = "${hostName}/var/postgresql";
70 fsType = "zfs"; # with sync=always,
71 # though loading OpenConcerto's initial SQL
72 # takes 1m40s instead of 40s :\
73 };
74 /*
75 services.syncoid.commands = {
76 "${hostName}/var/postgresql" = {
77 sendOptions = "raw";
78 target = "backup@mermet.${domain}:rpool/backup/${hostName}/var/postgresql";
79 };
80 };
81 */
82 services.sanoid.datasets = {
83 "${hostName}/var/postgresql" = {
84 use_template = [ "snap" ];
85 daily = 31;
86 };
87 };
88 systemd.services.postgresql = {
89 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
90 postStart = ''
91 set -eux
92 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
93 $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
94 -- Disallow access to the public schema
95 -- of individual users' databases by other users
96 REVOKE ALL ON DATABASE template0 FROM public;
97 REVOKE ALL ON DATABASE template1 FROM public;
98 REVOKE ALL ON SCHEMA public FROM public;
99 GRANT USAGE,CREATE ON SCHEMA public TO public;
100 GRANT ALL ON SCHEMA public TO "${postgresql.superUser}";
101 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
102 GRANT ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
103
104 -- Disallow access to database and user names for everyone
105 REVOKE ALL ON pg_catalog.pg_user FROM public;
106 REVOKE ALL ON pg_catalog.pg_group FROM public;
107 REVOKE ALL ON pg_catalog.pg_authid FROM public;
108 REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
109 REVOKE ALL ON pg_catalog.pg_settings FROM public;
110 -- The following must have SELECT reallowed if pg_dump is allowed
111 REVOKE ALL ON pg_catalog.pg_roles FROM public;
112 REVOKE ALL ON pg_catalog.pg_database FROM public;
113 REVOKE ALL ON pg_catalog.pg_tablespace FROM public;
114 EOF
115
116 pg_createdb () {
117 local db=$1
118 local owner=''${owner:-$db}
119 local template=''${template:-template1}
120 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
121 $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
122 CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ''${pass:+LOGIN PASSWORD '$pass'};
123 CREATE DATABASE "$db" WITH OWNER="$owner"
124 ''${encoding:+ENCODING='$encoding'}
125 ''${lc_collate:+LC_COLLATE='$lc_collate'}
126 ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
127 ''${tablespace:+TABLESPACE='$tablespace'}
128 ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
129 ;
130 REVOKE ALL ON DATABASE "$db" FROM public;
131 EOF
132 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
133 -- Grant all rights to the public schema in the new database to the main user
134 GRANT ALL ON SCHEMA public TO "$owner" WITH GRANT OPTION;
135 EOF
136 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
137 }
138 }
139
140 pg_adduser () {
141 local db=$1
142 local user=$2
143 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
144 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
145 CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ''${pass:+LOGIN ENCRYPTED PASSWORD '$pass'};
146 GRANT USAGE ON SCHEMA public TO "$user";
147 GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";
148 EOF
149 }
150 '';
151 };
152 }