How to configure postgresql postgresql.conf listen_addresses for multiple ip addresses

Question

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

Solution 1

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.

Solution 2

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).

Comments

[chrismarx]

ok, thats what I wanted to know, makes the issue of getting the comma separated list in there moot. thanks!

[chrismarx]

yeah, there is already ip based firewalls in place, just trying to make sure everything is configured as best it can be-

[Craig Ringer]

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.

[ted.strauss]

This solution worked for me on postgres 9.1. Thanks @CraigRinger

[Basil Bourque]

@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?

[Craig Ringer]

@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.

[Basil Bourque]

@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?. Perhaps you would be willing to provide a Answer there.

[gies0r]

/etc/postgresql/11/main/postgresql.conf is the place to add listen_addresses on Ubuntu

[leopold]

the path has been updated to /etc/postgresql/14/main/postgresql.conf