Configure OpenVPN to connect to PostgreSQL on Ubuntu 14.04?

February 23, 2016 1.7k views
VPN PostgreSQL Ubuntu

I recently installed the one-click app for Django and then installed OpenVPN with the follow script, https://github.com/Nyr/openvpn-install. I am able to VPN to the server and see my traffic gets routed to it. However I can not see port 5432. Is there a tutorial or previous post someone can refer me to on configuring PostgreSQL 9.3 over OpenVPN?

3 comments
  • By default on the Django One-Click, postgres is bound to localhost and not accessible remotely. Have you already edited your postgres config to allow external access? What's the output of netstat -plunt This should show you what services are listening and which ports they are listening on.

  • I do not want to open DB access up to the world. I thought If I set up a VPN I would be able to access the server, transfer files between it, and access the different services running on it, which I am having an issue accessing postgres. Are you saying in order to access postgres, even tho I am connected via VPN, I would need to append the config file with some other IP? Any way to do that so that anyone connected via VPN can have access to the DB?

  • I was able to figure it out. Not positive if openvpn will always bind my connection to the same ipaddress tho..

    This is what I did.

    1. Connect to openvpn server
    2. Run ifconfig and ipconfig to get the ipaddress for the server and local machine over vpn
    3. Edit /etc/postgresql/9.3.main/postgresql.conf. Uncommented and edited line: listen_addrress = '[ip address from for the local machine over the vpn], localhost'.
    4. Edit /etc/postgresql/9.3.main/pg_hba.conf. Added ipv4 record: host all all [ip address of local machine over vpn]/24 md5
    5. Installed iptables-persistant and ufw. Enabled ufw.
    6. Added to entries to iptables and saved: iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d [local machine ip over vpn] --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -p tcp -s [local machine ip over vpn] --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT
    7. Rebooted the server.
1 Answer

This question was answered by @speelman90:

I was able to figure it out. Not positive if openvpn will always bind my connection to the same ipaddress tho..

This is what I did.

  1. Connect to openvpn server
  2. Run ifconfig and ipconfig to get the ipaddress for the server and local machine over vpn
  3. Edit /etc/postgresql/9.3.main/postgresql.conf. Uncommented and edited line: listen_addrress = '[ip address from for the local machine over the vpn], localhost'.
  4. Edit /etc/postgresql/9.3.main/pg_hba.conf. Added ipv4 record: host all all [ip address of local machine over vpn]/24 md5
  5. Installed iptables-persistant and ufw. Enabled ufw.
  6. Added to entries to iptables and saved: iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d [local machine ip over vpn] --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -p tcp -s [local machine ip over vpn] --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT
  7. Rebooted the server.

View the original comment

Have another answer? Share your knowledge.