5.2. Connections and Authentication

5.2.1. Connection Settings

listen_addresses (string)

Specifies the hostname or IP address, on which Pgpool-II will accept TCP/IP connections. '*' accepts all incoming connections. '' disables TCP/IP connections. Default is 'localhost'. Connections via UNIX domain socket are always accepted.

This parameter can only be set at server start.

port (integer)

The port number used by Pgpool-II to listen for connections. Default is 9999.

This parameter can only be set at server start.

socket_dir (string)

The directory where the UNIX domain socket accepting connections for Pgpool-II will be created. Default is /tmp. Be aware that this socket might be deleted by a cron job. We recommend to set this value to /var/run or such directory.

This parameter can only be set at server start.

pcp_listen_addresses (string)

Specifies the hostname or IP address, on which pcp process will accept TCP/IP connections. * accepts all incoming connections. "" disables TCP/IP connections. Default is *. Connections via UNIX domain socket are always accepted.

This parameter can only be set at server start.

pcp_port (integer)

The port number used by PCP process to listen for connections. Default is 9898.

This parameter can only be set at server start.

pcp_socket_dir (string)

The directory where the UNIX domain socket accepting connections for PCP process will be created. Default is /tmp. Be aware that this socket might be deleted by a cron job. We recommend to set this value to /var/run or such directory.

This parameter can only be set at server start.

num_init_children (integer)

The number of preforked Pgpool-II server processes. Default is 32. num_init_children is also the concurrent connections limit to Pgpool-II from clients. If more than num_init_children clients try to connect to Pgpool-II, they are blocked (not rejected with an error, like PostgreSQL) until a connection to any Pgpool-II process is closed. Up to listen_backlog_multiplier* num_init_children can be queued.

The queue is inside the kernel called "listen queue". The length of the listen queue is called "backlog". There is an upper limit of the backlog in some systems, and if num_init_children*listen_backlog_multiplier exceeds the number, you need to set the backlog higher. Otherwise, following problems may occur in heavy loaded systems: 1) connecting to Pgpool-II fails 2) connecting to Pgpool-II is getting slow because of retries in the kernel. You can check if the listen queue is actually overflowed by using "netstat -s" command. If you find something like:

		535 times the listen queue of a socket overflowed
	      

then the listen queue is definitely overflowed. You should increase the backlog in this case (you will be required a super user privilege).

		# sysctl net.core.somaxconn
		net.core.somaxconn = 128
		# sysctl -w net.core.somaxconn = 256
	      

You could add following to /etc/sysctl.conf instead.

		net.core.somaxconn = 256
	      

Number of connections to each PostgreSQL is roughly max_pool*num_init_children.

However, canceling a query creates another connection to the backend; thus, a query cannot be canceled if all the connections are in use. If you want to ensure that queries can be canceled, set this value to twice the expected connections.

In addition, PostgreSQL allows concurrent connections for non superusers up to max_connections - superuser_reserved_connections.

In summary, max_pool, num_init_children, max_connections, superuser_reserved_connections must satisfy the following formula:

	      max_pool*num_init_children <= (max_connections - superuser_reserved_connections) (no query canceling needed)
	      max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections) (query canceling needed)
	    

This parameter can only be set at server start.

5.2.2. Authentication Settings

enable_pool_hba (boolean)

If true, Pgpool-II will use the pool_hba.conf for the client authentication. See Section 6.1 for details on how to configure pool_hba.conf for client authentication. Default is false.

This parameter can be changed by reloading the Pgpool-II configurations.

pool_passwd (string)

Specify the password file name for authentication. Default value is "pool_passwd". Specifying '' (empty) disables the use of password file.

Passwords can be stored in the pool_passwd file using three formats. AES256 encrypted format, plain text format and md5 format. Pgpool-II identifies the password format type by it's prefix, so each password entry in the pool_passwd must be prefixed as per the password format.

To store the password in the plain text format use TEXT prefix. For example. to store clear text password string "mypassword" in the pool_passwd, prepend the password string with TEXT prefix. e.g. TEXTmypassword

similarly md5 hashed passwords must be prefixed with md5 and AES256 encrypted password types can be stored using AES prefix. see Section 6.4 for more details on using AES256 encrypted passwords.

In the absence of a valid prefix, Pgpool-II will be considered the string as a plain text password.

This parameter can only be set at server start.

allow_clear_text_frontend_auth (boolean)

If PostgreSQL backend servers require md5 or SCRAM authentication for some user's authentication but the password for that user is not present in the "pool_passwd" file, then enabling allow_clear_text_frontend_auth will allow the Pgpool-II to use clear-text-password authentication with frontend clients to get the password in plain text form from the client and use it for backend authentication.

Default is false.

This parameter can be changed by reloading the Pgpool-II configurations.

Note: allow_clear_text_frontend_auth only works when enable_pool_hba is not enabled

authentication_timeout (integer)

Specify the timeout in seconds for Pgpool-II authentication. Specifying 0 disables the time out. Default value is 60.

This parameter can be changed by reloading the Pgpool-II configurations.