config = lib.mkIf cfg.enable {
networking.firewall.allowedTCPPorts = [ 5432 ];
- security.acme.certs."postgresql" = config.myServices.databasesCerts // {
+ security.acme2.certs."postgresql" = config.myServices.databasesCerts // {
user = "postgres";
group = "postgres";
- plugins = [ "fullchain.pem" "key.pem" "account_key.json" ];
+ plugins = [ "fullchain.pem" "key.pem" "account_key.json" "account_reg.json" ];
domain = "db-1.immae.eu";
postRun = ''
systemctl reload postgresql.service
SupplementaryGroups = "keys";
RuntimeDirectory = cfg.systemdRuntimeDirectory;
};
+ systemd.services.postgresql.postStart = lib.mkAfter ''
+ # This line is already defined in 19.09
+ PSQL="${pkgs.sudo}/bin/sudo -u postgres psql --port=5432"
+
+ ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
+ $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
+ | grep -q 1 \
+ || $PSQL -tAc 'CREATE USER "${role}" WITH REPLICATION'
+ '') cfg.replicationHosts)}
+
+ ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _:
+ let
+ sname = builtins.replaceStrings ["-"] ["_"] role;
+ in
+ ''
+ $PSQL -tAc "SELECT 1 FROM pg_replication_slots WHERE slot_name='${sname}'" \
+ | grep -q 1 \
+ || $PSQL -tAc "SELECT * FROM pg_create_physical_replication_slot('${sname}')"
+ '') cfg.replicationHosts)}
+ '';
+
services.postgresql = {
enable = true;
package = cfg.package;
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
+ # this introduces a small delay before storing on disk, but
+ # makes it order of magnitudes quicker
+ synchronous_commit = off
ssl = on
- ssl_cert_file = '${config.security.acme.directory}/postgresql/fullchain.pem'
- ssl_key_file = '${config.security.acme.directory}/postgresql/key.pem'
+ ssl_cert_file = '${config.security.acme2.certs.postgresql.directory}/fullchain.pem'
+ ssl_key_file = '${config.security.acme2.certs.postgresql.directory}/key.pem'
'';
authentication = let
hosts = builtins.concatStringsSep "\n" (