knot: sourcephile.fr: setup lebureau as a secondary NS
[sourcephile-nix.git] / hosts / losurdo / postgresql.nix
index ad3a92d32a5c63486490abb9c7bd74ceea878b00..f6c7d9e15f9030c907da5b2da89c281d0f88c013 100644 (file)
-{ pkgs, lib, config, hostName, ... }:
+{ pkgs, config, hostName, host, ... }:
 let
   inherit (config.networking) domain;
   inherit (config.services) postgresql;
   inherit (config.users) users;
 in
 {
-imports =
-  [];
+  imports =
+    [ ];
   /*
-  map (eta: (import postgresql/openconcerto.nix) eta) [
+    map (eta: (import postgresql/openconcerto.nix) eta) [
     {db = "openconcerto1";}
     {db = "openconcerto2";}
     {db = "lbec";}
     {db = "lbm";}
-  ];
+    ];
   */
-/*
-networking.nftables.ruleset = ''
-  add rule inet filter net2fw tcp dport 5432 counter accept comment "PostgreSQL"
-'';
-*/
-users.groups.acme.members = [ users."postgres".name ];
-security.acme.certs."${domain}" = {
-  postRun = "systemctl reload postgresql";
-};
-systemd.services.postgresql = {
-  wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service"];
-  after = [ "acme-selfsigned-${domain}.service" ];
-};
-services.postgresql = {
-  enable = true;
-  package = pkgs.postgresql_12;
-  #enableTCPIP = true;
-  settings = {
-    # ZFS is Copy on Write (CoW). As a result, it’s not possible
-    # to have a torn page because a page can’t be partially written
-    # without reverting to the previous copy.
-    full_page_writes = false;
-    log_connections = true;
-    log_disconnections = true;
-    log_hostname = false;
-    max_connections = 25;
-    max_locks_per_transaction = 1024;
-    password_encryption = "scram-sha-256"; # requires postfix >= 11
-    ssl = false;
-    ssl_cert_file = "/var/lib/acme/${domain}/fullchain.pem";
-    ssl_key_file = "/var/lib/acme/${domain}/key.pem";
-    unix_socket_permissions = "0770";
+  /*
+    networking.nftables.ruleset = ''
+    table inet filter {
+    chain input-net {
+      tcp dport postgresql counter accept comment "postgresql"
+    }
+    }
+    '';
+  */
+  users.groups.acme.members = [ users."postgres".name ];
+  security.acme.certs."${domain}" = {
+    postRun = "systemctl reload postgresql";
+  };
+  systemd.services.postgresql = {
+    wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service" ];
+    after = [ "acme-selfsigned-${domain}.service" ];
+  };
+  services.postgresql = {
+    enable = true;
+    package = pkgs.postgresql_12;
+    #enableTCPIP = true;
+    settings = {
+      log_connections = true;
+      log_disconnections = true;
+      log_hostname = false;
+      max_locks_per_transaction = 1024;
+      password_encryption = "scram-sha-256"; # requires postfix >= 11
+      ssl = false;
+      ssl_cert_file = "/var/lib/acme/${domain}/fullchain.pem";
+      ssl_key_file = "/var/lib/acme/${domain}/key.pem";
+      unix_socket_permissions = "0770";
+
+      # ZFS is Copy on Write (CoW). As a result, it’s not possible
+      # to have a torn page because a page can’t be partially written
+      # without reverting to the previous copy.
+      full_page_writes = false;
+      # Avoid zero-filling of new files used for Postgres’s Write Ahead Log.
+      # The reasoning here is that since ZFS overwrites these anyway,
+      # you might as well get yourself a new inode.
+      wal_init_zero = false;
+      wal_recycle = false;
+
+      max_connections = 50;
+      # The proper size for the POSTGRESQL shared buffer cache is the largest
+      # useful size that does not adversely affect other activity.
+      # Ideally we should limit ZFS's ARC's max size
+      # to a percentage of physical memory
+      # minus the shared_buffer cache for PostgreSQL
+      # minus the kernel's memory overhead.
+      shared_buffers = "1GB";
+      # Used by the optimizer to estimate the size of the kernel's disk buffer cache.
+      effective_cache_size = "3GB";
+      # Setting a large value helps in tasks like
+      # VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.
+      maintenance_work_mem = "256MB";
+      # Bigger value for wal_buffers in case of lot of concurrent connection gives better performance.
+      wal_buffers = "16MB";
+      min_wal_size = "1GB";
+      max_wal_size = "4GB";
+      default_statistics_target = 100;
+      random_page_cost = "1.1";
+      effective_io_concurrency = 200;
+      work_mem = "20971kB";
+      huge_pages = false;
+      max_worker_processes = host.CPUs;
+      max_parallel_workers_per_gather = host.CPUs / 2;
+      max_parallel_workers = host.CPUs;
+      max_parallel_maintenance_workers = host.CPUs / 2;
+    };
+    # If one record is chosen and the auth fails,
+    # subsequent records are not considered.
+    authentication = ''
+      # CONNECTION  DATABASE USER      AUTH  OPTIONS
+      local         all      postgres  peer  map=admin
+      local         samerole all       peer  map=user
+      #local         all      backup    peer
+    '';
+    identMap = ''
+      # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
+      admin      postgres         postgres
+      admin      root             postgres
+      # Commented to prefer explicit auth
+      # to handle revocation without droping the user yet
+      #user       /^(.*)$          \1
+    '';
   };
-  authentication = lib.mkForce ''
-    # CONNECTION  DATABASE USER      AUTH  OPTIONS
-    local         all      postgres  peer  map=admin
-    local         samerole all       peer  map=user
-    #local         all      backup    peer
-  '';
-  identMap = ''
-    # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
-    admin      postgres         postgres
-    admin      root             postgres
-    # Commented to prefer explicit auth
-    # to handle revocation without droping the user yet
-    #user       /^(.*)$          \1
-  '';
-};
-fileSystems."/var/lib/postgresql" = {
-  device = "${hostName}/var/postgresql";
-  fsType = "zfs"; # with sync=always,
-  # though loading OpenConcerto's initial SQL
-  # takes 1m40s instead of 40s :\
-};
-services.syncoid.commands = {
-  "${hostName}/var/postgresql" = {
+  /*
+    services.syncoid.commands = {
+    "${hostName}/var/postgresql" = {
     sendOptions = "raw";
     target = "backup@mermet.${domain}:rpool/backup/${hostName}/var/postgresql";
+    };
+    };
+  */
+  services.sanoid.datasets = {
+    "${hostName}/var/lib/postgresql" = {
+      use_template = [ "snap" ];
+      daily = 31;
+      hourly = 0;
+      recursive = true;
+    };
   };
-};
-services.sanoid.datasets = {
-  "${hostName}/var/postgresql" = {
-    use_template = [ "snap" ];
-    daily = 31;
-  };
-};
-systemd.services.postgresql = {
-  # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
-  postStart = ''
-    set -eux
-    # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
-    $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
-      -- Disallow access to the public schema
-      -- of individual users' databases by other users
-      REVOKE ALL ON DATABASE template0 FROM public;
-      REVOKE ALL ON DATABASE template1 FROM public;
-      REVOKE ALL ON SCHEMA public FROM public;
-      GRANT  ALL ON SCHEMA public TO ${postgresql.superUser};
-      REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
-      GRANT  ALL ON ALL TABLES IN SCHEMA public TO ${postgresql.superUser};
+  systemd.services.postgresql = {
+    # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
+    postStart = ''
+      set -eux
+      # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
+      $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
+        -- Disallow access to the public schema
+        -- of individual users' databases by other users
+        REVOKE ALL ON DATABASE template0 FROM public;
+        REVOKE ALL ON DATABASE template1 FROM public;
+        REVOKE ALL ON SCHEMA public FROM public;
+        GRANT  USAGE,CREATE ON SCHEMA public TO public;
+        GRANT  ALL ON SCHEMA public TO "${postgresql.superUser}";
+        REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
+        GRANT  ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";
 
-      -- Disallow access to database and user names for everyone
-      REVOKE ALL ON pg_catalog.pg_user         FROM public;
-      REVOKE ALL ON pg_catalog.pg_group        FROM public;
-      REVOKE ALL ON pg_catalog.pg_authid       FROM public;
-      REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
-      REVOKE ALL ON pg_catalog.pg_settings     FROM public;
-      -- The following must have SELECT reallowed if pg_dump is allowed
-      REVOKE ALL ON pg_catalog.pg_roles        FROM public;
-      REVOKE ALL ON pg_catalog.pg_database     FROM public;
-      REVOKE ALL ON pg_catalog.pg_tablespace   FROM public;
-    EOF
+        -- Disallow access to database and user names for everyone
+        REVOKE ALL ON pg_catalog.pg_user         FROM public;
+        REVOKE ALL ON pg_catalog.pg_group        FROM public;
+        REVOKE ALL ON pg_catalog.pg_authid       FROM public;
+        REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
+        REVOKE ALL ON pg_catalog.pg_settings     FROM public;
+        -- The following must have SELECT reallowed if pg_dump is allowed
+        REVOKE ALL ON pg_catalog.pg_roles        FROM public;
+        REVOKE ALL ON pg_catalog.pg_database     FROM public;
+        REVOKE ALL ON pg_catalog.pg_tablespace   FROM public;
+      EOF
 
-    pg_createdb () {
-      local db=$1
-      local owner=''${owner:-$db}
-      local template=''${template:-template1}
-      $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
-        $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
-          CREATE ROLE $owner NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN PASSWORD '$pass';
-          CREATE DATABASE $db WITH OWNER=$owner
-           ''${encoding:+ENCODING='$encoding'}
-           ''${lc_collate:+LC_COLLATE='$lc_collate'}
-           ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
-           ''${tablespace:+TABLESPACE='$tablespace'}
-           ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
-          ;
-          REVOKE ALL ON DATABASE $db FROM public;
-    EOF
-        $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
-          -- Grant all rights to the public schema in the new database to the main user
-          GRANT ALL ON SCHEMA public TO $owner WITH GRANT OPTION;
-    EOF
-        $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
+      pg_createdb () {
+        local db=$1
+        local owner=''${owner:-$db}
+        local template=''${template:-template1}
+        $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
+          $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
+            CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+PASSWORD '$pass'};
+            CREATE DATABASE "$db" WITH OWNER="$owner"
+             ''${encoding:+ENCODING='$encoding'}
+             ''${lc_collate:+LC_COLLATE='$lc_collate'}
+             ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
+             ''${tablespace:+TABLESPACE='$tablespace'}
+             ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
+            ;
+            REVOKE ALL ON DATABASE "$db" FROM public;
+      EOF
+          $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
+            -- Grant all rights to the public schema in the new database to the main user
+            GRANT ALL ON SCHEMA public TO "$owner" WITH GRANT OPTION;
+      EOF
+          $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
+        }
       }
-    }
 
-    pg_adduser () {
-      local db=$1
-      local user=$2
-      $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
-      $PSQL -d $db -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
-        CREATE ROLE $user NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '$pass';
-        GRANT USAGE ON SCHEMA public TO $user;
-        GRANT CONNECT,TEMPORARY ON DATABASE $db TO $user;
-    EOF
-    }
-  '';
-};
+      pg_adduser () {
+        local db=$1
+        local user=$2
+        $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
+        $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
+          CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+ENCRYPTED PASSWORD '$pass'};
+          GRANT USAGE ON SCHEMA public TO "$user";
+          GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";
+      EOF
+      }
+    '';
+  };
 }