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