Connect to Postgres problems

On 02/01/2010 08:36 AM, Christine Penner wrote:

Hi,

I'm having trouble getting a connection to Postgres to work from outside
of my local network. It was working fine at one point. Then I had to
change IP addresses and I can't get it to work. This is what I've done.

On the computer with Postgres installed I have this in the pg_hba.conf
file:
host all all 207.6.93.152/32 md5

in the postgresql.conf file I have this:
listen_addresses = '*'
port = 5432

I also have the router set up to forward port 5432 to the computer with
Postgres installed.

I have confirmed that the IP address in pg_hba.conf and the IP address
being used to connect to are correct. I know the user name and password
etc are correct because I can connect locally using the same stuff. The
only things that have changed since it was working is IP addresses. I
confirmed they are right. Also when trying to get it working I noticed
that the port forwarding got disabled somehow so I set that up again but
still no luck.

I'm at a loss here. What else can I be missing?

Christine Penner
Ingenious Software
250-352-9495
christine@ingenioussoftware.com

Did you have Postgres reload the file?:
pg_ctl reload

--
Adrian Klaver
adrian.klaver@gmail.com

Did you reload postgres configuration after changing pg_hba.conf?

On Mon, 2010-02-01 at 08:36 -0800, Christine Penner wrote:

I have re started the computer (a few times) since I did all that.

Christine

At 09:08 AM 01/02/2010, you wrote:

On 02/01/2010 09:10 AM, Christine Penner wrote:

I have re started the computer (a few times) since I did all that.

Christine

What is the error that you are seeing on the client and in the logs?

--
Adrian Klaver
adrian.klaver@gmail.com

When we try to connect we don't get a specific error, just that it
can't connect. I'm not sure what logs to look in or on what end.

Christine

At 09:13 AM 01/02/2010, you wrote:

You should be able to telnet to the port and get a response back as in the exmple below.

Of course substitude the ip for the database.

[postgres@pgprd01 londiste]$ telnet 127.0.0.1 5432
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.

Date: Mon, 1 Feb 2010 09:13:34 -0800
From: adrian.klaver@gmail.com
To: christine@ingenioussoftware.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Connect to Postgres problems

On 02/01/2010 09:10 AM, Christine Penner wrote:

I have re started the computer (a few times) since I did all that.

Christine

What is the error that you are seeing on the client and in the logs?

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Introducing Windows® phone.
http://go.microsoft.com/?linkid=9708122

Christine Penner wrote:

Hi,

I'm having trouble getting a connection to Postgres to work from outside
of my local network. It was working fine at one point. Then I had to
change IP addresses and I can't get it to work. This is what I've done.

On the computer with Postgres installed I have this in the pg_hba.conf
file:
host all all 207.6.93.152/32 md5

in the postgresql.conf file I have this:
listen_addresses = '*'
port = 5432

I also have the router set up to forward port 5432 to the computer with
Postgres installed.

Did you check the firewall on the machine running Pg? Iptables etc.

\\||/
Rod
--

On 02/01/2010 09:16 AM, Christine Penner wrote:

When we try to connect we don't get a specific error, just that it can't
connect. I'm not sure what logs to look in or on what end.

Christine

Logging is set up in postgresql.conf per the comments in the file and
for more information:

http://www.postgresql.org/docs/8.4/interactive/runtime-config-logging.html

If it has been set up the command show log_directory; will point you in
the right direction.

--
Adrian Klaver
adrian.klaver@gmail.com

I set up port forwarding. I assume that means all IP addresses using
port 5432 will be sent to my laptop (that Postgres is running on). I
don't remember setting up permissions for a specific IP in the router
but it was a while ago so I could be wrong. When I get home I will
have another look at the firewall settings and see.

Christine

At 09:20 AM 01/02/2010, you wrote:

Telnet is usually installed by default on windows or unix box, telnet to the database box should work.

telnet 207.6.93.IP 5432 should work

Make sure that windows firewall and antivirus software firewall are temporarily disabled to test.

Date: Mon, 1 Feb 2010 09:49:49 -0800
To: raanders@cyber-office.net; pgsql-general@postgresql.org
From: christine@ingenioussoftware.com
Subject: Re: [GENERAL] Connect to Postgres problems

I set up port forwarding. I assume that means all IP addresses using
port 5432 will be sent to my laptop (that Postgres is running on). I
don't remember setting up permissions for a specific IP in the router
but it was a while ago so I could be wrong. When I get home I will
have another look at the firewall settings and see.

Christine

At 09:20 AM 01/02/2010, you wrote:

Christine Penner wrote:

Hi,
I'm having trouble getting a connection to Postgres to work from
outside of my local network. It was working fine at one point. Then
I had to change IP addresses and I can't get it to work. This is
what I've done.
On the computer with Postgres installed I have this in the pg_hba.conf file:
host all all 207.6.93.152/32 md5
in the postgresql.conf file I have this:
listen_addresses = '*'
port = 5432
I also have the router set up to forward port 5432 to the computer
with Postgres installed.

Did you check the firewall on the machine running Pg? Iptables etc.

\\||/
Rod
--

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________

Christine Penner wrote:

I'm having trouble getting a connection to Postgres to work from
outside of my local network. It was working fine at one point. Then I
had to change IP addresses and I can't get it to work. This is what
I've done.

On the computer with Postgres installed I have this in the pg_hba.conf
file:
host all all 207.6.93.152/32 md5

This will only allow the host at 207.6.93.152 to connect. If you're
trying to reach the database from any other system, that won't work.

I know the user name and password etc are correct because I can
connect locally using the same stuff. The only things that have
changed since it was working is IP addresses. I confirmed they are
right. Also when trying to get it working I noticed that the port
forwarding got disabled somehow so I set that up again but still no luck.

Generally the troubleshooting works like this:

1) Confirm psql connects from the host (with the default, sockets based
connection)
2) Confirm psql connects using TCP/IP on that same host. So "psql -h
<server ip>" on the server itself. If that doesn't work, probably an
issue with listen_addresses or pg_hba.conf
3) Connect to the database from another host on the same local network.
If this fails, check pg_hba.conf again, and make sure the server's
firewall is configured to allow connections to the port
4) Connect from an outside network. Now any problem you have should be
limited to just being related to things like port forwarding.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us