157

I've got a PostgreSQL data base that I'd like to configure to accept all incoming connections regardless of the source IP address. How can this be configured in the pg_hba.conf file? I'm using postgreSQL version 8.4.

Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
Fergal
  • 5,213
  • 6
  • 35
  • 44

6 Answers6

324

Just use 0.0.0.0/0.

host    all             all             0.0.0.0/0            md5

Make sure the listen_addresses in postgresql.conf (or ALTER SYSTEM SET) allows incoming connections on all available IP interfaces.

listen_addresses = '*'

After the changes you have to reload the configuration. One way to do this is execute this SELECT as a superuser.

SELECT pg_reload_conf();

Note: to change listen_addresses, a reload is not enough, and you have to restart the server.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • does this really work with type `md5`? I thought it would be necessary to use type `trust`... – Dan LaRocque Jul 19 '10 at 16:56
  • 6
    "trust" allows all users to connect without any password. That's something I wouldn't use, a password is the bare minimum you should always use. Even on your own computer. – Frank Heikens Jul 19 '10 at 17:59
  • 1
    ah, i read the question differently -- i thought he meant accept connections from all clients unconditionally (for some unimportant testbed, maybe). i see what you're getting at now. – Dan LaRocque Jul 19 '10 at 19:00
  • 11
    **NOTE**: If your network is IPv6, you need to use `::/0` as opposed to `0.0.0.0/0` when modifying the pg_hba.conf file. – Aron Boyette May 11 '15 at 16:02
  • To change the listen port I had restart PostgreSQL: `service postgresql restart` – czerasz Jan 08 '16 at 11:33
  • 2
    Make sure `postgres` has strong password: `sudo -u postgres psql`, `\password`. – Adobe Jun 14 '16 at 15:45
  • 1
    I will mention that if you are running your database locally only, as in the db can only be accessed from the local machine, running with no password is both hassle free and as secure as running with the most encrypted password you can come up with. If someone has gained access to the machine then they can just as easily make modifications to whatever they want to then grant themselves access to the db as well. – Mike Dec 19 '17 at 20:18
72

0.0.0.0/0 for all IPv4 addresses

::0/0 for all IPv6 addresses

all to match any IP address

samehost to match any of the server's own IP addresses

samenet to match any address in any subnet that the server is directly connected to.

e.g.

host    all             all             0.0.0.0/0            md5
Owen Pauling
  • 11,349
  • 20
  • 53
  • 64
8

Addition to above great answers, if you want some range of IPs to be authorized, you could edit /var/lib/pgsql/{VERSION}/data file and put something like

host all all 172.0.0.0/8 trust

It will accept incoming connections from any host of the above range. Source: http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/c15679_002.htm

vvs14
  • 720
  • 8
  • 19
8

Configuration all files with postgres 12 on centos:

step 1: search and edit file

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

press "i" and at line IPv4 change

host    all             all             0.0.0.0/0            md5

step 2: search and edit file postgresql.conf

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

add last line: listen_addresses = '*' :wq! (save file)

step 3: restart

systemctl restart postgresql-12.service

Nick_Jo
  • 101
  • 9
gshoanganh
  • 137
  • 1
  • 3
3

Go to pg_hba.conf file in this location (/etc/postgresql/12/main) and add the following line at the end:

host  all  all 0.0.0.0/0 md5

It allows access to all databases for all users.

Restart Postgresql by writing this command

service postgresql restart

For further info, check out error details

Ahsan Ehtesham
  • 106
  • 6
-11

Add this line to pg_hba.conf of postgres folder

host    all    all    all    trust

"trust" allows all users to connect without any password.

Akash Tantri
  • 948
  • 9
  • 15
Guest
  • 58
  • Can you maybe add some commentary to the code? Where to put it and how it works? – Alistra Feb 14 '17 at 09:41
  • 6
    Whilst this code snippet is welcome, and may provide some help, it would be [greatly improved if it included an explanation](//meta.stackexchange.com/q/114762) of *how* and *why* this solves the problem. Remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Feb 14 '17 at 10:15
  • 1
    allow connections from all ips is really insecure – sandes Oct 22 '18 at 01:16