Understanding the parameters involved in PostgreSQL’s connection string and authentication process is essential for managing database access and server communication. These settings play a critical role in establishing secure connections, verifying user credentials, granting access to the server, monitoring query activity, and configuring key server properties such as listen_addresses, port, and max_connections.
Two important parameters related to authentication are:
authentication_timeout: This parameter defines the maximum time the server waits for a client to authenticate. If the client fails to complete authentication within this time frame, postgres terminates the connection attempt.
password_encryption: This setting determines how passwords are stored and transmitted. Common values include md5, scram-sha-256, and on (which enables encryption using the default method). Secure password encryption is crucial to protect user credentials and prevent unauthorized access.
listen_addresses:
The listen_addresses configuration parameter determines which TCP/IP addresses the server listens on for incoming connections. Introduced in PostgreSQL 8.0, it accepts a comma-separated list of IP addresses or hostnames, including special values like 0.0.0.0,::, and *.
What Does listen_addresses Do?
- Accepts a comma-separated list of IP addresses or hostnames.
- Supports special values:
- * — listen on all IP addresses (IPv4 and IPv6).
- 0.0.0.0 — all IPv4 addresses.
- :: — all IPv6 addresses.
If left blank, the server only listens via Unix domain sockets (no TCP access).Requires a server restart to take effect.
How to check the current value:
postgres=# show listen_addresses ;
listen_addresses
------------------
localhost
(1 row)How to Set listen_addresses
You can modify the parameter using the ALTER SYSTEM command:
postgres=# alter system set listen_addresses to '*';
ALTER SYSTEMTo apply the changes to PostgreSQL server, restart it.
Verifying the Change
After restart, re-check the parameter:
postgres=# show listen_addresses ;
listen_addresses
------------------
*
(1 row)This information can also be queried with the pg_settings system catalog for detailed metadata:
postgres=# select * from pg_settings where name ='listen_addresses';
-[ RECORD 1 ]---+-----------------------------------------------------
name | listen_addresses
setting | *
unit |
category | Connections and Authentication / Connection Settings
short_desc | Sets the host name or IP address(es) to listen to.
extra_desc |
context | postmaster
vartype | string
source | configuration file
min_val |
max_val |
enumvals |
boot_val | localhost
reset_val | *
sourcefile | /var/lib/pgsql/16/data/postgresql.auto.conf
sourceline | 20
pending_restart | fKey fields from pg_settings:
- name: The parameter name (listen_addresses)
- setting: The current value
- category: Connections and Authentication / Connection Settings
- context: postmaster means this setting requires a full restart
- pending_restart: f (false) means the current value is active
Important:
Setting listen_addresses = ‘*‘ allows PostgreSQL to accept connections from any network interface. This is useful for remote access but requires proper firewall rules and pg_hba.conf configuration to secure the server. Only use ‘“*” in trusted environments or with strong access controls.
Port :
The port parameter in PostgreSQL defines the TCP/IP port on which the server listens for incoming connections, with a default value of 5432. Introduced in PostgreSQL 7.1, this setting is critical for client-server communication and must be an integer between 1 and 65535. Since PostgreSQL can only listen on one port at a time, multiple port configurations require external tools like reverse proxies. The specified port also influences the Unix socket name, and any changes to this parameter require a server restart. For most setups, sticking with the default port ensures compatibility and ease of access.
How to check the current value:
postgres=# show port ;
port
------
5432
(1 row)How to Set Port
You can modify the parameter using the ALTER SYSTEM command:
postgres=# alter system set port to '5433';
ALTER SYSTEMTo apply the changes to the PostgreSQL server, restart it.
Verifying the Change
After the restart, re-check the parameter:
postgres=# alter system set port to '5433';
ALTER SYSTEMThis information can also be queried with the pg_settings system catalog for detailed metadata:
postgres=# select * from pg_settings where name ='port';
-[ RECORD 1 ]---+-----------------------------------------------------
name | port
setting | 5433
unit |
category | Connections and Authentication / Connection Settings
short_desc | Sets the TCP port the server listens on.
extra_desc |
context | postmaster
vartype | integer
source | configuration file
min_val | 1
max_val | 65535
enumvals |
boot_val | 5432
reset_val | 5433
sourcefile | /var/lib/pgsql/16/data/postgresql.auto.conf
sourceline | 21
pending_restart | fKey fields from pg_settings:
- name: The parameter name (port)
- setting: The current value (5433)
- category: Connections and Authentication / Connection Settings
- context: postmaster requires a full restart
- pending_restart: f (false) current value is active
Important:
Changing the port from the default 5432 to 5433 (or any other value) can avoid conflicts with other PostgreSQL instances, but all client connection settings (like connection strings and firewall rules) must be updated accordingly.
max_connections in PostgreSQL:
The max_connections parameter controls the maximum number of simultaneous client connections allowed to a PostgreSQL database server. This is set to 100 by default, although the actual default can be lower depending on the operating system’s kernel settings at the time of database initialization. This setting is vital for managing server load and ensuring optimal performance, especially in environments with multiple users or applications accessing the database concurrently. Any changes to this parameter require a server restart to take effect.
How to check the current value:
postgres=# show max_connections ;
max_connections
-----------------
100
(1 row)Set max_connections to 200:
postgres=# alter system set max_connections to '200';
ALTER SYSTEMTo apply the changes to the PostgreSQL server, restart it.
Verifying the Change
After the restart, re-check the parameter:
postgres=# show max_connections ;
max_connections
-----------------
200
(1 row)This information can also be queried with the pg_settings system catalog for detailed metadata:
postgres=# select * from pg_settings where name ='max_connections';
-[ RECORD 1 ]---+-----------------------------------------------------
name | max_connections
setting | 200
unit |
category | Connections and Authentication / Connection Settings
short_desc | Sets the maximum number of concurrent connections.
extra_desc |
context | postmaster
vartype | integer
source | configuration file
min_val | 1
max_val | 262143
enumvals |
boot_val | 100
reset_val | 200
sourcefile | /var/lib/pgsql/16/data/postgresql.auto.conf
sourceline | 22
pending_restart | fKey fields from pg_settings:
- name: The parameter name (max_connections)
- setting: The current value (200)
- category: Connections and Authentication / Connection Settings
- context: Postmaster requires a full restart
- pending_restart: f (false), current value is active
Important:
Increasing max_connections may require tuning shared memory parameters (like shared_buffers and max_locks_per_transaction) to prevent performance degradation or startup failure.
authentication_timeout:
The authentication_timeout parameter defines the maximum amount of time a client is allowed to complete the authentication process when connecting to the PostgreSQL server. Introduced with a default value of 60 seconds (1 minute) starting from PostgreSQL 8.4, earlier versions did not specify a default. The maximum allowable setting for this parameter is 600 seconds (10 minutes). It can be configured either in the postgresql.conf file or supplied as a command-line argument when starting the server. Categorized under Authentication settings in recent PostgreSQL versions, authentication_timeout plays a crucial role in preventing hung authentication sessions and protecting server resources.
How to check the current value:
postgres=# show authentication_timeout ;
authentication_timeout
------------------------
1min
(1 row)How to Set authentication_timeout
You can modify the parameter using the ALTER SYSTEM command:
postgres=# alter system set authentication_timeout to '2min';
ALTER SYSTEMTo apply the changes to the PostgreSQL server, reload it.
Verifying the Change
After reload, re-check the parameter:
postgres=# show authentication_timeout ;
authentication_timeout
------------------------
2min
(1 row)This information can also be queried with the pg_settings system catalog for detailed metadata:
postgres=# select * from pg_settings where name ='authentication_timeout';
-[ RECORD 1 ]---+-----------------------------------------------------------------
name | authentication_timeout
setting | 120
unit | s
category | Connections and Authentication / Authentication
short_desc | Sets the maximum allowed time to complete client authentication.
extra_desc |
context | sighup
vartype | integer
source | configuration file
min_val | 1
max_val | 600
enumvals |
boot_val | 60
reset_val | 120
sourcefile | /var/lib/pgsql/16/data/postgresql.auto.conf
sourceline | 23
pending_restart | fKey fields from pg_settings:
- name: The parameter name (authentication_timeout)
- setting: The current value (120 seconds)
- category: Connections and Authentication / Authentication
- context: sighup requires a reload, not a restart
- pending_restart: f (false) current value is active
Important:
A higher value may improve login success in slow or high-latency networks, but can expose the server to longer DoS windows if unauthenticated connections are abused.
password_encryption:
The password_encryption parameter determines how PostgreSQL encrypts passwords when they are stored or transmitted during authentication. It is one of the most important security-related settings, as it directly impacts how user credentials are protected. This parameter is essential for verifying user identities during login and ensuring secure access to the database. Introduced in PostgreSQL 7.2, it has evolved significantly across versions. In PostgreSQL versions 7.2 through 9.6, the default value was 'on', which enabled password encryption without specifying the method. Starting with PostgreSQL 10, the default was updated to md5, and from PostgreSQL 14 onward, it changed to the more secure scram-sha-256 algorithm.
How to check the current value:
postgres=# show password_encryption ;
password_encryption
---------------------
scram-sha-256
(1 row)
How to Set password_encryption
You can modify the parameter using the ALTER SYSTEM command:
postgres=# alter system set password_encryption to 'md5';
ALTER SYSTEMTo apply the changes to PostgreSQL server, reload it.
Verifying the Change
After reload, re-check the parameter:
postgres=# show password_encryption ;
password_encryption
---------------------
md5
(1 row)This information can also be queried with the pg_settings system catalog for detailed metadata:
postgres=# select * from pg_settings where name ='password_encryption';
-[ RECORD 1 ]---+------------------------------------------------
name | password_encryption
setting | md5
unit |
category | Connections and Authentication / Authentication
short_desc | Chooses the algorithm for encrypting passwords.
extra_desc |
context | user
vartype | enum
source | configuration file
min_val |
max_val |
enumvals | {md5,scram-sha-256}
boot_val | scram-sha-256
reset_val | md5
sourcefile | /var/lib/pgsql/16/data/postgresql.auto.conf
sourceline | 24
pending_restart | fKey fields from pg_settings:
- name: The parameter name (password_encryption)
- Setting: The current value (md5)
- category: Connections and Authentication / Authentication
- context: user no restart required
- pending_restart: f (false), current value is active
Important:
md5 is less secure than scram-sha-256. Use scram-sha-256 in environments where stronger password security is required.
Conclusion:
These configuration parameters those tied to connection handling and authentication, are vital for database administrators aiming to build secure, scalable, and stable environments. Parameters like listen_addresses, port, max_connections, authentication_timeout, and password_encryption are fundamental in controlling how the server interacts with clients, handles credentials, and maintains performance. These settings are configured to ensure that PostgreSQL remains resilient, secure, and efficient across a wide range of deployment scenarios.