Thread: 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@your.dyndns.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@your.dyndns.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@your.dyndns.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@my.pgserver.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