0

When setting up postgreSQL with a webserver (all on the same machine), is it critical to have 0.0.0.0:5432 open, or is just 127.0.0.1:5432 enough? Why or why not?

Overall, I'm trying to understand the role ports play in forwarding traffic to the pg service.

The background is that I'm trying to configure pgbouncer (a connection pooler) to work with postgresql, so need to ensure the relevant ports are open. However before I do that, I need to understand the way networking works at this level.

1 Answer 1

1

This is basic TCP/IP, rather than PostgreSQL-specific.

If the only things that will talk directly to the database server are on the same host as the database server, you only need listen_addresses = 127.0.0.1, i.e. listen only for local connections. For example, if you have pgbouncer proxying connections and accepting connections on a different port. Or if all user interaction with the DB happens via an application server and webapp.


The address 127.0.0.1 is by convention reserved for the local host. It's for talking to yourself.

There is no practical reason to allow (or block) connections to 127.0.0.1 from outside hosts, because no router will ever send packets to your host with that destination address, and your machine would never respond to ARP requests for that address. Most operating systems would ignore packets destined for 127.0.0.1 coming from external network interfaces as obviously bogus.

So you want to open connections from other hosts. 0.0.0.0/0 on port 5432 is "let anything connect to port 5432". That's an option if you don't want to be more restrictive about who can talk to the server.

Note that you may have to adjust both firewall rules and PostgreSQL's listen_addresses directive. Also possibly pg_hba.conf.

listen_addresses controls which local network interfaces PostgreSQL will accept connection attempts from.

pg_hba.conf controls which remote hosts PostgreSQL will accept connection hosts from, assuming it's even listening.

Firewall rules will stop those connection attempts even reaching PostgreSQL by filtering based on source address, destination port, and other criteria.

5
  • I see. My understanding has been that - in an 'everything-on-the-same-machine` scenario - outside traffic interfaces with my webserver on port 80/443. Next, my webserver passes traffic to my application server, which connects to the pg service as and when required. In other words, my understanding has been that only 80 and 443 need to be open to traffic from the internet, whereas the pg service only has to listen to traffic internally. Would you say this line of thinking is passable? Commented Oct 19, 2017 at 8:16
  • @HassanBaig Ah, your question didn't indicate that the webserver connected to the database, and that was the only thing that connected to the DB. Editing. Commented Oct 19, 2017 at 8:36
  • Sorry, you're right I should have been more explicit Commented Oct 19, 2017 at 8:38
  • Most appservers have their own connection poolers btw, in which case pgbouncer is typically unnecessary. Commented Oct 19, 2017 at 8:39
  • Well I'm using nginx reverse proxy with gunicorn appserver (Django app). In this scenario, I've seen pgbouncer give my environment a nice kick. From what I've read, gunicorn isn't the best at connection pooling, but the alternatives I've tried have had their own baggage. Commented Oct 19, 2017 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.