[Techtalk] adsl and dyndns issues with changing IP addresses

[Wim De Smet]

Hi Anne,

On Sun, Nov 21, 2010 at 10:21 PM, Anne Wainwright
<anotheranne at fables.co.za> wrote:
> From "Practical Postgresql"
> "When PostgreSQL receives a connection request it will check the
> pg_hba.conf file to verify that the machine from which the application
> is requesting a connection has rights to connect to the specified
> database. If the machine requesting access has permission to connect,
> PostgreSQL will check the conditions that the application must meet in
> order to successfully authenticate."
>
> Thus unless someone is able to imitate the IP address of my machine,
> & match the user password then they can't connect. Hopefully.
>
> This brings me back to the issue of my machine having a dynamically
> allocated IP address from my ISP which changes from the one given in
> the pg_hba.conf file mentioned above. I take this to be insoluble and
> will temporarily live with it.

Usually ISPs allocate these addresses in the same subnet. You could
allow access to all IPs in your range, which would not be perfect, but
it'd severely limit the number of clients accepted anyway.

> & Wim says:
>> but how about just letting postgres
>> listen on localhost and just using a ssh tunnel if you need direct
>> access?
>
> which sounds like the same thing. ie get my client to connect across an
> existing ssh connection running to a user account rather than connect
> through an open port. Sounds like this should be possible but not sure.

Quick recipe:
ssh -N -L localhost:5000:localhost:5000 <yourremotehost>

What this does is, if you connect with a client on localhost and port
5000 (the first 2 values) on the machine where you run ssh on, it will
tunnel the connection over to <yourremotehost> and connect to port
5000 on localhost there (the second two). This happens to connect to
localhost, but you could make it www.google.com:80 if you'd so wish.
You can add -f to background this ssh process (just keyb interrupt it
otherwise).

Using ssh-agent or the like you can make sure you don't have to fill
in password prompts of course. It's perhaps not super robust. For one,
I'm not sure what will happen when your IP address changes, probably
the tunnel will take a while to time out and you'd have to use upstart
or the like to keep an eye out for ssh dying. But it does the trick
for me when I need to do some testing from home and need to get at the
DB server.

cheers,
Wim