Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Here is my situation.

I have Postgresql installed on a Windows Server on a LAN.

I have an IIS web server set up outside the lan. I want to connect to the postgresql database from the webserver.

Postgresql server IP: 192.168.x.1

Firewall Internal IP: 192.168.x.2
Firewall External IP: 72.x.x.100

Webserver IP - Internal LAN access: 192.168.x.3
Webserver IP - External LAN access: 70.x.x.100

The pg_hba.conf file has an entry: host all all 0.0.0.0/24 md5
The postgresql.conf file has an entry: listen_addresses = '*'
This should remove all restrictions on access from the database end.

My ultimate intention is to have a GIS application accessing this database to incorporate data into a mapping website. At this point, I am just trying to access postgresql at all from the webserver using PgAdmin.

With this configuration, assuming that the 5432 port is opened correctly on the firewall, what IP address should I be placing in the Host box for the PGAdmin server connection? My initial thought is that it should be the Firewall External IP: 70.x.x.100, but is this correct?

Lastly, and I'll split this off into a new question if need be: Is this configuration even logical from a performance and security viewpoint, or should I be going about this a different way?

share|improve this question

1 Answer 1

host all all 0.0.0.0/24 md5

Um, what? That only matches 0.0.0.* which surely isn't what you want.

Did you mean 0.0.0.0/0, i.e all IP addresses?

Or 192.168.0.0/16, i.e "everything on my LAN, including the firewall and NATed traffic coming through it" ?

You're using NAT, so PostgreSQL will never see the external IPs. Connections through the firewall will come from the firewall's internal IP. If you wanted to add a rule only for connections from the firewall its self or connections coming through it, you'd add one that matches your firewall's exact internal IP with the mask /32, i.e. just that IP.

The setup you suggest is reasonable, btw. You'll need port forwarding on the firewall machine, of course. You'll probably want to enable TCP keepalives in PostgreSQL to stop the firewall from dropping idle connections due to connection tracking table timeouts.

share|improve this answer
    
Craig, thanks for the answer, it is much appreciated. I was a bit confused on the notation for the entries in the pg_hba file, so that helped. I am now using the 192.168.0.0/16 as you suggested, which has greatly simplified the entries. –  Get Spatial Apr 8 at 15:15
    
As a follow up, based on what you said, was my initial thought that the Firewall External IP: 70.x.x.100 should be used as the host in PGAdmin, correct? Thanks for your help! –  Get Spatial Apr 8 at 15:18
    
For connecting from outside the NAT firewall, you use the external IP, yes. This isn't really to do with PostgreSQL, it's just NAT and port forwarding. –  Craig Ringer Apr 9 at 0:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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