Thread: 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
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
> 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
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
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. >
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
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
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 >
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 >
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 >> >
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
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 >
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.
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