467,205 Members | 1,312 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,205 developers. It's quick & easy.

postgres "on in the internet"

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

Nov 23 '05 #1
  • viewed: 2773
Share:
12 Replies
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
> 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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #4
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


Nov 23 '05 #5
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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #6
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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (FreeBSD)

iD8DBQBBOP80JjGc5ftAw8wRAluBAKDnpo9JkNLpsg7j29q0dU WcuKliPACbBs58
hReOd7VBujPEIUfbYkvfdao=
=1kdi
-----END PGP SIGNATURE-----

Nov 23 '05 #7
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" <no***@gw.tssi.com>
To: "Paul Tillotson" <pn***@shentel.net>
Cc: <pg***********@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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #8
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #9
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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #10
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #11
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #12
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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by rcb845@yahoo.fr | last post: by
reply views Thread by Po Eddie Lim | last post: by
1 post views Thread by rmgalante@galaware.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.