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.
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
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.
postgresql.conf and find the line with
#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.
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 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
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
The order in which host records are entered in the
pg_hba.conf file matters. For a given host, Postgres will use the first rule that matches.
If a new rule you added does not work, place it at the top of the list to see if it works.
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,
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
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.