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