pyrox.dev
1{ lib, pkgs, config, ... } :
2
3with lib;
4
5let
6 cfg = config.services.pgbouncer;
7
8 confFile = pkgs.writeTextFile {
9 name = "pgbouncer.ini";
10 text = ''
11 [databases]
12 ${concatStringsSep "\n"
13 (mapAttrsToList (dbname : settings : "${dbname} = ${settings}") cfg.databases)}
14
15 [users]
16 ${concatStringsSep "\n"
17 (mapAttrsToList (username : settings : "${username} = ${settings}") cfg.users)}
18
19 [peers]
20 ${concatStringsSep "\n"
21 (mapAttrsToList (peerid : settings : "${peerid} = ${settings}") cfg.peers)}
22
23 [pgbouncer]
24 # general
25 ${optionalString (cfg.ignoreStartupParameters != null) "ignore_startup_parameters = ${cfg.ignoreStartupParameters}"}
26 listen_port = ${toString cfg.listenPort}
27 ${optionalString (cfg.listenAddress != null) "listen_addr = ${cfg.listenAddress}"}
28 pool_mode = ${cfg.poolMode}
29 max_client_conn = ${toString cfg.maxClientConn}
30 default_pool_size = ${toString cfg.defaultPoolSize}
31 max_user_connections = ${toString cfg.maxUserConnections}
32 max_db_connections = ${toString cfg.maxDbConnections}
33
34 #auth
35 auth_type = ${cfg.authType}
36 ${optionalString (cfg.authHbaFile != null) "auth_hba_file = ${cfg.authHbaFile}"}
37 ${optionalString (cfg.authFile != null) "auth_file = ${cfg.authFile}"}
38 ${optionalString (cfg.authUser != null) "auth_user = ${cfg.authUser}"}
39 ${optionalString (cfg.authQuery != null) "auth_query = ${cfg.authQuery}"}
40 ${optionalString (cfg.authDbname != null) "auth_dbname = ${cfg.authDbname}"}
41
42 # TLS
43 ${optionalString (cfg.tls.client != null) ''
44 client_tls_sslmode = ${cfg.tls.client.sslmode}
45 client_tls_key_file = ${cfg.tls.client.keyFile}
46 client_tls_cert_file = ${cfg.tls.client.certFile}
47 client_tls_ca_file = ${cfg.tls.client.caFile}
48 ''}
49 ${optionalString (cfg.tls.server != null) ''
50 server_tls_sslmode = ${cfg.tls.server.sslmode}
51 server_tls_key_file = ${cfg.tls.server.keyFile}
52 server_tls_cert_file = ${cfg.tls.server.certFile}
53 server_tls_ca_file = ${cfg.tls.server.caFile}
54 ''}
55
56 # log
57 ${optionalString (cfg.logFile != null) "logfile = ${cfg.homeDir}/${cfg.logFile}"}
58 ${optionalString (cfg.syslog != null) ''
59 syslog = ${if cfg.syslog.enable then "1" else "0"}
60 syslog_ident = ${cfg.syslog.syslogIdent}
61 syslog_facility = ${cfg.syslog.syslogFacility}
62 ''}
63 ${optionalString (cfg.verbose != null) "verbose = ${toString cfg.verbose}"}
64
65 # console access
66 ${optionalString (cfg.adminUsers != null) "admin_users = ${cfg.adminUsers}"}
67 ${optionalString (cfg.statsUsers != null) "stats_users = ${cfg.statsUsers}"}
68
69 # extra
70 ${cfg.extraConfig}
71 '';
72 };
73
74in {
75
76 options.services.pgbouncer = {
77
78 # NixOS settings
79
80 enable = mkEnableOption "PostgreSQL connection pooler";
81
82 package = mkPackageOption pkgs "pgbouncer" { };
83
84 openFirewall = mkOption {
85 type = types.bool;
86 default = false;
87 description = ''
88 Whether to automatically open the specified TCP port in the firewall.
89 '';
90 };
91
92 # Generic settings
93
94 logFile = mkOption {
95 type = types.nullOr types.str;
96 default = null;
97 description = ''
98 Specifies a log file in addition to journald.
99 '';
100 };
101
102 listenAddress = mkOption {
103 type = types.nullOr types.commas;
104 example = "*";
105 default = null;
106 description = ''
107 Specifies a list (comma-separated) of addresses where to listen for TCP connections.
108 You may also use * meaning “listen on all addresses”.
109 When not set, only Unix socket connections are accepted.
110
111 Addresses can be specified numerically (IPv4/IPv6) or by name.
112 '';
113 };
114
115 listenPort = mkOption {
116 type = types.port;
117 default = 6432;
118 description = ''
119 Which port to listen on. Applies to both TCP and Unix sockets.
120 '';
121 };
122
123 poolMode = mkOption {
124 type = types.enum [ "session" "transaction" "statement" ];
125 default = "session";
126 description = ''
127 Specifies when a server connection can be reused by other clients.
128
129 session
130 Server is released back to pool after client disconnects. Default.
131 transaction
132 Server is released back to pool after transaction finishes.
133 statement
134 Server is released back to pool after query finishes.
135 Transactions spanning multiple statements are disallowed in this mode.
136 '';
137 };
138
139 maxClientConn = mkOption {
140 type = types.int;
141 default = 100;
142 description = ''
143 Maximum number of client connections allowed.
144
145 When this setting is increased, then the file descriptor limits in the operating system
146 might also have to be increased. Note that the number of file descriptors potentially
147 used is more than maxClientConn. If each user connects under its own user name to the server,
148 the theoretical maximum used is:
149 maxClientConn + (max pool_size * total databases * total users)
150
151 If a database user is specified in the connection string (all users connect under the same user name),
152 the theoretical maximum is:
153 maxClientConn + (max pool_size * total databases)
154
155 The theoretical maximum should never be reached, unless somebody deliberately crafts a special load for it.
156 Still, it means you should set the number of file descriptors to a safely high number.
157 '';
158 };
159
160 defaultPoolSize = mkOption {
161 type = types.int;
162 default = 20;
163 description = ''
164 How many server connections to allow per user/database pair.
165 Can be overridden in the per-database configuration.
166 '';
167 };
168
169 maxDbConnections = mkOption {
170 type = types.int;
171 default = 0;
172 description = ''
173 Do not allow more than this many server connections per database (regardless of user).
174 This considers the PgBouncer database that the client has connected to,
175 not the PostgreSQL database of the outgoing connection.
176
177 This can also be set per database in the [databases] section.
178
179 Note that when you hit the limit, closing a client connection to one pool will
180 not immediately allow a server connection to be established for another pool,
181 because the server connection for the first pool is still open.
182 Once the server connection closes (due to idle timeout),
183 a new server connection will immediately be opened for the waiting pool.
184
185 0 = unlimited
186 '';
187 };
188
189 maxUserConnections = mkOption {
190 type = types.int;
191 default = 0;
192 description = ''
193 Do not allow more than this many server connections per user (regardless of database).
194 This considers the PgBouncer user that is associated with a pool,
195 which is either the user specified for the server connection
196 or in absence of that the user the client has connected as.
197
198 This can also be set per user in the [users] section.
199
200 Note that when you hit the limit, closing a client connection to one pool
201 will not immediately allow a server connection to be established for another pool,
202 because the server connection for the first pool is still open.
203 Once the server connection closes (due to idle timeout), a new server connection
204 will immediately be opened for the waiting pool.
205
206 0 = unlimited
207 '';
208 };
209
210 ignoreStartupParameters = mkOption {
211 type = types.nullOr types.commas;
212 example = "extra_float_digits";
213 default = null;
214 description = ''
215 By default, PgBouncer allows only parameters it can keep track of in startup packets:
216 client_encoding, datestyle, timezone and standard_conforming_strings.
217
218 All others parameters will raise an error.
219 To allow others parameters, they can be specified here, so that PgBouncer knows that
220 they are handled by the admin and it can ignore them.
221
222 If you need to specify multiple values, use a comma-separated list.
223
224 IMPORTANT: When using prometheus-pgbouncer-exporter, you need:
225 extra_float_digits
226 <https://github.com/prometheus-community/pgbouncer_exporter#pgbouncer-configuration>
227 '';
228 };
229
230 # Section [databases]
231 databases = mkOption {
232 type = types.attrsOf types.str;
233 default = {};
234 example = {
235 exampledb = "host=/run/postgresql/ port=5432 auth_user=exampleuser dbname=exampledb sslmode=require";
236 bardb = "host=localhost dbname=bazdb";
237 foodb = "host=host1.example.com port=5432";
238 };
239 description = ''
240 Detailed information about PostgreSQL database definitions:
241 <https://www.pgbouncer.org/config.html#section-databases>
242 '';
243 };
244
245 # Section [users]
246 users = mkOption {
247 type = types.attrsOf types.str;
248 default = {};
249 example = {
250 user1 = "pool_mode=session";
251 };
252 description = ''
253 Optional.
254
255 Detailed information about PostgreSQL user definitions:
256 <https://www.pgbouncer.org/config.html#section-users>
257 '';
258 };
259
260 # Section [peers]
261 peers = mkOption {
262 type = types.attrsOf types.str;
263 default = {};
264 example = {
265 "1" = "host=host1.example.com";
266 "2" = "host=/tmp/pgbouncer-2 port=5555";
267 };
268 description = ''
269 Optional.
270
271 Detailed information about PostgreSQL database definitions:
272 <https://www.pgbouncer.org/config.html#section-peers>
273 '';
274 };
275
276 # Authentication settings
277 authType = mkOption {
278 type = types.enum [ "cert" "md5" "scram-sha-256" "plain" "trust" "any" "hba" "pam" ];
279 default = "md5";
280 description = ''
281 How to authenticate users.
282
283 cert
284 Client must connect over TLS connection with a valid client certificate.
285 The user name is then taken from the CommonName field from the certificate.
286 md5
287 Use MD5-based password check. This is the default authentication method.
288 authFile may contain both MD5-encrypted and plain-text passwords.
289 If md5 is configured and a user has a SCRAM secret, then SCRAM authentication is used automatically instead.
290 scram-sha-256
291 Use password check with SCRAM-SHA-256. authFile has to contain SCRAM secrets or plain-text passwords.
292 plain
293 The clear-text password is sent over the wire. Deprecated.
294 trust
295 No authentication is done. The user name must still exist in authFile.
296 any
297 Like the trust method, but the user name given is ignored.
298 Requires that all databases are configured to log in as a specific user.
299 Additionally, the console database allows any user to log in as admin.
300 hba
301 The actual authentication type is loaded from authHbaFile.
302 This allows different authentication methods for different access paths,
303 for example: connections over Unix socket use the peer auth method, connections over TCP must use TLS.
304 pam
305 PAM is used to authenticate users, authFile is ignored.
306 This method is not compatible with databases using the authUser option.
307 The service name reported to PAM is “pgbouncer”. pam is not supported in the HBA configuration file.
308 '';
309 };
310
311 authHbaFile = mkOption {
312 type = types.nullOr types.path;
313 default = null;
314 example = "/secrets/pgbouncer_hba";
315 description = ''
316 HBA configuration file to use when authType is hba.
317
318 See HBA file format details:
319 <https://www.pgbouncer.org/config.html#hba-file-format>
320 '';
321 };
322
323 authFile = mkOption {
324 type = types.nullOr types.path;
325 default = null;
326 example = "/secrets/pgbouncer_authfile";
327 description = ''
328 The name of the file to load user names and passwords from.
329
330 See section Authentication file format details:
331 <https://www.pgbouncer.org/config.html#authentication-file-format>
332
333 Most authentication types require that either authFile or authUser be set;
334 otherwise there would be no users defined.
335 '';
336 };
337
338 authUser = mkOption {
339 type = types.nullOr types.str;
340 default = null;
341 example = "pgbouncer";
342 description = ''
343 If authUser is set, then any user not specified in authFile will be queried
344 through the authQuery query from pg_shadow in the database, using authUser.
345 The password of authUser will be taken from authFile.
346 (If the authUser does not require a password then it does not need to be defined in authFile.)
347
348 Direct access to pg_shadow requires admin rights.
349 It's preferable to use a non-superuser that calls a SECURITY DEFINER function instead.
350 '';
351 };
352
353 authQuery = mkOption {
354 type = types.nullOr types.str;
355 default = null;
356 example = "SELECT usename, passwd FROM pg_shadow WHERE usename=$1";
357 description = ''
358 Query to load user's password from database.
359
360 Direct access to pg_shadow requires admin rights.
361 It's preferable to use a non-superuser that calls a SECURITY DEFINER function instead.
362
363 Note that the query is run inside the target database.
364 So if a function is used, it needs to be installed into each database.
365 '';
366 };
367
368 authDbname = mkOption {
369 type = types.nullOr types.str;
370 default = null;
371 example = "authdb";
372 description = ''
373 Database name in the [database] section to be used for authentication purposes.
374 This option can be either global or overriden in the connection string if this parameter is specified.
375 '';
376 };
377
378 # TLS settings
379 tls.client = mkOption {
380 type = types.nullOr (types.submodule {
381 options = {
382 sslmode = mkOption {
383 type = types.enum [ "disable" "allow" "prefer" "require" "verify-ca" "verify-full" ];
384 default = "disable";
385 description = ''
386 TLS mode to use for connections from clients.
387 TLS connections are disabled by default.
388
389 When enabled, tls.client.keyFile and tls.client.certFile
390 must be also configured to set up the key and certificate
391 PgBouncer uses to accept client connections.
392
393 disable
394 Plain TCP. If client requests TLS, it's ignored. Default.
395 allow
396 If client requests TLS, it is used. If not, plain TCP is used.
397 If the client presents a client certificate, it is not validated.
398 prefer
399 Same as allow.
400 require
401 Client must use TLS. If not, the client connection is rejected.
402 If the client presents a client certificate, it is not validated.
403 verify-ca
404 Client must use TLS with valid client certificate.
405 verify-full
406 Same as verify-ca
407 '';
408 };
409 certFile = mkOption {
410 type = types.path;
411 example = "/secrets/pgbouncer.key";
412 description = "Path to certificate for private key. Clients can validate it";
413 };
414 keyFile = mkOption {
415 type = types.path;
416 example = "/secrets/pgbouncer.crt";
417 description = "Path to private key for PgBouncer to accept client connections";
418 };
419 caFile = mkOption {
420 type = types.path;
421 example = "/secrets/pgbouncer.crt";
422 description = "Path to root certificate file to validate client certificates";
423 };
424 };
425 });
426 default = null;
427 description = ''
428 <https://www.pgbouncer.org/config.html#tls-settings>
429 '';
430 };
431
432 tls.server = mkOption {
433 type = types.nullOr (types.submodule {
434 options = {
435 sslmode = mkOption {
436 type = types.enum [ "disable" "allow" "prefer" "require" "verify-ca" "verify-full" ];
437 default = "disable";
438 description = ''
439 TLS mode to use for connections to PostgreSQL servers.
440 TLS connections are disabled by default.
441
442 disable
443 Plain TCP. TLS is not even requested from the server. Default.
444 allow
445 FIXME: if server rejects plain, try TLS?
446 prefer
447 TLS connection is always requested first from PostgreSQL.
448 If refused, the connection will be established over plain TCP.
449 Server certificate is not validated.
450 require
451 Connection must go over TLS. If server rejects it, plain TCP is not attempted.
452 Server certificate is not validated.
453 verify-ca
454 Connection must go over TLS and server certificate must be valid according to tls.server.caFile.
455 Server host name is not checked against certificate.
456 verify-full
457 Connection must go over TLS and server certificate must be valid according to tls.server.caFile.
458 Server host name must match certificate information.
459 '';
460 };
461 certFile = mkOption {
462 type = types.path;
463 example = "/secrets/pgbouncer_server.key";
464 description = "Certificate for private key. PostgreSQL server can validate it.";
465 };
466 keyFile = mkOption {
467 type = types.path;
468 example = "/secrets/pgbouncer_server.crt";
469 description = "Private key for PgBouncer to authenticate against PostgreSQL server.";
470 };
471 caFile = mkOption {
472 type = types.path;
473 example = "/secrets/pgbouncer_server.crt";
474 description = "Root certificate file to validate PostgreSQL server certificates.";
475 };
476 };
477 });
478 default = null;
479 description = ''
480 <https://www.pgbouncer.org/config.html#tls-settings>
481 '';
482 };
483
484 # Log settings
485 syslog = mkOption {
486 type = types.nullOr (types.submodule {
487 options = {
488 enable = mkOption {
489 type = types.bool;
490 default = false;
491 description = ''
492 Toggles syslog on/off.
493 '';
494 };
495 syslogIdent = mkOption {
496 type = types.str;
497 default = "pgbouncer";
498 description = ''
499 Under what name to send logs to syslog.
500 '';
501 };
502 syslogFacility = mkOption {
503 type = types.enum [ "auth" "authpriv" "daemon" "user" "local0" "local1" "local2" "local3" "local4" "local5" "local6" "local7" ];
504 default = "daemon";
505 description = ''
506 Under what facility to send logs to syslog.
507 '';
508 };
509 };
510 });
511 default = null;
512 description = ''
513 <https://www.pgbouncer.org/config.html#log-settings>
514 '';
515 };
516
517 verbose = lib.mkOption {
518 type = lib.types.int;
519 default = 0;
520 description = ''
521 Increase verbosity. Mirrors the “-v” switch on the command line.
522 '';
523 };
524
525 # Console access control
526 adminUsers = mkOption {
527 type = types.nullOr types.commas;
528 default = null;
529 description = ''
530 Comma-separated list of database users that are allowed to connect and run all commands on the console.
531 Ignored when authType is any, in which case any user name is allowed in as admin.
532 '';
533 };
534
535 statsUsers = mkOption {
536 type = types.nullOr types.commas;
537 default = null;
538 description = ''
539 Comma-separated list of database users that are allowed to connect and run read-only queries on the console.
540 That means all SHOW commands except SHOW FDS.
541 '';
542 };
543
544 # Linux settings
545 openFilesLimit = lib.mkOption {
546 type = lib.types.int;
547 default = 65536;
548 description = ''
549 Maximum number of open files.
550 '';
551 };
552
553 user = mkOption {
554 type = types.str;
555 default = "pgbouncer";
556 description = ''
557 The user pgbouncer is run as.
558 '';
559 };
560
561 group = mkOption {
562 type = types.str;
563 default = "pgbouncer";
564 description = ''
565 The group pgbouncer is run as.
566 '';
567 };
568
569 homeDir = mkOption {
570 type = types.path;
571 default = "/var/lib/pgbouncer";
572 description = ''
573 Specifies the home directory.
574 '';
575 };
576
577 # Extra settings
578 extraConfig = mkOption {
579 type = types.lines;
580 description = ''
581 Any additional text to be appended to config.ini
582 <https://www.pgbouncer.org/config.html>.
583 '';
584 default = "";
585 };
586 };
587
588 config = mkIf cfg.enable {
589 users.groups.${cfg.group} = { };
590 users.users.${cfg.user} = {
591 description = "PgBouncer service user";
592 group = cfg.group;
593 home = cfg.homeDir;
594 createHome = true;
595 isSystemUser = true;
596 };
597
598 systemd.services.pgbouncer = {
599 description = "PgBouncer - PostgreSQL connection pooler";
600 wants = [ "network-online.target" ] ++ lib.optional config.services.postgresql.enable "postgresql.service";
601 after = [ "network-online.target" ] ++ lib.optional config.services.postgresql.enable "postgresql.service";
602 wantedBy = [ "multi-user.target" ];
603 serviceConfig = {
604 Type = "notify";
605 User = cfg.user;
606 Group = cfg.group;
607 ExecStart = "${lib.getExe pkgs.pgbouncer} ${confFile}";
608 ExecReload = "${pkgs.coreutils}/bin/kill -SIGHUP $MAINPID";
609 RuntimeDirectory = "pgbouncer";
610 LimitNOFILE = cfg.openFilesLimit;
611 };
612 };
613
614 networking.firewall.allowedTCPPorts = optional cfg.openFirewall cfg.listenPort;
615
616 };
617
618 meta.maintainers = [ maintainers._1000101 ];
619
620}