5.2. Connections and Authentication

Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. The entry 0.0.0.0 allows listening for all IPv4 addresses and :: allows listening for all IPv6 addresses. If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it. The default value is localhost, which allows only local TCP/IP "loopback" connections to be made. While client authentication (Section 6.1) allows fine-grained control over who can access the server, listen_addresses controls which interfaces accept connection attempts, which can help prevent repeated malicious connection requests on insecure network interfaces. This parameter can only be set at server start.

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(s) accepting connections for Pgpool-II will be created. Multiple sockets can be created by listing multiple directories separated by commas. Default is /tmp. Be aware that this sockets 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.

Sets the owning group of the Unix-domain socket(s). (The owning user of the sockets is always the user that starts the server.) In combination with the parameter unix_socket_permissions this can be used as an additional access control mechanism for Unix-domain connections. By default this is the empty string, which uses the default group of the server user.

This parameter can only be set at server start.

Sets the access permissions of the Unix-domain socket(s). Unix-domain sockets use the usual Unix file system permission set. The parameter value is expected to be a numeric mode specified in the format accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).)

The default permissions are 0777, meaning anyone can connect. Reasonable alternatives are 0770 (only user and group, see also unix_socket_group) and 0700 (only user). (Note that for a Unix-domain socket, only write permission matters, so there is no point in setting or revoking read or execute permissions.)

This parameter can only be set at server start.

Specifies the TCP/IP address(es) on which the pcp server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. The entry 0.0.0.0 allows listening for all IPv4 addresses and :: allows listening for all IPv6 addresses. If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it. The default value is localhost, which allows only local TCP/IP "loopback" connections to be made. While client authentication (Section 6.1) allows fine-grained control over who can access the server, pcp_listen_addresses controls which interfaces accept connection attempts, which can help prevent repeated malicious connection requests on insecure network interfaces. 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.