Allow remote connections to PostgreSQL

PostgreSQL (aka Postgres), is an open-source SQL relational database management system (RDBMS). These RDBMS are the key component for the majority of all the web sites and web applications, providing an efficient way to persist, organize, and access information.
The last blog was “Install PostgreSQL 9.6 on Ubuntu 14.04, or 16.04 LTS” . By default postgresql allows connections only from the localhost (127.0.0.1) i.e. any service on the same machine, only. So, a necessary step in PostgreSQL installation is to allow remote connections.


Step 1 – Update postgres.conf

As being a continuum of the last post “Install PostgreSQL 9.6 on Ubuntu 14.04, or 16.04 LTS” , the following paths are valid for Ubuntu 16.04 LTS. postgresql.conf has ‘listen_addresses‘ param set to ‘localhost’, by default. For our use-case we need to set it to ‘*’ i.e. all. Open postgresql.conf, using editor of your choice i.e. vi, nano, vim, etc.

For psql 9.6

sudo vi /etc/postgresql/9.6/main/postgresql.conf
psql remote

For psql 10

sudo vi /etc/postgresql/10/main/postgresql.conf
Or, you can search for postgresql.conf
 $ sudo find / -name "postgresql.conf"
Sample conf:
# - Connection Settings -
# what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
listen_addresses = '*'

Step 2. Configuring pg_hba.conf

Add the following line in the pg_hba.conf file to allow access to all databases for all users with an encrypted password, also change addresses i.e. from IPv4 127.0.0.1/32 to 0.0.0.0/0, and IPv6 from ::1/128 to ::0/0.
$ sudo vi /etc/postgresql/9.6/main/pg_hba.conf
The conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                    md5
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5
# IPv6 local connections:
host    all             all             ::0/0                 md5
# All IPs
host    all             all              all                 md5

Step 3. Restart PostgreSQL Server

After making changes, we have to restart the PostgreSQL server
$ sudo systemctl restart postgresql

Step 4. Adjusting Firewall (optional)

If you have your machine self-managed (i.e. from Digital Ocean or Linode, etc), you may need to open the postgresql listen port, default 5432.

For Ubuntu 16.04

$ sudo ufw allow 5432/tcp

Leave a Reply

Your email address will not be published. Required fields are marked *