Re: - what protocol for an Internet postgres

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
>