Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
Is your server on the same LAN as you are, or a different one? And how do you access the server using SSH? – PearsonArtPhoto Nov 22 '12 at 13:52

1 Answer

up vote 1 down vote

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.

share|improve this answer
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 – froot93 Nov 22 '12 at 14:14
2  
@froot93 to connect to postgresql behind the router, you must first configure port forwarding on the router. Google "port forwarding" for your router model. – Igor Romanchenko Nov 22 '12 at 14:38
Also check if the default port 5432 is blocked by the firewall in the remote machine. – Gopinagh.R Nov 22 '12 at 19:02

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.