87

I can connect just fine to a remote postgresql server that has connection restrictions to a few IPs in the pg_hba.conf, but is this enough if you have listen_addresses set to "*" in the postgresql.conf file?

That files indicates that that parameter can take a comma separated list of ip addresses, but if i do that, I lose the ability to connect remotely.

postgresql-8.4.9 rhel

AymDev
  • 6,626
  • 4
  • 29
  • 52
chrismarx
  • 11,488
  • 9
  • 84
  • 97
  • The default convention to allow connections from any IPv4 address is `0.0.0.0`. I hope this helps you. – MrGomez Mar 19 '12 at 02:04
  • 2
    i want to ensure connections are only made either locally or from two remote ips. I have this successfully configured in pg_hba.conf, but what should listen_addresses be set to – chrismarx Mar 19 '12 at 02:08
  • Have you tried a CSV like `192.168.0.1,192.168.0.2,127.0.0.1`? In the last case, `127.0.0.1` is the RFC-compliant loopback address for your system. See this document for more info: http://www.rfc-editor.org/rfc/rfc3330.txt – MrGomez Mar 19 '12 at 02:12
  • 11
    Unfortunately I can not answer because that question has been closed. The answer: `listen_addresses = 'localhost, 192.168.1.10'` – jap1968 Oct 13 '13 at 10:15
  • 11
    And how the heck is this question "off topic" – chrismarx Feb 10 '16 at 17:39
  • 2
    @chrismarx because it's not strictly about *programming*. You should have asked it on another StackExchange site (e.g. http://dba.stackexchange.com/ or http://superuser.com/). – Matthieu Sep 18 '17 at 08:52
  • @Matthieu So should this question be moved then? https://meta.stackoverflow.com/questions/318460/how-to-move-a-question-to-another-site – chrismarx Jun 14 '18 at 12:32
  • @chrismarx too late ;) There are people here who want to have a clear disctinction between *programming*, *configuration*, *administration*, etc. Each topic has it own stackexchange site where you're supposed to find better experts than (i.e. "ask the right guys"). It makes senses because SO is not a general-purpose Q&A site. But OTOH most people on SO are also cross-competent so it also makes sense to ask the biggest community (SO vs. DBA). In the end it's just a human perspective; and given the number of upvotes and favorites your question has, leaving it here is probably the best :) – Matthieu Jun 14 '18 at 17:12
  • See similar Questions on the DBA Stack Exchange: [*Can listen_addresses really be set to a list?*](https://dba.stackexchange.com/q/48372/19079) and [*Can `listen_addresses` system configuration setting in Postgres stop pre-authentication exploits?*](https://dba.stackexchange.com/q/220931/19079) and [*Howto disable Postgres listening on TCP?*](https://dba.stackexchange.com/q/52306/19079) – Basil Bourque Oct 24 '18 at 18:51
  • 2
    According to this: https://www.dbrnd.com/2018/04/postgresql-set-listen_addresses-for-multiple-host-addresses/, you can do it like that: `listen_addresses = '192.168.0.10, localhost, 192.168.1.8'`. – user2173353 Feb 04 '20 at 09:35

2 Answers2

114

listen_addresses controls which IPs the server will answer on, not which IPs the server will permit connections to authenticate from. It's entirely reasonable and normal to use listen_addresses '*' so the server will accept incoming connections on any ip assigned to an interface on the postgresql server host, while using pg_hba.conf to control access at a finer grained level for which IPs the server will accept logins from for specific databases and users.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
dbenhur
  • 20,008
  • 4
  • 48
  • 45
  • ok, thats what I wanted to know, makes the issue of getting the comma separated list in there moot. thanks! – chrismarx Mar 19 '12 at 13:52
  • 3
    Edited to make it more explicit that `pg_hba.conf` controls *login*. The server still accepts the TCP/IP connections on any listening interface and converses with the connecting client. It just quickly concludes that the client isn't from a permitted IP range and closes the connection. This means among other things that pre-auth exploits could work with `pg_hba` exclusion but not if Pg simply wasn't listening on the interface the exploit came in on. Firewall rules, not `listen_addresses`, are the answer to that. – Craig Ringer Nov 11 '12 at 10:01
  • This solution worked for me on postgres 9.1. Thanks @CraigRinger – ted.strauss Mar 30 '13 at 14:26
  • @CraigRinger This is still not clear to me, with the pronouns in your comment confusing me. Are you saying that pre-auth exploits are possible despite `listen_addresses` or despite `pg_hba`? In other words, the Postgres server engages the client as part of discerning the `listen_addresses` rule or does the `listen_addresses` rule prevent any server-client engagement? – Basil Bourque Oct 23 '18 at 21:01
  • @BasilBourque `listen_addresses` will work; nobody can connect even to pre-auth on an interface Pg doesn't listen on. *But* it has practical issues - it doesn't work if your IP changes, it doesn't work well on interfaces that may go up and down, etc. In practice I recommend limiting `listen_addresses` to localhost if you want a localhost-only server; otherwise, set it to `*` and use firewall rules to control which hosts can make TCP connections in a finer grained, interface-status-independent way. There's nothing wrong with using listen_addresses as a backstop if you have static ifaces. – Craig Ringer Oct 24 '18 at 07:47
  • 1
    @CraigRinger I believe this is an important enough topic to warrant its own specific Question, so I posted on the DBA Stack Exchange, [*Can `listen_addresses` system configuration setting in Postgres stop pre-authentication exploits?*](https://dba.stackexchange.com/q/220931/19079). Perhaps you would be willing to provide a Answer there. – Basil Bourque Oct 24 '18 at 18:36
  • 3
    `/etc/postgresql/11/main/postgresql.conf` is the place to add `listen_addresses ` on Ubuntu – gies0r Aug 13 '19 at 18:56
  • the path has been updated to `/etc/postgresql/14/main/postgresql.conf` – leopold Apr 19 '22 at 15:57
8

Setting listen_addresses to '*' is normal, as dbenhur points out. Also you can use tools such as iptables to deny access to the port apart from certain remote IPs. You can even do both: redundancy in security is not necessarily a bad thing (although, relying on IP address security isn't so good).

araqnid
  • 127,052
  • 24
  • 157
  • 134
  • yeah, there is already ip based firewalls in place, just trying to make sure everything is configured as best it can be- – chrismarx Mar 19 '12 at 13:53