Configure PostgreSQL to accept connections from network

wrote by Rafael Marangoni, from Consultoria Linux team.

By default, on some distros, PostgreSQL will only accept connections from localhost. When you have only access from localhost (from localhost Apache, by example) everything is ok, but when you need that postgresql accepts connections for other hosts, you need to make some configs.

First of all, edit the postgresql.conf file (on CentOS the default location is /var/lib/pgsql/data/postgresql.conf).

vi /var/lib/pgsql/data/postgresql.conf

Search the following line:

listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

Secondly, you need to change the permissions inside pg_hba.conf file (on CentOS, the default location is /var/lib/pgsql/data/pg_hba.conf)

vi /var/lib/pgsql/data/pg_hba.conf

Include the following line (at the end of the file):

host    username    all     md5

username: it's the name of the postgres user

all: the database name (here we enabled all of them) is the IP address/subnet to accept connections
md5: is the method of authentication (md5 requests password)

3 thoughts on “Configure PostgreSQL to accept connections from network”

  1. Very useful, thx!
    I guess can be used to restrict some users to access through the network although they can access locally?
    Same idea with the IP address where seems to work as a ‘wildcard’?

    • Just a quick addition to this, it seems it if first database then username (instead of username then database as written in the post)

      Full line should follow:
      host demodb johndoe md5

  2. Rafael, thanks for that hint. What about tunneling thru SSH?
    Imagine machine A, running somewhere. I can connect to DB-machine by ssh and i can establish a tunnel thru e.g. putty from machine A to machine DB. On machine A port 15432 is listening, which is forwarded to 5432 on machine DB by putty.
    Is that covered by “listen_addresses = ‘localhost'”? Or is the tunnel-endpoint on machine DB seen as non-localhost?
    PS: You should add the posting dates to both your posting and the comments for future readers to know how old the posting is. This was written on 28. November 2018.


Leave a Comment