Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
PGBOUNCER.INI(5)		   Databases		      PGBOUNCER.INI(5)

       pgbouncer.ini - configuration file for pgbouncer

       The  configuration  file	is in "ini" format.  Section names are between
       "[" and "]".  Lines starting with ";" or	"#" are	taken as comments  and
       ignored.	 The characters	";" and	"#" are	not recognized as special when
       they appear later in the	line.

       Specifies the log file.	The log	file is	kept open, so  after  rotation
       kill -HUP  or  on console RELOAD; should	be done.  On Windows, the ser-
       vice must be stopped and	started.

       Default:	not set

       Specifies the PID file.	Without	pidfile	set, daemonization is not  al-

       Default:	not set

       Specifies a list	of addresses where to listen for TCP connections.  You
       may also	use * meaning "listen on all addresses".  When not  set,  only
       Unix socket connections are accepted.

       Addresses can be	specified numerically (IPv4/IPv6) or by	name.

       Default:	not set

       Which port to listen on.	 Applies to both TCP and Unix sockets.

       Default:	6432

       Specifies  location for Unix sockets.  Applies to both listening	socket
       and server connections.	If set to an empty string,  Unix  sockets  are
       disabled.  Required for online reboot (-R) to work.

       Default:	/tmp (empty on Windows)

       File system mode	for Unix socket.  Not supported	on Windows.

       Default:	0777

       Group name to use for Unix socket.  Not supported on Windows.

       Default:	not set

       If set, specifies the Unix user to change to after startup.  Works only
       if PgBouncer is started as root or if it's already running as given us-
       er.  Not	supported on Windows.

       Default:	not set

       The  name  of the file to load user names and passwords from.  See sec-
       tion Authentication  file  format  (#authentication-file-format)	 below
       about details.

       Default:	not set

       HBA configuration file to use when auth_type is hba.

       Default:	not set

       How to authenticate users.

       pam    PAM  is  used to authenticate users, auth_file is	ignored.  This
	      method is	not compatible with databases using the	auth_user  op-
	      tion.   The service name reported	to PAM is "pgbouncer".	pam is
	      not supported in the HBA configuration file.

       hba    The actual authentication	type  is  loaded  from	auth_hba_file.
	      This  allows  different authentication methods for different ac-
	      cess paths, for example: connections over	Unix  socket  use  the
	      peer auth	method,	connections over TCP must use TLS.

       cert   Client must connect over TLS connection with a valid client cer-
	      tificate.	 The user name is then taken from the CommonName field
	      from the certificate.

       md5    Use  MD5-based  password check.  This is the default authentica-
	      tion method.   auth_file	may  contain  both  MD5-encrypted  and
	      plain-text  passwords.   If  md5	is configured and a user has a
	      SCRAM secret, then SCRAM authentication  is  used	 automatically

	      Use password check with SCRAM-SHA-256.  auth_file	has to contain
	      SCRAM secrets or plain-text passwords.

       plain  The clear-text password is sent over the wire.  Deprecated.

       trust  No authentication	is done.  The user name	must  still  exist  in

       any    Like  the	trust method, but the user name	given is ignored.  Re-
	      quires that all databases	are configured to log in as a specific
	      user.  Additionally, the console database	allows any user	to log
	      in as admin.

       Query to	load user's password from database.

       Direct access to	pg_shadow requires admin rights.  It's	preferable  to
       use a non-superuser that	calls a	SECURITY DEFINER function instead.

       Note  that  the query is	run inside the target database.	 So if a func-
       tion is used, it	needs to be installed into each	database.

       Default:	SELECT usename,	passwd FROM pg_shadow WHERE usename=$1

       If auth_user is set, then any user not specified	in auth_file  will  be
       queried	through	 the  auth_query query from pg_shadow in the database,
       using  auth_user.   The	password  of  auth_user	 will  be  taken  from

       Direct  access  to pg_shadow requires admin rights.  It's preferable to
       use a non-superuser that	calls a	SECURITY DEFINER function instead.

       Default:	not set

       Specifies when a	server connection can be reused	by other clients.

	      Server is	released back to pool after client  disconnects.   De-

	      Server is	released back to pool after transaction	finishes.

	      Server  is released back to pool after query finishes.  Transac-
	      tions spanning multiple statements are disallowed	in this	mode.

       Maximum number of client	connections allowed.  When increased then  the
       file  descriptor	limits should also be increased.  Note that the	actual
       number of file descriptors used is more than max_client_conn.  The the-
       oretical	maximum	used is:

	      max_client_conn +	(max pool_size * total databases * total users)

       if  each	 user  connects	 under	its own	user name to the server.  If a
       database	user is	specified in the connection string (all	users  connect
       under the same user name), the theoretical maximum is:

	      max_client_conn +	(max pool_size * total databases)

       The theoretical maximum should be never reached,	unless somebody	delib-
       erately crafts a	special	load for it.  Still, it	means you  should  set
       the number of file descriptors to a safely high number.

       Search  for  ulimit in your favorite shell man page.  Note: ulimit does
       not apply in a Windows environment.

       Default:	100

       How many	server connections to allow per	user/database  pair.   Can  be
       overridden in the per-database configuration.

       Default:	20

       Add more	server connections to pool if below this number.  Improves be-
       havior when usual load comes suddenly back after	period of total	 inac-
       tivity.	The value is effectively capped	at the pool size.

       Default:	0 (disabled)

       How   many   additional	connections  to	 allow	to  a  pool  (see  re-
       serve_pool_timeout).  0 disables.

       Default:	0 (disabled)

       If a client has not been	serviced in this many seconds, use  additional
       connections from	the reserve pool.  0 disables.

       Default:	5.0

       Do  not	allow more than	this many server connections per database (re-
       gardless	of user).  This	considers  the	PgBouncer  database  that  the
       client  has  connected  to, not the PostgreSQL database of the outgoing

       This can	also be	set per	database in the	[databases] section.

       Note that when you hit the limit, closing a client  connection  to  one
       pool  will  not immediately allow a server connection to	be established
       for another pool, because the server connection for the first  pool  is
       still open.  Once the server connection closes (due to idle timeout), a
       new server connection will immediately be opened	for the	waiting	pool.

       Default:	0 (unlimited)

       Do not allow more than this many	server connections per	user  (regard-
       less of database).  This	considers the PgBouncer	user that is associat-
       ed with a pool, which is	either the user	specified for the server  con-
       nection or in absence of	that the user the client has connected as.

       This can	also be	set per	user in	the [users] section.

       Note  that  when	 you hit the limit, closing a client connection	to one
       pool will not immediately allow a server	connection to  be  established
       for  another  pool, because the server connection for the first pool is
       still open.  Once the server connection closes (due to idle timeout), a
       new server connection will immediately be opened	for the	waiting	pool.

       Default:	0 (unlimited)

       By  default,  PgBouncer	reuses	server	connections  in	LIFO (last-in,
       first-out) manner, so that few connections get  the  most  load.	  This
       gives  best performance if you have a single server serving a database.
       But if there is TCP round-robin behind a	database IP address,  then  it
       is  better  if  PgBouncer  also	uses  connections in that manner, thus
       achieving uniform load.

       Default:	0

       By default, PgBouncer allows only parameters it can keep	 track	of  in
       startup packets:	client_encoding, datestyle, timezone and standard_con-
       forming_strings.	 All others parameters will raise an error.  To	 allow
       others  parameters, they	can be specified here, so that PgBouncer knows
       that they are handled by	the admin and it can ignore them.

       Default:	empty

       Disable Simple Query protocol (PQexec).	Unlike Extended	 Query	proto-
       col,  Simple  Query allows multiple queries in one packet, which	allows
       some classes of SQL-injection attacks.  Disabling it can	improve	 secu-
       rity.   Obviously  this means only clients that exclusively use the Ex-
       tended Query protocol will stay working.

       Default:	0

       Add the client host address and port to the  application	 name  setting
       set  on	connection start.  This	helps in identifying the source	of bad
       queries etc.  This logic	applies	only on	start of connection.   If  ap-
       plication_name  is later	changed	with SET, PgBouncer does not change it

       Default:	0

       Show location of	current	config file.  Changing it will make  PgBouncer
       use another config file for next	RELOAD / SIGHUP.

       Default:	file from command line

       Used on win32 service registration.

       Default:	pgbouncer

       Alias for service_name.

       Sets  how often the averages shown in various SHOW commands are updated
       and how often aggregated	statistics are written to  the	log  (but  see
       log_stats).  [seconds]

       Default:	60

       Toggles syslog on/off.  On Windows, the event log is used instead.

       Default:	0

       Under what name to send logs to syslog.

       Default:	pgbouncer (program name)

       Under what facility to send logs	to syslog.  Possibilities: auth, auth-
       priv, daemon, user, local0-7.

       Default:	daemon

       Log successful logins.

       Default:	1

       Log disconnections with reasons.

       Default:	1

       Log error messages the pooler sends to clients.

       Default:	1

       Write aggregated	statistics into	the log, every stats_period.  This can
       be disabled if external monitoring tools	are used to grab the same data
       from SHOW commands.

       Default:	1

       Increase	verbosity.  Mirrors the	"-v" switch on the command line.   Us-
       ing "-v -v" on the command line is the same as verbose=2.

       Default:	0

       Comma-separated	list of	database users that are	allowed	to connect and
       run all commands	on the console.	 Ignored when  auth_type  is  any,  in
       which case any user name	is allowed in as admin.

       Default:	empty

       Comma-separated	list of	database users that are	allowed	to connect and
       run read-only queries on	the console.  That means all SHOW commands ex-
       cept SHOW FDS.

       Default:	empty

       Query  sent to server on	connection release, before making it available
       to other	clients.  At that moment no transaction	is in progress	so  it
       should not include ABORT	or ROLLBACK.

       The query is supposed to	clean any changes made to the database session
       so that the next	client gets the	connection in  a  well-defined	state.
       The default is DISCARD ALL which	cleans everything, but that leaves the
       next client no pre-cached state.	 It can	be made	lighter, e.g.  DEALLO-
       CATE ALL	 to just drop prepared statements, if the application does not
       break when some state is	kept around.

       When transaction	pooling	is used, the server_reset_query	is  not	 used,
       as  clients must	not use	any session-based features as each transaction
       ends up in a different connection and thus  gets	 a  different  session

       Default:	DISCARD	ALL

       Whether	server_reset_query  should  be run in all pooling modes.  When
       this setting is off (default), the server_reset_query will be run  only
       in  pools  that	are in sessions-pooling	mode.  Connections in transac-
       tion-pooling mode should	not have any need for a	reset query.

       This setting is for working around broken setups	that run  applications
       that  use  session  features  over  a transaction-pooled	PgBouncer.  It
       changes non-deterministic breakage to deterministic  breakage:  Clients
       always lose their state after each transaction.

       Default:	0

       How  long  to keep released connections available for immediate re-use,
       without running sanity-check queries on it.  If 0 then the query	is ran

       Default:	30.0

       Simple do-nothing query to check	if the server connection is alive.

       If an empty string, then	sanity checking	is disabled.

       Default:	SELECT 1;

       Disconnect  a  server  in session pooling mode immediately or after the
       end of the current transaction if it is in "close_needed" mode (set  by
       RECONNECT,  RELOAD  that	 changes  connection settings, or DNS change),
       rather than waiting for the session end.	 In statement  or  transaction
       pooling	mode,  this  has  no effect since that is the default behavior

       If because of this setting a server connection is closed	before the end
       of  the client session, the client connection is	also closed.  This en-
       sures that the client notices that the session has been interrupted.

       This setting makes connection configuration changes take	effect	sooner
       if session pooling and long-running sessions are	used.  The downside is
       that client sessions are	liable to be interrupted  by  a	 configuration
       change,	so  client  applications  will	need  logic  to	 reconnect and
       reestablish session state.  But note that no transactions will be lost,
       because running transactions are	not interrupted, only idle sessions.

       Default:	0

       The  pooler  will  close	an unused server connection that has been con-
       nected longer than this.	 Setting it to 0 means the connection is to be
       used only once, then closed.  [seconds]

       Default:	3600.0

       If  a  server  connection  has been idle	more than this many seconds it
       will be dropped.	 If 0 then timeout is disabled.	 [seconds]

       Default:	600.0

       If connection and login won't finish in this amount of time,  the  con-
       nection will be closed.	[seconds]

       Default:	15.0

       If  login  failed,  because of failure from connect() or	authentication
       that pooler waits this much before retrying to connect.	[seconds]

       Default:	15.0

       If a client connects but	does not manage	to log in in  this  amount  of
       time, it	will be	disconnected.  Mainly needed to	avoid dead connections
       stalling	SUSPEND	and thus online	restart.  [seconds]

       Default:	60.0

       If the automatically created (via "*") database pools have been	unused
       this many seconds, they are freed.  The negative	aspect of that is that
       their statistics	are also forgotten.  [seconds]

       Default:	3600.0

       How long	the DNS	lookups	can be cached.	If a DNS lookup	returns	sever-
       al answers, PgBouncer will robin-between	them in	the meantime.  The ac-
       tual DNS	TTL is ignored.	 [seconds]

       Default:	15.0

       How long	error and NXDOMAIN DNS lookups can be cached.  [seconds]

       Default:	15.0

       Period to check if a zone serial	has changed.

       PgBouncer can collect DNS zones from host names (everything after first
       dot) and	then periodically check	if the zone serial changes.  If	it no-
       tices changes, all host names under that	zone are looked	up again.   If
       any host	IP changes, its	connections are	invalidated.

       Works  only  with UDNS and c-ares backends (--with-udns or --with-cares
       to configure).

       Default:	0.0 (disabled)

       The location of a custom	resolv.conf file.  This	is to allow specifying
       custom  DNS servers and perhaps other name resolution options, indepen-
       dent of the global operating system configuration.

       Requires	evdns (>= 2.0.3) or c-ares (>= 1.15.0) backend.

       The parsing of the file is done by the DNS  backend  library,  not  Pg-
       Bouncer,	so see the library's documentation for details on allowed syn-
       tax and directives.

       Default:	empty (use operating system defaults)

       TLS mode	to use for connections from clients.  TLS connections are dis-
       abled	by    default.	   When	  enabled,   client_tls_key_file   and
       client_tls_cert_file must be also configured to set up the key and cer-
       tificate	PgBouncer uses to accept client	connections.

	      Plain TCP.  If client requests TLS, it's ignored.	 Default.

       allow  If  client requests TLS, it is used.  If not, plain TCP is used.
	      If the client presents a client certificate, it is not  validat-

       prefer Same as allow.

	      Client must use TLS.  If not, the	client connection is rejected.
	      If the client presents a client certificate, it is not  validat-

	      Client must use TLS with valid client certificate.

	      Same as verify-ca.

       Private key for PgBouncer to accept client connections.

       Default:	not set

       Certificate for private key.  Clients can validate it.

       Default:	not set

       Root certificate	file to	validate client	certificates.

       Default:	not set

       Which  TLS  protocol  versions  are  allowed.  Allowed values: tlsv1.0,
       tlsv1.1,	       tlsv1.2,	       tlsv1.3.		Shortcuts:	   all
       (tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3),   secure  (tlsv1.2,tlsv1.3),	legacy

       Default:	secure

       Default:	fast

       Elliptic	Curve name to use for ECDH key exchanges.

       Allowed values: none (DH	is disabled), auto (256-bit ECDH), curve name.

       Default:	auto

       DHE key exchange	type.

       Allowed values: none (DH	 is  disabled),	 auto  (2048-bit  DH),	legacy
       (1024-bit DH).

       Default:	auto

       TLS mode	to use for connections to PostgreSQL servers.  TLS connections
       are disabled by default.

	      Plain TCP.  TCP is not even requested from the server.  Default.

       allow  FIXME: if	server rejects plain, try TLS?

       prefer TLS connection is	always requested first from  PostgreSQL,  when
	      refused  connection  will	be established over plain TCP.	Server
	      certificate is not validated.

	      Connection must go over TLS.  If server rejects it, plain	TCP is
	      not attempted.  Server certificate is not	validated.

	      Connection must go over TLS and server certificate must be valid
	      according	 to  server_tls_ca_file.   Server  host	 name  is  not
	      checked against certificate.

	      Connection must go over TLS and server certificate must be valid
	      according	to server_tls_ca_file.	Server host  name  must	 match
	      certificate information.

       Root certificate	file to	validate PostgreSQL server certificates.

       Default:	not set

       Private key for PgBouncer to authenticate against PostgreSQL server.

       Default:	not set

       Certificate for private key.  PostgreSQL	server can validate it.

       Default:	not set

       Which  TLS  protocol  versions  are  allowed.  Allowed values: tlsv1.0,
       tlsv1.1,	       tlsv1.2,	       tlsv1.3.		Shortcuts:	   all
       (tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3),   secure  (tlsv1.2,tlsv1.3),	legacy

       Default:	secure

       Default:	fast

       Setting the following timeouts can cause	unexpected errors.

       Queries running longer than that	are canceled.  This should be used on-
       ly  with	 slightly smaller server-side statement_timeout, to apply only
       for network problems.  [seconds]

       Default:	0.0 (disabled)

       Maximum time queries are	allowed	to spend waiting  for  execution.   If
       the  query  is not assigned to a	server during that time, the client is
       disconnected.  This is used to prevent unresponsive servers from	 grab-
       bing up connections.  [seconds]

       It  also	 helps when the	server is down or database rejects connections
       for any reason.	If this	is disabled, clients will  be  queued  indefi-

       Default:	120

       Client  connections  idling  longer  than this many seconds are closed.
       This should be larger than the  client-side  connection	lifetime  set-
       tings, and only used for	network	problems.  [seconds]

       Default:	0.0 (disabled)

       If  a client has	been in	"idle in transaction" state longer, it will be
       disconnected.  [seconds]

       Default:	0.0 (disabled)

       How many	seconds	to wait	for buffer  flush  during  SUSPEND  or	reboot
       (-R).  A	connection is dropped if the flush does	not succeed.

       Default:	10

       Internal	buffer size for	packets.  Affects size of TCP packets sent and
       general memory usage.  Actual libpq packets can be larger than this, so
       no need to set it large.

       Default:	4096

       Maximum size for	PostgreSQL packets that	PgBouncer allows through.  One
       packet is either	one query or one result	set row.  Full result set  can
       be larger.

       Default:	2147483647

       Backlog	argument  for  listen(2).   Determines how many	new unanswered
       connection attempts are kept in queue.  When the	queue is full, further
       new connections are dropped.

       Default:	128

       How  many  times	 to process data on one	connection, before proceeding.
       Without this limit, one connection with a big result set	can stall  Pg-
       Bouncer	for a long time.  One loop processes one pkt_buf amount	of da-
       ta.  0 means no limit.

       Default:	5

       Specifies whether to set	the socket option SO_REUSEPORT on TCP  listen-
       ing  sockets.   On some operating systems, this allows running multiple
       PgBouncer instances on the same host listening on  the  same  port  and
       having  the  kernel distribute the connections automatically.  This op-
       tion is a way to	get PgBouncer to use more CPU  cores.	(PgBouncer  is
       single-threaded and uses	one CPU	core per instance.)

       The  behavior  in detail	depends	on the operating system	kernel.	 As of
       this writing, this setting has the desired effect on (sufficiently  re-
       cent  versions  of)  Linux, DragonFlyBSD, and FreeBSD.  (On FreeBSD, it
       applies the socket option SO_REUSEPORT_LB instead.) Some	other  operat-
       ing systems support the socket option but it won't have the desired ef-
       fect: It	will allow multiple processes to bind to the same port but on-
       ly  one	of them	will get the connections.  See your operating system's
       setsockopt() documentation for details.

       On systems that don't support the socket	option at  all,	 turning  this
       setting on will result in an error.

       Each  PgBouncer	instance on the	same host needs	different settings for
       at least	unix_socket_dir	and pidfile, as	well as	 logfile  if  that  is
       used.  Also note	that if	you make use of	this option, you can no	longer
       connect to a specific PgBouncer instance	via TCP/IP, which  might  have
       implications for	monitoring and metrics collection.

       Default:	0

       For details on this and other TCP options, please see man 7 tcp.

       Default:	45 on Linux, otherwise 0

       Default:	not set

       Turns on	basic keepalive	with OS	defaults.

       On  Linux, the system defaults are tcp_keepidle=7200, tcp_keepintvl=75,
       tcp_keepcnt=9.  They are	probably similar on other operating systems.

       Default:	1

       Default:	not set

       Default:	not set

       Default:	not set

       Sets the	TCP_USER_TIMEOUT socket	option.	 This  specifies  the  maximum
       amount  of  time	in milliseconds	that transmitted data may remain unac-
       knowledged before the TCP connection is forcibly	closed.	 If set	to  0,
       then operating system's default is used.

       This is currently only supported	on Linux.

       Default:	0

       This contains key=value pairs where the key will	be taken as a database
       name and	the value as a libpq connection	string style list of key=value
       pairs.	Not  all features known	from libpq can be used (service=, .pg-
       pass), since the	actual libpq is	not used.

       The database name can contain characters	 _0-9A-Za-z  without  quoting.
       Names that contain other	characters need	to be quoted with standard SQL
       identifier quoting: double quotes, with "" for a	single instance	 of  a
       double quote.

       "*"  acts as a fallback database: if the	exact name does	not exist, its
       value is	taken as connection string for requested database.  Such auto-
       matically  created  database  entries  are cleaned up if	they stay idle
       longer than the time specified by the autodb_idle_timeout parameter.

       Destination database name.

       Default:	same as	client-side database name

       Host name or IP address to connect to.  Host names are resolved at con-
       nection	time,  the result is cached per	dns_max_ttl parameter.	When a
       host name's resolution changes, existing	server connections  are	 auto-
       matically  closed  when	they  are  released  (according	to the pooling
       mode), and new server connections immediately use the  new  resolution.
       If DNS returns several results, they are	used in	round-robin manner.

       Default:	not set, meaning to use	a Unix socket

       Default:	5432

       If  user=  is  set, all connections to the destination database will be
       done with the specified user, meaning that there	will be	only one  pool
       for this	database.

       Otherwise, PgBouncer logs into the destination database with the	client
       user name, meaning that there will be one pool per user.

       The length for password is limited to 160 characters maximum.

       If no password is specified here, the password from  the	 auth_file  or
       auth_query will be used.

       Override	of the global auth_user	setting, if specified.

       Set  the	 maximum size of pools for this	database.  If not set, the de-
       fault_pool_size is used.

       Set  additional	connections  for  this	database.   If	not  set,  re-
       serve_pool_size is used.

       Query  to be executed after a connection	is established,	but before al-
       lowing the connection to	be used	by any clients.	 If the	 query	raises
       errors, they are	logged but ignored otherwise.

       Set  the	 pool mode specific to this database.  If not set, the default
       pool_mode is used.

       Configure a database-wide maximum (i.e.	all pools within the  database
       will not	have more than this many server	connections).

       Ask specific client_encoding from server.

       Ask specific datestyle from server.

       Ask specific timezone from server.

       This  contains  key=value  pairs	 where the key will be taken as	a user
       name and	the value as a libpq connection	string style list of key=value
       pairs  of  configuration	 settings  specific for	this user.  Only a few
       settings	are available here.

       Set the pool mode to be used for	all connections	from  this  user.   If
       not set,	the database or	default	pool_mode is used.

       Configure  a  maximum  for the user (i.e.  all pools with the user will
       not have	more than this many server connections).

       The PgBouncer configuration file	can contain include directives,	 which
       specify	another	 configuration	file to	read and process.  This	allows
       splitting the configuration file	into physically	separate  parts.   The
       include directives look like this:

	      %include filename

       If  the	file name is not absolute path it is taken as relative to cur-
       rent working directory.

       PgBouncer needs its own user database.  The users  are  loaded  from  a
       text file in the	following format:

	      "username1" "password" ...
	      "username2" "md5abcdef012342345" ...
	      "username2" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"

       There  should  be  at least 2 fields, surrounded	by double quotes.  The
       first field is the user name and	the second is either a	plain-text,  a
       MD5-hashed  password, or	a SCRAM	secret.	 PgBouncer ignores the rest of
       the line.

       PostgreSQL MD5-hashed password format:

	      "md5" + md5(password + username)

       So  user	 admin	with  password	1234  will  have  MD5-hashed  password

       PostgreSQL SCRAM	secret format:


       See the PostgreSQL documentation	and RFC	5803 for details on this.

       The  passwords  or  secrets stored in the authentication	file serve two
       purposes.  First, they are used to verify  the  passwords  of  incoming
       client  connections,  if	a password-based authentication	method is con-
       figured.	 Second, they are used as the passwords	for  outgoing  connec-
       tions  to  the  backend	server,	 if  the backend server	requires pass-
       word-based authentication (unless the password is specified directly in
       the database's connection string).  The latter works if the password is
       stored in plain text or MD5-hashed.  SCRAM secrets can only be used for
       logging into a server if	the client authentication also uses SCRAM, the
       PgBouncer database definition does not specify a	 user  name,  and  the
       SCRAM  secrets  are  identical  in  PgBouncer and the PostgreSQL	server
       (same salt and iterations, not merely the same password).  This is  due
       to an inherent security property	of SCRAM: The stored SCRAM secret can-
       not by itself be	used for deriving login	credentials.

       The authentication file can be written by hand, but it's	also useful to
       generate	 it  from  some	 other	list  of  users	 and  passwords.   See
       ./etc/ for a sample script to generate the authentication file
       from the	pg_shadow system table.	 Alternatively,	use auth_query instead
       of auth_file to avoid having  to	 maintain  a  separate	authentication

       It   follows  the  format  of  the  PostgreSQL  pg_hba.conf  file  (see

       o Supported record types: local,	host, hostssl, hostnossl.

       o Database field: Supports all, sameuser, @file,	multiple  names.   Not
	 supported: replication, samerole, samegroup.

       o User name field: Supports all,	@file, multiple	names.	Not supported:

       o Address field:	Supports IPv4, IPv6.  Not supported: DNS names,	domain

       o Auth-method  field:  Only  methods supported by PgBouncer's auth_type
	 are supported,	except any and pam, which only	work  globally.	  User
	 name map (map=) parameter is not supported.

       Minimal config:

	      template1	= host= dbname=template1 auth_user=someuser

	      pool_mode	= session
	      listen_port = 6432
	      listen_addr =
	      auth_type	= md5
	      auth_file	= users.txt
	      logfile =	pgbouncer.log
	      pidfile =
	      admin_users = someuser
	      stats_users = stat_collector

       Database	defaults:


	      ;	foodb over Unix	socket
	      foodb =

	      ;	redirect bardb to bazdb	on localhost
	      bardb = host= dbname=bazdb

	      ;	access to destination database will go with single user
	      forcedb =	host= port=300	user=baz password=foo client_encoding=UNICODE datestyle=ISO

       Example of a secure function for	auth_query:

	      CREATE OR	REPLACE	FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
	      RETURNS record AS	$$
		  SELECT usename, passwd FROM pg_catalog.pg_shadow
		  WHERE	usename	= i_username INTO uname, phash;
	      REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
	      GRANT EXECUTE ON FUNCTION	pgbouncer.user_lookup(text) TO pgbouncer;

       pgbouncer(1) - man page for general usage, console commands


1.14.0							      PGBOUNCER.INI(5)


Want to link to this manual page? Use this URL:

home | help