Thread: - what protocol for an Internet postgres connection

- what protocol for an Internet postgres connection

From
Fernando Flores Prior
Date:
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



Re: - what protocol for an Internet postgres connection

From
Doug McNaught
Date:
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

Re: - what protocol for an Internet postgres connection

From
Richard Huxton
Date:
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

Re: - what protocol for an Internet postgres

From
Fernando Flores Prior
Date:
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



Re: - what protocol for an Internet postgres connection

From
Doug McNaught
Date:
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

Re: - what protocol for an Internet postgres connection

From
Tom Lane
Date:
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

Re: - what protocol for an Internet postgres

From
Fernando Flores Prior
Date:
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



Re: - what protocol for an Internet postgres

From
Richard Welty
Date:
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

Re: - what protocol for an Internet postgres

From
Mike Mascari
Date:
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






Re: - what protocol for an Internet postgres

From
"Matthew Nuzum"
Date:
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
>



Re: - what protocol for an Internet postgres

From
Richard Huxton
Date:
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

Re: - what protocol for an Internet postgres

From
Fernando Flores Prior
Date:
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...



Re: - what protocol for an Internet postgres

From
"Nigel J. Andrews"
Date:
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


Re: - what protocol for an Internet postgres

From
Network Administrator
Date:
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

Re: - what protocol for an Internet postgres

From
"Nigel J. Andrews"
Date:
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).
>



Re: - what protocol for an Internet postgres

From
Tom Lane
Date:
"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

Re: - what protocol for an Internet postgres

From
"Nigel J. Andrews"
Date:
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


Re: - what protocol for an Internet postgres

From
Tom Lane
Date:
"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

Re: - what protocol for an Internet postgres

From
"Nigel J. Andrews"
Date:
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


Re: - what protocol for an Internet postgres

From
"scott.marlowe"
Date:
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.


Re: - what protocol for an Internet postgres

From
Network Administrator
Date:
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

Re: - what protocol for an Internet postgres

From
Network Administrator
Date:
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

Re: - what protocol for an Internet postgres

From
Network Administrator
Date:
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

Re: - what protocol for an Internet postgres

From
"Nigel J. Andrews"
Date:
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



Re: - what protocol for an Internet postgres

From
Network Administrator
Date:
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