Thread: postgres "on in the internet"

postgres "on in the internet"

From
Paul Tillotson
Date:
At my company we are looking at deploying clients for our client/server
app outside our firewall, which will then require our postgres box to be
internet-accessible.

Does anyone out there have experience with this or recommended best
practices?  We have been looking at either (a) tunnelling everything
over ssh, or (b) just making sure that users have "strong" passwords and
requiring "md5" authentication in pg_hba.conf.

Our client app is in C# using the postgresql .net data provider.

Regards,
Paul Tillotson


Re: postgres "on in the internet"

From
"Joshua D. Drake"
Date:
Hello,

I don't know if the C# postgresql provider can do this, but you can use
PostgreSQL with SSL + MD5 which would be fairly secure.
Another option would be to have them authenticate via a web application
that would open a tunnel specifically for that user per IP.

Sincerely,

Joshua D. Drake


Paul Tillotson wrote:

> At my company we are looking at deploying clients for our
> client/server app outside our firewall, which will then require our
> postgres box to be internet-accessible.
> Does anyone out there have experience with this or recommended best
> practices?  We have been looking at either (a) tunnelling everything
> over ssh, or (b) just making sure that users have "strong" passwords
> and requiring "md5" authentication in pg_hba.conf.
>
> Our client app is in C# using the postgresql .net data provider.
>
> Regards,
> Paul Tillotson
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL


Re: postgres "on in the internet"

From
Mike Nolan
Date:
> Does anyone out there have experience with this or recommended best
> practices?  We have been looking at either (a) tunnelling everything
> over ssh, or (b) just making sure that users have "strong" passwords and
> requiring "md5" authentication in pg_hba.conf.

Have you considered using VPN routers to punch a hole through your firewall?

Can you do a a combination of A and B?  (Does that make much sense?)

You should also consider blocking all IP addresses other than the client
nodes at the firewall.  That won't help much if the client node gets
compromised.
--
Mike Nolan

Re: postgres "on in the internet"

From
Mike Mascari
Date:
Paul Tillotson wrote:

> At my company we are looking at deploying clients for our
> client/server app outside our firewall, which will then require
> our postgres box to be internet-accessible. Does anyone out there
> have experience with this or recommended best practices?  We have
> been looking at either (a) tunnelling everything over ssh, or (b)
> just making sure that users have "strong" passwords and requiring
> "md5" authentication in pg_hba.conf.
>
> Our client app is in C# using the postgresql .net data provider.

Is the .net provider capable of an SSL connection? I'd be hesitant
to throw around data over the Internet without using SSL for all the
various reasons: DNS hijacking, TCP replay, etc.

Mike Mascari


Re: postgres "on in the internet"

From
Jeff Amiel
Date:
Well...if it's truly a client/server app, why not have the client app
talk to the server who actually executes the SQL.  That way no remote
access to the database is required.
IMHO, it's a 'bad thing' to have your database exposed to the internet....

Jeff Amiel


Paul Tillotson wrote:

> At my company we are looking at deploying clients for our
> client/server app outside our firewall, which will then require our
> postgres box to be internet-accessible.
> Does anyone out there have experience with this or recommended best
> practices?  We have been looking at either (a) tunnelling everything
> over ssh, or (b) just making sure that users have "strong" passwords
> and requiring "md5" authentication in pg_hba.conf.
>


Re: postgres "on in the internet"

From
Gaetano Mendola
Date:
Mike Mascari wrote:

> Paul Tillotson wrote:
>
>> At my company we are looking at deploying clients for our
>> client/server app outside our firewall, which will then require
>> our postgres box to be internet-accessible. Does anyone out there
>> have experience with this or recommended best practices?  We have
>> been looking at either (a) tunnelling everything over ssh, or (b)
>> just making sure that users have "strong" passwords and requiring
>> "md5" authentication in pg_hba.conf.
>>
>> Our client app is in C# using the postgresql .net data provider.
>
>
> Is the .net provider capable of an SSL connection? I'd be hesitant to
> throw around data over the Internet without using SSL for all the
> various reasons: DNS hijacking, TCP replay, etc.

If not you can tunnel it.



Regards
Gaetano Mendola





Re: postgres "on in the internet"

From
Frank Knobbe
Date:
On Thu, 2004-09-02 at 18:35, Paul Tillotson wrote:
> At my company we are looking at deploying clients for our client/server
> app outside our firewall, which will then require our postgres box to be
> internet-accessible.

Besides the already mentioned SSL session, consider tunneling postgres
over SSH. For example, I can be anywhere with my laptop and thanks to my
local postgres port being forward over SSH to my database server, I can
issue commands locally as if I were on the server itself. All in a very
secure and relatively fast fashion. (data in the SSH tunnel is gzipped
and encrypted)

Regards,
Frank


Attachment

Re: postgres "on in the internet"

From
"Po Eddie Lim"
Date:
Hello...

Is it possible in PostGre 7.3 to query the size of a text[] array attribute
of a table? Does anyone know how this is queried in 7.3?

thanks... help is greatly needed.
eddie

----- Original Message -----
From: "Mike Nolan" <nolan@gw.tssi.com>
To: "Paul Tillotson" <pntil@shentel.net>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, September 03, 2004 8:42 AM
Subject: Re: [GENERAL] postgres "on in the internet"


> > Does anyone out there have experience with this or recommended best
> > practices?  We have been looking at either (a) tunnelling everything
> > over ssh, or (b) just making sure that users have "strong" passwords and
> > requiring "md5" authentication in pg_hba.conf.
>
> Have you considered using VPN routers to punch a hole through your
firewall?
>
> Can you do a a combination of A and B?  (Does that make much sense?)
>
> You should also consider blocking all IP addresses other than the client
> nodes at the firewall.  That won't help much if the client node gets
> compromised.
> --
> Mike Nolan
>
> ---------------------------(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
>


Re: postgres "on in the internet"

From
Lincoln Yeoh
Date:
I doubt it's a good idea to make your postgres server internet accessible.
You'll be using postgresql in what I'd consider to be a less tested
scenario. Most people don't expose their database servers to the Internet.

You could use the following configuration:

client (with IPSEC VPN)
|
Internet
|
Firewall #1 (VPN endpoint)
|
Staging network (for VPN clients)
|
Firewall #2
|
Database server

The clients with VPN access get access to whatever the staging network has
access to - which may be the postgresql db port and other services, but
nothing else not explicitly permitted by Firewall #2, or Firewall #1.

You can combine Firewall #1 and #2 - this example is just to make the
concept clear. However combining the firewalls increases the risk in event
the firewall is somehow subverted (e.g. prob with IPSEC implementation).
Whereas keeping the firewalls separate means you can simplify what features
the Firewall #2 needs - e.g. no IPSEC, and thus reduce the exposure to
potential bugs.

If you don't want to or can't use IPSEC VPNs, you could try SSL and drop
connections from clients with unrecognized certs. You may wish to put the
SSL endpoint on another server (openssl's security track record hasn't been
that good, neither has openssh for that matter).
e.g.

client (using SSL)
|
Internet
|
Firewall #1
|
Staging network---SSL endpoint server
(for SSL clients)
|
Firewall #2
|
Database server

Another factor to consider: you may wish to test out Postgresql's network
performance over higher latency connections first - what would the typical
and bad case network latencies be for your scenario? My guess is roundtrip
latencies would be about 50-150ms good case (same continent broadband), and
200-700 msec bad case - inter-continent or dial up. Depending on your
application, it may be OK. However if your client has to make 10 queries in
series (nonparallelizable) where the results of a query affects what is the
subsequent query, latency could be an issue.

Regards,
Link.

At 07:35 PM 9/2/2004 -0400, Paul Tillotson wrote:

>At my company we are looking at deploying clients for our client/server
>app outside our firewall, which will then require our postgres box to be
>internet-accessible.
>Does anyone out there have experience with this or recommended best
>practices?  We have been looking at either (a) tunnelling everything over
>ssh, or (b) just making sure that users have "strong" passwords and
>requiring "md5" authentication in pg_hba.conf.
>
>Our client app is in C# using the postgresql .net data provider.
>
>Regards,
>Paul Tillotson
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>


Re: postgres "on in the internet"

From
Paul Tillotson
Date:
How difficult is it to set up VPN?

I know that in the past my company has used VPN tunnels, but this was
difficult for our remote users to get set up.  I am actually willing to
trade *some* security for ease of installation and simplicity.  This
will have to be deployed on  30 to 40 client computers, several of them
in very (i.e., plane travel) distant locations for non-technical users
who will work remotely.

Whatever the solution is, the setup needs to be scriptable or else very
easy to walk someone through by telephone.

>
> You could use the following configuration:
>
> client (with IPSEC VPN)

[diagram truncated]

> If you don't want to or can't use IPSEC VPNs, you could try SSL and
> drop connections from clients with unrecognized certs. You may wish to
> put the SSL endpoint on another server (openssl's security track
> record hasn't been that good, neither has openssh for that matter).

For this reason, it occurred to me that an enemy that can't sniff your
traffic, postgres untunnelled is probably more secure than postgres
tunnelled through openssl or openssh.

>
> Another factor to consider: you may wish to test out Postgresql's
> network performance over higher latency connections first...
>
Already tested--this app will replace one already deployed that does the
same thing--the existing app is "3 tier" app, but it's a maintenance
nightmare because it is single-threaded (!) and written in a language
with no native RPC or serialization capability.  We realized that alot
of simplicity was to be gained by connecting directly to the database
and putting most of the middle-tier (there isn't that much business
logic anyway) inside postgres itself in the form of used defined
fuctions and triggers.

Regards,
Paul Tillotson

> At 07:35 PM 9/2/2004 -0400, Paul Tillotson wrote:
>
>> At my company we are looking at deploying clients for our
>> client/server app outside our firewall, which will then require our
>> postgres box to be internet-accessible.
>> Does anyone out there have experience with this or recommended best
>> practices?  We have been looking at either (a) tunnelling everything
>> over ssh, or (b) just making sure that users have "strong" passwords
>> and requiring "md5" authentication in pg_hba.conf.
>>
>> Our client app is in C# using the postgresql .net data provider.
>>
>> Regards,
>> Paul Tillotson
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>
>



Re: postgres "on in the internet"

From
Russell Smith
Date:
On Sat, 4 Sep 2004 05:55 am, Paul Tillotson wrote:
> How difficult is it to set up VPN?
>
> I know that in the past my company has used VPN tunnels, but this was
> difficult for our remote users to get set up.  I am actually willing to
> trade *some* security for ease of installation and simplicity.  This
> will have to be deployed on  30 to 40 client computers, several of them
> in very (i.e., plane travel) distant locations for non-technical users
> who will work remotely.
>
> Whatever the solution is, the setup needs to be scriptable or else very
> easy to walk someone through by telephone.
Are the clients using win32?

I would suggest something like www.poptop.org   PPTP clients VPN
allowing any OS to connect to it.  It's as easy as setting up a dial up link for
the client.  Getting it right on the server is a little harder (At least with encryption).

I'd take a look at it.

Regards

Russell Smith

Re: postgres "on in the internet"

From
William Yu
Date:
I'm using poptop successfully. The setup was pretty steep though because
it's extremely dependent on what has been compiled into the kernel. I
was able to get it running on Redhat9 w/o any kernel recompiles though.


Russell Smith wrote:
> On Sat, 4 Sep 2004 05:55 am, Paul Tillotson wrote:
>
>>How difficult is it to set up VPN?
>>
>>I know that in the past my company has used VPN tunnels, but this was
>>difficult for our remote users to get set up.  I am actually willing to
>>trade *some* security for ease of installation and simplicity.  This
>>will have to be deployed on  30 to 40 client computers, several of them
>>in very (i.e., plane travel) distant locations for non-technical users
>>who will work remotely.
>>
>>Whatever the solution is, the setup needs to be scriptable or else very
>>easy to walk someone through by telephone.
>
> Are the clients using win32?
>
> I would suggest something like www.poptop.org   PPTP clients VPN
> allowing any OS to connect to it.  It's as easy as setting up a dial up link for
> the client.  Getting it right on the server is a little harder (At least with encryption).
>
> I'd take a look at it.
>
> Regards
>
> Russell Smith
>
> ---------------------------(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
>

Re: postgres "on in the internet"

From
Lincoln Yeoh
Date:
At 03:55 PM 9/3/2004 -0400, Paul Tillotson wrote:

>How difficult is it to set up VPN?
>
>I know that in the past my company has used VPN tunnels, but this was
>difficult for our remote users to get set up.  I am actually willing to
>trade *some* security for ease of installation and simplicity.  This
>will have to be deployed on  30 to 40 client computers, several of them
>in very (i.e., plane travel) distant locations for non-technical users
>who will work remotely.
>
>Whatever the solution is, the setup needs to be scriptable or else very
>easy to walk someone through by telephone.

In which case I suggest you go with tunnelling over SSL. Bundle the SSL
software and configuration with your client software install, or even build
it into your client software.


>For this reason, it occurred to me that an enemy that can't sniff your
>traffic, postgres untunnelled is probably more secure than postgres
>tunnelled through openssl or openssh.

People can still attack the postgresql server directly. Given that most
people don't expose their database servers to the Internet nor even
consider it, I doubt postgresql has been tested very much in that scenario.

By implication you're assuming the postgresql developers would do a better
job securing postgresql (at least on the initial connection, setup and
authentication stages) than the openssl and openssh developers - who
ironically seem to be having trouble getting their "security oriented"
software secure.

While the Postgresql developers in general do quite high quality work from
the high performance SQL standards compliance DB server perspective, I'm
not so sure how postgresql measures up from the "server exposed to
Internet" security perspective.

Perhaps someone who has done a security audit of postgresql from that
perspective can chime in.

The SSL topology I proposed has the SSL gateway separate from the database
server, this means that even if a hacker successfully exploits the SSL
gateway (using an SSL exploit), the hacker only gains local user/root
access to the SSL gateway and still needs to hack the postgresql server
over the network through the firewall.

Note that once on the SSL gateway the hacker should easily be able to gain
the DB user accounts and passwords from sniffing the traffic :(. e.g. the
hacker would be attacking postgresql as a valid DB user.

This scenario is still better than having the SSL end point being the
database sever. Because if anything happens the attacker gains local
user/root access to the database server, and isn't limited to attacking
postgresql over the network as a valid DB user - the attacker can attack
via the file system level or unix sockets or whatever security loophole
there is.

BUT don't forget a determined hacker is likely to be able to get DB user
accounts and passwords by getting hold of a copy of your client application
- assuming you bundle the user account and password with the app/app config.

Basically the attacker can do whatever the DB account which your client app
uses can do- the attacker can try arbitrary SQL commands and likely tamper
with the data on the database. This could be disastrous.

Whereas if the client application talks to the server and the application
server is the only one talking SQL to the database this would not
necessarily be possible (assuming you don't have SQL injection problems in
your app server ;) ).

Regards,
Link.

Re: postgres "on in the internet"

From
Chris Travers
Date:
Hi all;

Comments inline.

Lincoln Yeoh wrote:

> I doubt it's a good idea to make your postgres server internet
> accessible. You'll be using postgresql in what I'd consider to be a
> less tested scenario. Most people don't expose their database servers
> to the Internet.
>
> You could use the following configuration:
>
> client (with IPSEC VPN)
> |
> Internet
> |
> Firewall #1 (VPN endpoint)
> |
> Staging network (for VPN clients)
> |
> Firewall #2
> |
> Database server
>
> The clients with VPN access get access to whatever the staging network
> has access to - which may be the postgresql db port and other
> services, but nothing else not explicitly permitted by Firewall #2, or
> Firewall #1.
>
I have to agree with this architecture.  However, I would also suggest
that you think about public key management so that in the event that the
client key becomes corrupt while traveling, they can call in and have
the situation resolved quickly.  I.e. "Run this tool.  It will generate
your keys and email your public key to me."

Also, although this is likely to be the hardest environment to set up,
it will probably be the most mainenance-free in the long-run.  I.e. PPTP
is more vulnerable to a wide variety of attacks including DoS, etc. than
IPSec is, and having a good set of security barriers is critical when
you are looking at business data.  The SSL issue could be used as well,
but I don't really know what sorts of options are available on Windows
for SSL-based VPN's.

Best Wishes,
Chris Travers
Metatron Technology Consulting

Attachment