PostgreSQL Allow Remote Connections

After installing PostgreSQL, you can log in to the Server Locally using the postgres system user without a password. However, we are not allowed to connect to the server from a remote computer.

That is because, by default, PostgreSQL does not allow remote connections to the server.

Run the ss -tlnp command, you will see that the Postgres process only listens on 127.0.0.1 IP address (localhost).

Allowing Remote Connections is a Two-step Process

Step 01: Change listen_addresses - First, we need to change the listen_addresses option in the postgresql.conf, the main configuration file.

By default PostgreSQL only listens on localhost, which means we can only connect to the server locally. This behavior of the PostgreSQL server is controlled by the listen_addresses directive of the postgresql.conf.

In Ubuntu, the location of the main configuration file is /etc/postgresql/13/main/postgresql.conf, if you are running PostgreSQL 13.

Step 02: Configure Host-based access control - After that, we should add a host record(s) to the pg_hba.conf file (Host Based Authentication file) to allow access to the remote computer.

A host entry in the pg_hba.conf file is used to specify hosts that are allowed to connect to the PostgreSQL server.

In Ubuntu, the pg_hba.conf file is in the same directory as the postgresql.conf file.

Change listen_addresses

Open the postgresql.conf and find the line with listen_addresses:

#listen_addresses = 'localhost'

Change it to read as follows:

listen_addresses = '*'

If it is commented (there is a # sign at the beginning of the line), delete the hash sign.

After changing listen_addresses settings, it requires a PostgreSQL service restart:

systemctl restart postgresql

Configure Host-based Access Control

After configuring the listen_addresses, we can move on to the next step. That is to add host entries to the pg_hba.conf file.

The pg_hba.conf file manages client authentication between the PostgreSQL server and the client computer (Local and Remote). It allows you to define who is allowed to connect to which databases from which computers, like a Firewall.

connection_type    database    user    address    auth_method

For example, if you add the following entry to the host section, it will allow remote connections from the 192.168.1.200 IP address, using the postgres user without a password.

host    all    postgres    192.168.1.200/32    trust

Changing pg_hba.conf requires a PostgreSQL service reload:

systemctl reload postgresql

The trust authentication method allows the postgres system user to log in to the server without a password. It works on Linux, Ubuntu, and CentOS, but not in Windows.

When connecting to the server, the client needs to specify the server IP Address (or hostname) and the username from the remote computer:

psql -h 192.168.1.100 --username postgres

The following host record allows any computer in the 192.168.1.0/24 network to connect to any database as the postgres user, without the use of a password.

host    all             postgres        192.168.1.0/24          trust

If you want to allow connections from anywhere, put 0.0.0.0/0 in the ADDRESS section.

host    all             postgres        0.0.0.0/0               trust

Password Authentication

In the previous example, we allowed the postgres superuser to connect to the server without a password using the trust authentication method. However, connecting without a password is not the best choice for remote access. Instead, you should use password authentication methods, scram-sha-256(recommended) or md5.

In the following example, we will be creating a new database called marketing and a database user named admin1. And allow the admin1 user to connect to the marketing database, but only from a remote computer that has the IPv4 address of 192.168.1.200, using the md5 password authentication.

First, create the user and the database:

CREATE ROLE admin1 WITH LOGIN PASSWORD 'mypassword';
CREATE DATABASE marketing WITH owner = admin1;

Add following host record to the pg_hba.conf:

host    marketing       admin1          192.168.1.200/32        md5

Reload the postgres service:

systemctl reload postgresql

The admin1 user can now connect to the server from the 192.168.1.200 host using the password.

psql -h 192.168.1.100 -U admin1 -d marketing

In the preceding example, the admin1 user is restricted to the marketing database only.