O$: Connecting to Postgres over the Internet

[Christine Penner]

I'm trying to connect to a Postgres database over the Internet. I
thought I had all the settings the way they should be but it's not working.

In the file pg_hba.conf I have a line:
host all all 75.157.109.88/32 md5

I changed the port Postgres uses to 5913 because I already had port
forwarding set up on that port.
I know the user name and password being used are correct.

At this point I am using an SQL Browser session to connect. The port
is set correctly in this session too.

Anyone have any other ideas?

Christine

[Serban Teodorescu]

Hello

How is the server connected to Internet? Is it behind a router, with
NAT? If it is, PostgreSQL might "see" the router internal IP when you're
trying to connect over Internet, not the client Internet address. Check
PostgreSQL logs, you might see there why the connection request was denied.

Best regards
Serban Teodorescu
Unimate Software

Post by Christine Penner
I'm trying to connect to a Postgres database over the Internet. I
thought I had all the settings the way they should be but it's not working.
host all all 75.157.109.88/32 md5
I changed the port Postgres uses to 5913 because I already had port
forwarding set up on that port.
I know the user name and password being used are correct.
At this point I am using an SQL Browser session to connect. The port
is set correctly in this session too.
Anyone have any other ideas?
Christine
_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com

[Christine Penner]

The server is just my home computer. I have a modem/router that it
has to go through. I have port forwarding set up on that router
through port 5913 to my computer. I will check the log when I get
home today. I don't know what NAT is. I'm not much of a network person.

I'm concerned the line in the pg_hba.conf file may not be right. I
don't know what the /32 does. I know to allow connections on the
local network, I replace that with a network mask. In this case I
want to allow only the one IP address.

Christine

Post by Serban Teodorescu
Hello
How is the server connected to Internet? Is it behind a router, with
NAT? If it is, PostgreSQL might "see" the router internal IP when
you're trying to connect over Internet, not the client Internet
address. Check PostgreSQL logs, you might see there why the
connection request was denied.
Best regards
Serban Teodorescu
Unimate Software

Post by Christine Penner
I'm trying to connect to a Postgres database over the Internet. I
thought I had all the settings the way they should be but it's not working.
host all all 75.157.109.88/32 md5
I changed the port Postgres uses to 5913 because I already had port
forwarding set up on that port.
I know the user name and password being used are correct.
At this point I am using an SQL Browser session to connect. The
port is set correctly in this session too.
Anyone have any other ideas?
Christine
_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com

[Bastiaan Olij]

Hi Christine,

Just to rule out one of the stupidest things of the century I ran into
relating to this. Are you on Vista?

If so, you have likely ended up editing a copy of your postgres
configuration files that end up in your virtual store while postgres
keeps using the originals. Try running PGAdmin by rightclicking on it
and selecting "run as administrator" and then edit the configuration
files again (assuming you are using PGAdmin to edit the files, if you
are using a text editor, run that text editor in the same way, make sure
its elevated to administrator right, the fact that you are an
administrator user has nothing to do with it).

Greetz,

Bas

The server is just my home computer. I have a modem/router that it has
to go through. I have port forwarding set up on that router through
port 5913 to my computer. I will check the log when I get home today.
I don't know what NAT is. I'm not much of a network person.
I'm concerned the line in the pg_hba.conf file may not be right. I
don't know what the /32 does. I know to allow connections on the local
network, I replace that with a network mask. In this case I want to
allow only the one IP address.
Christine

[Doug Easterbrook]

if you are using port 5913 at the router, then you have to do one of:
a) have to forward that port to 5432 at the postgres server
b) change postgresql.conf to listen on 5913 instead of 5432 and
restart the server
c) reconfigure the router to use port 5432 instead of 5913 and forward
that to the postgres server

easiest is to change the router and use installation standards


/32 means only one IP address
/24 means everything on the subnet (as if the mask was 255.255.255.0).

hope that helsp.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:***@artsman.com
http://www.artsman.com
Phone [phone] Fax [phone]

Date: Thu, 12 Mar 2009 08:43:57 -0700
Subject: Re: O$: Connecting to Postgres over the Internet
Content-Type: text/plain; charset="us-ascii"; format=flowed
The server is just my home computer. I have a modem/router that it
has to go through. I have port forwarding set up on that router
through port 5913 to my computer. I will check the log when I get
home today. I don't know what NAT is. I'm not much of a network
person.
I'm concerned the line in the pg_hba.conf file may not be right. I
don't know what the /32 does. I know to allow connections on the
local network, I replace that with a network mask. In this case I
want to allow only the one IP address.

[CLIFFORD ILKAY]

Post by Christine Penner
I'm trying to connect to a Postgres database over the Internet. I
thought I had all the settings the way they should be but it's not working.
host all all 75.157.109.88/32 md5
I changed the port Postgres uses to 5913 because I already had port
forwarding set up on that port.

I assume your network topology is fairly conventional and is something
like this.

Internet <==> (75.157.109.88 external interface) Firewall/Gateway (some
private IP like 192.168.0.1 on the internal interface, for example) <==>
Ethernet switch <==> PostgreSQL server (some private IP like
192.168.0.2, for example)

If that is the case, and assuming you want to allow connections from the
Internet port forwarded through your firewall/gateway and other machines
on the LAN to be able to connect to the PostgreSQL server, that line in
pg_hba.conf should be:

hostssl all all 192.168.0.1/24 md5

Your firewall/gateway is doing NAT (Network Address Translation) so it
makes all requests to hosts on the Internet appear to come from
75.157.109.88 and it maintains a lookup table so that it knows to
forward the responses to the appropriate machine on your LAN. As far as
your PostgreSQL server is concerned, any connection requests from the
Internet are coming from your firewall/gateway's internal interface. It
will never requests from the IP address bound to the external interface
of your firewall/gateway because the packets will have been rewritten
via NAT.

The "hostssl" bit is to ensure that any connections from the Internet or
your LAN are SSL-encrypted.

Doug Easterbrook gave you good advice on why you should have PostgreSQL
listen on its default port. If you change the default port PostgreSQL
listens on, you will always have to remember to qualify connections from
admin tools, like psql, with the port number whereas if you don't, you
won't.
- --
Regards,

Clifford Ilkay
Dinamis
1419-3266 Yonge St.
Toronto, ON
Canada M4N 3P6

<http://dinamis.com>
[phone]