Postgresql server remote connection

Question

I am trying to set up a postgresql 9.1 server on ubuntu, for remote access of data. I have postgres properly installed, the server process is running and I am trying to configure it so that I can access the server remotely over the internet from a few other computers outside my LAN.

I have already modified my pg_hba.conf with:

host all all 0.0.0.0 trust

and the postgresql.conf with:

listen_addresses = '*'
port = 5432

I have additionally modified my iptables to accept connections on port 5432.

When I try to connect using psycopg2 on python:

conn=psycopg2.connect('host=XX.XX.XX.XX port=5432 dbname=postgres user=myUser password=mypassword')

I receive the following error:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/dist-packages/psycopg2/__init__.py", line 179, in connect
connection_factory=connection_factory, async=async) 
psycopg2.OperationalError: could not connect to server: Connection refused
Is the server running on host "XX.XX.XX.XX" and accepting
TCP/IP connections on port 5432?

I am not sure if I inserted the correct IP address, and I am curious how I would figure out exactly what IP address to use here to connect to my server. I used the public IP of my computer which is running the postgres server, but I'm not sure that is correct. Or is there a whole other step I am still missing? I know this is bad security style, but for the moment I would like to just establish a connection. Also my computer is behind a router, so how would I access my server specifically?

Any help greatly appreciated.

• PearsonArtPhoto: Is your server on the same LAN as you are, or a different one? And how do you access the server using SSH?

Answer 1

Your pg_hba.conf should NOT use trust!!! trust means no password is required and I don't think that's what you want.

This is the correct configuration

host all all 0.0.0.0/0 md5

Notice the /0 behind 0.0.0.0.

The full pg_hba.conf should be this:-

local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
host all all 0.0.0.0/0 md5

Notice that trust is only applicable for local connections. i.e. for applications running on localhost IP 127.0.0.1 on the machine which also runs your postgresql server.

• froot93: Yes, I know its bad practice but for the moment I am just trying to establish a connection, and then I will improve security after that. I also have all of that already in the files, I just omitted it to be concise. My server is behind a router, so I need to know how to access that machine directly from remote connection
• Ihor Romanchenko: @froot93 to connect to postgresql behind the router, you must first configure port forwarding on the router. Google "port forwarding" for your router model.
• Gopinagh.R: Also check if the default port 5432 is blocked by the firewall in the remote machine.