Re: ssl tunneling in postgres 8.1

Thanks Travis for the detailed explanation and apologies to the
faithful postgres folks for lashing out on their documentations.
Nevertheless, I think it can still be improved to incorporate this
"localhost" caveat and/or relevant postgresql.conf modifications.

On 11/19/08, Travis Veazey <travis(dot)veazey(at)uberforge(dot)com> wrote:
>
> client$ ssh -L 3333:localhost:5432 joe(at)foo(dot)com
> client$ psql -h localhost -p 3333 postgres

OK.. this is the real deal then... and it works... fantastic.

> I think the confusion you are having stems from the perspective from which
> the hostname (in this case, "localhost") is interpreted. The first part of
> the connection, the 3333, is the port on your local machine that you will
> connect to to enter the tunnel. The second part, localhost, is the hostname
> of the machine you will connect to, but it is the hostname as the
> remote machine
> you have SSH-ed into sees it. That is, "localhost" here will be the machine
> foo.com, as that is what you are connecting to. The final part, the 5432, is
> the port on foo that you want to connect to after exiting the tunnel.

Exactly, the misunderstanding, in retrospect, was: which machine
(local_client or remote_server) interprets "localhost:543x". It the
remote_server, and hence x=2 (or whatever is configured in
postgresql.conf as "port = ????")!!

> SSH tunneling can be pretty confusing when you first start to get into it.
> Just be sure to think of it like this: If you have two machine, 'foo' and
> 'bar', and you want to open an SSH tunnel from foo to bar to connect to a
> resource running on bar, the tunnel looks like this conceptually:
> [foo:port]===SSH===[bar:port]
> The first part of your tunnel command specifies the entry point into your
> tunnel, with an assumed localhost if you don't specify a host. The second
> part is the destination after you exit the tunnel.
>
> The reason your tunnel didn't work the first time when you had a PostgreSQL
> install on your local machine is because you were trying to open the entry
> point on the same port that PostgreSQL was listening on. If you had shut
> down Postgres first, or if you had specified an entry port other than 5432,
> it would have worked beautifully the first time. This is what the

I don't get this again. Why should the postgresql server be
shutdown/restarted, if there was no change in the conf at that end?
Instead I think, the error, as you mentioned above, was in
understanding the tunneling. See my comment below.

> documentation was referring to when it said
> "For personal desktop use, we tend to use
> localhost:5432 (if you are not running a postgresql dev server
> locally) or localhost:someotherunusedport (e.g. localhost:5433)"
> You have to specify your SSH tunnel's entry point to be a port that you
> don't already have a program listening on. 3333 works for you, so stick with
> it.

Now, I got confused and had to go back and revisit Regina's doc. The
correct way to configure PuTTY (if not using the 5432 port on both
ends) would be:

L5433 -> localhost:5432

and not

L5432 -> localhost:5433

This is immaterial of whether the local machine (client) is running a
Postgres server or not.

[snip]
> I hope this helps.

Again thanks for the detailed explanations... They were certainly helpful.

> -Travis

Thanks again.
--
Regards
PK
--------------------------------------
http://counter.li.org #402424