-{ lib, pkgs, config, myconfig, ... }:
+{ lib, pkgs, config, ... }:
let
cfg = config.myServices.databases.postgresql;
in {
options.myServices.databases = {
postgresql = {
enable = lib.mkOption {
- default = cfg.enable;
+ default = false;
example = true;
description = "Whether to enable postgresql database";
type = lib.types.bool;
};
+ package = lib.mkOption {
+ type = lib.types.package;
+ default = pkgs.postgresql;
+ description = ''
+ Postgresql package to use.
+ '';
+ };
+ ldapConfig = lib.mkOption {
+ description = "LDAP configuration to allow PAM identification via LDAP";
+ type = lib.types.submodule {
+ options = {
+ host = lib.mkOption { type = lib.types.str; };
+ base = lib.mkOption { type = lib.types.str; };
+ dn = lib.mkOption { type = lib.types.str; };
+ password = lib.mkOption { type = lib.types.str; };
+ filter = lib.mkOption { type = lib.types.str; };
+ };
+ };
+ };
+ replicationLdapConfig = lib.mkOption {
+ description = "LDAP configuration to allow replication";
+ type = lib.types.submodule {
+ options = {
+ host = lib.mkOption { type = lib.types.str; };
+ base = lib.mkOption { type = lib.types.str; };
+ dn = lib.mkOption { type = lib.types.str; };
+ password = lib.mkOption { type = lib.types.str; };
+ };
+ };
+ };
+ authorizedHosts = lib.mkOption {
+ default = {};
+ description = "Hosts to allow connections from";
+ type = lib.types.attrsOf (lib.types.listOf (lib.types.submodule {
+ options = {
+ method = lib.mkOption {
+ default = "md5";
+ type = lib.types.str;
+ };
+ username = lib.mkOption {
+ default = "all";
+ type = lib.types.str;
+ };
+ database = lib.mkOption {
+ default = "all";
+ type = lib.types.str;
+ };
+ ip4 = lib.mkOption {
+ default = [];
+ type = lib.types.listOf lib.types.str;
+ };
+ ip6 = lib.mkOption {
+ default = [];
+ type = lib.types.listOf lib.types.str;
+ };
+ };
+ }));
+ };
+ replicationHosts = lib.mkOption {
+ default = {};
+ description = "Hosts to allow replication from";
+ type = lib.types.attrsOf (lib.types.submodule {
+ options = {
+ ip4 = lib.mkOption {
+ type = lib.types.listOf lib.types.str;
+ };
+ ip6 = lib.mkOption {
+ type = lib.types.listOf lib.types.str;
+ };
+ };
+ });
+ };
# Output variables
socketsDir = lib.mkOption {
type = lib.types.path;
'';
readOnly = true;
};
- systemdRuntimeDirectory = lib.mkOption {
- type = lib.types.str;
- # Use ReadWritePaths= instead if socketsDir is outside of /run
- default = assert lib.strings.hasPrefix "/run/" cfg.socketsDir;
- lib.strings.removePrefix "/run/" cfg.socketsDir;
- description = ''
- Adjusted Postgresql sockets directory for systemd
- '';
- readOnly = true;
- };
};
};
config = lib.mkIf cfg.enable {
- nixpkgs.overlays = [ (self: super: rec {
- postgresql = self.postgresql_11_custom;
- }) ];
-
networking.firewall.allowedTCPPorts = [ 5432 ];
security.acme.certs."postgresql" = config.myServices.databasesCerts // {
user = "postgres";
group = "postgres";
- plugins = [ "fullchain.pem" "key.pem" "account_key.json" ];
domain = "db-1.immae.eu";
postRun = ''
systemctl reload postgresql.service
systemd.services.postgresql.serviceConfig = {
SupplementaryGroups = "keys";
- RuntimeDirectory = cfg.systemdRuntimeDirectory;
};
- services.postgresql = rec {
+ 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 = pkgs.postgresql;
+ package = cfg.package;
enableTCPIP = true;
extraConfig = ''
max_connections = 100
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 = '/var/lib/acme/postgresql/fullchain.pem'
- ssl_key_file = '/var/lib/acme/postgresql/key.pem'
+ ssl_cert_file = '${config.security.acme.certs.postgresql.directory}/fullchain.pem'
+ ssl_key_file = '${config.security.acme.certs.postgresql.directory}/key.pem'
'';
- authentication = ''
+ authentication = let
+ hosts = builtins.concatStringsSep "\n" (
+ lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v:
+ map (ip6: "hostssl ${v.database} ${v.username} ${ip6}/128 ${v.method}") v.ip6
+ ++ map (ip4: "hostssl ${v.database} ${v.username} ${ip4}/32 ${v.method}") v.ip4
+ ) vs) cfg.authorizedHosts
+ ));
+ replication = builtins.concatStringsSep "\n" (
+ lib.lists.flatten (lib.mapAttrsToList (k: v:
+ map (ip6: "hostssl replication ${k} ${ip6}/128 pam pamservice=postgresql_replication") v.ip6
+ ++ map (ip4: "hostssl replication ${k} ${ip4}/32 pam pamservice=postgresql_replication") v.ip4
+ ) cfg.replicationHosts
+ ));
+ in ''
local all postgres ident
local all all md5
- hostssl all all 188.165.209.148/32 md5
- hostssl all all 178.33.252.96/32 md5
+ ${hosts}
hostssl all all all pam
- hostssl replication backup-1 2001:41d0:302:1100::9:e5a9/128 pam pamservice=postgresql_replication
- hostssl replication backup-1 54.37.151.137/32 pam pamservice=postgresql_replication
+ ${replication}
'';
};
permissions = "0400";
group = "postgres";
user = "postgres";
- text = with myconfig.env.databases.postgresql.pam; ''
- host ${myconfig.env.ldap.host}
- base ${myconfig.env.ldap.base}
+ text = with cfg.ldapConfig; ''
+ host ${host}
+ base ${base}
binddn ${dn}
bindpw ${password}
pam_filter ${filter}
permissions = "0400";
group = "postgres";
user = "postgres";
- text = ''
- host ${myconfig.env.ldap.host}
- base ${myconfig.env.ldap.base}
- binddn ${myconfig.env.ldap.host_dn}
- bindpw ${myconfig.env.ldap.password}
+ text = with cfg.replicationLdapConfig; ''
+ host ${host}
+ base ${base}
+ binddn ${dn}
+ bindpw ${password}
pam_login_attribute cn
ssl start_tls
'';
security.pam.services = let
pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
- in [
- {
- name = "postgresql";
+ in {
+ postgresql = {
text = ''
auth required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
'';
- }
- {
- name = "postgresql_replication";
+ };
+ postgresql_replication = {
text = ''
auth required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication
account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication
'';
- }
- ];
+ };
+ };
};
}