How to enable remote connections to PostgreSQL database server

Remote access to a PostgreSQL database server is disabled by default for security reasons. However, there are situations when you would want to allow that acccess. So follow this quick guide to enable that.

To get started you’ll first have to ssh to the server.

Update Postgres settings

Update postgres.conf

For this you’ll have too first find the location of postgres.conf. Its usual location is /etc/postgresql/<POSTGRES_VER>/main/postgres.conf. So fire your favorite editor and open this file.

$ sudo vim /etc/postgresql/10/main/postgresql.conf

In this file you’ll have to change listen_addresses = 'localhost' to listen_addresses = '*' So after the change your file should look something like this.

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

This would open remote access to any server of the internet. However, if you want to restrict access to few IP’s, then you should set listen_addresses to something like this:

# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = 'X.X.X.X,Y.Y.Y.Y'   # what IP address(es) to listen on;

Update pg_hba.conf

Next step would be to update pg_hba.conf.

$ sudo vim /etc/postgresql/10/main/pg_hba.conf

In this file you’ll just have to append a following line

host    all             all             0.0.0.0/0               md5

Restart Postgres

Finally to take this all in effect, you’ll have to restart the postgres server.

$ /etc/init.d/postgresql restart

Now you should be able to connect to the PostgreSQL instance remotely.

You can also refer official documentation for further information.