]> Git — Sourcephile - sourcephile-nix.git/blob - hosts/mermet/postgresql.nix
losurdo: syncoid: move to LoadCredentialEncrypted=
[sourcephile-nix.git] / hosts / mermet / postgresql.nix
1 { pkgs, lib, config, hostName, ... }:
2 let
3 inherit (config.networking) domain;
4 inherit (config.services) postgresql;
5 inherit (config.users) users;
6 in
7 {
8 /*
9 networking.nftables.ruleset = ''
10 table inet filter {
11 chain input-net {
12 tcp dport postgresql counter accept comment "PostgreSQL"
13 }
14 }
15 '';
16 users.groups.acme.members = [ users."postgres".name ];
17 security.acme.certs."${domain}" = {
18 postRun = "systemctl reload postgresql";
19 };
20 systemd.services.postgresql = {
21 wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service"];
22 after = [ "acme-selfsigned-${domain}.service" ];
23 };
24 */
25 services.postgresql = {
26 enable = true;
27 package = pkgs.postgresql_12;
28 enableTCPIP = false;
29 settings = {
30 # ZFS is Copy on Write (CoW). As a result, it’s not possible
31 # to have a torn page because a page can’t be partially written
32 # without reverting to the previous copy.
33 full_page_writes = false;
34 log_connections = true;
35 log_disconnections = true;
36 log_hostname = false;
37 max_connections = 25;
38 max_locks_per_transaction = 1024;
39 password_encryption = "scram-sha-256"; # requires postfix >= 11
40 unix_socket_permissions = "0770";
41 };
42 # If one record is chosen and the auth fails,
43 # subsequent records are not considered.
44 authentication = ''
45 # CONNECTION DATABASE USER AUTH OPTIONS
46 local all postgres peer map=admin
47 local samerole all peer map=user
48 #local all backup peer
49 '';
50 identMap = ''
51 # MAPNAME SYSTEM-USERNAME PG-USERNAME
52 admin postgres postgres
53 admin root postgres
54 # Commented to prefer explicit auth
55 # to handle revocation without droping the user yet
56 #user /^(.*)$ \1
57 '';
58 };
59 fileSystems."/var/lib/postgresql" = {
60 device = "rpool/var/postgresql";
61 fsType = "zfs"; # with sync=always,
62 # though loading OpenConcerto's initial SQL
63 # takes 1m40s instead of 40s :\
64 };
65 services.sanoid.datasets = {
66 "rpool/var/postgresql" = {
67 use_template = [ "snap" ];
68 daily = 31;
69 };
70 };
71 systemd.services.postgresql = {
72 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
73 postStart = ''
74 set -eux
75 # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
76 $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
77 -- Disallow access to the public schema
78 -- of individual users' databases by other users
79 REVOKE ALL ON DATABASE template0 FROM public;
80 REVOKE ALL ON DATABASE template1 FROM public;
81 REVOKE ALL ON SCHEMA public FROM public;
82 GRANT USAGE,CREATE ON SCHEMA public TO public;
83 GRANT ALL ON SCHEMA public TO "${postgresql.superUser}";
84 REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
85 GRANT ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
86
87 -- Disallow access to database and user names for everyone
88 REVOKE ALL ON pg_catalog.pg_user FROM public;
89 REVOKE ALL ON pg_catalog.pg_group FROM public;
90 REVOKE ALL ON pg_catalog.pg_authid FROM public;
91 REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
92 REVOKE ALL ON pg_catalog.pg_settings FROM public;
93 -- The following must have SELECT reallowed if pg_dump is allowed
94 REVOKE ALL ON pg_catalog.pg_roles FROM public;
95 REVOKE ALL ON pg_catalog.pg_database FROM public;
96 REVOKE ALL ON pg_catalog.pg_tablespace FROM public;
97 EOF
98 '';
99 };
100 }