Postgresql listen_addresses

In this Postgresql tutorial, we will learn about “Postgresql listen_addresses” how to connect to Postgresql from any IP address using different environments and we will do a lot’s example.

• Postgresql listen_addresses example
• Postgresql listen_addresses multiple
• Postgresql listen_addresses pg_hba.conf
• Postgresql listen_addresses cidr
• Postgresql listen_addresses address all

Before beginning, we need to know the “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.

Postgresql listen_addresses example

In Postgresql, listen_addresses control the IPs/addresses that belong to a different client application.

Let’s understand with an example, For that, we are going to use Postgresql installed on two different operating systems.

We are going to 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 running.

Read PostgreSQL Like With Examples

Adding Client Authentication record in pg_hba.conf file.

Open pg_hba.conf file on windows (C:\Program Files\PostgreSQL\13\data) using the 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 location on your windows (C:\Program Files\PostgreSQL\13\data) open postgresql.conf file, by default the listen-address will be localhost.

if it is asterisk *, then we don’t need to change anything.

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

psql -U postgres -h 192.168.20.129

Now you have successfully logged in Postgres database remotely.

Read: How to create a table in PostgreSQL

Postgresql listen_addresses multiple and list

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

We are going to need three machines, one for the Postgresql server and the other two for making connections with the Postgresql database server.

So Postgresql server is installed on Ubuntu machine and we will connect with the Postgresql database server from Debian and Window 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 below command.

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

And add the following connection record for the 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 below command.

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

Add the IP addresses of Debian and the Windows machines or if we want to connect with many clients, then enter all IP addresses here with comma-separated values.

As we can see 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 below command on the Ubuntu machine.

systemctl restart postgresql

Go to the Windows machine and open cmd, and type the below command to connect 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 below command.

psql -U postgres -h 912.168.253.1

As we can see in the above output, we have successfully connected to the Postgresql database server using the machines Windows and Debian.

Read: How to connect to PostgreSQL database

Postgresql listen_addresses pg_hba.conf

In Postgresql, the pg_hba.conf file is a configuration file that helps in controlling the 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 pg_hba conf file is installed.

pg_hba file contains a set of records, each record consists of fields that are separated by spaces and/ or tabs.

Each record represents a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters.

The first record with a matching connection type, client address, requested database, and user name is used to perform 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:

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

Read: How to Restart PostgreSQL

Postgresql listen_addresses cidr

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 pg_hba.conf file in any text editor.

sudo nano pg_hba.conf

Client authentication allows/restricts entry follows the below format.

[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 above-added line denotes that client authentication is allowed from the host which has an IP address between the range 192.168.1.1 and 192.168.1.254 to any/all database on the PostgreSQL database server and can be any database that exists user using trust authentication mode.

Note: you need to restart the PostgreSQL database server to allow these changes to get effective.

sudo service postgresql start

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

Read: PostgreSQL WHERE IN with examples

Postgresql listen_addresses address all

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

There is a special value that we provide to listen_addresses is called asterisk ( * ), if we specify this value, it means the Postgresql server can accept all the incoming connections from different IP addresses.

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

And for Linux go to the folder /etc/postgresql/13/main and open file postgresql.conf using any 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 to take effect.

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

You may also like to read the following PostgreSQL tutorials.

• PostgreSQL CASE with Examples
• PostgreSQL WHERE with examples
• PostgreSQL DROP TABLE
• Postgresql date comparison
• Postgresql create database
• Postgresql date to string
• Postgresql group_concat
• PostgreSQL INSERT Multiple Rows
• How to migrate from MySQL to Postgres

So in this tutorial, we have learned about “Postgresql listen_addresses” which helps in connecting the Postgresql database server remotely. We have covered the following topics.

• Postgresql listen_addresses example
• Postgresql listen_addresses multiple
• Postgresql listen_addresses pg_hba.conf
• Postgresql listen_addresses cidr
• Postgresql listen_addresses address all