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