4

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.

froot93
  • 86
  • 1
  • 6

1 Answers1

7

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.

Calvin Cheng
  • 35,640
  • 39
  • 116
  • 167
  • 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
  • 4
    @froot93 to connect to postgresql behind the router, you must first configure port forwarding on the router. Google "port forwarding" for your router model. – Ihor Romanchenko Nov 22 '12 at 14:38
  • 1
    Also check if the default port 5432 is blocked by the firewall in the remote machine. – Gopinagh.R Nov 22 '12 at 19:02