--- /dev/null
+{ lib, pkgs, config, ... }:
+let
+ cfg = config.myServices.databases.postgresql;
+in {
+ options.myServices.databases = {
+ postgresql = {
+ enable = lib.mkOption {
+ 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;
+ default = "/run/postgresql";
+ description = ''
+ The directory where Postgresql puts sockets.
+ '';
+ readOnly = true;
+ };
+ };
+ };
+
+ config = lib.mkIf cfg.enable {
+ networking.firewall.allowedTCPPorts = [ 5432 ];
+
+ security.acme.certs."postgresql" = {
+ group = "postgres";
+ domain = "db-1.immae.eu";
+ postRun = ''
+ systemctl reload postgresql.service
+ '';
+ };
+
+ systemd.services.postgresql.serviceConfig = {
+ SupplementaryGroups = "keys";
+ ExecStartPre = [ ("+" + (pkgs.writeShellScript "postgresql-fix-cert" ''
+ # postgresql complains:
+ # private key file "${config.security.acme.certs.postgresql.directory}/key.pem" must be owned by the database user or root
+ cp -f "${config.security.acme.certs.postgresql.directory}/key.pem" "${config.services.postgresql.dataDir}/key.pem"
+ chown postgres:postgres "${config.services.postgresql.dataDir}/key.pem"
+ chmod go-r "${config.services.postgresql.dataDir}/key.pem"
+ '')) ];
+ };
+ systemd.services.postgresql.postStart = lib.mkAfter ''
+ # This line is already defined in 19.09
+ PSQL="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;
+ enableTCPIP = true;
+ checkConfig = false;
+ logLinePrefix = "%h %q%u@%d "; # Default: '%m [%p] ', already stored independently by journald. %h needed for fail2ban
+ settings = {
+ max_connections = 300;
+ wal_level = "logical";
+ shared_buffers = "512MB";
+ work_mem = "10MB";
+ max_wal_size = "1GB";
+ min_wal_size = "80MB";
+ log_timezone = "Europe/Paris";
+ datestyle = "iso, mdy";
+ timezone = "Europe/Paris";
+ lc_messages = "en_US.UTF-8";
+ lc_monetary = "en_US.UTF-8";
+ 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.certs.postgresql.directory}/fullchain.pem";
+ ssl_key_file = "${config.services.postgresql.dataDir}/key.pem";
+ };
+ authentication = let
+ hosts = builtins.concatStringsSep "\n" (
+ lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v:
+ map (ip6: "hostssl ${v.database} ${v.username} ${ip6} ${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
+ ${hosts}
+ hostssl all all all pam
+ ${replication}
+ '';
+ };
+
+ secrets.keys = {
+ "postgresql/pam" = {
+ permissions = "0400";
+ group = "postgres";
+ user = "postgres";
+ text = with cfg.ldapConfig; ''
+ host ${host}
+ base ${base}
+ binddn ${dn}
+ bindpw ${password}
+ pam_filter ${filter}
+ ssl start_tls
+ '';
+ };
+ "postgresql/pam_replication" = {
+ permissions = "0400";
+ group = "postgres";
+ user = "postgres";
+ 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 {
+ postgresql = {
+ text = ''
+ auth required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
+ account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
+ '';
+ };
+ postgresql_replication = {
+ text = ''
+ auth required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
+ account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
+ '';
+ };
+ };
+ };
+}
+
projects / perso / Immae / Config / Nix.git / blobdiff