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
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 = '*'
# 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
# "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