How to enable remote access to PostgreSQL server

This is a "how to" kind of post, on how to configure your postgresql server running on a cloud service, to be able to accept a remote access call to it from your or any computer.

Error messages

There are various error messages associated with not being able to successfully access a postgresql server remotely, some of which are as listed below:

psql: error: FATAL:  no pg_hba.conf entry for host "<your system IP address>", user "postgres", database "postgres", SSL on
FATAL:  no pg_hba.conf entry for host "<your system IP address>", user "postgres", database "postgres", SSL off

psql: error: could not connect to server: Connection refused Is the server running on host "<your system IP address>" and accepting
TCP/IP connections on port 5432?

Note: this third error is not absolutely the result of the problem we are trying to solve, but it is a possible error to come by in the process of make the remote call.

psql: error: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

Fix:

1. Make an ssh connection to your server, using the command pattern below.

$ ssh <username>@<ip-address>

or

$ ssh <username>@<server's url>

1. Change user to postgres.

$ sudo su postgres

1. Get location of postgresql.conf file by executing the command:

$ psql -U postgres -c 'SHOW config_file'

The file path should be of either the form /var/lib/pgsql/data/postgresql.conf or /etc/postgres/9.5/main/postgresql.conf.

1. Open postgresql.conf file using the file path from step 3.

$ nano .../.../postgresql.conf

1. Add the following line to the of the file.

listen_addresses = '*'

1. Open the pg_hba.conf file. It is usually in the same folder as postgresql.conf.

$ nano .../.../pg_hba.conf

1. Locate the line with the text: # IPv4 local connections: and change all the method value: md5 to trust.

Before change:

After change

Note: Save the file using Ctrl + o, exit it with Ctrl + x in nano. Then restart you postgres service.

1. Make sure you are in 'postgres' user (if not, use step 2). Run the command below to create a password.

$ ALTER USER postgres PASSWORD '<Password>'

1. Revert the changes you make in step 7(i.e change the method values from trust back to md5), and add the following line to the end of the # IPv4 local connections:.

hostnossl    all        all             <your ip address>/0         trust

host    all        all             <your ip address>/32         trust

You can easily find your IP address by google search this key words: what is my ip address.

1. Restart your postgres service. using the command below.

sudo systemctl restart postgres.service

Note: In your case the service may be name differently.

1. Run the command below to make a remote call to your postgres server.

psql -U postgres -h <postgresql server ip address>

I hope this helps?