1PGBOUNCER.INI(5)                   Databases                  PGBOUNCER.INI(5)
2
3
4

NAME

6       pgbouncer.ini - configuration file for pgbouncer
7

DESCRIPTION

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

GENERIC SETTINGS

15   logfile
16       Specifies the log file.  For daemonization (-d), either this or  syslog
17       need to be set.
18
19       The  log  file  is kept open, so after rotation kill -HUP or on console
20       RELOAD; should be done.  On Windows, the service must  be  stopped  and
21       started.
22
23       Note  that  setting  logfile  does  not  by  itself turn off logging to
24       stderr.  Use the command-line option -q or -d for that.
25
26       Default: not set
27
28   pidfile
29       Specifies the PID file.  Without pidfile set, daemonization (-d) is not
30       allowed.
31
32       Default: not set
33
34   listen_addr
35       Specifies a list of addresses where to listen for TCP connections.  You
36       may also use * meaning “listen on all addresses”.  When not  set,  only
37       Unix socket connections are accepted.
38
39       Addresses can be specified numerically (IPv4/IPv6) or by name.
40
41       Default: not set
42
43   listen_port
44       Which port to listen on.  Applies to both TCP and Unix sockets.
45
46       Default: 6432
47
48   unix_socket_dir
49       Specifies  location for Unix sockets.  Applies to both listening socket
50       and server connections.  If set to an empty string,  Unix  sockets  are
51       disabled.   A  value that starts with @ specifies that a Unix socket in
52       the abstract namespace should be created (currently supported on  Linux
53       and Windows).
54
55       For  online  reboot (-R) to work, a Unix socket needs to be configured,
56       and it needs to be in the file-system namespace.
57
58       Default: /tmp (empty on Windows)
59
60   unix_socket_mode
61       File system mode for Unix socket.  Ignored for sockets in the  abstract
62       namespace.  Not supported on Windows.
63
64       Default: 0777
65
66   unix_socket_group
67       Group name to use for Unix socket.  Ignored for sockets in the abstract
68       namespace.  Not supported on Windows.
69
70       Default: not set
71
72   user
73       If set, specifies the Unix user to change to after startup.  Works only
74       if PgBouncer is started as root or if it’s already running as given us‐
75       er.  Not supported on Windows.
76
77       Default: not set
78
79   auth_file
80       The name of the file to load user names and passwords from.   See  sec‐
81       tion Authentication file format below about details.
82
83       Default: not set
84
85   auth_hba_file
86       HBA configuration file to use when auth_type is hba.
87
88       Default: not set
89
90   auth_type
91       How to authenticate users.
92
93       pam    PAM  is  used to authenticate users, auth_file is ignored.  This
94              method is not compatible with databases using the auth_user  op‐
95              tion.   The service name reported to PAM is “pgbouncer”.  pam is
96              not supported in the HBA configuration file.
97
98       hba    The actual authentication type  is  loaded  from  auth_hba_file.
99              This  allows  different authentication methods for different ac‐
100              cess paths, for example: connections over Unix  socket  use  the
101              peer auth method, connections over TCP must use TLS.
102
103       cert   Client must connect over TLS connection with a valid client cer‐
104              tificate.  The user name is then taken from the CommonName field
105              from the certificate.
106
107       md5    Use  MD5-based  password check.  This is the default authentica‐
108              tion method.   auth_file  may  contain  both  MD5-encrypted  and
109              plain-text  passwords.   If  md5  is configured and a user has a
110              SCRAM secret, then SCRAM authentication  is  used  automatically
111              instead.
112
113       scram-sha-256
114              Use password check with SCRAM-SHA-256.  auth_file has to contain
115              SCRAM secrets or plain-text passwords.
116
117       plain  The clear-text password is sent over the wire.  Deprecated.
118
119       trust  No authentication is done.  The user name must  still  exist  in
120              auth_file.
121
122       any    Like  the trust method, but the user name given is ignored.  Re‐
123              quires that all databases are configured to log in as a specific
124              user.  Additionally, the console database allows any user to log
125              in as admin.
126
127   auth_query
128       Query to load user’s password from database.
129
130       Direct access to pg_shadow requires admin rights.  It’s  preferable  to
131       use a non-superuser that calls a SECURITY DEFINER function instead.
132
133       Note  that  the query is run inside the target database.  So if a func‐
134       tion is used, it needs to be installed into each database.
135
136       Default: SELECT usename, passwd FROM pg_shadow WHERE usename=$1
137
138   auth_user
139       If auth_user is set, then any user not specified in auth_file  will  be
140       queried  through  the  auth_query query from pg_shadow in the database,
141       using  auth_user.   The  password  of  auth_user  will  be  taken  from
142       auth_file.   (If the auth_user does not require a password then it does
143       not need to be defined in auth_file.)
144
145       Direct access to pg_shadow requires admin rights.  It’s  preferable  to
146       use a non-superuser that calls a SECURITY DEFINER function instead.
147
148       Default: not set
149
150   pool_mode
151       Specifies when a server connection can be reused by other clients.
152
153       session
154              Server  is  released back to pool after client disconnects.  De‐
155              fault.
156
157       transaction
158              Server is released back to pool after transaction finishes.
159
160       statement
161              Server is released back to pool after query finishes.   Transac‐
162              tions spanning multiple statements are disallowed in this mode.
163
164   max_client_conn
165       Maximum  number of client connections allowed.  When increased then the
166       file descriptor limits should also be increased.  Note that the  actual
167       number of file descriptors used is more than max_client_conn.  The the‐
168       oretical maximum used is:
169
170              max_client_conn + (max pool_size * total databases * total users)
171
172       if each user connects under its own user name  to  the  server.   If  a
173       database  user is specified in the connection string (all users connect
174       under the same user name), the theoretical maximum is:
175
176              max_client_conn + (max pool_size * total databases)
177
178       The theoretical maximum should be never reached, unless somebody delib‐
179       erately  crafts  a special load for it.  Still, it means you should set
180       the number of file descriptors to a safely high number.
181
182       Search for ulimit in your favorite shell man page.  Note:  ulimit  does
183       not apply in a Windows environment.
184
185       Default: 100
186
187   default_pool_size
188       How  many  server  connections to allow per user/database pair.  Can be
189       overridden in the per-database configuration.
190
191       Default: 20
192
193   min_pool_size
194       Add more server connections to pool if below this number.  Improves be‐
195       havior  when usual load comes suddenly back after period of total inac‐
196       tivity.  The value is effectively capped at the pool size.
197
198       Default: 0 (disabled)
199
200   reserve_pool_size
201       How  many  additional  connections  to  allow  to  a  pool   (see   re‐
202       serve_pool_timeout).  0 disables.
203
204       Default: 0 (disabled)
205
206   reserve_pool_timeout
207       If  a client has not been serviced in this many seconds, use additional
208       connections from the reserve pool.  0 disables.
209
210       Default: 5.0
211
212   max_db_connections
213       Do not allow more than this many server connections per  database  (re‐
214       gardless  of  user).   This  considers  the PgBouncer database that the
215       client has connected to, not the PostgreSQL database  of  the  outgoing
216       connection.
217
218       This can also be set per database in the [databases] section.
219
220       Note  that  when  you hit the limit, closing a client connection to one
221       pool will not immediately allow a server connection to  be  established
222       for  another  pool, because the server connection for the first pool is
223       still open.  Once the server connection closes (due to idle timeout), a
224       new server connection will immediately be opened for the waiting pool.
225
226       Default: 0 (unlimited)
227
228   max_user_connections
229       Do  not  allow more than this many server connections per user (regard‐
230       less of database).  This considers the PgBouncer user that is associat‐
231       ed  with a pool, which is either the user specified for the server con‐
232       nection or in absence of that the user the client has connected as.
233
234       This can also be set per user in the [users] section.
235
236       Note that when you hit the limit, closing a client  connection  to  one
237       pool  will  not immediately allow a server connection to be established
238       for another pool, because the server connection for the first  pool  is
239       still open.  Once the server connection closes (due to idle timeout), a
240       new server connection will immediately be opened for the waiting pool.
241
242       Default: 0 (unlimited)
243
244   server_round_robin
245       By default, PgBouncer  reuses  server  connections  in  LIFO  (last-in,
246       first-out)  manner,  so  that  few connections get the most load.  This
247       gives best performance if you have a single server serving a  database.
248       But  if  there is TCP round-robin behind a database IP address, then it
249       is better if PgBouncer also  uses  connections  in  that  manner,  thus
250       achieving uniform load.
251
252       Default: 0
253
254   ignore_startup_parameters
255       By  default,  PgBouncer  allows only parameters it can keep track of in
256       startup packets: client_encoding, datestyle, timezone and standard_con‐
257       forming_strings.   All others parameters will raise an error.  To allow
258       others parameters, they can be specified here, so that PgBouncer  knows
259       that they are handled by the admin and it can ignore them.
260
261       Default: empty
262
263   disable_pqexec
264       Disable  Simple  Query protocol (PQexec).  Unlike Extended Query proto‐
265       col, Simple Query allows multiple queries in one packet,  which  allows
266       some  classes of SQL-injection attacks.  Disabling it can improve secu‐
267       rity.  Obviously this means only clients that exclusively use  the  Ex‐
268       tended Query protocol will stay working.
269
270       Default: 0
271
272   application_name_add_host
273       Add  the  client  host address and port to the application name setting
274       set on connection start.  This helps in identifying the source  of  bad
275       queries  etc.   This logic applies only on start of connection.  If ap‐
276       plication_name is later changed with SET, PgBouncer does not change  it
277       again.
278
279       Default: 0
280
281   conffile
282       Show  location of current config file.  Changing it will make PgBouncer
283       use another config file for next RELOAD / SIGHUP.
284
285       Default: file from command line
286
287   service_name
288       Used on win32 service registration.
289
290       Default: pgbouncer
291
292   job_name
293       Alias for service_name.
294
295   stats_period
296       Sets how often the averages shown in various SHOW commands are  updated
297       and  how  often  aggregated  statistics are written to the log (but see
298       log_stats).  [seconds]
299
300       Default: 60
301

LOG SETTINGS

303   syslog
304       Toggles syslog on/off.  On Windows, the event log is used instead.
305
306       Default: 0
307
308   syslog_ident
309       Under what name to send logs to syslog.
310
311       Default: pgbouncer (program name)
312
313   syslog_facility
314       Under what facility to send logs to syslog.  Possibilities: auth, auth‐
315       priv, daemon, user, local0-7.
316
317       Default: daemon
318
319   log_connections
320       Log successful logins.
321
322       Default: 1
323
324   log_disconnections
325       Log disconnections with reasons.
326
327       Default: 1
328
329   log_pooler_errors
330       Log error messages the pooler sends to clients.
331
332       Default: 1
333
334   log_stats
335       Write aggregated statistics into the log, every stats_period.  This can
336       be disabled if external monitoring tools are used to grab the same data
337       from SHOW commands.
338
339       Default: 1
340
341   verbose
342       Increase  verbosity.  Mirrors the “-v” switch on the command line.  Us‐
343       ing “-v -v” on the command line is the same as verbose=2.
344
345       Default: 0
346

CONSOLE ACCESS CONTROL

348   admin_users
349       Comma-separated list of database users that are allowed to connect  and
350       run  all  commands  on  the console.  Ignored when auth_type is any, in
351       which case any user name is allowed in as admin.
352
353       Default: empty
354
355   stats_users
356       Comma-separated list of database users that are allowed to connect  and
357       run read-only queries on the console.  That means all SHOW commands ex‐
358       cept SHOW FDS.
359
360       Default: empty
361

CONNECTION SANITY CHECKS, TIMEOUTS

363   server_reset_query
364       Query sent to server on connection release, before making it  available
365       to  other  clients.  At that moment no transaction is in progress so it
366       should not include ABORT or ROLLBACK.
367
368       The query is supposed to clean any changes made to the database session
369       so  that  the  next client gets the connection in a well-defined state.
370       The default is DISCARD ALL which cleans everything, but that leaves the
371       next  client no pre-cached state.  It can be made lighter, e.g. DEALLO‐
372       CATE ALL to just drop prepared statements, if the application does  not
373       break when some state is kept around.
374
375       When  transaction  pooling is used, the server_reset_query is not used,
376       as clients must not use any session-based features as each  transaction
377       ends  up  in  a  different connection and thus gets a different session
378       state.
379
380       Default: DISCARD ALL
381
382   server_reset_query_always
383       Whether server_reset_query should be run in all  pooling  modes.   When
384       this  setting is off (default), the server_reset_query will be run only
385       in pools that are in sessions-pooling mode.   Connections  in  transac‐
386       tion-pooling mode should not have any need for a reset query.
387
388       This  setting is for working around broken setups that run applications
389       that use session features  over  a  transaction-pooled  PgBouncer.   It
390       changes  non-deterministic  breakage to deterministic breakage: Clients
391       always lose their state after each transaction.
392
393       Default: 0
394
395   server_check_delay
396       How long to keep released connections available for  immediate  re-use,
397       without running sanity-check queries on it.  If 0 then the query is ran
398       always.
399
400       Default: 30.0
401
402   server_check_query
403       Simple do-nothing query to check if the server connection is alive.
404
405       If an empty string, then sanity checking is disabled.
406
407       Default: SELECT 1;
408
409   server_fast_close
410       Disconnect a server in session pooling mode immediately  or  after  the
411       end  of the current transaction if it is in “close_needed” mode (set by
412       RECONNECT, RELOAD that changes connection  settings,  or  DNS  change),
413       rather  than  waiting for the session end.  In statement or transaction
414       pooling mode, this has no effect since that  is  the  default  behavior
415       there.
416
417       If because of this setting a server connection is closed before the end
418       of the client session, the client connection is also closed.  This  en‐
419       sures that the client notices that the session has been interrupted.
420
421       This  setting makes connection configuration changes take effect sooner
422       if session pooling and long-running sessions are used.  The downside is
423       that  client  sessions  are liable to be interrupted by a configuration
424       change, so  client  applications  will  need  logic  to  reconnect  and
425       reestablish session state.  But note that no transactions will be lost,
426       because running transactions are not interrupted, only idle sessions.
427
428       Default: 0
429
430   server_lifetime
431       The pooler will close an unused server connection that  has  been  con‐
432       nected longer than this.  Setting it to 0 means the connection is to be
433       used only once, then closed.  [seconds]
434
435       Default: 3600.0
436
437   server_idle_timeout
438       If a server connection has been idle more than  this  many  seconds  it
439       will be dropped.  If 0 then timeout is disabled.  [seconds]
440
441       Default: 600.0
442
443   server_connect_timeout
444       If  connection  and login won’t finish in this amount of time, the con‐
445       nection will be closed.  [seconds]
446
447       Default: 15.0
448
449   server_login_retry
450       If login failed, because of failure from  connect()  or  authentication
451       that pooler waits this much before retrying to connect.  [seconds]
452
453       Default: 15.0
454
455   client_login_timeout
456       If  a  client  connects but does not manage to log in in this amount of
457       time, it will be disconnected.  Mainly needed to avoid dead connections
458       stalling SUSPEND and thus online restart.  [seconds]
459
460       Default: 60.0
461
462   autodb_idle_timeout
463       If  the automatically created (via "*") database pools have been unused
464       this many seconds, they are freed.  The negative aspect of that is that
465       their statistics are also forgotten.  [seconds]
466
467       Default: 3600.0
468
469   dns_max_ttl
470       How long the DNS lookups can be cached.  If a DNS lookup returns sever‐
471       al answers, PgBouncer will robin-between them in the meantime.  The ac‐
472       tual DNS TTL is ignored.  [seconds]
473
474       Default: 15.0
475
476   dns_nxdomain_ttl
477       How long error and NXDOMAIN DNS lookups can be cached.  [seconds]
478
479       Default: 15.0
480
481   dns_zone_check_period
482       Period to check if a zone serial has changed.
483
484       PgBouncer can collect DNS zones from host names (everything after first
485       dot) and then periodically check if the zone serial changes.  If it no‐
486       tices  changes, all host names under that zone are looked up again.  If
487       any host IP changes, its connections are invalidated.
488
489       Works only with UDNS and c-ares backends (--with-udns  or  --with-cares
490       to configure).
491
492       Default: 0.0 (disabled)
493
494   resolv_conf
495       The location of a custom resolv.conf file.  This is to allow specifying
496       custom DNS servers and perhaps other name resolution options,  indepen‐
497       dent of the global operating system configuration.
498
499       Requires evdns (>= 2.0.3) or c-ares (>= 1.15.0) backend.
500
501       The  parsing  of  the  file is done by the DNS backend library, not Pg‐
502       Bouncer, so see the library’s documentation for details on allowed syn‐
503       tax and directives.
504
505       Default: empty (use operating system defaults)
506

TLS SETTINGS

508   client_tls_sslmode
509       TLS mode to use for connections from clients.  TLS connections are dis‐
510       abled   by   default.    When    enabled,    client_tls_key_file    and
511       client_tls_cert_file must be also configured to set up the key and cer‐
512       tificate PgBouncer uses to accept client connections.
513
514       disable
515              Plain TCP.  If client requests TLS, it’s ignored.  Default.
516
517       allow  If client requests TLS, it is used.  If not, plain TCP is  used.
518              If  the client presents a client certificate, it is not validat‐
519              ed.
520
521       prefer Same as allow.
522
523       require
524              Client must use TLS.  If not, the client connection is rejected.
525              If  the client presents a client certificate, it is not validat‐
526              ed.
527
528       verify-ca
529              Client must use TLS with valid client certificate.
530
531       verify-full
532              Same as verify-ca.
533
534   client_tls_key_file
535       Private key for PgBouncer to accept client connections.
536
537       Default: not set
538
539   client_tls_cert_file
540       Certificate for private key.  Clients can validate it.
541
542       Default: not set
543
544   client_tls_ca_file
545       Root certificate file to validate client certificates.
546
547       Default: not set
548
549   client_tls_protocols
550       Which TLS protocol versions  are  allowed.   Allowed  values:  tlsv1.0,
551       tlsv1.1,         tlsv1.2,        tlsv1.3.         Shortcuts:        all
552       (tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3),  secure  (tlsv1.2,tlsv1.3),   legacy
553       (all).
554
555       Default: secure
556
557   client_tls_ciphers
558       Allowed  TLS  ciphers,  in  OpenSSL syntax.  Shortcuts: default/secure,
559       compat/legacy, insecure/all, normal, fast.
560
561       Only connections using TLS version 1.2 and lower are  affected.   There
562       is  currently  no  setting that controls the cipher choices used by TLS
563       version 1.3 connections.
564
565       Default: fast
566
567   client_tls_ecdhcurve
568       Elliptic Curve name to use for ECDH key exchanges.
569
570       Allowed values: none (DH is disabled), auto (256-bit ECDH), curve name.
571
572       Default: auto
573
574   client_tls_dheparams
575       DHE key exchange type.
576
577       Allowed values: none (DH  is  disabled),  auto  (2048-bit  DH),  legacy
578       (1024-bit DH).
579
580       Default: auto
581
582   server_tls_sslmode
583       TLS mode to use for connections to PostgreSQL servers.  TLS connections
584       are disabled by default.
585
586       disable
587              Plain TCP.  TCP is not even requested from the server.  Default.
588
589       allow  FIXME: if server rejects plain, try TLS?
590
591       prefer TLS connection is always requested first from  PostgreSQL,  when
592              refused  connection  will be established over plain TCP.  Server
593              certificate is not validated.
594
595       require
596              Connection must go over TLS.  If server rejects it, plain TCP is
597              not attempted.  Server certificate is not validated.
598
599       verify-ca
600              Connection must go over TLS and server certificate must be valid
601              according  to  server_tls_ca_file.   Server  host  name  is  not
602              checked against certificate.
603
604       verify-full
605              Connection must go over TLS and server certificate must be valid
606              according to server_tls_ca_file.  Server host  name  must  match
607              certificate information.
608
609   server_tls_ca_file
610       Root certificate file to validate PostgreSQL server certificates.
611
612       Default: not set
613
614   server_tls_key_file
615       Private key for PgBouncer to authenticate against PostgreSQL server.
616
617       Default: not set
618
619   server_tls_cert_file
620       Certificate for private key.  PostgreSQL server can validate it.
621
622       Default: not set
623
624   server_tls_protocols
625       Which  TLS  protocol  versions  are  allowed.  Allowed values: tlsv1.0,
626       tlsv1.1,        tlsv1.2,        tlsv1.3.         Shortcuts:         all
627       (tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3),   secure  (tlsv1.2,tlsv1.3),  legacy
628       (all).
629
630       Default: secure
631
632   server_tls_ciphers
633       Allowed TLS ciphers, in  OpenSSL  syntax.   Shortcuts:  default/secure,
634       compat/legacy, insecure/all, normal, fast.
635
636       Only  connections  using TLS version 1.2 and lower are affected.  There
637       is currently no setting that controls the cipher choices  used  by  TLS
638       version 1.3 connections.
639
640       Default: fast
641

DANGEROUS TIMEOUTS

643       Setting the following timeouts can cause unexpected errors.
644
645   query_timeout
646       Queries running longer than that are canceled.  This should be used on‐
647       ly with slightly smaller server-side statement_timeout, to  apply  only
648       for network problems.  [seconds]
649
650       Default: 0.0 (disabled)
651
652   query_wait_timeout
653       Maximum  time  queries  are allowed to spend waiting for execution.  If
654       the query is not assigned to a server during that time, the  client  is
655       disconnected.   This is used to prevent unresponsive servers from grab‐
656       bing up connections.  [seconds]
657
658       It also helps when the server is down or database  rejects  connections
659       for  any  reason.   If this is disabled, clients will be queued indefi‐
660       nitely.
661
662       Default: 120
663
664   client_idle_timeout
665       Client connections idling longer than this  many  seconds  are  closed.
666       This  should  be  larger  than the client-side connection lifetime set‐
667       tings, and only used for network problems.  [seconds]
668
669       Default: 0.0 (disabled)
670
671   idle_transaction_timeout
672       If a client has been in “idle in transaction” state longer, it will  be
673       disconnected.  [seconds]
674
675       Default: 0.0 (disabled)
676
677   suspend_timeout
678       How  many  seconds  to  wait  for buffer flush during SUSPEND or reboot
679       (-R).  A connection is dropped if the flush does not succeed.
680
681       Default: 10
682

LOW-LEVEL NETWORK SETTINGS

684   pkt_buf
685       Internal buffer size for packets.  Affects size of TCP packets sent and
686       general memory usage.  Actual libpq packets can be larger than this, so
687       no need to set it large.
688
689       Default: 4096
690
691   max_packet_size
692       Maximum size for PostgreSQL packets that PgBouncer allows through.  One
693       packet  is either one query or one result set row.  Full result set can
694       be larger.
695
696       Default: 2147483647
697
698   listen_backlog
699       Backlog argument for listen(2).  Determines  how  many  new  unanswered
700       connection attempts are kept in queue.  When the queue is full, further
701       new connections are dropped.
702
703       Default: 128
704
705   sbuf_loopcnt
706       How many times to process data on one  connection,  before  proceeding.
707       Without  this limit, one connection with a big result set can stall Pg‐
708       Bouncer for a long time.  One loop processes one pkt_buf amount of  da‐
709       ta.  0 means no limit.
710
711       Default: 5
712
713   so_reuseport
714       Specifies  whether to set the socket option SO_REUSEPORT on TCP listen‐
715       ing sockets.  On some operating systems, this allows  running  multiple
716       PgBouncer  instances  on  the  same host listening on the same port and
717       having the kernel distribute the connections automatically.   This  op‐
718       tion  is  a  way to get PgBouncer to use more CPU cores.  (PgBouncer is
719       single-threaded and uses one CPU core per instance.)
720
721       The behavior in detail depends on the operating system kernel.   As  of
722       this  writing, this setting has the desired effect on (sufficiently re‐
723       cent versions of) Linux, DragonFlyBSD, and FreeBSD.   (On  FreeBSD,  it
724       applies  the socket option SO_REUSEPORT_LB instead.) Some other operat‐
725       ing systems support the socket option but it won’t have the desired ef‐
726       fect: It will allow multiple processes to bind to the same port but on‐
727       ly one of them will get the connections.  See your  operating  system’s
728       setsockopt() documentation for details.
729
730       On  systems  that  don’t support the socket option at all, turning this
731       setting on will result in an error.
732
733       Each PgBouncer instance on the same host needs different  settings  for
734       at  least  unix_socket_dir  and  pidfile, as well as logfile if that is
735       used.  Also note that if you make use of this option, you can no longer
736       connect  to  a specific PgBouncer instance via TCP/IP, which might have
737       implications for monitoring and metrics collection.
738
739       Default: 0
740
741   tcp_defer_accept
742       For details on this and other TCP options, please see man 7 tcp.
743
744       Default: 45 on Linux, otherwise 0
745
746   tcp_socket_buffer
747       Default: not set
748
749   tcp_keepalive
750       Turns on basic keepalive with OS defaults.
751
752       On Linux, the system defaults are tcp_keepidle=7200,  tcp_keepintvl=75,
753       tcp_keepcnt=9.  They are probably similar on other operating systems.
754
755       Default: 1
756
757   tcp_keepcnt
758       Default: not set
759
760   tcp_keepidle
761       Default: not set
762
763   tcp_keepintvl
764       Default: not set
765
766   tcp_user_timeout
767       Sets  the  TCP_USER_TIMEOUT  socket option.  This specifies the maximum
768       amount of time in milliseconds that transmitted data may  remain  unac‐
769       knowledged  before the TCP connection is forcibly closed.  If set to 0,
770       then operating system’s default is used.
771
772       This is currently only supported on Linux.
773
774       Default: 0
775

SECTION [DATABASES]

777       This contains key=value pairs where the key will be taken as a database
778       name and the value as a libpq connection string style list of key=value
779       pairs.  Not all features known from libpq can be used  (service=,  .pg‐
780       pass), since the actual libpq is not used.
781
782       The  database  name  can contain characters _0-9A-Za-z without quoting.
783       Names that contain other characters need to be quoted with standard SQL
784       identifier  quoting:  double quotes, with "" for a single instance of a
785       double quote.
786
787       The database name “pgbouncer” is reserved for  the  admin  console  and
788       cannot be used as a key here.
789
790       "*"  acts as a fallback database: If the exact name does not exist, its
791       value is taken as connection string for the  requested  database.   For
792       example, if there is an entry (and no other overriding entries)
793
794              * = host=foo
795
796       then  a connection to PgBouncer specifying a database “bar” will effec‐
797       tively behave as if an entry
798
799              bar = host=foo dbname=bar
800
801       exists  (taking  advantage  of  the  default  for  dbname   being   the
802       client-side database name; see below).
803
804       Such automatically created database entries are cleaned up if they stay
805       idle longer than the time specified by the autodb_idle_timeout  parame‐
806       ter.
807
808   dbname
809       Destination database name.
810
811       Default: same as client-side database name
812
813   host
814       Host name or IP address to connect to.  Host names are resolved at con‐
815       nection time, the result is cached per dns_max_ttl parameter.   When  a
816       host  name’s  resolution changes, existing server connections are auto‐
817       matically closed when they  are  released  (according  to  the  pooling
818       mode),  and  new server connections immediately use the new resolution.
819       If DNS returns several results, they are used in round-robin manner.
820
821       If the value begins with /, then a Unix socket in the file-system name‐
822       space  is  used.  If the value begins with @, then a Unix socket in the
823       abstract namespace is used.
824
825       Default: not set, meaning to use a Unix socket
826
827   port
828       Default: 5432
829
830   user
831       If user= is set, all connections to the destination  database  will  be
832       done  with the specified user, meaning that there will be only one pool
833       for this database.
834
835       Otherwise, PgBouncer logs into the destination database with the client
836       user name, meaning that there will be one pool per user.
837
838   password
839       If  no  password  is specified here, the password from the auth_file or
840       auth_query will be used.
841
842   auth_user
843       Override of the global auth_user setting, if specified.
844
845   pool_size
846       Set the maximum size of pools for this database.  If not set,  the  de‐
847       fault_pool_size is used.
848
849   min_pool_size
850       Set  the  minimum  pool size for this database.  If not set, the global
851       min_pool_size is used.
852
853   reserve_pool
854       Set  additional  connections  for  this  database.   If  not  set,  re‐
855       serve_pool_size is used.
856
857   connect_query
858       Query  to be executed after a connection is established, but before al‐
859       lowing the connection to be used by any clients.  If the  query  raises
860       errors, they are logged but ignored otherwise.
861
862   pool_mode
863       Set  the  pool mode specific to this database.  If not set, the default
864       pool_mode is used.
865
866   max_db_connections
867       Configure a database-wide maximum (i.e. all pools within  the  database
868       will not have more than this many server connections).
869
870   client_encoding
871       Ask specific client_encoding from server.
872
873   datestyle
874       Ask specific datestyle from server.
875
876   timezone
877       Ask specific timezone from server.
878

SECTION [USERS]

880       This  contains  key=value  pairs  where the key will be taken as a user
881       name and the value as a libpq connection string style list of key=value
882       pairs  of  configuration  settings  specific for this user.  Only a few
883       settings are available here.
884
885   pool_mode
886       Set the pool mode to be used for all connections from  this  user.   If
887       not set, the database or default pool_mode is used.
888
889   max_user_connections
890       Configure a maximum for the user (i.e. all pools with the user will not
891       have more than this many server connections).
892

INCLUDE DIRECTIVE

894       The PgBouncer configuration file can contain include directives,  which
895       specify  another  configuration  file to read and process.  This allows
896       splitting the configuration file into physically separate  parts.   The
897       include directives look like this:
898
899              %include filename
900
901       If  the  file name is not absolute path it is taken as relative to cur‐
902       rent working directory.
903

AUTHENTICATION FILE FORMAT

905       PgBouncer needs its own user database.  The users  are  loaded  from  a
906       text file in the following format:
907
908              "username1" "password" ...
909              "username2" "md5abcdef012342345" ...
910              "username2" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"
911
912       There  should  be  at least 2 fields, surrounded by double quotes.  The
913       first field is the user name and the second is either a  plain-text,  a
914       MD5-hashed  password, or a SCRAM secret.  PgBouncer ignores the rest of
915       the line.  Double quotes in a field value can be escaped by writing two
916       double quotes.
917
918       PostgreSQL MD5-hashed password format:
919
920              "md5" + md5(password + username)
921
922       So  user  admin  with  password  1234  will  have  MD5-hashed  password
923       md545f2603610af569b6155c45067268c6b.
924
925       PostgreSQL SCRAM secret format:
926
927              SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>
928
929       See the PostgreSQL documentation and RFC 5803 for details on this.
930
931       The passwords or secrets stored in the authentication  file  serve  two
932       purposes.   First,  they  are  used to verify the passwords of incoming
933       client connections, if a password-based authentication method  is  con‐
934       figured.   Second,  they are used as the passwords for outgoing connec‐
935       tions to the backend server,  if  the  backend  server  requires  pass‐
936       word-based authentication (unless the password is specified directly in
937       the database’s connection string).  The latter works if the password is
938       stored in plain text or MD5-hashed.  SCRAM secrets can only be used for
939       logging into a server if the client authentication also uses SCRAM, the
940       PgBouncer  database  definition  does  not specify a user name, and the
941       SCRAM secrets are identical in  PgBouncer  and  the  PostgreSQL  server
942       (same  salt and iterations, not merely the same password).  This is due
943       to an inherent security property of SCRAM: The stored SCRAM secret can‐
944       not by itself be used for deriving login credentials.
945
946       The authentication file can be written by hand, but it’s also useful to
947       generate  it  from  some  other  list  of  users  and  passwords.   See
948       ./etc/mkauth.py for a sample script to generate the authentication file
949       from the pg_shadow system table.  Alternatively, use auth_query instead
950       of  auth_file  to  avoid  having  to maintain a separate authentication
951       file.
952

HBA FILE FORMAT

954       It  follows  the  format  of  the  PostgreSQL  pg_hba.conf  file   (see
955       <https://www.postgresql.org/docs/current/auth-pg-hba-conf.html>).
956
957       • Supported record types: local, host, hostssl, hostnossl.
958
959       • Database  field:  Supports all, sameuser, @file, multiple names.  Not
960         supported: replication, samerole, samegroup.
961
962       • User name field: Supports all, @file, multiple names.  Not supported:
963         +groupname.
964
965       • Address field: Supports IPv4, IPv6.  Not supported: DNS names, domain
966         prefixes.
967
968       • Auth-method field: Only methods supported  by  PgBouncer’s  auth_type
969         are  supported,  except  any and pam, which only work globally.  User
970         name map (map=) parameter is not supported.
971

EXAMPLE

973       Minimal config:
974
975              [databases]
976              template1 = host=localhost dbname=template1 auth_user=someuser
977
978              [pgbouncer]
979              pool_mode = session
980              listen_port = 6432
981              listen_addr = localhost
982              auth_type = md5
983              auth_file = users.txt
984              logfile = pgbouncer.log
985              pidfile = pgbouncer.pid
986              admin_users = someuser
987              stats_users = stat_collector
988
989       Database defaults:
990
991              [databases]
992
993              ; foodb over Unix socket
994              foodb =
995
996              ; redirect bardb to bazdb on localhost
997              bardb = host=localhost dbname=bazdb
998
999              ; access to destination database will go with single user
1000              forcedb = host=localhost port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO
1001
1002       Example of a secure function for auth_query:
1003
1004              CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
1005              RETURNS record AS $$
1006              BEGIN
1007                  SELECT usename, passwd FROM pg_catalog.pg_shadow
1008                  WHERE usename = i_username INTO uname, phash;
1009                  RETURN;
1010              END;
1011              $$ LANGUAGE plpgsql SECURITY DEFINER;
1012              REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
1013              GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;
1014

SEE ALSO

1016       pgbouncer(1) - man page for general usage, console commands
1017
1018       <https://www.pgbouncer.org/>
1019
1020
1021
10221.16.1                                                        PGBOUNCER.INI(5)
Impressum