5.2. Connections and Authentication

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.

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

This parameter can only be set at server start.

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.

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 localhost. Connections via UNIX domain socket are always accepted.

This parameter can only be set at server start.

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

This parameter can only be set at server start.

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.

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 unless reserved_connections is set to 1 or more. 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:

• connecting to Pgpool-II fails

• 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.

When this parameter is set to 1 or greater, incoming connections from clients are not accepted with error message "Sorry, too many clients already", rather than blocked if the number of current connections from clients is more than (num_init_children - reserved_connections). For example, if reserved_connections = 1 and num_init_children = 32, then the 32th connection from a client will be refused. This behavior is similar to PostgreSQL and good for systems on which the number of connections from clients is large and each session may take long time. In this case length of the listen queue could be very long and may cause the system unstable. In this situation setting this parameter to non 0 is a good idea to prevent the listen queue becomes very long.

If this parameter is set to 0, no connection from clients will be refused. The default value is 0. This parameter can only be set at server start.

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.

Specify the path (absolute or relative) to password file for authentication. Default value is "pool_passwd". A relative path will be interpreted with respect to the directory where configuration file is placed. 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.

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.

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.