Postgresql listen_addresses

In this PostgreSQL tutorial, we will learn about “PostgreSQL listen_addresses” and how to connect to PostgreSQL from any IP address across different environments, using various examples.

Before beginning, we need to know “What is listen_address?” listen_addresses is found in the section of the PostgreSQL.conf file. It enables the database server to listen for incoming connections on the specified IP addresses.

The following is the line from the PostgreSQL.conf file.

# - Connection Settings -

listen_addresses = '*'	# what IP address(es) to listen on;
			# comma-separated list of addresses;                  
			# defaults to 'localhost';use'*' for all
                        # (change requires restart)

After modifying the listen_addresses command in PostgreSQL.conf, restart the PostgreSQL server.

How to Configure listen_addresses in PostgreSQL

Let’s explore different ways to configure this important parameter:

Approach 1: Editing PostgreSQL.conf Directly

This is the most straightforward approach I use for one-time configurations:

Locate your postgresql.conf file:

sudo find / -name postgresql.conf

2. Open the file with your preferred text editor:

sudo nano /etc/postgresql/13/main/postgresql.conf

3. Find the listen_addresses parameter and modify it according to your needs:

listen_addresses = '*'    # Listen on all interfaces

Or for specific addresses:

listen_addresses = 'localhost,192.168.1.100'

4. Save the file and restart PostgreSQL:

sudo systemctl restart postgresql

Approach 2: Using the ALTER SYSTEM Command

For dynamic environments or when you need to make changes programmatically, I recommend using PostgreSQL’s ALTER SYSTEM command:

ALTER SYSTEM SET listen_addresses TO '*';

Then reload the configuration:

SELECT pg_reload_conf();

Note that some settings like listen_addresses Require a complete restart, not just a reload:

sudo systemctl restart postgresql

Format Options

The listen_addresses parameter can accept several different formats, giving you flexible control over your PostgreSQL deployment:

Hostname

listen_addresses = 'db.example.com'

PostgreSQL will resolve this hostname to an IP address and listen on the corresponding interface.

IPv4 Address

listen_addresses = '192.168.1.100'

Listen on the specific IPv4 interface.

IPv6 Address

listen_addresses = '::1'

Listen on the IPv6 loopback interface.

All IPv4 Addresses

listen_addresses = '0.0.0.0'

Listen on all available IPv4 interfaces.

All IPv6 Addresses

listen_addresses = '::'

Listen on all available IPv6 interfaces.

All Available Addresses

listen_addresses = '*'

Listen on all available interfaces (both IPv4 and IPv6).

Multiple Addresses

listen_addresses = 'localhost, 192.168.1.100, 10.0.0.5'

Listen to the specified list of interfaces.

Example

In PostgreSQL, listen_addresses controls the IPs/addresses that belong to a different client application.

Let’s understand this with an example. For that, we will use PostgreSQL installed on two different operating systems.

We will connect from the Ubuntu machine to the PostgreSQL server running on Windows, so we need to perform two steps on the machine where the PostgreSQL database is running.

Read PostgreSQL Like With Examples

Adding Client Authentication record in pg_hba.conf file.

Open the pg_hba.conf file on Windows (C:\Program Files\PostgreSQL\13\data) using Notepad, add the following line according to your IP addresses, and save the file.

# "local" is for Unix domain socket connections only  
host    all      all         0.0.0.0/0      trust

Setting the Listen Address in PostgreSQL.conf

Now go to the location on your Windows (C: Program FilesPostgreSQL13data) and open the PostgreSQL.conf file. By default, the listen address will be localhost.

If it is an asterisk (*), then we don’t need to make any changes.

Let’s go to the Ubuntu machine or another machine and test the remote connection using the command below.

psql -U postgres -h 192.168.20.129

You have now successfully logged in to the Postgres database remotely.

Read: How to create a table in PostgreSQL

Connecting to the PostgreSQL database server from multiple clients or multiple IP addresses

We can connect to the PostgreSQL database server from multiple clients or multiple IP addresses.

We will need three machines: one for the PostgreSQL server and two for connecting to the PostgreSQL database server.

The PostgreSQL server is installed on the Ubuntu machine, and we will connect to the PostgreSQL database server from Debian and Windows machines.

First, find the file pg_hba.conf from the Ubuntu machine where the PostgreSQL server is installed.

Open the file from your terminal using the command below.

sudo nano /etc/postgresql/12/main/pg_hba.conf

Add the following connection record for client authentication.

host   all     all    0.0.0.0/0    trust

Second, find the PostgreSQL.conf file on the same machine.

Open the file from your terminal using the command below.

sudo nano /etc/postgresql/12/main/postgresql.conf

Add the IP addresses of the Debian and Windows machines, or if we want to connect with multiple clients, enter all IP addresses here, separated by commas.

postgresql listen_addresses configuration

As shown in the above picture, we have provided three IP addresses: localhost, 92.168.81.135, where Windows is running, and 192.168.264.1, where Debian is running.

Save the file and restart the PostgreSQL database server using the command below on the Ubuntu machine.

systemctl restart postgresql

Go to the Windows machine and open the Command Prompt (CMD), then type the command below to connect to the database running on the Ubuntu machine.

psql -U postgres -h 92.168.81.135

Now go to the Debian machine, open your terminal, and type the following command.

psql -U postgres -h 912.168.253.1

As shown in the output above, we have successfully connected to the PostgreSQL database server using both the machine’s Windows and Debian operating systems.

Read: How to connect to a PostgreSQL database

Configuring the pg_hba.conf file

In PostgreSQL, the pg_hba.conf file is a configuration file that helps control client authentication.

pg_hba.conf and is stored in the database cluster’s data directory, where HBA stands for host-based authentication.

When the data directory is initialized by initdb, at that time, the pg_hba. Conf file is installed.

The pg_hba file contains a set of records, each consisting of fields separated by spaces and/ or tabs.

Each record represents a connection type, a client IP address range (if applicable for the connection type), a database name, a username, and the authentication method for connections matching these parameters.

The first record with a matching connection type, client address, requested database, and username is used for authentication.

A record in pg_hba.conf file can be in any of the following formats.

local      database  user  auth-method
  
host       database  user  address  auth-method  

hostssl    database  user  address  auth-method  

hostnossl  database  user  address  auth-method  

host       database  user  IP-address  IP-mask  auth-method  

hostssl    database  user  IP-address  IP-mask  auth-method  

hostnossl  database  user  IP-address  IP-mask  auth-method

The meaning of the above fields is as follows:

local
This record matches connection attempts using Unix-domain sockets. Without this kind of record, Unix-domain socket connections are not permitted.
host
This record matches connection attempts made using TCP/IP. The host records will match SSL or non-SSL connection attempts.
hostssl
It is the same as the host, but it will connect only when the connection is SSL encrypted. You must have a server with SSL encryption for this option.
hostnossl
It is the opposite of the hostssl record and only matches a connection that does not use SSL encryption.
database
Specifies which database name(s) this record matches. The value all specifies that it will match all databases.
user
Specifies which database user name(s) this record matches. The value all specifies that it will match all users.
address
Specifies the client machine address(es) that the record will match with this.

Read: How to Restart PostgreSQL

Connecting to the PostgreSQL database using the CIDR notation

In PostgreSQL, we will use CIDR notation 192.168.253.0 / 24, where we want to connect to the PostgreSQL database, which is hosted on IP addresses, something 192.168.1.105 from a client machine with IP Address 192.168.1.128

Open the pg_hba.conf file in any text editor.

sudo nano pg_hba.conf

Client authentication allows or restricts entry according to the format below.

[TYPE] [DATABASE] [USER] [ADDRESS] [METHOD]

Find a line that resembles.

host all all 127.0.0.1/32 md5

And add the following line after the above entry.

host all all 192.168.1.0/24 trust

The added line above denotes that client authentication is allowed from hosts with IP addresses within the range 192.168.1.1 to 192.168.1.254 to any/all databases on the PostgreSQL database server. This applies to any database that exists for a user using the trust authentication mode.

Note: You must restart the PostgreSQL database server to apply these changes.

sudo service postgresql start

After a restart, your database will allow connections from remote client machines.

Read: PostgreSQL WHERE IN with examples

Connecting to the PostgreSQL database from anywhere or using any IP address.

In PostgreSQL, we connect to the PostgreSQL database server from anywhere or using any IP address.

An exceptional value that we provide for listening addresses is called an asterisk ( * ). If we specify this value, it means the PostgreSQL server can accept all the incoming connections from different IP addresses.

For Windows, navigate to the C:\Program Files\PostgreSQL\13\data folder and open the postgresql.conf file using any text editor.

For Linux, navigate to the/etc/PostgreSQL/13/main folder and open the postgresql.conf file using any text editor.

In the above output, if the listen_addresses is set to localhost, then change it to asterisk*.

‘*’: Asterisk represents all IP addresses.

After making changes, restart the PostgreSQL database for the changes to take effect.

Now we can connect it from any application, client, and IP address.

Best Practices

Based on my experience deploying PostgreSQL in production environments, I recommend the following best practices:

Default to Restrictive Settings: Start with the minimum access required and expand only as needed
Layer Your Security: Never rely solely on listen_addresses for security
Document Your Configuration: Maintain documentation explaining why specific interfaces are enabled.
Regular Auditing: Periodically review which interfaces are enabled and whether they’re still needed
Utilize Infrastructure as Code: Manage PostgreSQL configurations through tools such as Ansible, Chef, or Terraform.
Monitor Connection Attempts: Set up logging and alerting for connection attempts from an unexpected source.s

Conclusion

Properly configuring PostgreSQL’s listen_addresses parameter is crucial for striking a balance between security and accessibility in your database deployment. While the default ‘localhost’ setting is secure, most real-world applications require more flexible configurations.

By understanding the available options and following the approaches outlined in this article, you can easily configure the listen_addresses parameter.

You may also find the following PostgreSQL tutorials helpful.

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.