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 when they appear
12       later in the line.
13

GENERIC SETTINGS

15   logfile
16       Specifies log file. Log file is kept open so after rotation  kill  -HUP
17       or  on  console RELOAD; should be done.  Note: On Windows machines, the
18       service must be stopped and started.
19
20       Default: not set.
21
22   pidfile
23       Specifies the  pid  file.  Without  a  pidfile,  daemonization  is  not
24       allowed.
25
26       Default: not set.
27
28   listen_addr
29       Specifies  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 allowed.
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.  Note: Not sup‐
46       ported on Windows machines.
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
63       user.
64
65       Note: Not supported on Windows machines.
66
67       Default: not set
68
69   auth_file
70       The name of the file to load user names and passwords  from.  The  file
71       format  is  the same as the PostgreSQL 8.x pg_auth/pg_pwd file, so this
72       setting can be pointed directly to one of those backend  files.   Since
73       version 9.0, PostgreSQL does not use such text file, so it must be gen‐
74       erated manually.  See section Authentication file  format  below  about
75       details.
76
77       Default: not set.
78
79   auth_hba_file
80       HBA  configuration  file  to use when auth_type is hba.  Supported from
81       version 1.7 onwards.
82
83       Default: not set
84
85   auth_type
86       How to authenticate users.
87
88       pam    PAM is used to authenticate users, auth_file  is  ignored.  This
89              method  is not compatible with databases using auth_user option.
90              Service name reported to PAM is "pgbouncer". Also, pam is  still
91              not supported in HBA configuration file.
92
93       hba    Actual auth type is loaded from auth_hba_file.  This allows dif‐
94              ferent authentication methods different access paths.   Example:
95              connection  over  Unix  socket  use peer auth method, connection
96              over TCP must use TLS. Supported from version 1.7 onwards.
97
98       cert   Client must connect over TLS connection with valid client  cert.
99              Username is then taken from CommonName field from certificate.
100
101       md5    Use   MD5-based  password  check.  auth_file  may  contain  both
102              MD5-encrypted or plain-text  passwords.   This  is  the  default
103              authentication method.
104
105       plain  Clear-text password is sent over wire.  Deprecated.
106
107       trust  No   authentication  is  done.  Username  must  still  exist  in
108              auth_file.
109
110       any    Like the trust  method,  but  the  username  given  is  ignored.
111              Requires that all databases are configured to log in as 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 non-admin user that calls SECURITY DEFINER function instead.
120
121       Note that the query is run inside target database, so if a function  is
122       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,  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. Auth_user's password will be taken from auth_file.
130
131       Direct  access  to pg_shadow requires admin rights.  It's preferable to
132       use non-admin user that calls SECURITY DEFINER function instead.
133
134       Default: not set.
135
136   pool_mode
137       Specifies when a server connection can be reused by other clients.
138
139       session
140              Server is  released  back  to  pool  after  client  disconnects.
141              Default.
142
143       transaction
144              Server is released back to pool after transaction finishes.
145
146       statement
147              Server  is  released  back  to  pool  after query finishes. Long
148              transactions spanning multiple statements are disallowed in this
149              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 actual num‐
154       ber of file descriptors used is more than max_client_conn.  Theoretical
155       maximum used is:
156
157          max_client_conn + (max pool_size * total databases * total users)
158
159       if each user connects under its own username to server.  If a  database
160       user is specified in connect string (all users connect under same user‐
161       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 special load for it.  Still, it means you should set the
167       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
182       behavior  when  usual  load  comes  suddenly back after period of total
183       inactivity.
184
185       Default: 0 (disabled)
186
187   reserve_pool_size
188       How many additional connections to allow to a pool. 0 disables.
189
190       Default: 0 (disabled)
191
192   reserve_pool_timeout
193       If a client has not been  serviced  in  this  many  seconds,  pgbouncer
194       enables use of additional connections from reserve pool.  0 disables.
195
196       Default: 5.0
197
198   max_db_connections
199       Do  not  allow more than this many connections per-database (regardless
200       of pool - i.e.  user). It should be noted that when you hit the  limit,
201       closing  a  client  connection to one pool will not immediately allow a
202       server connection to be  established  for  another  pool,  because  the
203       server  connection  for  the first pool is still open.  Once the server
204       connection closes (due to idle timeout), a new server  connection  will
205       immediately be opened for the waiting pool.
206
207       Default: unlimited
208
209   max_user_connections
210       Do  not  allow  more than this many connections per-user (regardless of
211       pool - i.e.  user). It should be noted that when  you  hit  the  limit,
212       closing  a  client  connection to one pool will not immediately allow a
213       server connection to be  established  for  another  pool,  because  the
214       server  connection  for  the first pool is still open.  Once the server
215       connection closes (due to idle timeout), a new server  connection  will
216       immediately be opened for the waiting pool.
217
218   server_round_robin
219       By  default,  pgbouncer  reuses  server  connections  in LIFO (last-in,
220       first-out) manner, so that few connections get  the  most  load.   This
221       gives  best performance if you have a single server serving a database.
222       But if there is TCP round-robin behind a database IP, then it is better
223       if  pgbouncer also uses connections in that manner, thus achieving uni‐
224       form load.
225
226       Default: 0
227
228   ignore_startup_parameters
229       By default, PgBouncer allows only parameters it can keep  track  of  in
230       startup  packets  -  client_encoding,  datestyle,  timezone  and  stan‐
231       dard_conforming_strings.
232
233       All others parameters will raise an error.  To allow others parameters,
234       they  can be specified here, so that pgbouncer knows that they are han‐
235       dled by admin and it can ignore them.
236
237       Default: empty
238
239   disable_pqexec
240       Disable Simple Query protocol (PQexec).  Unlike Extended  Query  proto‐
241       col,  Simple  Query allows multiple queries in one packet, which allows
242       some classes of SQL-injection attacks.  Disabling it can improve  secu‐
243       rity.   Obviously this means only clients that exclusively use Extended
244       Query protocol will stay working.
245
246       Default: 0
247
248   application_name_add_host
249       Add the client host address and port to the  application  name  setting
250       set  on  connection start.  This helps in identifying the source of bad
251       queries etc.  This logic applies only on start of connection, if appli‐
252       cation_name  is  later  changed  with SET, pgbouncer does not change it
253       again.
254
255       Default: 0
256
257   conffile
258       Show location of current config file.  Changing it will make  PgBouncer
259       use another config file for next RELOAD / SIGHUP.
260
261       Default: file from command line.
262
263   service_name
264       Used on win32 service registration.
265
266       Default: pgbouncer
267
268   job_name
269       Alias for service_name.
270

LOG SETTINGS

272   syslog
273       Toggles  syslog  on/off  As  for  windows environment, eventlog is used
274       instead.
275
276       Default: 0
277
278   syslog_ident
279       Under what name to send logs to syslog.
280
281       Default: pgbouncer (program name)
282
283   syslog_facility
284       Under what facility to send logs to syslog.  Possibilities: auth, auth‐
285       priv, daemon, user, local0-7.
286
287       Default: daemon
288
289   log_connections
290       Log successful logins.
291
292       Default: 1
293
294   log_disconnections
295       Log disconnections with reasons.
296
297       Default: 1
298
299   log_pooler_errors
300       Log error messages pooler sends to clients.
301
302       Default: 1
303
304   stats_period
305       Period for writing aggregated stats into log.
306
307       Default: 60
308
309   verbose
310       Increase  verbosity.   Mirrors  "-v" switch on command line.  Using "-v
311       -v" on command line is same as verbose=2 in config.
312
313       Default: 0
314

CONSOLE ACCESS CONTROL

316   admin_users
317       Comma-separated list of database users that are allowed to connect  and
318       run  all  commands on console.  Ignored when auth_type is any, in which
319       case any username is allowed in as admin.
320
321       Default: empty
322
323   stats_users
324       Comma-separated list of database users that are allowed to connect  and
325       run  read-only  queries on console. That means all SHOW commands except
326       SHOW FDS.
327
328       Default: empty.
329

CONNECTION SANITY CHECKS, TIMEOUTS

331   server_reset_query
332       Query sent to server on connection release, before making it  available
333       to  other  clients.  At that moment no transaction is in progress so it
334       should not include ABORT or ROLLBACK.
335
336       The query is supposed to clean any changes made to database session  so
337       that  next  client  gets  connection in well-defined state.  Default is
338       DISCARD ALL which cleans everything, but that  leaves  next  client  no
339       pre-cached  state.  It can be made lighter, e.g. DEALLOCATE ALL to just
340       drop prepared statements, if application does not break when some state
341       is kept around.
342
343       When  transaction  pooling is used, the server_reset_query is not used,
344       as clients must not use any session-based features as each  transaction
345       ends up in different connection and thus gets different session state.
346
347       Default: DISCARD ALL
348
349   server_reset_query_always
350       Whether  server_reset_query  should  be run in all pooling modes.  When
351       this setting is off (default), the server_reset_query will be run  only
352       in  pools  that  are in sessions-pooling mode.  Connections in transac‐
353       tion-pooling mode should not have any need for reset query.
354
355       It is workaround for broken setups that run apps that use session  fea‐
356       tures  over transaction-pooled pgbouncer.  Is changes non-deterministic
357       breakage to deterministic breakage - client  always  lose  their  state
358       after each transaction.
359
360       Default: 0
361
362   server_check_delay
363       How  long  to keep released connections available for immediate re-use,
364       without running sanity-check queries on it. If 0 then the query is  ran
365       always.
366
367       Default: 30.0
368
369   server_check_query
370       Simple do-nothing query to check if the server connection is alive.
371
372       If an empty string, then sanity checking is disabled.
373
374       Default: SELECT 1;
375
376   server_fast_close
377       Disconnect  a  server  in session pooling mode immediately or after the
378       end of the current transaction if it is in "close_needed" mode (set  by
379       RECONNECT,  RELOAD  that  changes  connection settings, or DNS change),
380       rather than waiting for the session end.  In statement  or  transaction
381       pooling  mode,  this  has  no effect since that is the default behavior
382       there.
383
384       If because of this setting a server connection is closed before the end
385       of  the  client  session,  the  client connection is also closed.  This
386       ensures that the client notices that the session has been interrupted.
387
388       This setting makes connection configuration changes take effect  sooner
389       if session pooling and long-running sessions are used.  The downside is
390       that client sessions are liable to be interrupted  by  a  configuration
391       change,  so  client  applications  will  need  logic  to  reconnect and
392       reestablish session state.  But note that no transactions will be lost,
393       because running transactions are not interrupted, only idle sessions.
394
395       Default: 0
396
397   server_lifetime
398       The  pooler  will  close an unused server connection that has been con‐
399       nected longer than this. Setting it to 0 means the connection is to  be
400       used only once, then closed. [seconds]
401
402       Default: 3600.0
403
404   server_idle_timeout
405       If  a  server  connection  has been idle more than this many seconds it
406       will be dropped.  If 0 then timeout is disabled.  [seconds]
407
408       Default: 600.0
409
410   server_connect_timeout
411       If connection and login won't finish in this amount of time,  the  con‐
412       nection will be closed. [seconds]
413
414       Default: 15.0
415
416   server_login_retry
417       If  login  failed,  because of failure from connect() or authentication
418       that pooler waits this much before retrying to connect. [seconds]
419
420       Default: 15.0
421
422   client_login_timeout
423       If a client connects but does not manage to login  in  this  amount  of
424       time,  it will be disconnected. Mainly needed to avoid dead connections
425       stalling SUSPEND and thus online restart. [seconds]
426
427       Default: 60.0
428
429   autodb_idle_timeout
430       If the automatically created (via "*") database pools have been  unused
431       this many seconds, they are freed.  The negative aspect of that is that
432       their statistics are also forgotten.  [seconds]
433
434       Default: 3600.0
435
436   dns_max_ttl
437       How long the DNS lookups can be cached.  If a DNS lookup  returns  sev‐
438       eral  answers,  pgbouncer  will  robin-between  them  in  the meantime.
439       Actual DNS TTL is ignored.  [seconds]
440
441       Default: 15.0
442
443   dns_nxdomain_ttl
444       How long error and NXDOMAIN DNS lookups can be cached. [seconds]
445
446       Default: 15.0
447
448   dns_zone_check_period
449       Period to check if zone serial has changed.
450
451       PgBouncer can collect DNS zones from host names (everything after first
452       dot) and then periodically check if zone serial changes.  If it notices
453       changes, all host names under that zone are looked up  again.   If  any
454       host IP changes, its connections are invalidated.
455
456       Works  only  with UDNS and c-ares backends (--with-udns or --with-cares
457       to configure).
458
459       Default: 0.0 (disabled)
460

TLS SETTINGS

462   client_tls_sslmode
463       TLS mode to use for connections from clients.  TLS connections are dis‐
464       abled    by    default.     When   enabled,   client_tls_key_file   and
465       client_tls_cert_file must be also configured to set  up  key  and  cert
466       PgBouncer uses to accept client connections.
467
468       disable
469              Plain TCP.  If client requests TLS, it's ignored.  Default.
470
471       allow  If  client requests TLS, it is used.  If not, plain TCP is used.
472              If client uses client-certificate, it is not validated.
473
474       prefer Same as allow.
475
476       require
477              Client must use TLS.  If not, client connection is rejected.  If
478              client uses client-certificate, it is not validated.
479
480       verify-ca
481              Client must use TLS with valid client certificate.
482
483       verify-full
484              Same as verify-ca.
485
486   client_tls_key_file
487       Private key for PgBouncer to accept client connections.
488
489       Default: not set.
490
491   client_tls_cert_file
492       Certificate for private key.  Clients can validate it.
493
494       Default: not set.
495
496   client_tls_ca_file
497       Root certificate file to validate client certificates.
498
499       Default: unset.
500
501   client_tls_protocols
502       Which  TLS  protocol  versions  are  allowed.  Allowed values: tlsv1.0,
503       tlsv1.1, tlsv1.2.   Shortcuts:  all  (tlsv1.0,tlsv1.1,tlsv1.2),  secure
504       (tlsv1.2), legacy (all).
505
506       Default: all
507
508   client_tls_ciphers
509       Default: fast
510
511   client_tls_ecdhcurve
512       Elliptic Curve name to use for ECDH key exchanges.
513
514       Allowed values: none (DH is disabled), auto (256-bit ECDH), curve name.
515
516       Default: auto
517
518   client_tls_dheparams
519       DHE key exchange type.
520
521       Allowed  values:  none  (DH  is  disabled),  auto (2048-bit DH), legacy
522       (1024-bit DH).
523
524       Default: auto
525
526   server_tls_sslmode
527       TLS mode to use for connections to PostgreSQL servers.  TLS connections
528       are disabled by default.
529
530       disable
531              Plain TCP.  TCP is not event requested from server.  Default.
532
533       allow  FIXME: if server rejects plain, try TLS?
534
535       prefer TLS  connection  is always requested first from PostgreSQL, when
536              refused connection will be established over plain  TCP.   Server
537              certificate is not validated.
538
539       require
540              Connection must go over TLS.  If server rejects it, plain TCP is
541              not attempted.  Server certificate is not validated.
542
543       verify-ca
544              Connection must go over TLS and server certificate must be valid
545              according  to  server_tls_ca_file.   Server  host  name  is  not
546              checked against certificate.
547
548       verify-full
549              Connection must go over TLS and server certificate must be valid
550              according  to  server_tls_ca_file.   Server host name must match
551              certificate info.
552
553   server_tls_ca_file
554       Root certificate file to validate PostgreSQL server certificates.
555
556       Default: unset.
557
558   server_tls_key_file
559       Private key for PgBouncer to authenticate against PostgreSQL server.
560
561       Default: not set.
562
563   server_tls_cert_file
564       Certificate for private key.  PostgreSQL server can validate it.
565
566       Default: not set.
567
568   server_tls_protocols
569       Which TLS protocol versions  are  allowed.   Allowed  values:  tlsv1.0,
570       tlsv1.1,  tlsv1.2.   Shortcuts:  all  (tlsv1.0,tlsv1.1,tlsv1.2), secure
571       (tlsv1.2), legacy (all).
572
573       Default: all
574
575   server_tls_ciphers
576       Default: fast
577

DANGEROUS TIMEOUTS

579       Setting following timeouts cause unexpected errors.
580
581   query_timeout
582       Queries running longer than that are canceled. This should be used only
583       with  slightly smaller server-side statement_timeout, to apply only for
584       network problems. [seconds]
585
586       Default: 0.0 (disabled)
587
588   query_wait_timeout
589       Maximum time queries are allowed to spend waiting for execution. If the
590       query  is not assigned to a server during that time, the client is dis‐
591       connected. This is used to prevent unresponsive servers  from  grabbing
592       up connections. [seconds]
593
594       It  also  helps when server is down or database rejects connections for
595       any reason.  If this is disabled, clients will be queued infinitely.
596
597       Default: 120
598
599   client_idle_timeout
600       Client connections idling longer than this  many  seconds  are  closed.
601       This  should  be  larger  than the client-side connection lifetime set‐
602       tings, and only used for network problems. [seconds]
603
604       Default: 0.0 (disabled)
605
606   idle_transaction_timeout
607       If client has been in "idle in transaction" state longer,  it  will  be
608       disconnected.  [seconds]
609
610       Default: 0.0 (disabled)
611

LOW-LEVEL NETWORK SETTINGS

613   pkt_buf
614       Internal  buffer size for packets. Affects size of TCP packets sent and
615       general memory usage. Actual libpq packets can be larger than this  so,
616       no need to set it large.
617
618       Default: 4096
619
620   max_packet_size
621       Maximum size for PostgreSQL packets that PgBouncer allows through.  One
622       packet is either one query or one result set row.  Full result set  can
623       be larger.
624
625       Default: 2147483647
626
627   listen_backlog
628       Backlog  argument  for  listen(2).   Determines how many new unanswered
629       connection attempts are kept in queue.  When queue is full, further new
630       connections are dropped.
631
632       Default: 128
633
634   sbuf_loopcnt
635       How  many  times  to process data on one connection, before proceeding.
636       Without this limit, one connection with a  big  result  set  can  stall
637       PgBouncer  for  a  long time.  One loop processes one pkt_buf amount of
638       data.  0 means no limit.
639
640       Default: 5
641
642   suspend_timeout
643       How many seconds to wait for buffer  flush  during  SUSPEND  or  reboot
644       (-R).  Connection is dropped if flush does not succeed.
645
646       Default: 10
647
648   tcp_defer_accept
649       For details on this and other tcp options, please see man 7 tcp.
650
651       Default: 45 on Linux, otherwise 0
652
653   tcp_socket_buffer
654       Default: not set
655
656   tcp_keepalive
657       Turns on basic keepalive with OS defaults.
658
659       On  Linux, the system defaults are tcp_keepidle=7200, tcp_keepintvl=75,
660       tcp_keepcnt=9.  They are probably similar on other OS-es.
661
662       Default: 1
663
664   tcp_keepcnt
665       Default: not set
666
667   tcp_keepidle
668       Default: not set
669
670   tcp_keepintvl
671       Default: not set
672

SECTION [DATABASES]

674       This contains key=value pairs where key will be  taken  as  a  database
675       name and value as a libpq connect-string style list of key=value pairs.
676       As actual libpq is not used, so not all features from libpq can be used
677       (service=, .pgpass).
678
679       Database name can contain characters _0-9A-Za-z without quoting.  Names
680       that contain other chars need to be  quoted  with  standard  SQL  ident
681       quoting: double quotes where "" is taken as single quote.
682
683       "*"  acts  as  fallback database: if the exact name does not exist, its
684       value is taken as connect string for requested database.  Such automat‐
685       ically created database entries are cleaned up if they stay idle longer
686       then the time specified in autodb_idle_timeout parameter.
687
688   dbname
689       Destination database name.
690
691       Default: same as client-side database name.
692
693   host
694       Host name or IP address to connect to.  Host names are resolved at con‐
695       nect time, the result is cached per dns_max_ttl parameter.  When a host
696       name's resolution changes, existing server  connections  are  automati‐
697       cally  closed  when  they are released (according to the pooling mode),
698       and new server connections immediately use the new resolution.  If  DNS
699       returns several results, they are used in round-robin manner.
700
701       Default: not set, meaning to use a Unix socket.
702
703   port
704       Default: 5432
705
706   user, password
707       If  user=  is  set, all connections to the destination database will be
708       done with the specified user, meaning that there will be only one  pool
709       for this database.
710
711       Otherwise  PgBouncer  tries  to  log into the destination database with
712       client username, meaning that there will be one pool per user.
713
714       The length for password is limited to 128 characters maximum.
715
716   auth_user
717       Override of the global auth_user setting, if specified.
718
719   pool_size
720       Set maximum  size  of  pools  for  this  database.   If  not  set,  the
721       default_pool_size is used.
722
723   reserve_pool
724       Set   additional   connections   for   this   database.   If  not  set,
725       reserve_pool_size is used.
726
727   connect_query
728       Query to be executed after a  connection  is  established,  but  before
729       allowing  the connection to be used by any clients. If the query raises
730       errors, they are logged but ignored otherwise.
731
732   pool_mode
733       Set the pool mode specific to this database. If not  set,  the  default
734       pool_mode is used.
735
736   max_db_connections
737       Configure  a  database-wide maximum (i.e. all pools within the database
738       will not have more than this many server connections).
739
740   client_encoding
741       Ask specific client_encoding from server.
742
743   datestyle
744       Ask specific datestyle from server.
745
746   timezone
747       Ask specific timezone from server.
748

SECTION [USERS]

750       This contains key=value pairs where key will be taken as  a  user  name
751       and  value  as a libpq connect-string style list of key=value pairs. As
752       actual libpq is not used, so not all features from libpq can be used.
753
754   pool_mode
755       Set the pool mode to be used for all connections from this user. If not
756       set, the database or default pool_mode is used.
757

INCLUDE DIRECTIVE

759       The PgBouncer config file can contain include directives, which specify
760       another config file to read and process. This allows for splitting  the
761       configuration  file  into physically separate parts. The include direc‐
762       tives look like this:
763
764          %include filename
765
766       If the file name is not absolute path it is taken as relative  to  cur‐
767       rent working directory.
768

AUTHENTICATION FILE FORMAT

770       PgBouncer needs its own user database. The users are loaded from a text
771       file in following format:
772
773          "username1" "password" ...
774          "username2" "md5abcdef012342345" ...
775
776       There should be at least 2 fields, surrounded  by  double  quotes.  The
777       first  field is the username and the second is either a plain-text or a
778       MD5-hidden password.  PgBouncer ignores the rest of the line.
779
780       This file format is equivalent to text files used by PostgreSQL 8.x for
781       authentication  info, thus allowing PgBouncer to work directly on Post‐
782       greSQL authentication files in data directory.
783
784       Since PostgreSQL 9.0, the text files are not used  anymore.   Thus  the
785       auth  file  needs  to  be  generated.    See ./etc/mkauth.py for sample
786       script to generate auth file from pg_shadow table.
787
788       PostgreSQL MD5-hidden password format:
789
790          "md5" + md5(password + username)
791
792       So  user  admin  with  password  1234  will  have  MD5-hidden  password
793       md545f2603610af569b6155c45067268c6b.
794

HBA FILE FORMAT

796       It    follows   the   format   of   PostgreSQL   pg_hba.conf   file   -
797       http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html
798
799       There are following differences:
800
801       · Supported record types: local, host, hostssl, hostnossl.
802
803       · Database field: Supports all, sameuser, @file, multiple  names.   Not
804         supported: replication, samerole, samegroup.
805
806       · Username  field: Supports all, @file, multiple names.  Not supported:
807         +groupname.
808
809       · Address field: Supported  IPv4,  IPv6.   Not  supported:  DNS  names,
810         domain prefixes.
811
812       · Auth-method  field:  Supported methods: trust, reject, md5, password,
813         peer, cert.  Not supported: gss,  sspi,  ident,  ldap,  radius,  pam.
814         Also username map (map=) parameter is not supported.
815

EXAMPLE

817       Minimal config:
818
819          [databases]
820          template1 = host=127.0.0.1 dbname=template1 auth_user=someuser
821
822          [pgbouncer]
823          pool_mode = session
824          listen_port = 6543
825          listen_addr = 127.0.0.1
826          auth_type = md5
827          auth_file = users.txt
828          logfile = pgbouncer.log
829          pidfile = pgbouncer.pid
830          admin_users = someuser
831          stats_users = stat_collector
832
833       Database defaults:
834
835          [databases]
836
837          ; foodb over Unix socket
838          foodb =
839
840          ; redirect bardb to bazdb on localhost
841          bardb = host=127.0.0.1 dbname=bazdb
842
843          ; access to destination database will go with single user
844          forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO
845
846       Example of secure function for auth_query:
847
848          CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
849          RETURNS record AS $$
850          BEGIN
851              SELECT usename, passwd FROM pg_catalog.pg_shadow
852              WHERE usename = i_username INTO uname, phash;
853              RETURN;
854          END;
855          $$ LANGUAGE plpgsql SECURITY DEFINER;
856          REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
857          GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;
858

SEE ALSO

860       pgbouncer(1) - man page for general usage, console commands.
861
862       https://pgbouncer.github.io/
863
864       https://wiki.postgresql.org/wiki/PgBouncer
865
866
867
868
8691.9.0                             2018-08-13                  PGBOUNCER.INI(5)
Impressum