how to access my lan based postgresql db from my website

Question

We have lan based .Net application with Postgresql 8.1 , now i have task to provide interface on website to read and write two tables on LAN based database I don't know where to start Please help me to solve this problem.

Lan: Windows NT 2003 .Net Postgresql 8.1

Website: PHP 5.3 Mysql

Answer 1

This isn't exactly a well-researched question and a bit light on details. How you should approach this depends on the sensitivity of the level of security required and the volume of traffic you expect. But there are a few approaches you could take.

1. Setup a network connection (i.e. VPN/ipsec) between the sites and connect directly to the database.
2. Setup an SSH tunnel and forward the ports. A bit "hackish", and you need to ensure the SSH tunnel starts automatically, fine for admin work but this isn't really a production solution.
3. Add a Postgresql server to the website LAN and replicate the data between the sites (Postgresql replication). This sounds like it would be a messy solution because the website interface needs to write to the lan database... and I'm assuming the LAN database would need to be written locally as well. Really requires a VPN as well.
4. Setup SSL, expose Postgresql's SSL port externally via NAT, connect on the LAN's external IP from the website, and lock down via IP policy at the firewall. I personally don't like exposing databases in this manor, even over SSL, but if the data it holds isn't sensitive it can be an option. Secure TCP/IP Connections with SSL

Of course having two separate applications writing to the same underlying database is generally a bad idea anyway - you need to lock records properly to ensure no data is overwritten. It is much better to expose an API where the web-side application can request data and tell the LAN-side application what changes to make. You could then wrap it up in HTTPS with authentication or IP-based policy and simply forward ports which is simpler and more reliable than a VPN.