1PGBOUNCER.INI(5) Databases PGBOUNCER.INI(5)
2
3
4
6 pgbouncer.ini - configuration file for pgbouncer
7
9 The configuration file is in "ini" format. Section names are between
10 "[" and "]". Lines starting with ";" or "#" are taken as comments and
11 ignored. The characters ";" and "#" are not recognized as special when
12 they appear later in the line.
13
15 logfile
16 Specifies the log file. The log file is kept open, so after rotation
17 kill -HUP or on console RELOAD; should be done. On Windows, the ser‐
18 vice must be stopped and started.
19
20 Default: not set
21
22 pidfile
23 Specifies the PID file. Without pidfile set, daemonization is not al‐
24 lowed.
25
26 Default: not set
27
28 listen_addr
29 Specifies a list of addresses where to listen for TCP connections. You
30 may also use * meaning "listen on all addresses". When not set, only
31 Unix socket connections are accepted.
32
33 Addresses can be specified numerically (IPv4/IPv6) or by name.
34
35 Default: not set
36
37 listen_port
38 Which port to listen on. Applies to both TCP and Unix sockets.
39
40 Default: 6432
41
42 unix_socket_dir
43 Specifies location for Unix sockets. Applies to both listening socket
44 and server connections. If set to an empty string, Unix sockets are
45 disabled. Required for online reboot (-R) to work. Not supported on
46 Windows.
47
48 Default: /tmp
49
50 unix_socket_mode
51 File system mode for Unix socket.
52
53 Default: 0777
54
55 unix_socket_group
56 Group name to use for Unix socket.
57
58 Default: not set
59
60 user
61 If set, specifies the Unix user to change to after startup. Works only
62 if PgBouncer is started as root or if it's already running as given us‐
63 er. Not supported on Windows.
64
65 Default: not set
66
67 auth_file
68 The name of the file to load user names and passwords from. See sec‐
69 tion Authentication file format (#authentication-file-format) below
70 about details.
71
72 Default: not set
73
74 auth_hba_file
75 HBA configuration file to use when auth_type is hba.
76
77 Default: not set
78
79 auth_type
80 How to authenticate users.
81
82 pam PAM is used to authenticate users, auth_file is ignored. This
83 method is not compatible with databases using the auth_user op‐
84 tion. The service name reported to PAM is "pgbouncer". pam is
85 not supported in the HBA configuration file.
86
87 hba The actual authentication type is loaded from auth_hba_file.
88 This allows different authentication methods for different ac‐
89 cess paths, for example: connections over Unix socket use the
90 peer auth method, connections over TCP must use TLS.
91
92 cert Client must connect over TLS connection with a valid client cer‐
93 tificate. The user name is then taken from the CommonName field
94 from the certificate.
95
96 md5 Use MD5-based password check. This is the default authentica‐
97 tion method. auth_file may contain both MD5-encrypted and
98 plain-text passwords. If md5 is configured and a user has a
99 SCRAM secret, then SCRAM authentication is used automatically
100 instead.
101
102 scram-sha-256
103 Use password check with SCRAM-SHA-256. auth_file has to contain
104 SCRAM secrets or plain-text passwords. Note that SCRAM secrets
105 can only be used for verifying the password of a client but not
106 for logging into a server. To be able to use SCRAM on server
107 connections, use plain-text passwords.
108
109 plain The clear-text password is sent over the wire. Deprecated.
110
111 trust No authentication is done. The user name must still exist in
112 auth_file.
113
114 any Like the trust method, but the user name given is ignored. Re‐
115 quires that all databases are configured to log in as a specific
116 user. Additionally, the console database allows any user to log
117 in as admin.
118
119 auth_query
120 Query to load user's password from database.
121
122 Direct access to pg_shadow requires admin rights. It's preferable to
123 use a non-superuser that calls a SECURITY DEFINER function instead.
124
125 Note that the query is run inside the target database. So if a func‐
126 tion is used, it needs to be installed into each database.
127
128 Default: SELECT usename, passwd FROM pg_shadow WHERE usename=$1
129
130 auth_user
131 If auth_user is set, then any user not specified in auth_file will be
132 queried through the auth_query query from pg_shadow in the database,
133 using auth_user. The password of auth_user will be taken from
134 auth_file.
135
136 Direct access to pg_shadow requires admin rights. It's preferable to
137 use a non-superuser that calls a SECURITY DEFINER function instead.
138
139 Default: not set
140
141 pool_mode
142 Specifies when a server connection can be reused by other clients.
143
144 session
145 Server is released back to pool after client disconnects. De‐
146 fault.
147
148 transaction
149 Server is released back to pool after transaction finishes.
150
151 statement
152 Server is released back to pool after query finishes. Transac‐
153 tions spanning multiple statements are disallowed in this mode.
154
155 max_client_conn
156 Maximum number of client connections allowed. When increased then the
157 file descriptor limits should also be increased. Note that the actual
158 number of file descriptors used is more than max_client_conn. The the‐
159 oretical maximum used is:
160
161 max_client_conn + (max pool_size * total databases * total users)
162
163 if each user connects under its own user name to the server. If a
164 database user is specified in the connection string (all users connect
165 under the same user name), the theoretical maximum is:
166
167 max_client_conn + (max pool_size * total databases)
168
169 The theoretical maximum should be never reached, unless somebody delib‐
170 erately crafts a special load for it. Still, it means you should set
171 the number of file descriptors to a safely high number.
172
173 Search for ulimit in your favorite shell man page. Note: ulimit does
174 not apply in a Windows environment.
175
176 Default: 100
177
178 default_pool_size
179 How many server connections to allow per user/database pair. Can be
180 overridden in the per-database configuration.
181
182 Default: 20
183
184 min_pool_size
185 Add more server connections to pool if below this number. Improves be‐
186 havior when usual load comes suddenly back after period of total inac‐
187 tivity. The value is effectively capped at the pool size.
188
189 Default: 0 (disabled)
190
191 reserve_pool_size
192 How many additional connections to allow to a pool (see re‐
193 serve_pool_timeout). 0 disables.
194
195 Default: 0 (disabled)
196
197 reserve_pool_timeout
198 If a client has not been serviced in this many seconds, use additional
199 connections from the reserve pool. 0 disables.
200
201 Default: 5.0
202
203 max_db_connections
204 Do not allow more than this many connections per database (regardless
205 of pool, i.e. user). It should be noted that when you hit the limit,
206 closing a client connection to one pool will not immediately allow a
207 server connection to be established for another pool, because the serv‐
208 er connection for the first pool is still open. Once the server con‐
209 nection closes (due to idle timeout), a new server connection will im‐
210 mediately be opened for the waiting pool.
211
212 Default: unlimited
213
214 max_user_connections
215 Do not allow more than this many connections per-user (regardless of
216 pool, i.e. user). It should be noted that when you hit the limit,
217 closing a client connection to one pool will not immediately allow a
218 server connection to be established for another pool, because the serv‐
219 er connection for the first pool is still open. Once the server con‐
220 nection closes (due to idle timeout), a new server connection will im‐
221 mediately be opened for the waiting pool.
222
223 server_round_robin
224 By default, PgBouncer reuses server connections in LIFO (last-in,
225 first-out) manner, so that few connections get the most load. This
226 gives best performance if you have a single server serving a database.
227 But if there is TCP round-robin behind a database IP address, then it
228 is better if PgBouncer also uses connections in that manner, thus
229 achieving uniform load.
230
231 Default: 0
232
233 ignore_startup_parameters
234 By default, PgBouncer allows only parameters it can keep track of in
235 startup packets: client_encoding, datestyle, timezone and standard_con‐
236 forming_strings. All others parameters will raise an error. To allow
237 others parameters, they can be specified here, so that PgBouncer knows
238 that they are handled by the admin and it can ignore them.
239
240 Default: empty
241
242 disable_pqexec
243 Disable Simple Query protocol (PQexec). Unlike Extended Query proto‐
244 col, Simple Query allows multiple queries in one packet, which allows
245 some classes of SQL-injection attacks. Disabling it can improve secu‐
246 rity. Obviously this means only clients that exclusively use the Ex‐
247 tended Query protocol will stay working.
248
249 Default: 0
250
251 application_name_add_host
252 Add the client host address and port to the application name setting
253 set on connection start. This helps in identifying the source of bad
254 queries etc. This logic applies only on start of connection. If ap‐
255 plication_name is later changed with SET, PgBouncer does not change it
256 again.
257
258 Default: 0
259
260 conffile
261 Show location of current config file. Changing it will make PgBouncer
262 use another config file for next RELOAD / SIGHUP.
263
264 Default: file from command line
265
266 service_name
267 Used on win32 service registration.
268
269 Default: pgbouncer
270
271 job_name
272 Alias for service_name.
273
274 stats_period
275 Sets how often the averages shown in various SHOW commands are updated
276 and how often aggregated statistics are written to the log (but see
277 log_stats). [seconds]
278
279 Default: 60
280
282 syslog
283 Toggles syslog on/off. On Windows, the event log is used instead.
284
285 Default: 0
286
287 syslog_ident
288 Under what name to send logs to syslog.
289
290 Default: pgbouncer (program name)
291
292 syslog_facility
293 Under what facility to send logs to syslog. Possibilities: auth, auth‐
294 priv, daemon, user, local0-7.
295
296 Default: daemon
297
298 log_connections
299 Log successful logins.
300
301 Default: 1
302
303 log_disconnections
304 Log disconnections with reasons.
305
306 Default: 1
307
308 log_pooler_errors
309 Log error messages the pooler sends to clients.
310
311 Default: 1
312
313 log_stats
314 Write aggregated statistics into the log, every stats_period. This can
315 be disabled if external monitoring tools are used to grab the same data
316 from SHOW commands.
317
318 Default: 1
319
320 verbose
321 Increase verbosity. Mirrors the "-v" switch on the command line. Us‐
322 ing "-v -v" on the command line is the same as verbose=2.
323
324 Default: 0
325
327 admin_users
328 Comma-separated list of database users that are allowed to connect and
329 run all commands on the console. Ignored when auth_type is any, in
330 which case any user name is allowed in as admin.
331
332 Default: empty
333
334 stats_users
335 Comma-separated list of database users that are allowed to connect and
336 run read-only queries on the console. That means all SHOW commands ex‐
337 cept SHOW FDS.
338
339 Default: empty
340
342 server_reset_query
343 Query sent to server on connection release, before making it available
344 to other clients. At that moment no transaction is in progress so it
345 should not include ABORT or ROLLBACK.
346
347 The query is supposed to clean any changes made to the database session
348 so that the next client gets the connection in a well-defined state.
349 The default is DISCARD ALL which cleans everything, but that leaves the
350 next client no pre-cached state. It can be made lighter, e.g. DEALLO‐
351 CATE ALL to just drop prepared statements, if the application does not
352 break when some state is kept around.
353
354 When transaction pooling is used, the server_reset_query is not used,
355 as clients must not use any session-based features as each transaction
356 ends up in a different connection and thus gets a different session
357 state.
358
359 Default: DISCARD ALL
360
361 server_reset_query_always
362 Whether server_reset_query should be run in all pooling modes. When
363 this setting is off (default), the server_reset_query will be run only
364 in pools that are in sessions-pooling mode. Connections in transac‐
365 tion-pooling mode should not have any need for a reset query.
366
367 This setting is for working around broken setups that run applications
368 that use session features over a transaction-pooled PgBouncer. It
369 changes non-deterministic breakage to deterministic breakage: Clients
370 always lose their state after each transaction.
371
372 Default: 0
373
374 server_check_delay
375 How long to keep released connections available for immediate re-use,
376 without running sanity-check queries on it. If 0 then the query is ran
377 always.
378
379 Default: 30.0
380
381 server_check_query
382 Simple do-nothing query to check if the server connection is alive.
383
384 If an empty string, then sanity checking is disabled.
385
386 Default: SELECT 1;
387
388 server_fast_close
389 Disconnect a server in session pooling mode immediately or after the
390 end of the current transaction if it is in "close_needed" mode (set by
391 RECONNECT, RELOAD that changes connection settings, or DNS change),
392 rather than waiting for the session end. In statement or transaction
393 pooling mode, this has no effect since that is the default behavior
394 there.
395
396 If because of this setting a server connection is closed before the end
397 of the client session, the client connection is also closed. This en‐
398 sures that the client notices that the session has been interrupted.
399
400 This setting makes connection configuration changes take effect sooner
401 if session pooling and long-running sessions are used. The downside is
402 that client sessions are liable to be interrupted by a configuration
403 change, so client applications will need logic to reconnect and
404 reestablish session state. But note that no transactions will be lost,
405 because running transactions are not interrupted, only idle sessions.
406
407 Default: 0
408
409 server_lifetime
410 The pooler will close an unused server connection that has been con‐
411 nected longer than this. Setting it to 0 means the connection is to be
412 used only once, then closed. [seconds]
413
414 Default: 3600.0
415
416 server_idle_timeout
417 If a server connection has been idle more than this many seconds it
418 will be dropped. If 0 then timeout is disabled. [seconds]
419
420 Default: 600.0
421
422 server_connect_timeout
423 If connection and login won't finish in this amount of time, the con‐
424 nection will be closed. [seconds]
425
426 Default: 15.0
427
428 server_login_retry
429 If login failed, because of failure from connect() or authentication
430 that pooler waits this much before retrying to connect. [seconds]
431
432 Default: 15.0
433
434 client_login_timeout
435 If a client connects but does not manage to log in in this amount of
436 time, it will be disconnected. Mainly needed to avoid dead connections
437 stalling SUSPEND and thus online restart. [seconds]
438
439 Default: 60.0
440
441 autodb_idle_timeout
442 If the automatically created (via "*") database pools have been unused
443 this many seconds, they are freed. The negative aspect of that is that
444 their statistics are also forgotten. [seconds]
445
446 Default: 3600.0
447
448 dns_max_ttl
449 How long the DNS lookups can be cached. If a DNS lookup returns sever‐
450 al answers, PgBouncer will robin-between them in the meantime. The ac‐
451 tual DNS TTL is ignored. [seconds]
452
453 Default: 15.0
454
455 dns_nxdomain_ttl
456 How long error and NXDOMAIN DNS lookups can be cached. [seconds]
457
458 Default: 15.0
459
460 dns_zone_check_period
461 Period to check if a zone serial has changed.
462
463 PgBouncer can collect DNS zones from host names (everything after first
464 dot) and then periodically check if the zone serial changes. If it no‐
465 tices changes, all host names under that zone are looked up again. If
466 any host IP changes, its connections are invalidated.
467
468 Works only with UDNS and c-ares backends (--with-udns or --with-cares
469 to configure).
470
471 Default: 0.0 (disabled)
472
473 resolv_conf
474 The location of a custom resolv.conf file. This is to allow specifying
475 custom DNS servers and perhaps other name resolution options, indepen‐
476 dent of the global operating system configuration.
477
478 Requires evdns (>= 2.0.3) or c-ares (>= 1.15.0) backend.
479
480 The parsing of the file is done by the DNS backend library, not Pg‐
481 Bouncer, so see the library's documentation for details on allowed syn‐
482 tax and directives.
483
484 Default: empty (use operating system defaults)
485
487 client_tls_sslmode
488 TLS mode to use for connections from clients. TLS connections are dis‐
489 abled by default. When enabled, client_tls_key_file and
490 client_tls_cert_file must be also configured to set up the key and cer‐
491 tificate PgBouncer uses to accept client connections.
492
493 disable
494 Plain TCP. If client requests TLS, it's ignored. Default.
495
496 allow If client requests TLS, it is used. If not, plain TCP is used.
497 If the client presents a client certificate, it is not validat‐
498 ed.
499
500 prefer Same as allow.
501
502 require
503 Client must use TLS. If not, the client connection is rejected.
504 If the client presents a client certificate, it is not validat‐
505 ed.
506
507 verify-ca
508 Client must use TLS with valid client certificate.
509
510 verify-full
511 Same as verify-ca.
512
513 client_tls_key_file
514 Private key for PgBouncer to accept client connections.
515
516 Default: not set
517
518 client_tls_cert_file
519 Certificate for private key. Clients can validate it.
520
521 Default: not set
522
523 client_tls_ca_file
524 Root certificate file to validate client certificates.
525
526 Default: not set
527
528 client_tls_protocols
529 Which TLS protocol versions are allowed. Allowed values: tlsv1.0,
530 tlsv1.1, tlsv1.2, tlsv1.3. Shortcuts: all
531 (tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3), secure (tlsv1.2,tlsv1.3), legacy
532 (all).
533
534 Default: all
535
536 client_tls_ciphers
537 Default: fast
538
539 client_tls_ecdhcurve
540 Elliptic Curve name to use for ECDH key exchanges.
541
542 Allowed values: none (DH is disabled), auto (256-bit ECDH), curve name.
543
544 Default: auto
545
546 client_tls_dheparams
547 DHE key exchange type.
548
549 Allowed values: none (DH is disabled), auto (2048-bit DH), legacy
550 (1024-bit DH).
551
552 Default: auto
553
554 server_tls_sslmode
555 TLS mode to use for connections to PostgreSQL servers. TLS connections
556 are disabled by default.
557
558 disable
559 Plain TCP. TCP is not even requested from the server. Default.
560
561 allow FIXME: if server rejects plain, try TLS?
562
563 prefer TLS connection is always requested first from PostgreSQL, when
564 refused connection will be established over plain TCP. Server
565 certificate is not validated.
566
567 require
568 Connection must go over TLS. If server rejects it, plain TCP is
569 not attempted. Server certificate is not validated.
570
571 verify-ca
572 Connection must go over TLS and server certificate must be valid
573 according to server_tls_ca_file. Server host name is not
574 checked against certificate.
575
576 verify-full
577 Connection must go over TLS and server certificate must be valid
578 according to server_tls_ca_file. Server host name must match
579 certificate information.
580
581 server_tls_ca_file
582 Root certificate file to validate PostgreSQL server certificates.
583
584 Default: not set
585
586 server_tls_key_file
587 Private key for PgBouncer to authenticate against PostgreSQL server.
588
589 Default: not set
590
591 server_tls_cert_file
592 Certificate for private key. PostgreSQL server can validate it.
593
594 Default: not set
595
596 server_tls_protocols
597 Which TLS protocol versions are allowed. Allowed values: tlsv1.0,
598 tlsv1.1, tlsv1.2, tlsv1.3. Shortcuts: all
599 (tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3), secure (tlsv1.2,tlsv1.3), legacy
600 (all).
601
602 Default: all
603
604 server_tls_ciphers
605 Default: fast
606
608 Setting the following timeouts can cause unexpected errors.
609
610 query_timeout
611 Queries running longer than that are canceled. This should be used on‐
612 ly with slightly smaller server-side statement_timeout, to apply only
613 for network problems. [seconds]
614
615 Default: 0.0 (disabled)
616
617 query_wait_timeout
618 Maximum time queries are allowed to spend waiting for execution. If
619 the query is not assigned to a server during that time, the client is
620 disconnected. This is used to prevent unresponsive servers from grab‐
621 bing up connections. [seconds]
622
623 It also helps when the server is down or database rejects connections
624 for any reason. If this is disabled, clients will be queued indefi‐
625 nitely.
626
627 Default: 120
628
629 client_idle_timeout
630 Client connections idling longer than this many seconds are closed.
631 This should be larger than the client-side connection lifetime set‐
632 tings, and only used for network problems. [seconds]
633
634 Default: 0.0 (disabled)
635
636 idle_transaction_timeout
637 If a client has been in "idle in transaction" state longer, it will be
638 disconnected. [seconds]
639
640 Default: 0.0 (disabled)
641
642 suspend_timeout
643 How many seconds to wait for buffer flush during SUSPEND or reboot
644 (-R). A connection is dropped if the flush does not succeed.
645
646 Default: 10
647
649 pkt_buf
650 Internal buffer size for packets. Affects size of TCP packets sent and
651 general memory usage. Actual libpq packets can be larger than this, so
652 no need to set it large.
653
654 Default: 4096
655
656 max_packet_size
657 Maximum size for PostgreSQL packets that PgBouncer allows through. One
658 packet is either one query or one result set row. Full result set can
659 be larger.
660
661 Default: 2147483647
662
663 listen_backlog
664 Backlog argument for listen(2). Determines how many new unanswered
665 connection attempts are kept in queue. When the queue is full, further
666 new connections are dropped.
667
668 Default: 128
669
670 sbuf_loopcnt
671 How many times to process data on one connection, before proceeding.
672 Without this limit, one connection with a big result set can stall Pg‐
673 Bouncer for a long time. One loop processes one pkt_buf amount of da‐
674 ta. 0 means no limit.
675
676 Default: 5
677
678 so_reuseport
679 Specifies whether to set the socket option SO_REUSEPORT on TCP listen‐
680 ing sockets. On some operating systems, this allows running multiple
681 PgBouncer instances on the same host listening on the same port and
682 having the kernel distribute the connections automatically. This op‐
683 tion is a way to get PgBouncer to use more CPU cores. (PgBouncer is
684 single-threaded and uses one CPU core per instance.)
685
686 The behavior in detail depends on the operating system kernel. As of
687 this writing, this setting has the desired effect on (sufficiently re‐
688 cent versions of) Linux, DragonFlyBSD, and FreeBSD. (On FreeBSD, it
689 applies the socket option SO_REUSEPORT_LB instead.) Some other operat‐
690 ing systems support the socket option but it won't have the desired ef‐
691 fect: It will allow multiple processes to bind to the same port but on‐
692 ly one of them will get the connections. See your operating system's
693 setsockopt() documentation for details.
694
695 On systems that don't support the socket option at all, turning this
696 setting on will result in an error.
697
698 Each PgBouncer instance on the same host needs different settings for
699 at least unix_socket_dir and pidfile, as well as logfile if that is
700 used. Also note that if you make use of this option, you can no longer
701 connect to a specific PgBouncer instance via TCP/IP, which might have
702 implications for monitoring and metrics collection.
703
704 Default: 0
705
706 tcp_defer_accept
707 For details on this and other TCP options, please see man 7 tcp.
708
709 Default: 45 on Linux, otherwise 0
710
711 tcp_socket_buffer
712 Default: not set
713
714 tcp_keepalive
715 Turns on basic keepalive with OS defaults.
716
717 On Linux, the system defaults are tcp_keepidle=7200, tcp_keepintvl=75,
718 tcp_keepcnt=9. They are probably similar on other operating systems.
719
720 Default: 1
721
722 tcp_keepcnt
723 Default: not set
724
725 tcp_keepidle
726 Default: not set
727
728 tcp_keepintvl
729 Default: not set
730
732 This contains key=value pairs where the key will be taken as a database
733 name and the value as a libpq connection string style list of key=value
734 pairs. Not all features known from libpq can be used (service=, .pg‐
735 pass), since the actual libpq is not used.
736
737 The database name can contain characters _0-9A-Za-z without quoting.
738 Names that contain other characters need to be quoted with standard SQL
739 identifier quoting: double quotes, with "" for a single instance of a
740 double quote.
741
742 "*" acts as a fallback database: if the exact name does not exist, its
743 value is taken as connection string for requested database. Such auto‐
744 matically created database entries are cleaned up if they stay idle
745 longer than the time specified by the autodb_idle_timeout parameter.
746
747 dbname
748 Destination database name.
749
750 Default: same as client-side database name
751
752 host
753 Host name or IP address to connect to. Host names are resolved at con‐
754 nection time, the result is cached per dns_max_ttl parameter. When a
755 host name's resolution changes, existing server connections are auto‐
756 matically closed when they are released (according to the pooling
757 mode), and new server connections immediately use the new resolution.
758 If DNS returns several results, they are used in round-robin manner.
759
760 Default: not set, meaning to use a Unix socket
761
762 port
763 Default: 5432
764
765 user
766 If user= is set, all connections to the destination database will be
767 done with the specified user, meaning that there will be only one pool
768 for this database.
769
770 Otherwise, PgBouncer logs into the destination database with the client
771 user name, meaning that there will be one pool per user.
772
773 password
774 The length for password is limited to 160 characters maximum.
775
776 If no password is specified here, the password from the auth_file or
777 auth_query will be used.
778
779 auth_user
780 Override of the global auth_user setting, if specified.
781
782 pool_size
783 Set the maximum size of pools for this database. If not set, the de‐
784 fault_pool_size is used.
785
786 reserve_pool
787 Set additional connections for this database. If not set, re‐
788 serve_pool_size is used.
789
790 connect_query
791 Query to be executed after a connection is established, but before al‐
792 lowing the connection to be used by any clients. If the query raises
793 errors, they are logged but ignored otherwise.
794
795 pool_mode
796 Set the pool mode specific to this database. If not set, the default
797 pool_mode is used.
798
799 max_db_connections
800 Configure a database-wide maximum (i.e. all pools within the database
801 will not have more than this many server connections).
802
803 client_encoding
804 Ask specific client_encoding from server.
805
806 datestyle
807 Ask specific datestyle from server.
808
809 timezone
810 Ask specific timezone from server.
811
813 This contains key=value pairs where the key will be taken as a user
814 name and the value as a libpq connection string style list of key=value
815 pairs of configuration settings specific for this user. Only a few
816 settings are available here.
817
818 pool_mode
819 Set the pool mode to be used for all connections from this user. If
820 not set, the database or default pool_mode is used.
821
822 max_user_connections
823 Configure a maximum for the user (i.e. all pools with the user will
824 not have more than this many server connections).
825
827 The PgBouncer configuration file can contain include directives, which
828 specify another configuration file to read and process. This allows
829 splitting the configuration file into physically separate parts. The
830 include directives look like this:
831
832 %include filename
833
834 If the file name is not absolute path it is taken as relative to cur‐
835 rent working directory.
836
838 PgBouncer needs its own user database. The users are loaded from a
839 text file in the following format:
840
841 "username1" "password" ...
842 "username2" "md5abcdef012342345" ...
843 "username2" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"
844
845 There should be at least 2 fields, surrounded by double quotes. The
846 first field is the user name and the second is either a plain-text, a
847 MD5-hashed password, or a SCRAM secret. PgBouncer ignores the rest of
848 the line.
849
850 PostgreSQL MD5-hashed password format:
851
852 "md5" + md5(password + username)
853
854 So user admin with password 1234 will have MD5-hashed password
855 md545f2603610af569b6155c45067268c6b.
856
857 PostgreSQL SCRAM secret format:
858
859 SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>
860
861 See the PostgreSQL documentation and RFC 5803 for details on this.
862
863 The authentication file can be written by hand, but it's also useful to
864 generate it from some other list of users and passwords. See
865 ./etc/mkauth.py for a sample script to generate the authentication file
866 from the pg_shadow system table.
867
869 It follows the format of the PostgreSQL pg_hba.conf file (see
870 <https://www.postgresql.org/docs/current/auth-pg-hba-conf.html>).
871
872 · Supported record types: local, host, hostssl, hostnossl.
873
874 · Database field: Supports all, sameuser, @file, multiple names. Not
875 supported: replication, samerole, samegroup.
876
877 · User name field: Supports all, @file, multiple names. Not supported:
878 +groupname.
879
880 · Address field: Supported IPv4, IPv6. Not supported: DNS names, do‐
881 main prefixes.
882
883 · Auth-method field: Only methods supported by PgBouncer's auth_type
884 are supported, except any and pam, which only work globally. User
885 name map (map=) parameter is not supported.
886
888 Minimal config:
889
890 [databases]
891 template1 = host=127.0.0.1 dbname=template1 auth_user=someuser
892
893 [pgbouncer]
894 pool_mode = session
895 listen_port = 6432
896 listen_addr = 127.0.0.1
897 auth_type = md5
898 auth_file = users.txt
899 logfile = pgbouncer.log
900 pidfile = pgbouncer.pid
901 admin_users = someuser
902 stats_users = stat_collector
903
904 Database defaults:
905
906 [databases]
907
908 ; foodb over Unix socket
909 foodb =
910
911 ; redirect bardb to bazdb on localhost
912 bardb = host=127.0.0.1 dbname=bazdb
913
914 ; access to destination database will go with single user
915 forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO
916
917 Example of a secure function for auth_query:
918
919 CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
920 RETURNS record AS $$
921 BEGIN
922 SELECT usename, passwd FROM pg_catalog.pg_shadow
923 WHERE usename = i_username INTO uname, phash;
924 RETURN;
925 END;
926 $$ LANGUAGE plpgsql SECURITY DEFINER;
927 REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
928 GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;
929
931 pgbouncer(1) - man page for general usage, console commands
932
933 <https://www.pgbouncer.org/>
934
935
936
9371.12.0 PGBOUNCER.INI(5)