Thread: Remote access

In response to George Weaver :
> Hi,
>
> I have a client with a main office and a branch office about 90 miles away.
>
> They have a server at the main office but it is not a web server.
>
> What would be the best solution for them to access a PostgreSQL database
> located at the main office from the branch office?
>
> I am not "network savvy", but if I get pointed in the right direction I should
> be able to fill in the blanks.

There are some ways to do that, for instance a so called SSH-Tunnel,
read http://docs.planetargon.com/PostgreSQL_SSH_Tunnel

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

>From: "A. Kretschmer"

> In response to George Weaver :
>> Hi,
>>
>> I have a client with a main office and a branch office about 90 miles
>> away.
>>
>> They have a server at the main office but it is not a web server.
>>
>> What would be the best solution for them to access a PostgreSQL database
>> located at the main office from the branch office?
>>
>> I am not "network savvy", but if I get pointed in the right direction I
>> should
>> be able to fill in the blanks.
>
> There are some ways to do that, for instance a so called SSH-Tunnel,
> read http://docs.planetargon.com/PostgreSQL_SSH_Tunnel

Hi Andreas,

Thanks for the link!

(Pardon my ignorance) but does this require that the server be a web server
with a fixed IP address?

George

A. Kretschmer wrote:

> There are some ways to do that, for instance a so called SSH-Tunnel,
> read http://docs.planetargon.com/PostgreSQL_SSH_Tunnel

SSH tunneling transports TCP over a TCP encapsulation. The adaptive rate
control may not work how you'd expect; I've had some odd issues with SSH
tunnels in the past that've turned out to be caused by TCP rate control
issues.

In any case, a MUCH better option in almost all cases will be SSL,
possibly with client certificates.

--
Craig Ringer

Andrew Gould  in reponse to George Weaver wrote:

>>Hi Andrew,

>>>>Hi,

>>>>I have a client with a main office and a branch office about 90 miles
>>>>away.

>>>>They have a server at the main office but it is not a web server.

>>>>What would be the best solution for them to access a PostgreSQL database
>>>>located at the main office from the branch office?

>>>>I am not "network savvy", but if I get pointed in the right direction I
>>>>should be able to fill in the blanks.

>>>>Thanks in advance!

>>>The best solution will depend upon the type of activity the branch office
needs to perform with the database server.

>>The application is an inventory management system.  Their current solution
is an MS Access based system and they use Microsoft Terminal Services to
enable the branch office to access the main office server.  They are
considering a move to an application that I provide which utilizes
PostgreSQL.

>>>Also, what operating system(s) are they running at the branch office?

>>Everything is Windows based.

>>George

>Hi George,

>MS Access can access PostgreSQL servers via ODBC links.  There is an SSL
>Mode configuration option in the PostgreSQL ODBC driver for security.

Hi Andrew,

I have clients with web-based servers which utilize my application
connecting via the internet.  I guess where my ignorance manifests itself is
how to connect when the server is not a web server and doesn't have a fixed
IP address.  Is it necessary for them to set the server up with a fixed
address, or is there some other alternative?

Thanks for your time.

George

In response to "George Weaver" <gweaver@shaw.ca>:

[snip]

> I have clients with web-based servers which utilize my application
> connecting via the internet.  I guess where my ignorance manifests itself is
> how to connect when the server is not a web server and doesn't have a fixed
> IP address.  Is it necessary for them to set the server up with a fixed
> address, or is there some other alternative?

There are alternatives to a fixed IP ... all of them are Very Bad
Ideas, and generally unreliable.  If you ask around on enough message
boards, you will have people recommending all sorts of hacks where the
server periodically reports it's IP to a DNS server that updates its
records.

If you're trying to put together something that will be reliable and
professional, don't do any of those -- just spend the extra money
to get a static IP.  In fact, take a serious look at having that
server colocated at a facility with 24/7 monitoring, and redundant
power and redundant ethernet.  There are many places that will
proved you a static IP and rent you rack space for ~$150/month.
That kind of thing will provide you with the professional reliability
that most people expect but will never get from consumer DSL and
cable connections.  If you're budget is so tight that $150/month
is too much, then you'll have to fall back on one of the "magically
keep my DNS updated" solutions that others can surely give you
details on, and my heart will weep for your pain.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Fri, 2009-05-08 at 10:45 -0400, Bill Moran wrote:
> In response to "George Weaver" <gweaver@shaw.ca>:

> proved you a static IP and rent you rack space for ~$150/month.
> That kind of thing will provide you with the professional reliability
> that most people expect but will never get from consumer DSL and
> cable connections.  If you're budget is so tight that $150/month
> is too much, then you'll have to fall back on one of the "magically
> keep my DNS updated" solutions that others can surely give you
> details on, and my heart will weep for your pain.

Or get a VPS... for 40.00 bucks a month.

Joshua D. Drkae


>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

George Weaver wrote:
> Hi,
>
> I have a client with a main office and a branch office about 90 miles away.
>
> They have a server at the main office but it is not a web server.
>
> What would be the best solution for them to access a PostgreSQL database
> located at the main office from the branch office?
>
> I am not "network savvy", but if I get pointed in the right direction I
> should be able to fill in the blanks.
>
> Thanks in advance!
>
> George

You mention "network savvy" so I will assume the branch office is
connected via a wide area network to the main office.

In that case:
You probably don't need to do anything except making sure that the
server and PG allows access from the IP address range used at the branch
office.

 From the branch office you need to make your connection point to the IP
address of the PG server...

HTH,

Johan Nel
Pretoria, South Africa.

Hi Andrew,

>>Hi,

>>I have a client with a main office and a branch office about 90 miles
>>away.

>>They have a server at the main office but it is not a web server.

>>What would be the best solution for them to access a PostgreSQL database
>>located at the main office from the branch office?

>>I am not "network savvy", but if I get pointed in the right direction I
>>should be able to fill in the blanks.

>>Thanks in advance!

>The best solution will depend upon the type of activity the branch office
>needs to perform with the database server.

The application is an inventory management system.  Their current solution
is an MS Access based system and they use Microsoft Terminal Services to
enable the branch office to access the main office server.  They are
considering a move to an application that I provide which utilizes
PostgreSQL.

>Also, what operating system(s) are they running at the branch office?

Everything is Windows based.

George

----- Original Message -----

From: Andrew Gould

To: George Weaver

Cc: pgsql-general

Sent: Friday, May 08, 2009 9:57 AM

Subject: Re: [GENERAL] Remote access

On Fri, May 8, 2009 at 9:37 AM, George Weaver <gweaver@shaw.ca> wrote:

Andrew Gould  in reponse to George Weaver wrote:

Hi Andrew,

Hi,

I have a client with a main office and a branch office about 90 miles
away.

They have a server at the main office but it is not a web server.

What would be the best solution for them to access a PostgreSQL database
located at the main office from the branch office?

I am not "network savvy", but if I get pointed in the right direction I
should be able to fill in the blanks.

Thanks in advance!

The best solution will depend upon the type of activity the branch office

needs to perform with the database server.

The application is an inventory management system.  Their current solution

is an MS Access based system and they use Microsoft Terminal Services to
enable the branch office to access the main office server.  They are
considering a move to an application that I provide which utilizes
PostgreSQL.

Also, what operating system(s) are they running at the branch office?

Everything is Windows based.

George

Hi George,

MS Access can access PostgreSQL servers via ODBC links.  There is an SSL
Mode configuration option in the PostgreSQL ODBC driver for security.

Hi Andrew,

I have clients with web-based servers which utilize my application connecting via the internet.  I guess where my ignorance manifests itself is how to connect when the server is not a web server and doesn't have a fixed IP address.  Is it necessary for them to set the server up with a fixed address, or is there some other alternative?

Thanks for your time.

George

You should either get a static IP address or use a service that maps your changing IP address to a server name.

I use DynDNS.com and a perl application called ddclient.  DynDNS manages my domain name in their DNS.  ddclient monitors my home internet IP address and sends an update to DynDNS automatically whenever the IP address changes.  When I try to access my domain name, the domain name is mapped to my home IP address and my home router forwards the allowed ports to the appropriate computer.

DynDNS is not the only provider of this kind of service.  ddclient is not the only (free) application that performs this function.  They have worked for me; but there are many options available to you.

Andrew

----- Original Message -----
From: "Bill Moran" <wmoran@potentialtech.com>
To: "George Weaver" <gweaver@shaw.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Friday, May 08, 2009 9:45 AM
Subject: Re: [GENERAL] Remote access


> In response to "George Weaver" <gweaver@shaw.ca>:
>
> [snip]
>
>> I have clients with web-based servers which utilize my application
>> connecting via the internet.  I guess where my ignorance manifests itself
>> is
>> how to connect when the server is not a web server and doesn't have a
>> fixed
>> IP address.  Is it necessary for them to set the server up with a fixed
>> address, or is there some other alternative?
>
> There are alternatives to a fixed IP ... all of them are Very Bad
> Ideas, and generally unreliable.  If you ask around on enough message
> boards, you will have people recommending all sorts of hacks where the
> server periodically reports it's IP to a DNS server that updates its
> records.
>
> If you're trying to put together something that will be reliable and
> professional, don't do any of those -- just spend the extra money
> to get a static IP.  In fact, take a serious look at having that
> server colocated at a facility with 24/7 monitoring, and redundant
> power and redundant ethernet.  There are many places that will
> proved you a static IP and rent you rack space for ~$150/month.
> That kind of thing will provide you with the professional reliability
> that most people expect but will never get from consumer DSL and
> cable connections.  If you're budget is so tight that $150/month
> is too much, then you'll have to fall back on one of the "magically
> keep my DNS updated" solutions that others can surely give you
> details on, and my heart will weep for your pain.

Good info.  I want to avoid pain so will suggest to the client that they
seriously consider this approach.

Thanks Bill.

George Weaver wrote:
> Hi,
>
> I have a client with a main office and a branch office about 90 miles
> away.
>
> They have a server at the main office but it is not a web server.
>
> What would be the best solution for them to access a PostgreSQL
> database located at the main office from the branch office?
>
> I am not "network savvy", but if I get pointed in the right direction
> I should be able to fill in the blanks.

how are these two offices connected?       do they have any sort of
network connection between them, or are they just both 'on the internet'
via local firewall/routers?

if the latter, what you really want is a site to site VPN.   upgrade the
'routers' at both ends to something that supports VPN, like a Juniper
SSG5's (I'm assuming these are small offices), configure the two
networks to use different subnets, like 192.168.10.xxx and
192.168.11.xxx, and the VPN will transparently route between these.
So, if your PG server is 192.168.10.25, users on 192.168.11.xxx can just
direcfly connect to it by IP address.

George Weaver schrieb:
> I have a client with a main office and a branch office about 90 miles
> away.
> [...]
> What would be the best solution for them to access a PostgreSQL
> database located at the main office from the branch office?

I read the other messages and the advice about fixed IP or dynamic DNS
(cheap) is relevant but depends on your access provider.
As I understand your client uses already remote access via terminal
server, so the basic connection has proven viable, isn't it?
If there are already a couple of users on daily basis then the
connection should be rather fast.
Possibly you've fixed IP anyway?
If your access gets routinely disconnected every x houres then you
should figure out if this might be anoying for your new application.

Now you want to move the client-application from the server to local PCs.
You'll need to connect the local application to the remote db-server.
It depends on a few parameters.
1)  Will the new client be still MS-Access? Access can be real pain with
slow networks. It depends on low latency.
2)  If you can't do a big restucturing of the LANs, like change all the
local IPs, you cant have a VPN if the LANs arent already on different
subnets. (See the other mail)
3)  You can allways have a P2P tunnel by SSH or probaply VPN where just
one PC in the client LAN joins the server's LAN.
4)  You can set up on PC to provide a proxy for every other pc. The
proxy would just manage the ssh connect so that not every single pc
would need it's own ssh-connection with passwords.
Or you could let every pc connect itselves. With SSH its rather trivial
to map the remote db-server into every local PC.
5)  You could use SSL to connect to the remote db, but then you'd need
to open the db-server dirctly to the wierd wild web and this would make
me nervous even though ssl should be pretty secure. On top of that you'd
have to get your head around the ssl key topic first.
6)  If you feel really desperately lucky you could just open the remote
db-server's 5432 port to the web and don't encrypt anything. Then you'd
have to rely that noone guesses your user/passwords of postres.
7)  Decide on one protection method. Don't go SSL on top of SSH.   ;)

A simple solution would be:
*   setup a ssh server on the server-lan
*   open just the ssh port in the server-lan's internet-router
*   use e.g. PuTTY to tunnel the remote db-server's 5432 port into your
local PCs
Local PC(5432) ---internet---> || --->remote-lan---> ssh-server --->
db-server(5432)
Your pc asks the ssh-server to pass over the port 5432 of the db-server
on his lan.
*   in your PCs access 127.0.0.1:5432 via ODBC like the db was actually
running on it

It's no enterprise solution, mind you, but it might work just OK.
It depends really if you have enough bandwidth to carry all your local
clients.

On 2009-05-08, George Weaver <gweaver@shaw.ca> wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_003C_01C9CFB8.5F323DB0
> Content-Type: text/plain;
>     charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
>
> I have a client with a main office and a branch office about 90 miles =
> away.

what's that in milliseconds?