ssl tunneling in postgres 8.1

Hi,

I am reading the documentation from here:
http://www.postgresql.org/docs/8.1/static/ssh-tunnels.html

I am able to ssh (I use ssh keys) in to foo.com using the username
joe. My client machine (localhost) is bar.com with username sam (say).

bar$ ssh -L 3333:foo.com:5432 joe(at)foo(dot)com
[this logs me into foo as joe; here i have access to a database named
"joe" using the password "joepass"]

So I try to connect to this local port (but remote server) by:

bar$ psql -h localhost -p 3333 joe -U joe -W
Password for user joe: joepass
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

And on the remote ssh terminal I get the following error:

foo$ channel 3: open failed: connect failed: Connection refused

What am I doing wrong? I don't know if this matters: foo.com (remote)
runs the 8.1 server and bar.com (my client machine/localhost) runs
8.3. What server parameter needs to be tweaked?

Is this relevant: http://www.postgresql.org/docs/8.1/static/ssl-tcp.html ?

foo$ postgres --describe-config | grep ssl
ssl postmaster Connections and Authentication / Security and
Authentication BOOLEAN FALSE Enables SSL connections.

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

am Tue, dem 18.11.2008, um 3:46:34 -0500 mailte P Kapat folgendes:
> Hi,
>
> I am reading the documentation from here:
> http://www.postgresql.org/docs/8.1/static/ssh-tunnels.html
>
> I am able to ssh (I use ssh keys) in to foo.com using the username
> joe. My client machine (localhost) is bar.com with username sam (say).
>
> bar$ ssh -L 3333:foo.com:5432 joe(at)foo(dot)com
> [this logs me into foo as joe; here i have access to a database named
> "joe" using the password "joepass"]
>
> So I try to connect to this local port (but remote server) by:
>
> bar$ psql -h localhost -p 3333 joe -U joe -W
> Password for user joe: joepass
> psql: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
>
> And on the remote ssh terminal I get the following error:
>
> foo$ channel 3: open failed: connect failed: Connection refused
>
> What am I doing wrong? I don't know if this matters: foo.com (remote)
> runs the 8.1 server and bar.com (my client machine/localhost) runs
> 8.3. What server parameter needs to be tweaked?

I can reproduce it with an 8.3 Server and a 8.1 psql-client. If i try it
in the other direction, i got this:

WARNING: You are connected to a server with major version 8.1,
but your psql client is major version 8.3. Some backslash commands,
such as \d, might not work properly.

I would simply use ssh to login into the remote server and use the local
psql. Is this possible for you? Or install the propper psql-client.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Hi,

On Tue, Nov 18, 2008 at 4:22 AM, A. Kretschmer
<andreas(dot)kretschmer(at)schollglas(dot)com> wrote:
> am Tue, dem 18.11.2008, um 3:46:34 -0500 mailte P Kapat folgendes:
>> bar$ psql -h localhost -p 3333 joe -U joe -W
>> Password for user joe: joepass
>> psql: server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>>
>> And on the remote ssh terminal I get the following error:
>>
>> foo$ channel 3: open failed: connect failed: Connection refused
>>
>> What am I doing wrong? I don't know if this matters: foo.com (remote)
>> runs the 8.1 server and bar.com (my client machine/localhost) runs
>> 8.3. What server parameter needs to be tweaked?
>
> I can reproduce it with an 8.3 Server and a 8.1 psql-client. If i try it
> in the other direction, i got this:
>
> WARNING: You are connected to a server with major version 8.1,
> but your psql client is major version 8.3. Some backslash commands,
> such as \d, might not work properly.

On the contrary, in the reverse direction (8.1 client to 8.3 server ie
bar to foo), I get the exact same error as for the connection from 8.3
to 8.1!! Not the WARNING! I should also mention that the local machine
(bar.com) is behind a NAT a router, so for the reverse direction to
even give error, I had to forward the 5432 port form the router to the
intended machine.

> I would simply use ssh to login into the remote server and use the local
> psql. Is this possible for you? Or install the propper psql-client.

Well, I am of the opinion that the different versions is not the
curlprit here. In other words, even if I use the 8.1 psql-client, I am
sure I'll get this error. There is some configuration/misconfiguration
on the server's end that is blocking the connection.

I certainly appreciate any help in getting this to work. My main goal
is to use pgadminIII later. But first I want to make sure that this
step works.

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

"P Kapat" <kap4lin(at)gmail(dot)com> writes:
> I am reading the documentation from here:
> http://www.postgresql.org/docs/8.1/static/ssh-tunnels.html

> I am able to ssh (I use ssh keys) in to foo.com using the username
> joe. My client machine (localhost) is bar.com with username sam (say).

> bar$ ssh -L 3333:foo.com:5432 joe(at)foo(dot)com
> [this logs me into foo as joe; here i have access to a database named
> "joe" using the password "joepass"]

> So I try to connect to this local port (but remote server) by:

> bar$ psql -h localhost -p 3333 joe -U joe -W
> Password for user joe: joepass
> psql: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.

> And on the remote ssh terminal I get the following error:

> foo$ channel 3: open failed: connect failed: Connection refused

> What am I doing wrong?

My bet is that you have the Postgres server configured so that it
only accepts Unix-socket connections and not local TCP connections
(which is what the SSH tunnel will try to connect to). If you do
"psql -h localhost" on the remote server, does it work?

If not, you need to fool with listen_addresses and possibly your
pg_hba.conf setup.

regards, tom lane

On Tue, Nov 18, 2008 at 2:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "P Kapat" <kap4lin(at)gmail(dot)com> writes:
>> I am reading the documentation from here:
>> http://www.postgresql.org/docs/8.1/static/ssh-tunnels.html
>
>> I am able to ssh (I use ssh keys) in to foo.com using the username
>> joe. My client machine (localhost) is bar.com with username sam (say).
>
>> bar$ ssh -L 3333:foo.com:5432 joe(at)foo(dot)com
>> [this logs me into foo as joe; here i have access to a database named
>> "joe" using the password "joepass"]
>
>> So I try to connect to this local port (but remote server) by:
>
>> bar$ psql -h localhost -p 3333 joe -U joe -W
>> Password for user joe: joepass
>> psql: server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>
>> And on the remote ssh terminal I get the following error:
>
>> foo$ channel 3: open failed: connect failed: Connection refused
>
>> What am I doing wrong?
>
> My bet is that you have the Postgres server configured so that it
> only accepts Unix-socket connections and not local TCP connections
> (which is what the SSH tunnel will try to connect to). If you do
> "psql -h localhost" on the remote server, does it work?

Well, it seems to connect:

foo$ psql -h localhost
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
[usual messages: snipped]

> If not, you need to fool with listen_addresses and possibly your
> pg_hba.conf setup.

I am not sure how to tinker with these conf files. The server policy
will not allow any non-local connection. But as a test case I can play
with the reverse connection - remote as client and local as the
server. I have full access to my local (bar) machine. As I said, the
errors are exactly similar.

Any pointers?
--
Regards
PK
--------------------------------------
http://counter.li.org #402424

"P Kapat" <kap4lin(at)gmail(dot)com> writes:
> On Tue, Nov 18, 2008 at 2:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> My bet is that you have the Postgres server configured so that it
>> only accepts Unix-socket connections and not local TCP connections
>> (which is what the SSH tunnel will try to connect to). If you do
>> "psql -h localhost" on the remote server, does it work?

> Well, it seems to connect:

Huh. It should work then ... and does work when I try it here.
Are you sure you put the right port numbers in the SSH command
line (eg, 5432 is really the port Postgres is listening to)?

regards, tom lane

On 11/18/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Huh. It should work then ... and does work when I try it here.
> Are you sure you put the right port numbers in the SSH command
> line (eg, 5432 is really the port Postgres is listening to)?

Thanks Tom. Your patience is extremely valuable to me. I need to get
this sorted out. So...

Something ridiculous is going on here.... I have a XP machine (say
'duh', different from 'bar' but behind the same router) at home. I
installed pgadminIII (just the frontend, not with the whole postgresql
server) from here:

http://www.postgresql.org/ftp/pgadmin3/release/v1.8.4/win32/

And I followed the "ssh tunneling via PuTTY" instructions from here (verbatim):

http://www.postgresonline.com/journal/index.php?/archives/38-PuTTY-for-SSH-Tunneling-to-PostgreSQL-Server.html

And voila, everything worked perfect!!! I was able to connect to the
postgres server on foo (or bar). NOTE THE PORTS:

Source port: 5432 (on foo, I guess)
Destination: localhost:5432 (on duh, same 5432!!!)

The site also notes this: "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)"

Now, I do not understand this statement completely! If I use
"localhost:5433" in the Destination box in PuTTY, then I get the
following TCP error in pgadmin:

Server doesn't listen
The server doesn't accept connections: the connection library reports
could not connect to server: Connection refused (0x0000274D/10061) Is
the server running on host "127.0.0.1" and accepting TCP/IP
connections on port 5433

Why is 5432 so sacred on the client side? For connecting from 'bar' to
'foo' (or vice versa), I can not do this, there is already a local
postgresql server running on 5432.

Help?

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

PK,

Sorry about that I should have elaborated on the optional in that article. I'll update that article with this if this works for you.

There is actually nothing magical about 5432. Its just that if you have the ports the same on local and remote, you just need one forwarding rule. If you are forwarding to different ports - then you need a rule to go one way and a rule to go the other way.

Anyrate to fix your problem - your tunneling should look something like this

R5432 -- > localhost:5433
L5433 --> localhost:5432

Hope that clarifies it.

Thanks,
Regina

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org on behalf of P Kapat
Sent: Wed 11/19/2008 2:57 AM
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] ssl tunneling in postgres 8.1

On 11/18/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Huh. It should work then ... and does work when I try it here.
> Are you sure you put the right port numbers in the SSH command
> line (eg, 5432 is really the port Postgres is listening to)?

Thanks Tom. Your patience is extremely valuable to me. I need to get
this sorted out. So...

Something ridiculous is going on here.... I have a XP machine (say
'duh', different from 'bar' but behind the same router) at home. I
installed pgadminIII (just the frontend, not with the whole postgresql
server) from here:

http://www.postgresql.org/ftp/pgadmin3/release/v1.8.4/win32/

And I followed the "ssh tunneling via PuTTY" instructions from here (verbatim):

http://www.postgresonline.com/journal/index.php?/archives/38-PuTTY-for-SSH-Tunneling-to-PostgreSQL-Server.html

And voila, everything worked perfect!!! I was able to connect to the
postgres server on foo (or bar). NOTE THE PORTS:

Source port: 5432 (on foo, I guess)
Destination: localhost:5432 (on duh, same 5432!!!)

The site also notes this: "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)"

Now, I do not understand this statement completely! If I use
"localhost:5433" in the Destination box in PuTTY, then I get the
following TCP error in pgadmin:

Server doesn't listen
The server doesn't accept connections: the connection library reports
could not connect to server: Connection refused (0x0000274D/10061) Is
the server running on host "127.0.0.1" and accepting TCP/IP
connections on port 5433

Why is 5432 so sacred on the client side? For connecting from 'bar' to
'foo' (or vice versa), I can not do this, there is already a local
postgresql server running on 5432.

Help?

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

--
Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

oh sweet sweet god.. finally!!!

On 11/19/08, Obe, Regina <robe(dot)dnd(at)cityofboston(dot)gov> wrote:
> PK,
>
> Sorry about that I should have elaborated on the optional in that article.
> I'll update that article with this if this works for you.
>
> There is actually nothing magical about 5432. Its just that if you have
> the ports the same on local and remote, you just need one forwarding rule.
> If you are forwarding to different ports - then you need a rule to go one
> way and a rule to go the other way.
>
> Anyrate to fix your problem - your tunneling should look something like
> this
>
> R5432 -- > localhost:5433
> L5433 --> localhost:5432

Thank you Regina. If I ever come to Boston, I owe you a drink :))

So, let us consider the the following "official" postgresql documentations:
http://www.postgresql.org/docs/8.1/static/ssh-tunnels.html (carried
over to: 8.2, 8.3 and also valid for the "interactive" versions in
place of "static")
The suggested commands are:

client$ ssh -L 3333:foo.com:5432 joe(at)foo(dot)com
client$ psql -h localhost -p 3333 postgres

They are just INCOMPLETE, half-baked mess :( In fact, they are simply
wrong! These pages state: "The name or IP address between the port
numbers is the host with the database server you are going to connect
to." !!!!!!!

The correct commands are (at least the ones that worked in my case):

client$ ssh -R 5432:localhost:3333 -L 3333:localhost:5432 joe(at)foo(dot)com
client$ psql -h localhost -p 3333 postgres -U joe (or whatever the DB
username/Role is)

Right? If so, then the documentation needs to be updated (rather,
rectified), for heaven's sake!

Once again, thanks a ton, Regina :)
--
Regards
PK
--------------------------------------
http://counter.li.org #402424

"P Kapat" <kap4lin(at)gmail(dot)com> writes:
> The suggested commands are:

> client$ ssh -L 3333:foo.com:5432 joe(at)foo(dot)com
> client$ psql -h localhost -p 3333 postgres

> They are just INCOMPLETE, half-baked mess :( In fact, they are simply
> wrong!

They're not wrong. There's still something funny about your setup
if that doesn't work ... and I'm afraid Regina's suggestion of a
reverse channel is just nonsense.

> The correct commands are (at least the ones that worked in my case):

> client$ ssh -R 5432:localhost:3333 -L 3333:localhost:5432 joe(at)foo(dot)com

The -R switch is useless here. The important point AFAICT is that you
used localhost rather than foo.com in the -L switch. That name is being
evaluated at the remote end. What I suppose is happening is that the
Postgres server is configured to listen to 127.0.0.1 (ie, "localhost")
but not its external IP address (whatever "foo.com" resolves as).
If you don't want to change that then "localhost" is the correct thing
to be using.

regards, tom lane

> They're not wrong. There's still something funny about your setup
> if that doesn't work ... and I'm afraid Regina's suggestion of a
> reverse channel is just nonsense.

>> The correct commands are (at least the ones that worked in my case):

>> client$ ssh -R 5432:localhost:3333 -L 3333:localhost:5432 joe(at)foo(dot)com

> The -R switch is useless here. The important point AFAICT is that you
> used localhost rather than foo.com in the -L switch. That name is being
> evaluated at the remote end. What I suppose is happening is that the
> Postgres server is configured to listen to 127.0.0.1 (ie, "localhost")
> but not its external IP address (whatever "foo.com" resolves as).
> If you don't want to change that then "localhost" is the correct thing
> to be using.
Tom,

I'll have to try this with just an ssh raw command. I was trying it in putty
and in order for it to work if my localhost port was different from the server's listening port
I had to put in that extra forward rule. I fiddled with the port thingys and it seemed I needed 2. I'll give it another go.

If the ports were the same, all worked fine. This is in situation similar to above where my remote postgresql is only listening on localhost.

I thought it was strange too, but that was just merely my speculation of why I needed to put that extra rule in there. Maybe I just had the command wrong to begin with.

So why did I need to put that extra one in? Anyrate all these forwarding port direction/firewall inbound/outbound things confuse the hell out of me. I do have a firewall that doesn't allow inbound connections on my local network. Wonder if that plays a role somehow.

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

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

Tom, thanks again for correcting the confusion and my lack of
knowledge in SSH tunneling.

On 11/19/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "P Kapat" <kap4lin(at)gmail(dot)com> writes:
>> The suggested commands are:
>
>> client$ ssh -L 3333:foo.com:5432 joe(at)foo(dot)com
>> client$ psql -h localhost -p 3333 postgres
>
>> They are just INCOMPLETE, half-baked mess :( In fact, they are simply
>> wrong!
>
> They're not wrong. There's still something funny about your setup
> if that doesn't work ...

OK, agreed, it is not wrong, but it is certainly not transparent
either. If I understand the things now, the documentation as is (-L
3333:foo.com:5432) works as long as

listen_addresses = '*' OR listen_addresses = 'a.b.c.d' (where a.b.c.d
is the IP of foo.com)

in postgresql.conf. Am I right? If so, is this obvious from the online
documentation? Unfortunately, it wasn't to me!

>> The correct commands are (at least the ones that worked in my case):
>
>> client$ ssh -R 5432:localhost:3333 -L 3333:localhost:5432 joe(at)foo(dot)com
>
> The -R switch is useless here. The important point AFAICT is that you
> used localhost rather than foo.com in the -L switch. That name is being
> evaluated at the remote end. What I suppose is happening is that the
> Postgres server is configured to listen to 127.0.0.1 (ie, "localhost")
> but not its external IP address (whatever "foo.com" resolves as).

Precisely, that is the configuration: " listen_addresses = 'localhost'
".... In which case, " -L 3333:localhost:5432 " has to be used.
Shouldn't this be mentioned in the documentation? This whole thing is
not trivial!!!

> regards, tom lane

Thanks again for the help, sincerely appreciated.

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

> OK, agreed, it is not wrong, but it is certainly not transparent
> either. If I understand the things now, the documentation as is (-L
> 3333:foo.com:5432) works as long as

> Precisely, that is the configuration: " listen_addresses = 'localhost'
> ".... In which case, " -L 3333:localhost:5432 " has to be used.
> Shouldn't this be mentioned in the documentation? This whole thing is
> not trivial!!!

Sorry for the misinformation guys.
Thanks all for the clarification. That works for me too. I have corrected my journal entry.

Anyrate I agree that the localhost localhost thing is not an obvious thing and should be documented. I mean that's the common reason I use tunneling so I can have my server only listen on localhost.

Thanks,
Regina

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

"Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov> writes:
> I'll have to try this with just an ssh raw command. I was trying it in putty
> and in order for it to work if my localhost port was different from the server's listening port
> I had to put in that extra forward rule.

Hmm, I don't know anything about putty. But there's really only one
connection involved ...

regards, tom lane

> Hmm, I don't know anything about putty. But there's really only one
> connection involved ...

> regards, tom lane

Yap agreed and as stated and as PK mentioned, You are very very right Tom.
Putty ends up being the same thing. It just provides a gui for you is all but it ends up setting up an
SSH Tunnel syntax that looks like this

L8888 localhost:5432

So I never noticed my follie before because when you have 5432 5432 its symmetric so doesn't matter
L5432 localhost:5432

Damn I hate things that aren't symmetric. I have a mental block when it comes to distinguishing my left from my right.
The pain of being ambidextrous brain-damaged. :).

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.