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