]> Git — Sourcephile - sourcephile-nix.git/blob - servers/losurdo/postgresql.nix
systemd: fix reload of services
[sourcephile-nix.git] / servers / losurdo / postgresql.nix
1 { pkgs, lib, config, ... }:
2 let
3 inherit (config) networking;
4 inherit (config.services) postgresql;
5 inherit (config.users) users;
6 in
7 {
8 imports =
9 map (eta: (import postgresql/openconcerto.nix) eta) [
10 {db = "openconcerto1";}
11 {db = "openconcerto2";}
12 {db = "labascule";}
13 {db = "lbec";}
14 {db = "lbm";}
15 ];
16 networking.nftables.ruleset = ''
17 add rule inet filter net2fw tcp dport 5432 counter accept comment "PostgreSQL"
18 '';
19 users.groups.acme.members = [ users."postgres".name ];
20 security.acme.certs."${networking.domain}" = {
21 postRun = "systemctl reload postgresql";
22 };
23 systemd.services.postgresql = {
24 wants = [ "acme-selfsigned-${networking.domain}.service" "acme-${networking.domain}.service"];
25 after = [ "acme-selfsigned-${networking.domain}.service" ];
26 };
27 services.postgresql = {
28 enable = true;
29 package = pkgs.postgresql_9_6;
30 enableTCPIP = true;
31 extraConfig = ''
32 # ZFS is Copy on Write (CoW). As a result, it’s not possible
33 # to have a torn page because a page can’t be partially written
34 # without reverting to the previous copy.
35 full_page_writes = off
36 log_connections = true
37 log_disconnections = true
38 log_hostname = false
39 max_connections = 25
40 max_locks_per_transaction = 1024
41 password_encryption = on # FIXME: replace md5 by scram-sha-256, which requires postfix >= 11
42 ssl = on
43 ssl_cert_file = '/var/lib/acme/${networking.domain}/fullchain.pem'
44 ssl_key_file = '/var/lib/acme/${networking.domain}/key.pem'
45 unix_socket_permissions = 0770
46 '';
47 authentication = lib.mkForce ''
48 # CONNECTION DATABASE USER AUTH OPTIONS
49 local all postgres peer map=admin
50 local samerole all peer map=user
51 #local all backup peer
52 '';
53 identMap = ''
54 # MAPNAME SYSTEM-USERNAME PG-USERNAME
55 admin postgres postgres
56 admin root postgres
57 user /^(.*)$ \1
58 '';
59 };
60 systemd.services.postgresql = {
61 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
62 postStart = ''
63 set -eux
64 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
65 $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
66 -- Disallow access to the public schema
67 -- of individual users' databases by other users
68 REVOKE ALL ON DATABASE template0 FROM public;
69 REVOKE ALL ON DATABASE template1 FROM public;
70 REVOKE ALL ON SCHEMA public FROM public;
71 GRANT ALL ON SCHEMA public TO ${postgresql.superUser};
72 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
73 GRANT ALL ON ALL TABLES IN SCHEMA public TO ${postgresql.superUser};
74
75 -- Disallow access to database and user names for everyone
76 REVOKE ALL ON pg_catalog.pg_user FROM public;
77 REVOKE ALL ON pg_catalog.pg_group FROM public;
78 REVOKE ALL ON pg_catalog.pg_authid FROM public;
79 REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
80 REVOKE ALL ON pg_catalog.pg_settings FROM public;
81 -- The following must have SELECT reallowed if pg_dump is allowed
82 REVOKE ALL ON pg_catalog.pg_roles FROM public;
83 REVOKE ALL ON pg_catalog.pg_database FROM public;
84 REVOKE ALL ON pg_catalog.pg_tablespace FROM public;
85 EOF
86
87 pg_createdb () {
88 local db=$1
89 local owner=''${owner:-$db}
90 local template=''${template:-template1}
91 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
92 $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
93 CREATE ROLE $owner NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN PASSWORD '$pass';
94 CREATE DATABASE $db WITH OWNER=$owner
95 ''${encoding:+ENCODING='$encoding'}
96 ''${lc_collate:+LC_COLLATE='$lc_collate'}
97 ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
98 ''${tablespace:+TABLESPACE='$tablespace'}
99 ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
100 ;
101 REVOKE ALL ON DATABASE $db FROM public;
102 EOF
103 $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
104 -- Grant all rights to the public schema in the new database to the main user
105 GRANT ALL ON SCHEMA public TO $owner WITH GRANT OPTION;
106 EOF
107 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
108 }
109 }
110
111 pg_adduser () {
112 local db=$1
113 local user=$2
114 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
115 $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
116 CREATE ROLE $user NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '$pass';
117 GRANT USAGE ON SCHEMA public TO $user;
118 GRANT CONNECT,TEMPORARY ON DATABASE $db TO $user;
119 EOF
120 }
121 '';
122 };
123 }