Thread: Connect to Postgres problems

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

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:

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 

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 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

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:
>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

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

---

All your Hotmail contacts on your phone. Try it now.

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:
>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

 
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

---

Not using Hotmail on your phone? Why not? Get it now.

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