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