- what protocol for an Internet postgres connection

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

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:

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:

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

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:

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

"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.