tunneling through ssh

[David Bear]

I'm attempting to run pgsql through a tunnel. I'm using the default
pg_hba.conf file for now which has the relevant information:

------------
local all all
trust
host all all 127.0.0.1 255.255.255.255
trust
------------

I assume this means that the back end will bind to 127.0.0.1:5432
since that seems to be the default port number.

Yet, when trying to come through the tunnel I get this in my logs on
the 'server' machine - the one running postgres backend.

-----------
Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to ::1 port 5432:
Connection refused
Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to 127.0.0.1 port
5432: Connection refused
Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to localhost port
5432: failed.
-----------

Am I missing something obvious?
--
David Bear
phone: [phone]
fax: [phone]
College of Public Programs/ASU
Wilson Hall 232
Tempe, AZ 85287-0803
"Beware the IP portfolio, everyone will be suspect of trespassing"

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Steve Crawford]

Post by David Bear
I'm attempting to run pgsql through a tunnel. I'm using the default
------------
local all all
trust
host all all 127.0.0.1 255.255.255.255
trust
------------
I assume this means that the back end will bind to 127.0.0.1:5432
since that seems to be the default port number.
Yet, when trying to come through the tunnel I get this in my logs
on the 'server' machine - the one running postgres backend.
-----------
Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to ::1 port
5432: Connection refused
Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to 127.0.0.1
port 5432: Connection refused
Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to localhost
port 5432: failed.
-----------
Am I missing something obvious?

Is PG set to accept tcp/ip connections? Check postgresql.conf for:
tcpip_socket=true

127.0.0.1 is connecting through tcp/ip, not local domain sockets.

Cheers,
Steve


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[David Bear]

Post by Steve Crawford

Post by David Bear
I'm attempting to run pgsql through a tunnel. I'm using the default
------------
local all all
trust
host all all 127.0.0.1 255.255.255.255
trust
------------
I assume this means that the back end will bind to 127.0.0.1:5432
since that seems to be the default port number.
Yet, when trying to come through the tunnel I get this in my logs
on the 'server' machine - the one running postgres backend.
-----------
Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to ::1 port
5432: Connection refused
Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to 127.0.0.1
port 5432: Connection refused
Aug 18 16:00:40 dbsrv1 sshd[41006]: error: connect_to localhost
port 5432: failed.
-----------
Am I missing something obvious?

tcpip_socket=true
127.0.0.1 is connecting through tcp/ip, not local domain sockets.

thanks for the info. I've check the postgresql.conf file and have the
following lines:
----------
tcpip_socket = true
max_connections = 40
port = 5432
virtual_host = '127.0.0.1' # what interface to listen on; defaults to any
----------

I assume I am binding to tcp socket 5432 as sockstat reveals:
----------
USER COMMAND PID FD PROTO LOCAL ADDRESS FOREIGN
pgsql postgres 41229 3 tcp4 127.0.0.1:5432 *:*
----------

I still get the error:

---------
$>psql -h localhost -p 4001 test1
psql: could not receive server response to SSL negotiation packet: No
such file or directory
---------
and on the ssh'd terminal tunnel session:
--------
$ channel 3: open failed: administratively prohibited: open failed
-------

Any other suggestions?

Post by Steve Crawford
Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

--
David Bear
phone: [phone]
fax: [phone]
College of Public Programs/ASU
Wilson Hall 232
Tempe, AZ 85287-0803
"Beware the IP portfolio, everyone will be suspect of trespassing"

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Post by David Bear
--------
$ channel 3: open failed: administratively prohibited: open failed
-------

Perhaps a firewalling problem? Look at your kernel packet filtering
setup ... it's not uncommon for even local-loopback traffic to be
aggressively filtered by default, and none of the people who set these
things up think port 5432 is a standard service :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[David Bear]

Post by Tom Lane

Post by David Bear
--------
$ channel 3: open failed: administratively prohibited: open failed
-------

Perhaps a firewalling problem? Look at your kernel packet filtering
setup ... it's not uncommon for even local-loopback traffic to be
aggressively filtered by default, and none of the people who set these
things up think port 5432 is a standard service :-(

thanks for all the suggestions. The solution was to tunnel
'localhost' -- the key is knowing which localhost localhost is
referring too.. so, here's what I did.

ssh -L 4001:localhost:5432 ***@dbsrv1

This allows postgresql to listen on the 'localhost' interface..

thanks for all the replies.
--
David Bear
phone: [phone]
fax: [phone]
College of Public Programs/ASU
Wilson Hall 232
Tempe, AZ 85287-0803
"Beware the IP portfolio, everyone will be suspect of trespassing"

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Jim Seymour]

Post by David Bear
I'm attempting to run pgsql through a tunnel.

[snip]

Through an ssh tunnel, using port-forwarding, I'm guessing?

Post by David Bear
Am I missing something obvious?

Can you "psql -p 5432" on the machine on which the server is running?

Here's what I just did successfully:

On local machine, ssh to host running pgsql server:

$ xterm -e ssh -L 57000:remote.example.com:5432 remote.example.com &

After doing the ssh pass-word/-phrase thing...

On local machine:

$ psql -U jseymour -p 57000 -h localhost
Password:
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

jseymour=> \q
$

Worked like a champ. This is using OpenSSH, btw.

Jim

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings