]> Git — Sourcephile - sourcephile-nix.git/blob - servers/losurdo/postgresql.nix
nix: upgrade to latests nixos-unstable-small, fix boot.initrd.network.ssh.hostKeys
[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 = "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 try-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 extraConfig = ''
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 = off
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 = on
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 user /^(.*)$ \1
57 '';
58 };
59 systemd.services.postgresql = {
60 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
61 postStart = ''
62 set -eux
63 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
64 $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
65 -- Disallow access to the public schema
66 -- of individual users' databases by other users
67 REVOKE ALL ON DATABASE template0 FROM public;
68 REVOKE ALL ON DATABASE template1 FROM public;
69 REVOKE ALL ON SCHEMA public FROM public;
70 GRANT ALL ON SCHEMA public TO ${postgresql.superUser};
71 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
72 GRANT ALL ON ALL TABLES IN SCHEMA public TO ${postgresql.superUser};
73
74 -- Disallow access to database and user names for everyone
75 REVOKE ALL ON pg_catalog.pg_user FROM public;
76 REVOKE ALL ON pg_catalog.pg_group FROM public;
77 REVOKE ALL ON pg_catalog.pg_authid FROM public;
78 REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
79 REVOKE ALL ON pg_catalog.pg_settings FROM public;
80 -- The following must have SELECT reallowed if pg_dump is allowed
81 REVOKE ALL ON pg_catalog.pg_roles FROM public;
82 REVOKE ALL ON pg_catalog.pg_database FROM public;
83 REVOKE ALL ON pg_catalog.pg_tablespace FROM public;
84 EOF
85
86 pg_createdb () {
87 local db=$1
88 local owner=''${owner:-$db}
89 local template=''${template:-template1}
90 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
91 $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
92 CREATE ROLE $owner NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN PASSWORD '$pass';
93 CREATE DATABASE $db WITH OWNER=$owner
94 ''${encoding:+ENCODING='$encoding'}
95 ''${lc_collate:+LC_COLLATE='$lc_collate'}
96 ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
97 ''${tablespace:+TABLESPACE='$tablespace'}
98 ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
99 ;
100 REVOKE ALL ON DATABASE $db FROM public;
101 EOF
102 $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
103 -- Grant all rights to the public schema in the new database to the main user
104 GRANT ALL ON SCHEMA public TO $owner WITH GRANT OPTION;
105 EOF
106 $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
107 }
108 }
109
110 pg_adduser () {
111 local db=$1
112 local user=$2
113 $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
114 $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
115 CREATE ROLE $user NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '$pass';
116 GRANT USAGE ON SCHEMA public TO $user;
117 GRANT CONNECT,TEMPORARY ON DATABASE $db TO $user;
118 EOF
119 }
120 '';
121 };
122 }