Prerequisites

In this instructional guide, we will be discussing how to enable remote connections to a PostgreSQL server installed on Ubuntu. To follow along with the steps provided, you will need to have the following installed on your system:

• Ubuntu 20.04
• PostgreSQL server version 12

Please ensure that you have these requirements installed before proceeding with the guide.

 Enable remote access to Postgres

To allow remote access to a PostgreSQL 12 server on Ubuntu 20.04, you need to follow the steps below:

1. Modify the PostgreSQL configuration file

Open the PostgreSQL configuration file “postgresql.conf” using your preferred text editor. The file is typically located in the /etc/postgresql/12/main directory. To open the file from the Linux Terminal, execute: sudo nano /etc/postgresql/12/main/postgresql.conf 

Then, find the line #listen_addresses = 'localhost' and uncomment it (remove the # character at the beginning of the line).

Next, change the value of “listen_addresses” to “*”. This allows PostgreSQL to listen on all available IP addresses. Alternatively, you can specify a specific IP address or a range of IP addresses that are allowed to connect to the server.

2. Modify the pg_hba.conf file

Open the “pg_hba.conf” file using your preferred text editor. The file is typically located in the /etc/postgresql/<version>/main directory. To open the file from the Linux Terminal, execute: sudo nano /etc/postgresql/<version>/main/pg_hba.con

Take the following section:

Add new line to IPv4 local connection: 

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5 

3. Allow port 5432 through the firewall

To enable traffic on port 5432 through the firewall, execute the following command: sudo ufw allow 5432/tcp 

4. Restart PostgreSQL

Run the following command to restart PostgreSQL: sudo service postgresql restart

After completing these steps, you should be able to connect to the PostgreSQL server from a remote machine using a PostgreSQL client. However, please note that allowing remote access to a PostgreSQL server can bear a security risk, so it is recommended to use secure passwords, encryption, and firewall rules to protect your system.

How to allow remote connections to PostgreSQL on Windows

By default, when installing PostgreSQL, you get it configured to only accept connections from the local machine. This is a security measure to prevent unauthorized access to the database server. However, in some cases, you may need to allow connections from remote hosts. Let us look at how to do this.

To allow remote connections to PostgreSQL on Windows, follow these steps:

1. Edit the PostgreSQL configuration file

1.1 Open the PostgreSQL installation directory and locate the postgresql.conf file. By default, the configuration file for PostgreSQL (version 14) is located at C:\Program Files\PostgreSQL\<version>\data\postgresql.conf.

1.2 Make a backup of the file before making any changes.

1.3 Open the postgresql.conf file in a text editor.

1.4 Find the line that reads #listen_addresses = 'localhost' and uncomment it if it is commented (remove the ‘#’ character at the beginning of the line). Next, to ensure that PostgreSQL is configured to accept connections from any address, check the value of “listen_addresses” – it should be set to “*”.

Note: You can also use a specific IP address for the PostgreSQL server to listen on.

2. Edit the pg_hba.conf file to allow remote connections

2.1 Open the PostgreSQL installation directory and locate the pg_hba.conf file. By default, it is located at C:\Program Files\PostgreSQL\14\data\pg_hba.conf (for PostgreSQL 14).

2.2 Make a backup of the file before making any changes.

2.3 Open the pg_hba.conf file in a text editor.

2.4 Add a new line at the end of the file to allow remote connections. The line should have the following format:

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5 

This line allows connections from any IP address (0.0.0.0/0) and requires a password for authentication (md5).

2.5 Restart PostgreSQL (for example, from the Computer Management console).

3. Configure the Windows Firewall to allow incoming connections to PostgreSQL

3.1 Launch Windows Control Pane.

3.2 Open Windows Defender Firewall.

3.3 Click Advanced settings on the left-hand side of the window.

3.4 Click Inbound Rules on the left-hand side of the window.

3.5 Click New Rule on the right-hand side of the window.

3.6 Select Port as the type of rule and click Next.

3.7 Select TCP as the protocol and enter 5432 as the port number. Click Next.

3.8 Select Allow the connection and click Next.

3.9 Select the network types for which the rule should apply (Domain, Private, or Public). Click Next.

3.10 Enter a name and description for the rule and click Finish.

After completing these steps, remote connections to PostgreSQL should be allowed on the Windows machine.

References