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

LOG SETTINGS

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

CONSOLE ACCESS CONTROL

335   admin_users
336       Comma-separated  list of database users that are allowed to connect and
337       run all commands on the console.  Ignored when  auth_type  is  any,  in
338       which case any user name is allowed in as admin.
339
340       Default: empty
341
342   stats_users
343       Comma-separated  list of database users that are allowed to connect and
344       run read-only queries on the console.  That means all SHOW commands ex‐
345       cept SHOW FDS.
346
347       Default: empty
348

CONNECTION SANITY CHECKS, TIMEOUTS

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

TLS SETTINGS

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

DANGEROUS TIMEOUTS

616       Setting the following timeouts can cause unexpected errors.
617
618   query_timeout
619       Queries running longer than that are canceled.  This should be used on‐
620       ly  with  slightly smaller server-side statement_timeout, to apply only
621       for network problems.  [seconds]
622
623       Default: 0.0 (disabled)
624
625   query_wait_timeout
626       Maximum time queries are allowed to spend waiting  for  execution.   If
627       the  query  is not assigned to a server during that time, the client is
628       disconnected.  This is used to prevent unresponsive servers from  grab‐
629       bing up connections.  [seconds]
630
631       It  also  helps when the server is down or database rejects connections
632       for any reason.  If this is disabled, clients will  be  queued  indefi‐
633       nitely.
634
635       Default: 120
636
637   client_idle_timeout
638       Client  connections  idling  longer  than this many seconds are closed.
639       This should be larger than the  client-side  connection  lifetime  set‐
640       tings, and only used for network problems.  [seconds]
641
642       Default: 0.0 (disabled)
643
644   idle_transaction_timeout
645       If  a client has been in "idle in transaction" state longer, it will be
646       disconnected.  [seconds]
647
648       Default: 0.0 (disabled)
649
650   suspend_timeout
651       How many seconds to wait for buffer  flush  during  SUSPEND  or  reboot
652       (-R).  A connection is dropped if the flush does not succeed.
653
654       Default: 10
655

LOW-LEVEL NETWORK SETTINGS

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

SECTION [DATABASES]

750       This contains key=value pairs where the key will be taken as a database
751       name and the value as a libpq connection string style list of key=value
752       pairs.   Not  all features known from libpq can be used (service=, .pg‐
753       pass), since the actual libpq is not used.
754
755       The database name can contain characters  _0-9A-Za-z  without  quoting.
756       Names that contain other characters need to be quoted with standard SQL
757       identifier quoting: double quotes, with "" for a single instance  of  a
758       double quote.
759
760       "*"  acts as a fallback database: if the exact name does not exist, its
761       value is taken as connection string for requested database.  Such auto‐
762       matically  created  database  entries  are cleaned up if they stay idle
763       longer than the time specified by the autodb_idle_timeout parameter.
764
765   dbname
766       Destination database name.
767
768       Default: same as client-side database name
769
770   host
771       Host name or IP address to connect to.  Host names are resolved at con‐
772       nection  time,  the result is cached per dns_max_ttl parameter.  When a
773       host name's resolution changes, existing server connections  are  auto‐
774       matically  closed  when  they  are  released  (according to the pooling
775       mode), and new server connections immediately use the  new  resolution.
776       If DNS returns several results, they are used in round-robin manner.
777
778       Default: not set, meaning to use a Unix socket
779
780   port
781       Default: 5432
782
783   user
784       If  user=  is  set, all connections to the destination database will be
785       done with the specified user, meaning that there will be only one  pool
786       for this database.
787
788       Otherwise, PgBouncer logs into the destination database with the client
789       user name, meaning that there will be one pool per user.
790
791   password
792       The length for password is limited to 160 characters maximum.
793
794       If no password is specified here, the password from  the  auth_file  or
795       auth_query will be used.
796
797   auth_user
798       Override of the global auth_user setting, if specified.
799
800   pool_size
801       Set  the  maximum size of pools for this database.  If not set, the de‐
802       fault_pool_size is used.
803
804   reserve_pool
805       Set  additional  connections  for  this  database.   If  not  set,  re‐
806       serve_pool_size is used.
807
808   connect_query
809       Query  to be executed after a connection is established, but before al‐
810       lowing the connection to be used by any clients.  If the  query  raises
811       errors, they are logged but ignored otherwise.
812
813   pool_mode
814       Set  the  pool mode specific to this database.  If not set, the default
815       pool_mode is used.
816
817   max_db_connections
818       Configure a database-wide maximum (i.e.  all pools within the  database
819       will not have more than this many server connections).
820
821   client_encoding
822       Ask specific client_encoding from server.
823
824   datestyle
825       Ask specific datestyle from server.
826
827   timezone
828       Ask specific timezone from server.
829

SECTION [USERS]

831       This  contains  key=value  pairs  where the key will be taken as a user
832       name and the value as a libpq connection string style list of key=value
833       pairs  of  configuration  settings  specific for this user.  Only a few
834       settings are available here.
835
836   pool_mode
837       Set the pool mode to be used for all connections from  this  user.   If
838       not set, the database or default pool_mode is used.
839
840   max_user_connections
841       Configure  a  maximum  for the user (i.e.  all pools with the user will
842       not have more than this many server connections).
843

INCLUDE DIRECTIVE

845       The PgBouncer configuration file can contain include directives,  which
846       specify  another  configuration  file to read and process.  This allows
847       splitting the configuration file into physically separate  parts.   The
848       include directives look like this:
849
850              %include filename
851
852       If  the  file name is not absolute path it is taken as relative to cur‐
853       rent working directory.
854

AUTHENTICATION FILE FORMAT

856       PgBouncer needs its own user database.  The users  are  loaded  from  a
857       text file in the following format:
858
859              "username1" "password" ...
860              "username2" "md5abcdef012342345" ...
861              "username2" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"
862
863       There  should  be  at least 2 fields, surrounded by double quotes.  The
864       first field is the user name and the second is either a  plain-text,  a
865       MD5-hashed  password, or a SCRAM secret.  PgBouncer ignores the rest of
866       the line.
867
868       PostgreSQL MD5-hashed password format:
869
870              "md5" + md5(password + username)
871
872       So  user  admin  with  password  1234  will  have  MD5-hashed  password
873       md545f2603610af569b6155c45067268c6b.
874
875       PostgreSQL SCRAM secret format:
876
877              SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>
878
879       See the PostgreSQL documentation and RFC 5803 for details on this.
880
881       The  passwords  or  secrets stored in the authentication file serve two
882       purposes.  First, they are used to verify  the  passwords  of  incoming
883       client  connections,  if a password-based authentication method is con‐
884       figured.  Second, they are used as the passwords for  outgoing  connec‐
885       tions  to  the  backend  server,  if  the backend server requires pass‐
886       word-based authentication (unless the password is specified directly in
887       the database's connection string).  The latter works if the password is
888       stored in plain text or MD5-hashed.  SCRAM secrets can only be used for
889       logging into a server if the client authentication also uses SCRAM, the
890       PgBouncer database definition does not specify a  user  name,  and  the
891       SCRAM  secrets  are  identical  in  PgBouncer and the PostgreSQL server
892       (same salt and iterations, not merely the same password).  This is  due
893       to an inherent security property of SCRAM: The stored SCRAM secret can‐
894       not by itself be used for deriving login credentials.
895
896       The authentication file can be written by hand, but it's also useful to
897       generate  it  from  some  other  list  of  users  and  passwords.   See
898       ./etc/mkauth.py for a sample script to generate the authentication file
899       from the pg_shadow system table.  Alternatively, use auth_query instead
900       of auth_file to avoid having  to  maintain  a  separate  authentication
901       file.
902

HBA FILE FORMAT

904       It   follows  the  format  of  the  PostgreSQL  pg_hba.conf  file  (see
905       <https://www.postgresql.org/docs/current/auth-pg-hba-conf.html>).
906
907       · Supported record types: local, host, hostssl, hostnossl.
908
909       · Database field: Supports all, sameuser, @file, multiple  names.   Not
910         supported: replication, samerole, samegroup.
911
912       · User name field: Supports all, @file, multiple names.  Not supported:
913         +groupname.
914
915       · Address field: Supports IPv4, IPv6.  Not supported: DNS names, domain
916         prefixes.
917
918       · Auth-method  field:  Only  methods supported by PgBouncer's auth_type
919         are supported, except any and pam, which only  work  globally.   User
920         name map (map=) parameter is not supported.
921

EXAMPLE

923       Minimal config:
924
925              [databases]
926              template1 = host=127.0.0.1 dbname=template1 auth_user=someuser
927
928              [pgbouncer]
929              pool_mode = session
930              listen_port = 6432
931              listen_addr = 127.0.0.1
932              auth_type = md5
933              auth_file = users.txt
934              logfile = pgbouncer.log
935              pidfile = pgbouncer.pid
936              admin_users = someuser
937              stats_users = stat_collector
938
939       Database defaults:
940
941              [databases]
942
943              ; foodb over Unix socket
944              foodb =
945
946              ; redirect bardb to bazdb on localhost
947              bardb = host=127.0.0.1 dbname=bazdb
948
949              ; access to destination database will go with single user
950              forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO
951
952       Example of a secure function for auth_query:
953
954              CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
955              RETURNS record AS $$
956              BEGIN
957                  SELECT usename, passwd FROM pg_catalog.pg_shadow
958                  WHERE usename = i_username INTO uname, phash;
959                  RETURN;
960              END;
961              $$ LANGUAGE plpgsql SECURITY DEFINER;
962              REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
963              GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;
964

SEE ALSO

966       pgbouncer(1) - man page for general usage, console commands
967
968       <https://www.pgbouncer.org/>
969
970
971
9721.14.0                                                        PGBOUNCER.INI(5)
Impressum