Обсуждение: tunneling through ssh
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: 480-965-8257 fax: 480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 "Beware the IP portfolio, everyone will be suspect of trespassing"
On Wednesday 18 August 2004 4:13 pm, David Bear wrote: > 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? 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
On Wed, Aug 18, 2004 at 04:21:24PM -0700, Steve Crawford wrote: > On Wednesday 18 August 2004 4:13 pm, David Bear wrote: > > 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? > > 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. 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? > > 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: 480-965-8257 fax: 480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 "Beware the IP portfolio, everyone will be suspect of trespassing"
David Bear <David.Bear@asu.edu> writes:
> and on the ssh'd terminal tunnel session:
> --------
> $ 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
David Bear <David.Bear@asu.edu> wrote:
>
> I'm attempting to run pgsql through a tunnel.
[snip]
Through an ssh tunnel, using port-forwarding, I'm guessing?
>
> 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
On Wed, Aug 18, 2004 at 09:21:54PM -0400, Tom Lane wrote: > David Bear <David.Bear@asu.edu> writes: > > and on the ssh'd terminal tunnel session: > > -------- > > $ 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 iddwb@dbsrv1 This allows postgresql to listen on the 'localhost' interface.. thanks for all the replies. -- David Bear phone: 480-965-8257 fax: 480-965-9189 College of Public Programs/ASU Wilson Hall 232 Tempe, AZ 85287-0803 "Beware the IP portfolio, everyone will be suspect of trespassing"