1PGBOUNCER.INI(5) Databases PGBOUNCER.INI(5)
2
6 pgbouncer.ini - configuration file for pgbouncer
7
9 The configuration file is in "ini" format. Section names are between
10 "[" and "]". Lines starting with ";" or "#" are taken as comments and
11 ignored. The characters ";" and "#" are not recognized as special when
12 they appear later in the line.
13
15 logfile
16 Specifies the log file. The log file is kept open, so after rotation
17 kill -HUP or on console RELOAD; should be done. On Windows, the ser‐
18 vice must be stopped and started.
19 Default: not set
21 pidfile
23 Specifies the PID file. Without pidfile set, daemonization is not al‐
24 lowed.
25 Default: not set
27 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 Addresses can be specified numerically (IPv4/IPv6) or by name.
34 Default: not set
36 listen_port
38 Which port to listen on. Applies to both TCP and Unix sockets.
39 Default: 6432
41 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 Default: /tmp (empty on Windows)
48 unix_socket_mode
50 File system mode for Unix socket. Not supported on Windows.
51 Default: 0777
53 unix_socket_group
55 Group name to use for Unix socket. Not supported on Windows.
56 Default: not set
58 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 Default: not set
65 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 Default: not set
72 auth_hba_file
74 HBA configuration file to use when auth_type is hba.
75 Default: not set
77 auth_type
79 How to authenticate users.
80 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 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 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 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 scram-sha-256
102 Use password check with SCRAM-SHA-256. auth_file has to contain
103 SCRAM secrets or plain-text passwords.
104 plain The clear-text password is sent over the wire. Deprecated.
106 trust No authentication is done. The user name must still exist in
108 auth_file.
109 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 auth_query
116 Query to load user's password from database.
117 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 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 Default: SELECT usename, passwd FROM pg_shadow WHERE usename=$1
125 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 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 Default: not set
136 pool_mode
138 Specifies when a server connection can be reused by other clients.
139 session
141 Server is released back to pool after client disconnects. De‐
142 fault.
143 transaction
145 Server is released back to pool after transaction finishes.
146 statement
148 Server is released back to pool after query finishes. Transac‐
149 tions spanning multiple statements are disallowed in this mode.
150 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 max_client_conn + (max pool_size * total databases * total users)
158 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 max_client_conn + (max pool_size * total databases)
164 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 Search for ulimit in your favorite shell man page. Note: ulimit does
170 not apply in a Windows environment.
171 Default: 100
173 default_pool_size
175 How many server connections to allow per user/database pair. Can be
176 overridden in the per-database configuration.
177 Default: 20
179 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 Default: 0 (disabled)
186 reserve_pool_size
188 How many additional connections to allow to a pool (see re‐
189 serve_pool_timeout). 0 disables.
190 Default: 0 (disabled)
192 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 Default: 5.0
198 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 This can also be set per database in the [databases] section.
206 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 Default: 0 (unlimited)
214 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 This can also be set per user in the [users] section.
222 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 Default: 0 (unlimited)
230 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 Default: 0
240 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 Default: empty
249 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 Default: 0
258 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 Default: 0
267 conffile
269 Show location of current config file. Changing it will make PgBouncer
270 use another config file for next RELOAD / SIGHUP.
271 Default: file from command line
273 service_name
275 Used on win32 service registration.
276 Default: pgbouncer
278 job_name
280 Alias for service_name.
281 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 Default: 60
288
290 syslog
291 Toggles syslog on/off. On Windows, the event log is used instead.
292 Default: 0
294 syslog_ident
296 Under what name to send logs to syslog.
297 Default: pgbouncer (program name)
299 syslog_facility
301 Under what facility to send logs to syslog. Possibilities: auth, auth‐
302 priv, daemon, user, local0-7.
303 Default: daemon
305 log_connections
307 Log successful logins.
308 Default: 1
310 log_disconnections
312 Log disconnections with reasons.
313 Default: 1
315 log_pooler_errors
317 Log error messages the pooler sends to clients.
318 Default: 1
320 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 Default: 1
327 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 Default: 0
333
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 Default: empty
341 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 Default: empty
348
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 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 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 Default: DISCARD ALL
368 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 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 Default: 0
381 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 Default: 30.0
388 server_check_query
390 Simple do-nothing query to check if the server connection is alive.
391 If an empty string, then sanity checking is disabled.
393 Default: SELECT 1;
395 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 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 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 Default: 0
416 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 Default: 3600.0
423 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 Default: 600.0
429 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 Default: 15.0
435 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 Default: 15.0
441 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 Default: 60.0
448 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 Default: 3600.0
455 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 Default: 15.0
462 dns_nxdomain_ttl
464 How long error and NXDOMAIN DNS lookups can be cached. [seconds]
465 Default: 15.0
467 dns_zone_check_period
469 Period to check if a zone serial has changed.
470 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 Works only with UDNS and c-ares backends (--with-udns or --with-cares
477 to configure).
478 Default: 0.0 (disabled)
480 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 Requires evdns (>= 2.0.3) or c-ares (>= 1.15.0) backend.
487 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 Default: empty (use operating system defaults)
493
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 disable
502 Plain TCP. If client requests TLS, it's ignored. Default.
503 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 prefer Same as allow.
509 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 verify-ca
516 Client must use TLS with valid client certificate.
517 verify-full
519 Same as verify-ca.
520 client_tls_key_file
522 Private key for PgBouncer to accept client connections.
523 Default: not set
525 client_tls_cert_file
527 Certificate for private key. Clients can validate it.
528 Default: not set
530 client_tls_ca_file
532 Root certificate file to validate client certificates.
533 Default: not set
535 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 Default: secure
543 client_tls_ciphers
545 Default: fast
546 client_tls_ecdhcurve
548 Elliptic Curve name to use for ECDH key exchanges.
549 Allowed values: none (DH is disabled), auto (256-bit ECDH), curve name.
551 Default: auto
553 client_tls_dheparams
555 DHE key exchange type.
556 Allowed values: none (DH is disabled), auto (2048-bit DH), legacy
558 (1024-bit DH).
559 Default: auto
561 server_tls_sslmode
563 TLS mode to use for connections to PostgreSQL servers. TLS connections
564 are disabled by default.
565 disable
567 Plain TCP. TCP is not even requested from the server. Default.
568 allow FIXME: if server rejects plain, try TLS?
570 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 require
576 Connection must go over TLS. If server rejects it, plain TCP is
577 not attempted. Server certificate is not validated.
578 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 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 server_tls_ca_file
590 Root certificate file to validate PostgreSQL server certificates.
591 Default: not set
593 server_tls_key_file
595 Private key for PgBouncer to authenticate against PostgreSQL server.
596 Default: not set
598 server_tls_cert_file
600 Certificate for private key. PostgreSQL server can validate it.
601 Default: not set
603 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 Default: secure
611 server_tls_ciphers
613 Default: fast
614
616 Setting the following timeouts can cause unexpected errors.
617 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 Default: 0.0 (disabled)
624 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 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 Default: 120
636 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 Default: 0.0 (disabled)
643 idle_transaction_timeout
645 If a client has been in "idle in transaction" state longer, it will be
646 disconnected. [seconds]
647 Default: 0.0 (disabled)
649 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 Default: 10
655
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 Default: 4096
663 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 Default: 2147483647
670 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 Default: 128
677 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 Default: 5
685 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 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 On systems that don't support the socket option at all, turning this
704 setting on will result in an error.
705 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 Default: 0
713 tcp_defer_accept
715 For details on this and other TCP options, please see man 7 tcp.
716 Default: 45 on Linux, otherwise 0
718 tcp_socket_buffer
720 Default: not set
721 tcp_keepalive
723 Turns on basic keepalive with OS defaults.
724 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 Default: 1
729 tcp_keepcnt
731 Default: not set
732 tcp_keepidle
734 Default: not set
735 tcp_keepintvl
737 Default: not set
738 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 This is currently only supported on Linux.
746 Default: 0
748
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 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 "*" 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 dbname
766 Destination database name.
767 Default: same as client-side database name
769 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 Default: not set, meaning to use a Unix socket
779 port
781 Default: 5432
782 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 Otherwise, PgBouncer logs into the destination database with the client
789 user name, meaning that there will be one pool per user.
790 password
792 The length for password is limited to 160 characters maximum.
793 If no password is specified here, the password from the auth_file or
795 auth_query will be used.
796 auth_user
798 Override of the global auth_user setting, if specified.
799 pool_size
801 Set the maximum size of pools for this database. If not set, the de‐
802 fault_pool_size is used.
803 reserve_pool
805 Set additional connections for this database. If not set, re‐
806 serve_pool_size is used.
807 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 pool_mode
814 Set the pool mode specific to this database. If not set, the default
815 pool_mode is used.
816 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 client_encoding
822 Ask specific client_encoding from server.
823 datestyle
825 Ask specific datestyle from server.
826 timezone
828 Ask specific timezone from server.
829
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 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 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
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 %include filename
851 If the file name is not absolute path it is taken as relative to cur‐
853 rent working directory.
854
856 PgBouncer needs its own user database. The users are loaded from a
857 text file in the following format:
858 "username1" "password" ...
860 "username2" "md5abcdef012342345" ...
861 "username2" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"
862 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 PostgreSQL MD5-hashed password format:
869 "md5" + md5(password + username)
871 So user admin with password 1234 will have MD5-hashed password
873 md545f2603610af569b6155c45067268c6b.
874 PostgreSQL SCRAM secret format:
876 SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>
878 See the PostgreSQL documentation and RFC 5803 for details on this.
880 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 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
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 · Supported record types: local, host, hostssl, hostnossl.
908 · Database field: Supports all, sameuser, @file, multiple names. Not
910 supported: replication, samerole, samegroup.
911 · User name field: Supports all, @file, multiple names. Not supported:
913 +groupname.
914 · Address field: Supports IPv4, IPv6. Not supported: DNS names, domain
916 prefixes.
917 · 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
923 Minimal config:
924 [databases]
926 template1 = host=127.0.0.1 dbname=template1 auth_user=someuser
927 [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 Database defaults:
940 [databases]
942 ; foodb over Unix socket
944 foodb =
945 ; redirect bardb to bazdb on localhost
947 bardb = host=127.0.0.1 dbname=bazdb
948 ; 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 Example of a secure function for auth_query:
953 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
966 pgbouncer(1) - man page for general usage, console commands
967 <https://www.pgbouncer.org/>
9691.14.0 PGBOUNCER.INI(5)