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