Re: [GENERAL] postgres on in the internet
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 begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgres on in the internet
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 [EMAIL PROTECTED]
Re: [GENERAL] postgres on in the internet
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] postgres on in the internet
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: Po Eddie Lim [EMAIL PROTECTED] To: Mike Nolan [EMAIL PROTECTED]; Paul Tillotson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 03, 2004 4:12 PM Subject: Re: [GENERAL] postgres on in the internet 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 [EMAIL PROTECTED] To: Paul Tillotson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] postgres on in the internet
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 signature.asc Description: This is a digitally signed message part
Re: [GENERAL] postgres on in the internet
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 [EMAIL PROTECTED] To: Paul Tillotson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
Re: [GENERAL] postgres on in the internet
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
Re: [GENERAL] postgres on in the internet
Hi, Citing Paul Tillotson [EMAIL PROTECTED]: 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 This, implementation of a VPN or using the builtin ssl-support in postgresql is the way to go, because *everything* that goes through the channel will be encrypted. With SSL you have the additional value of being able to guarantee the identity of the server. The information in the source code distribution in src/backend/libpq/README.SSL might prove useful to make your decision. (b) just making sure that users have strong passwords and requiring md5 authentication in pg_hba.conf. Too weak, IMHO. md5 is there to protect the password data stored in the database, not the password transmission (someone already mentioned tcp replay attacks). Regards, Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] postgres on in the internet
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
[GENERAL] 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
Re: [GENERAL] postgres on in the internet
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 - [EMAIL PROTECTED] - 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
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgres on in the internet
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 [EMAIL PROTECTED]
