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