Connect to PostgreSQL server from an ADSL connection

Hi all,

I am writing because I would like to know that how can I connect
remotely to a PostgreSQL database from an ADSL connection, which has
different IP address every time when it connects to the Internet. This
ADSL connection is registered to www.dyndns.org, so its dynamic IP
address is replaceable with the registered domain name.
My problem is the changing IP address: I would not like to modify the
pg_hba.conf file every time when the remote computer's IP address
changes. I know that there is the "listen_addresses" parameter in the
postgresql.conf file, which can have not only IP addresses as value
but domain names also, and it is required for remote connections. But
in the pg_hba.conf file - as far as I know - I can not use domain
names only IP addresses, so it seems to me that I can not push the
advantage of registered domain name at dyndns.org.
Is there any way to configure the PostgreSQL server to accept remote
connection from one (but always the same) computer which has dynamic
IP address?

Thank you in advance,
Szabolcs

p.s.: Sorry for my bad English... If something is not clear, I will
try to explain it more detailed.

Le dimanche 01 janvier 2006 à 16:40 +0100, Szabolcs Andrási a écrit :
> Hi all,
>
> I am writing because I would like to know that how can I connect
> remotely to a PostgreSQL database from an ADSL connection, which has
> different IP address every time when it connects to the Internet. This
> ADSL connection is registered to www.dyndns.org, so its dynamic IP
> address is replaceable with the registered domain name.
> My problem is the changing IP address: I would not like to modify the
> pg_hba.conf file every time when the remote computer's IP address
> changes.

ssh -v -L 5432:localhost:5432 dbuser(at)your(dot)dyndns(dot)domaine

Then connect to your database on localhost.

Cheers

Tony

--
Tony Grant
www.tgds.net - vente d'ordinateurs mini-itx en ligne
www.tgds.net/epiaix1.html - A fedora Core 4 based live CD for VIA Epia-M

> ssh -v -L 5432:localhost:5432 dbuser(at)your(dot)dyndns(dot)domaine
>
> Then connect to your database on localhost.

Hi Tony,

Thank you very much for your answer. Sorry to bother you again with
the same question but unfortunately it is still not clear to me how to
configure the PostgreSQL server. Could you please be more specific and
detailed? Let me write an exact situation to make the answer easier:

- PostgreSQL is newly installed, so now everything is set to default values.
- The PostgreSQL server is runing on a computer with a domain name for
example "my.pgserver.com" and listening on port 5432.
- There is another remote computer with ADSL connection to Internet
and it is registered to dyndns.org with a name for example
"my.dyndns.domain".
- A client application is running on this remote computer. This
application would like to connect to the PostgreSQL server on
"my.pgserver.com" and fetch some data from a table.

The documentation says about the "listen_addresses" configuration
parameter in the postgresql.conf file, that to accept TCP/IP
connections its value must be '*' or at least its value must contain
the host names on which the server is listening for connections from
client applications. So I guess I should add "my.dyndns.domain" to it
to accept TCP/IP connection from that remote computer.
But I don't know if am I right? How should I configure the pg_hba.conf
file? What else should I do? Or just explain more detailed (through
the example above) that SSH connection please...
Could you please help me?

Thank you in advance again...
Best regars,
Szabolcs

Le dimanche 01 janvier 2006 à 20:05 +0100, Szabolcs Andrási a écrit :
> > ssh -v -L 5432:localhost:5432 dbuser(at)your(dot)dyndns(dot)domaine
> >
> > Then connect to your database on localhost.

> Thank you very much for your answer. Sorry to bother you again with
> the same question but unfortunately it is still not clear to me how to
> configure the PostgreSQL server. Could you please be more specific and
> detailed? Let me write an exact situation to make the answer easier:

The postgresql server must also be running SSH server. You use the SSH
client to forward the postgresql port to your local computer. Your
client computer does not need to have a domain name.

> - PostgreSQL is newly installed, so now everything is set to default values.
> - The PostgreSQL server is runing on a computer with a domain name for
> example "my.pgserver.com" and listening on port 5432.

So for you the SSH comand line would be
ssh -v -L 5432:localhost:5432 dbuser(at)my(dot)pgserver(dot)com

> - A client application is running on this remote computer. This
> application would like to connect to the PostgreSQL server on
> "my.pgserver.com" and fetch some data from a table.

You open your client and point it to localhost:5432

SSH is forwarding the distant 5432 port to your local computer through a
secure tunnel. This is totally transparent for server and client - the
server needs no configuration. You can forward the postgres port to
another port number if you wish (for example you are running a
postgresql server on your local machine too).

Cheers

Tony
--
Tony Grant
www.tgds.net - vente d'ordinateurs mini-itx en ligne
www.tgds.net/epiaix1.html - A fedora Core 4 based live CD for VIA Epia-M

Szabolcs,

On Jan 1, 2006, at 2:05 PM, Szabolcs Andrási wrote:

> The documentation says about the "listen_addresses" configuration
> parameter in the postgresql.conf file, that to accept TCP/IP
> connections its value must be '*' or at least its value must contain
> the host names on which the server is listening for connections from
> client applications. So I guess I should add "my.dyndns.domain" to it
> to accept TCP/IP connection from that remote computer.
> But I don't know if am I right? How should I configure the pg_hba.conf
> file? What else should I do? Or just explain more detailed (through
> the example above) that SSH connection please...
> Could you please help me?

Here is a bit more detail about SSH tunneling with PostgreSQL:

http://pgedit.com/tip/postgresql/ssh_tunneling

As far as your original question, I don't think there is a solution.
pg_hba.conf requires IP addresses and will not work with domain
names. The possibility of changing this is currently being discussed
on the hackers list.

I suppose if you were really industrious and had the right access you
could write a script updates the pg_hba.conf file if the dynamic
address changes. I would also like to see DNS names supported in
pb_hba.conf.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL