1 { lib, pkgs, config, ... }:
3 cfg = config.myServices.databases.postgresql;
5 options.myServices.databases = {
7 enable = lib.mkOption {
10 description = "Whether to enable postgresql database";
11 type = lib.types.bool;
13 package = lib.mkOption {
14 type = lib.types.package;
15 default = pkgs.postgresql;
17 Postgresql package to use.
20 ldapConfig = lib.mkOption {
21 description = "LDAP configuration to allow PAM identification via LDAP";
22 type = lib.types.submodule {
24 host = lib.mkOption { type = lib.types.str; };
25 base = lib.mkOption { type = lib.types.str; };
26 dn = lib.mkOption { type = lib.types.str; };
27 password = lib.mkOption { type = lib.types.str; };
28 filter = lib.mkOption { type = lib.types.str; };
32 replicationLdapConfig = lib.mkOption {
33 description = "LDAP configuration to allow replication";
34 type = lib.types.submodule {
36 host = lib.mkOption { type = lib.types.str; };
37 base = lib.mkOption { type = lib.types.str; };
38 dn = lib.mkOption { type = lib.types.str; };
39 password = lib.mkOption { type = lib.types.str; };
43 authorizedHosts = lib.mkOption {
45 description = "Hosts to allow connections from";
46 type = lib.types.attrsOf (lib.types.listOf (lib.types.submodule {
48 method = lib.mkOption {
52 username = lib.mkOption {
56 database = lib.mkOption {
62 type = lib.types.listOf lib.types.str;
66 type = lib.types.listOf lib.types.str;
71 replicationHosts = lib.mkOption {
73 description = "Hosts to allow replication from";
74 type = lib.types.attrsOf (lib.types.submodule {
77 type = lib.types.listOf lib.types.str;
80 type = lib.types.listOf lib.types.str;
86 socketsDir = lib.mkOption {
87 type = lib.types.path;
88 default = "/run/postgresql";
90 The directory where Postgresql puts sockets.
97 config = lib.mkIf cfg.enable {
98 networking.firewall.allowedTCPPorts = [ 5432 ];
100 security.acme.certs."postgresql" = config.myServices.databasesCerts // {
102 domain = "db-1.immae.eu";
104 systemctl reload postgresql.service
108 systemd.services.postgresql.serviceConfig = {
109 SupplementaryGroups = "keys";
110 ExecStartPre = [ ("+" + (pkgs.writeShellScript "postgresql-fix-cert" ''
111 # postgresql complains:
112 # private key file "${config.security.acme.certs.postgresql.directory}/key.pem" must be owned by the database user or root
114 "${config.security.acme.certs.postgresql.directory}/key.pem" "${config.services.postgresql.dataDir}/key.pem"
115 chown postgres:postgres "${config.services.postgresql.dataDir}/key.pem"
116 chmod go-r "${config.services.postgresql.dataDir}/key.pem"
119 systemd.services.postgresql.postStart = lib.mkAfter ''
120 # This line is already defined in 19.09
121 PSQL="psql --port=5432"
123 ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
124 $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
126 || $PSQL -tAc 'CREATE USER "${role}" WITH REPLICATION'
127 '') cfg.replicationHosts)}
129 ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _:
131 sname = builtins.replaceStrings ["-"] ["_"] role;
134 $PSQL -tAc "SELECT 1 FROM pg_replication_slots WHERE slot_name='${sname}'" \
136 || $PSQL -tAc "SELECT * FROM pg_create_physical_replication_slot('${sname}')"
137 '') cfg.replicationHosts)}
140 services.postgresql = {
142 package = cfg.package;
146 max_connections = 100;
147 wal_level = "logical";
148 shared_buffers = "512MB";
150 max_wal_size = "1GB";
151 min_wal_size = "80MB";
152 log_timezone = "Europe/Paris";
153 datestyle = "iso, mdy";
154 timezone = "Europe/Paris";
155 lc_messages = "en_US.UTF-8";
156 lc_monetary = "en_US.UTF-8";
157 lc_numeric = "en_US.UTF-8";
158 lc_time = "en_US.UTF-8";
159 default_text_search_config = "pg_catalog.english";
160 # this introduces a small delay before storing on disk, but
161 # makes it order of magnitudes quicker
162 synchronous_commit = "off";
164 ssl_cert_file = "${config.security.acme.certs.postgresql.directory}/fullchain.pem";
165 ssl_key_file = "${config.services.postgresql.dataDir}/key.pem";
168 hosts = builtins.concatStringsSep "\n" (
169 lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v:
170 map (ip6: "hostssl ${v.database} ${v.username} ${ip6} ${v.method}") v.ip6
171 ++ map (ip4: "hostssl ${v.database} ${v.username} ${ip4}/32 ${v.method}") v.ip4
172 ) vs) cfg.authorizedHosts
174 replication = builtins.concatStringsSep "\n" (
175 lib.lists.flatten (lib.mapAttrsToList (k: v:
176 map (ip6: "hostssl replication ${k} ${ip6}/128 pam pamservice=postgresql_replication") v.ip6
177 ++ map (ip4: "hostssl replication ${k} ${ip4}/32 pam pamservice=postgresql_replication") v.ip4
178 ) cfg.replicationHosts
181 local all postgres ident
184 hostssl all all all pam
191 permissions = "0400";
194 text = with cfg.ldapConfig; ''
203 "postgresql/pam_replication" = {
204 permissions = "0400";
207 text = with cfg.replicationLdapConfig; ''
212 pam_login_attribute cn
218 security.pam.services = let
219 pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
223 auth required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
224 account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
227 postgresql_replication = {
229 auth required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
230 account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}