Обсуждение: - what protocol for an Internet postgres connection
Hi, I've been toying around with postgresql for some time now. Our database server is based on a linux box (RH 8) and the client side was developed in CBuilder (and Delphi) connecting via ODBC. It works just great in the LAN. At this time we are commited to deliver a similar application but with a different setup. The database server must be internet available. The client side will be basicly the same app we have now. It is not very data intensive by any means, per session it will use about 50Kb of data at most. Concurrent users are not expected to be more than five. Its a very light application but still given the special features that it has to encompass is not feasible or cost-wise to evolve it into a Web application. The question is: what connection protocol would be the best suited for this task ? any input is very appreciated -Fernando
Fernando Flores Prior <fprior@tlaloc.imta.mx> writes: > Our database server is based on a linux box (RH 8) and > the client side was developed in CBuilder (and Delphi) connecting > via ODBC. It works just great in the LAN. > > At this time we are commited to deliver a similar application but with > a different > setup. The database server must be internet available. The client side will > be basicly the same app we have now. It is not very data intensive by any > means, per session it will use about 50Kb of data at most. Concurrent users > are not expected to be more than five. Its a very light application but still > given the special features that it has to encompass is not feasible or > cost-wise > to evolve it into a Web application. > > The question is: what connection protocol would be the best suited > for this task ? There is just one PostgreSQL wire protocol. Different libraries such as ODBC, libpq etc just give you a different API for using it. There shouldn't be too much difference in bandwidth usage between the APIs in the general case. So your app should work fine, but pay very careful attention to security anytime you expose the database directly to the 'net at large. Use the 'md5' password scheme, and, if you can, restrict the allowed IPs in pg_hba.conf to valid client addresses. -Doug > > any input is very appreciated > > > -Fernando > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
On Wednesday 14 May 2003 4:11 pm, Fernando Flores Prior wrote: > At this time we are commited to deliver a similar application but with a > different > setup. The database server must be internet available. The client side will > be basicly the same app we have now. It is not very data intensive by any > means, per session it will use about 50Kb of data at most. Concurrent users > are not expected to be more than five. Its a very light application but > still given the special features that it has to encompass is not feasible > or cost-wise > to evolve it into a Web application. > > The question is: what connection protocol would be the best suited for > this task ? Since you don't seem to have many users, I'd be tempted to look at ssh-tunnelling. You can use ssh to redirect your local PGPORT over an encrypted, authenticated link to the PGPORT on the remote server. Firewall this port off and have Postgresql only allow access via localhost. Your ODBC settings on the client point to localhost where they get redirected to the server. The application never knows or cares what is happening. Your users just connect to the db server first (using ssh), then use the application as before. There are two ssh clients for windows I know of: Putty and (I think) TeraTerm. Don't know about licencing for these, but both are freely downloadable. On Linux/BSD there is the openssh project. One step up from that would be some kind of SSL tunnelling, but SSH is probably the least-effort solution. -- Richard Huxton
Hi Doug, So, it is safe then to use ODBC for an Internet connection ? I'm in doubt about that, mainly because of the lag involved on this kind of network and its transient failures that may cause a disconnection. Thanks a lot for your advice ! At 11:39 a.m. 14/05/2003 -0400, Doug McNaught wrote: >Fernando Flores Prior <fprior@tlaloc.imta.mx> writes: > > > Our database server is based on a linux box (RH 8) and > > the client side was developed in CBuilder (and Delphi) connecting > > via ODBC. It works just great in the LAN. > > > > At this time we are commited to deliver a similar application but with > > a different > > setup. The database server must be internet available. The client side will > > be basicly the same app we have now. It is not very data intensive by any > > means, per session it will use about 50Kb of data at most. Concurrent users > > are not expected to be more than five. Its a very light application > but still > > given the special features that it has to encompass is not feasible or > > cost-wise > > to evolve it into a Web application. > > > > The question is: what connection protocol would be the best suited > > for this task ? > >There is just one PostgreSQL wire protocol. Different libraries such >as ODBC, libpq etc just give you a different API for using it. There >shouldn't be too much difference in bandwidth usage between the APIs >in the general case. > >So your app should work fine, but pay very careful attention to >security anytime you expose the database directly to the 'net at >large. Use the 'md5' password scheme, and, if you can, restrict the >allowed IPs in pg_hba.conf to valid client addresses. > >-Doug > > > > > > any input is very appreciated > > > > > > -Fernando > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster
Fernando Flores Prior <fprior@tlaloc.imta.mx> writes: > Hi Doug, > > So, it is safe then to use ODBC for an Internet connection ? > I'm in doubt about that, mainly because of the lag involved > on this kind of network and its transient failures that may > cause a disconnection. It's as safe as any other API--as I said, there's only one wire protocol used by all libraries. Lag will just slow things down but should not cause any data loss. If your connection drops too often due to packet loss there isn't much you can do about that at the PG level--get a better network connection. :) -Doug
Doug McNaught <doug@mcnaught.org> writes:
> Fernando Flores Prior <fprior@tlaloc.imta.mx> writes:
>> So, it is safe then to use ODBC for an Internet connection ?
>> I'm in doubt about that, mainly because of the lag involved
>> on this kind of network and its transient failures that may
>> cause a disconnection.
> It's as safe as any other API--as I said, there's only one wire
> protocol used by all libraries. Lag will just slow things down but
> should not cause any data loss. If your connection drops too often
> due to packet loss there isn't much you can do about that at the PG
> level--get a better network connection. :)
However, from a security standpoint I don't think there's any question
that you want to use SSL encryption for any database connection that
passes over the open Internet. I seem to recall that the ODBC driver
(still) doesn't support SSL --- if so, that would be reason enough not
to use it. Or to invest the effort to fix it.
regards, tom lane
Ok, Now I have a better vision of what can be accomplished with the options that you gave me. Thanks a lot (Richard, Doug and Tom). -Fernando At 12:37 p.m. 14/05/2003 -0400, Tom Lane wrote: >Doug McNaught <doug@mcnaught.org> writes: > > Fernando Flores Prior <fprior@tlaloc.imta.mx> writes: > >> So, it is safe then to use ODBC for an Internet connection ? > >> I'm in doubt about that, mainly because of the lag involved > >> on this kind of network and its transient failures that may > >> cause a disconnection. > > > It's as safe as any other API--as I said, there's only one wire > > protocol used by all libraries. Lag will just slow things down but > > should not cause any data loss. If your connection drops too often > > due to packet loss there isn't much you can do about that at the PG > > level--get a better network connection. :) > >However, from a security standpoint I don't think there's any question >that you want to use SSL encryption for any database connection that >passes over the open Internet. I seem to recall that the ODBC driver >(still) doesn't support SSL --- if so, that would be reason enough not >to use it. Or to invest the effort to fix it. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster
On Wed, 14 May 2003 11:54:49 -0500 Fernando Flores Prior <fprior@tlaloc.imta.mx> wrote:
> Now I have a better vision of what can be accomplished with the
> options that you gave me.
> At 12:37 p.m. 14/05/2003 -0400, Tom Lane wrote:
> >However, from a security standpoint I don't think there's any question
> >that you want to use SSL encryption for any database connection that
> >passes over the open Internet. I seem to recall that the ODBC driver
> >(still) doesn't support SSL --- if so, that would be reason enough not
> >to use it. Or to invest the effort to fix it.
i just caught the end of this.
if for some reason you need to use a driver that doesn't support SSL (say,
the aforementioned ODBC driver) there may be tunneling options that can be
made to work, using tools such as ssh, stunnel, or IPSec.
those are really more network engineering/sysadmin things than postgresql
things, and the appropriate method will vary depending on circumstances.
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security
Richard Welty wrote: > On Wed, 14 May 2003 11:54:49 -0500 Fernando Flores Prior <fprior@tlaloc.imta.mx> wrote: > >>Now I have a better vision of what can be accomplished with the >>options that you gave me. > > >>At 12:37 p.m. 14/05/2003 -0400, Tom Lane wrote: >> >>>However, from a security standpoint I don't think there's any question >>>that you want to use SSL encryption for any database connection that >>>passes over the open Internet. I seem to recall that the ODBC driver >>>(still) doesn't support SSL --- if so, that would be reason enough not >>>to use it. Or to invest the effort to fix it. > > > i just caught the end of this. > > if for some reason you need to use a driver that doesn't support SSL (say, > the aforementioned ODBC driver) there may be tunneling options that can be > made to work, using tools such as ssh, stunnel, or IPSec. > > those are really more network engineering/sysadmin things than postgresql > things, and the appropriate method will vary depending on circumstances. If I recall correctly, ComandPrompt Inc. (www.commandprompt.com) sells an SSL-enabled ODBC driver. I've never used it though... Mike Mascari mascarm@mascari.com
This thread got my curiosity going, so I endeavored to try to get it working and am happy to say that it was very easy to tunnel a postgresql connection over ssh. Here's what I did... 1. Download Putty (actually, I already had it installed) http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html 2. Set up a session in Putty that allowed me to SSH into the database server. For reference, we'll pretend the session is named "postgres_serv" 3. Once that was working, open the putty configuration box, choose the session name, click Load, on the right, click "Tunnels" under Connection -> SSH 4. In the "Add new forwarded port" box, type 5432 into the Source port and put localhost:5432 into the Destination field. Make sure "Local" is marked. 5. In the left hand panel, click Session and then the "Save" button. 6. Close the Putty configuration box. 7. Right click on your desktop, choose New -> Shortcut 8. Use the browse button to find the path to your Putty install folder, but don't choose the Putty.exe file, instead choose "plink.exe". 9. In the box that shows you the path to the file, add the name of your saved session to the end (outside of the quotes if you have quotes around the exe name. For example, your box may say: "C:\Program Files\Putty\plink.exe" postgres_serv 10. Give the short some useful name such as "connect to postgres" and save it. 11. Test it by double clicking on it. A dos window should open, connect to your postgresql server and depending on your public key configuration, may ask you for a password. I prefer to set up a key pair so that no password is required to open a connection. There are instructions on how to do this around the internet. 12. Create a new ODBC connection. For the server name, type localhost, for port, use 5432. Choose a database name and enter a password. 13. Test it by opening access, excel or some other program that uses ODBC connections. You should see all your tables and views that you have access too. Sweet, right? When you're done with your connection, you should type exit at the ssh prompt that opened when you made your database connection. I'm thinking it would be cool to whip out a little VC++ prog that would do the work for me. I'll bet a COM app could be made that comes with plink and lets your VB or other windows program make an SSH connection to a server for port forwarding purposes. The article here http://www.codeguru.com/console/QuickWin.shtml explains how to make a program that can send commands to/from a console app such as plink. It can really be done pretty seamlessly. Plink looks in the registry under HKEY_CURRENT_USER\Software\SimonTatham\PuTTY\Sessions\postgres_serv for the information it needs to make the connection to your postgres_serv. You can then create the registry values, put plink.exe in the same folder as your other exes, create your COM program that takes the username/password as params, opens the connection using plink, provides the credentials when needed, opens the ports for your program to use with the odbc driver. When the connection is not needed, your COM would send "exit", tear down the connection and then re-connect again when needed. I haven't tried it yet, but it sounds like it would work and provide a very secure connection. -- Matthew Nuzum www.bearfruit.org cobalt@bearfruit.org > -----Original Message----- > From: Richard Welty [mailto:rwelty@averillpark.net] > Sent: Wednesday, May 14, 2003 1:07 PM > To: pgsql-general@postgresql.org > Subject: Re: - what protocol for an Internet postgres > > On Wed, 14 May 2003 11:54:49 -0500 Fernando Flores Prior > <fprior@tlaloc.imta.mx> wrote: > > Now I have a better vision of what can be accomplished with the > > options that you gave me. > > > At 12:37 p.m. 14/05/2003 -0400, Tom Lane wrote: > > >However, from a security standpoint I don't think there's any question > > >that you want to use SSL encryption for any database connection that > > >passes over the open Internet. I seem to recall that the ODBC driver > > >(still) doesn't support SSL --- if so, that would be reason enough not > > >to use it. Or to invest the effort to fix it. > > i just caught the end of this. > > if for some reason you need to use a driver that doesn't support SSL (say, > the aforementioned ODBC driver) there may be tunneling options that can be > made to work, using tools such as ssh, stunnel, or IPSec. > > those are really more network engineering/sysadmin things than postgresql > things, and the appropriate method will vary depending on circumstances. > > richard > -- > Richard Welty > rwelty@averillpark.net > Averill Park Networking 518-573- > 7592 > Unix, Linux, IP Network Engineering, Security >
On Thursday 15 May 2003 1:49 am, Matthew Nuzum wrote: > This thread got my curiosity going, so I endeavored to try to get it > working and am happy to say that it was very easy to tunnel a postgresql > connection over ssh. > > Here's what I did... [snipped summary of setting up ssh tunnel] Could I tempt you into tidying this up a little (just needs an introduction really) and posting it on techdocs.postgresql.org? It's a good short summary, and would be useful to point people at in future. If you don't have the time, I could post it up for you and you could review it. Contact me off list if you're interested. Thanks -- Richard Huxton
Nice ! It works just great. This will be a fine asset for the thechdocs. -Fernando At 08:49 p.m. 14/05/2003 -0400, Matthew Nuzum wrote: >This thread got my curiosity going, so I endeavored to try to get it working >and am happy to say that it was very easy to tunnel a postgresql connection >over ssh. > >Here's what I did...
On Thu, 15 May 2003, Fernando Flores Prior wrote: > Nice ! > > It works just great. This will be a fine asset for the thechdocs. > > -Fernando > > > At 08:49 p.m. 14/05/2003 -0400, Matthew Nuzum wrote: > >This thread got my curiosity going, so I endeavored to try to get it working > >and am happy to say that it was very easy to tunnel a postgresql connection > >over ssh. > > > >Here's what I did... > Yes, smashing. Anyone got some similar destructions for SSH tunneling with SSH Communications Corp's (www.ssh.com) stuff. I've tried pointing and clicking in the appropiate dialog several times now and the best I can get is a notice from the firewall on the system that my test connection has connected to the localhost but get a connection refused and don't see any network traffic out to the server. I can ssh into the server and get a shell (just to clarify that point). -- Nigel J. Andrews
I use SSH all the time to tunnel shell and X-windows traffic. I think I remember getting a local psql connection to a remote database going following the instruction s in section 3.8 of the Adminstrators guide but its been a little bit. If no one else response I'll take a wack at it and send in a step-by-step example. As a note though, generally, I just SSH into the box that has the database and then run psql from there or open up pgaccess. Quoting "Nigel J. Andrews" <nandrews@investsystems.co.uk>: > On Thu, 15 May 2003, Fernando Flores Prior wrote: > > > Nice ! > > > > It works just great. This will be a fine asset for the thechdocs. > > > > -Fernando > > > > > > At 08:49 p.m. 14/05/2003 -0400, Matthew Nuzum wrote: > > >This thread got my curiosity going, so I endeavored to try to get it > working > > >and am happy to say that it was very easy to tunnel a postgresql > connection > > >over ssh. > > > > > >Here's what I did... > > > > Yes, smashing. Anyone got some similar destructions for SSH tunneling with > SSH > Communications Corp's (www.ssh.com) stuff. I've tried pointing and clicking > in > the appropiate dialog several times now and the best I can get is a notice > from > the firewall on the system that my test connection has connected to the > localhost but get a connection refused and don't see any network traffic out > to > the server. I can ssh into the server and get a shell (just to clarify that > point). > > > -- > Nigel J. Andrews > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
On Fri, 16 May 2003, Network Administrator wrote: > I use SSH all the time to tunnel shell and X-windows traffic. I think I > remember getting a local psql connection to a remote database going following > the instruction s in section 3.8 of the Adminstrators guide but its been a > little bit. > > If no one else response I'll take a wack at it and send in a step-by-step example. Thanks for that, race you ;) (see below) > > As a note though, generally, I just SSH into the box that has the database and > then run psql from there or open up pgaccess. This is very odd. Over the space of 4 months I've tried this several times and each time getting no where, including after reading the initial message in this thread. This evening I try again and as before get no where. However, I then go into the firewall setup and disable one particular rule that opens all remote hosts/ports for a particular service. I couldn't find one that matched the ssh port on the remote test machine and this was the only candidate despite the service name looking wrong for SSH and the associated program listed as a Windows system folder one. I verify the normal ssh login and firewall alerts. Try the tunneling, without having an open ssh session to the target system, and unsurprisingly it fails. Then I login to the target system, after finally remembering the password, and try the forwarding again and it works! Basically the only thing different in my setup from earlier attempts is this disabled firewall rule. I am very baffled but obviously in a position to write a short piece on using SSH tunneling with SSH Communications software. I say very short because there's little to say except the couple of setup steps and may be some vague mutterings about firewalls [but quite what I don't know]. Sorry for the rambling, it is late here, and I'm baffled, amazed and wondering how I'm going to explain to the two remote developers why they should try it after all these months. -- Nigel J. Andrews > > Quoting "Nigel J. Andrews" <nandrews@investsystems.co.uk>: > > > On Thu, 15 May 2003, Fernando Flores Prior wrote: > > > > > Nice ! > > > > > > It works just great. This will be a fine asset for the thechdocs. > > > > > > -Fernando > > > > > > > > > At 08:49 p.m. 14/05/2003 -0400, Matthew Nuzum wrote: > > > >This thread got my curiosity going, so I endeavored to try to get it > > working > > > >and am happy to say that it was very easy to tunnel a postgresql > > connection > > > >over ssh. > > > > > > > >Here's what I did... > > > > > > > Yes, smashing. Anyone got some similar destructions for SSH tunneling with > > SSH > > Communications Corp's (www.ssh.com) stuff. I've tried pointing and clicking > > in > > the appropiate dialog several times now and the best I can get is a notice > > from > > the firewall on the system that my test connection has connected to the > > localhost but get a connection refused and don't see any network traffic out > > to > > the server. I can ssh into the server and get a shell (just to clarify that > > point). >
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> This evening I try again and as before get no where. However, I then go into
> the firewall setup and disable one particular rule that opens all remote
> hosts/ports for a particular service. [ and then things worked ]
Ooh, been there done that...
I'd suggest watching the traffic with tcpdump or some such tool. I'll
bet there are transactions going on between ports that you didn't think
you needed to open.
regards, tom lane
On Sat, 17 May 2003, Tom Lane wrote: > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > > This evening I try again and as before get no where. However, I then go into > > the firewall setup and disable one particular rule that opens all remote > > hosts/ports for a particular service. [ and then things worked ] > > Ooh, been there done that... > > I'd suggest watching the traffic with tcpdump or some such tool. I'll > bet there are transactions going on between ports that you didn't think > you needed to open. It's a toss up; do I reply to this email or the one with the subject "loverly russian brides"? Oh, what the heck, I'll do this one. I probably wasn't clear. The rule was a _permit_ from localhost to any remote host/port for something that looked like a core Windows service. I never saw the network traffic (with tcpdump of course) for the port forwarding until I disabled that permit rule, thereby actually tightening the firewall. Sure I got a firewall prompt for the initial ssh connection to the remote system without the rule but that was expected. Anyway, I'm thinking of just adding a little bit to the docs in the ssh tunneling section since there's very little to add past the company's own documentation and common sense. Is it acceptable to put such specific product notes in there? -- Nigel J. Andrews
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> I probably wasn't clear. The rule was a _permit_ from localhost to any remote
> host/port for something that looked like a core Windows service. I never saw
> the network traffic (with tcpdump of course) for the port forwarding until I
> disabled that permit rule, thereby actually tightening the firewall.
[ scratches head... ] That makes no sense at all to me; does it to you?
regards, tom lane
On Sat, 17 May 2003, Tom Lane wrote: > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > > I probably wasn't clear. The rule was a _permit_ from localhost to any remote > > host/port for something that looked like a core Windows service. I never saw > > the network traffic (with tcpdump of course) for the port forwarding until I > > disabled that permit rule, thereby actually tightening the firewall. > > [ scratches head... ] That makes no sense at all to me; does it to you? None what so ever. Is it any surprise I couldn't make it work earlier? Indeed, having fired things up again so I could say the executable associated with the rule I find it all working straight away [after starting a ssh session of course] and that's with the rule automatically enabled after the reboot. Just fwiw, the executable for the rule is \winnt\system32\services.exe. I do dislike getting different behaviour out of systems, even if the different behaviour is the working one. -- Nigel J. Andrews
On Sat, 17 May 2003, Tom Lane wrote: > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > > This evening I try again and as before get no where. However, I then go into > > the firewall setup and disable one particular rule that opens all remote > > hosts/ports for a particular service. [ and then things worked ] > > Ooh, been there done that... > > I'd suggest watching the traffic with tcpdump or some such tool. I'll > bet there are transactions going on between ports that you didn't think > you needed to open. FYI, if you have to take a look at your net traffic, look for ethereal. It comes with RH nowadays, and is a very nice GUI traffic sniffer.
Sorry for being MIA gentlemen but I thought I would just pop a note in here- my apologies if this has been answered already it has been 4 days :) On the firewall thing- the only port I have open is 22. The trick was to make sure the sshd process was going allow X11 forwarding. On the client side, whether it ssh or say PuTTY, you have to make sure you are allowing X11 connections back to yourself. For example the default ssh on the slackware run-time CD does not have this feature enabled so if I'm at a clients site, I boot from this CD, mount my floppy with my private key on it and type something like this (in an xterm window after I bring up X-windows): ssh -X -C -i /mnt/privkey someaccount@someserver.com The "-C" compresses the datastream. At this point, I can launch X apps and they'll display on my screen. I'll provide the exact options if needed... I agree Tom, I don't quite understand why you are not seeing the traffic unless it is denied. Quoting Tom Lane <tgl@sss.pgh.pa.us>: > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > > I probably wasn't clear. The rule was a _permit_ from localhost to any > remote > > host/port for something that looked like a core Windows service. I never > saw > > the network traffic (with tcpdump of course) for the port forwarding until > I > > disabled that permit rule, thereby actually tightening the firewall. > > [ scratches head... ] That makes no sense at all to me; does it to you? > > regards, tom lane > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
Best sniffer ever written- I teach course with it!! Quoting "scott.marlowe" <scott.marlowe@ihs.com>: > FYI, if you have to take a look at your net traffic, look for ethereal. > It comes with RH nowadays, and is a very nice GUI traffic sniffer. > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
Quoting "Nigel J. Andrews" <nandrews@investsystems.co.uk>: > Sorry for the rambling, it is late here, and I'm baffled, amazed and > wondering > how I'm going to explain to the two remote developers why they should try it > after all these months. > > -- > Nigel J. Andrews I'm surprised they hadn't asked for it. There are a great many eyes on the internet's traffic- information brokering of your information may have someone in a basement somewhere drooling... > > > > Quoting "Nigel J. Andrews" <nandrews@investsystems.co.uk>: > > > > > On Thu, 15 May 2003, Fernando Flores Prior wrote: > > > > > > > Nice ! > > > > > > > > It works just great. This will be a fine asset for the thechdocs. > > > > > > > > -Fernando > > > > > > > > > > > > At 08:49 p.m. 14/05/2003 -0400, Matthew Nuzum wrote: > > > > >This thread got my curiosity going, so I endeavored to try to get it > > > working > > > > >and am happy to say that it was very easy to tunnel a postgresql > > > connection > > > > >over ssh. > > > > > > > > > >Here's what I did... > > > > > > > > > > Yes, smashing. Anyone got some similar destructions for SSH tunneling > with > > > SSH > > > Communications Corp's (www.ssh.com) stuff. I've tried pointing and > clicking > > > in > > > the appropiate dialog several times now and the best I can get is a > notice > > > from > > > the firewall on the system that my test connection has connected to the > > > localhost but get a connection refused and don't see any network traffic > out > > > to > > > the server. I can ssh into the server and get a shell (just to clarify > that > > > point). > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
On Tue, 20 May 2003, Network Administrator wrote: > Sorry for being MIA gentlemen but I thought I would just pop a note in here- my > apologies if this has been answered already it has been 4 days :) > > On the firewall thing- the only port I have open is 22. The trick was to make > sure the sshd process was going allow X11 forwarding. I almost hate to restart this thread along with you but...but do you why X11 forwarding makes the difference? It never occured to me to flip the X11 forwarding toggle for the ssh comms corp client because a) I don't have a x server running on the Windows systems (nor do the developers) and b) X11 forwarding isn't the same thing as tunneling to a remote postgres server. I do not have it set fwiw. > On the client side, whether it ssh or say PuTTY, you have to make sure you are > allowing X11 connections back to yourself. > > For example the default ssh on the slackware run-time CD does not have this > feature enabled so if I'm at a clients site, I boot from this CD, mount my > floppy with my private key on it and type something like this (in an xterm > window after I bring up X-windows): > > ssh -X -C -i /mnt/privkey someaccount@someserver.com > > The "-C" compresses the datastream. At this point, I can launch X apps and > they'll display on my screen. > > I'll provide the exact options if needed... That could be useful but I'm starting to wonder if we're on about slightly different things. I'm on about a client process on a local system talking to a postgresql server on a remote system. May be you are concerned with the issue of logging into a remote system and running client applications there but displaying on the local system? On the documentation front, I don't think this is a 7.3 series issue but just so you know I'll probably not get around to do anything before the deadline this week. > I agree Tom, I don't quite understand why you are not seeing the traffic unless > it is denied. You can still colour me baffled on that. > > Quoting Tom Lane <tgl@sss.pgh.pa.us>: > > > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > > > I probably wasn't clear. The rule was a _permit_ from localhost to any > > remote > > > host/port for something that looked like a core Windows service. I never > > saw > > > the network traffic (with tcpdump of course) for the port forwarding until > > I > > > disabled that permit rule, thereby actually tightening the firewall. > > > > [ scratches head... ] That makes no sense at all to me; does it to you? > > > > regards, tom lane > > -- Nigel J. Andrews
I just wanted to be complete. In that I mixed too different things. I think the mentioning of X11 connections was in the context of providing documentation but as you point out, it is not needed. Only the port forwarding pieces. Sorry for the confusion. Lack of sleep and not having enough hours in a day to get stuff done turn my mind into mush. Quoting "Nigel J. Andrews" <nandrews@investsystems.co.uk>: > On Tue, 20 May 2003, Network Administrator wrote: > > > Sorry for being MIA gentlemen but I thought I would just pop a note in > here- my > > apologies if this has been answered already it has been 4 days :) > > > > On the firewall thing- the only port I have open is 22. The trick was to > make > > sure the sshd process was going allow X11 forwarding. > > I almost hate to restart this thread along with you but...but do you why X11 > forwarding makes the difference? > > It never occured to me to flip the X11 forwarding toggle for the ssh comms > corp > client because a) I don't have a x server running on the Windows systems > (nor > do the developers) and b) X11 forwarding isn't the same thing as tunneling to > a > remote postgres server. I do not have it set fwiw. > > > On the client side, whether it ssh or say PuTTY, you have to make sure you > are > > allowing X11 connections back to yourself. > > > > For example the default ssh on the slackware run-time CD does not have > this > > feature enabled so if I'm at a clients site, I boot from this CD, mount my > > floppy with my private key on it and type something like this (in an xterm > > window after I bring up X-windows): > > > > ssh -X -C -i /mnt/privkey someaccount@someserver.com > > > > The "-C" compresses the datastream. At this point, I can launch X apps > and > > they'll display on my screen. > > > > I'll provide the exact options if needed... > > That could be useful but I'm starting to wonder if we're on about slightly > different things. I'm on about a client process on a local system talking to > a > postgresql server on a remote system. May be you are concerned with the > issue > of logging into a remote system and running client applications there but > displaying on the local system? > > On the documentation front, I don't think this is a 7.3 series issue but > just > so you know I'll probably not get around to do anything before the deadline > this week. > > > > I agree Tom, I don't quite understand why you are not seeing the traffic > unless > > it is denied. > > You can still colour me baffled on that. > > > > > Quoting Tom Lane <tgl@sss.pgh.pa.us>: > > > > > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > > > > I probably wasn't clear. The rule was a _permit_ from localhost to any > > > remote > > > > host/port for something that looked like a core Windows service. I > never > > > saw > > > > the network traffic (with tcpdump of course) for the port forwarding > until > > > I > > > > disabled that permit rule, thereby actually tightening the firewall. > > > > > > [ scratches head... ] That makes no sense at all to me; does it to you? > > > > > > regards, tom lane > > > > > > -- > Nigel J. Andrews > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com