How to allow remote connections to PostgreSQL database server

After installing PostgreSQL database server, remote access mode is disabled by default for security reasons. However, sometimes you may want to allow remote connections to PostgreSQL database server from other locations, your home or office for example. In the next few lines I’ll guide you to do just that.

Connect to the remote server

First things first, you need to login to the remote server:

Change The Listen Address

By default, PostgreSQL DB server listen address is set to the 'localhost' , and we need to change it so it accepts connection from any IP address; or you can use comma separated list of addresses. Here is how it looks by default:

Open your postgresql.conf  file in your editor:

search for listen_addresses , and set it to '*' :

or if you want to set connection restrictions to a few IP’s, then you should set listen_addresses  to something like this:

To find out more about connections and authentication and available parameters, check the official documentation page.

Open PostgreSQL to the world

In this step, you need to allow remote connections to actually reach your PostgreSQL server. Open pg_hba.conf :

To allow connections from absolutely any address with password authentication add this line at the end of pg_hba.conf  file:

You can also use your network/mask instead just 0.0.0.0/0 .

Restart

You have made it! Just make sure to restart your PostgreSQL instance before leaving remote SSH session:

Now you should be able to connect to the PostgreSQL instance with any of DB tools.

Follow me

Mirza Pasic

Full Stack Developer at OLX
Web Developer. Geek. Systematic. Dreamer
Follow me

Latest posts by Mirza Pasic (see all)

Published by

Mirza Pasic

Web Developer. Geek. Systematic. Dreamer

  • Tomás Reynoso

    Very usefull!
    Thank you so much

  • Pingback: Cannot re-execute code until manually shutdown local PostgreSQL server – FeuTex – #ForAuthors()

  • Joana Pinto

    Mirza are you running this from the cmd or from sql shell?

    • Var

      terminal

  • lovemachine101

    Hi Mirza, I connected my server to local Postgres by using
    const client = new Client({
    user: “postgres”,
    host: “localhost”,
    database: “postgres”,
    password: “password”,
    port: 5432
    })

    But now after all the steps in your article, how do I connect to my remote Postgres? My remote Postgres is on an AWS’s EC2 instance. I tried changing the host value from ‘localhost’ to the EC2’s public IPv4 address but it didn’t work.