Thread: looking for a secure
Hi, I apologize in advance for the long post but I'm faced with a thorny problem and my lack of experience is showing. We are trying to figure out the best arrangement for our Pg server. We have what appears to me to be a difficult-to-satisfy set of security requirements/implications. Here's our setup... We have a Pg database here at the central office. We have two main groups of clients that need to talk to this database. In the field, we have/need to plan for upwards of 10,000 client sites that need to both put data into (log messages) and take data out of (downloading patches) this database. These 10,000 clients are talking to us over the internet, not a private network of any sort. We also have a couple of dozen people that we need to access a different section of the db through a web interface. Some of the data in the database is of a sensitive nature (IP addresses, account names and passwords to connect to the client sites). So, the challenge is to provide access to this db from the internet while making it reasonably hard to allow the wrong people to get access. We've been tossing over a lot of different scenarios, and here's what we've come up with so far: Scenario 1: We put a Pg server outside our firewall, and another one behind it. The outer database contains only a subset of the total db schema, just enough to receive the log messages and provide the patches that need to be available for download. The internet clients connect to this outer database. The internal database contains the full db schema. Then, the log and patch tables are replicated over to the internal, main database. Cons: replication doesn't seem to be a solid product yet, would require two-way replication (log messages need to be moved internally, new available patches need to be moved onto the outer db), means we have two databases to maintain Scenario 2: Same hardware setup as Scenario 1 but instead of replication we have a cron'ed perl script or psql script or something similar select from one db and insert into the other, and vice versa. Cons: still have two seperate databases, not real time, seems like a hack to me Scenario 3: Punch a hole through the firewall or move the main Pg database outside of the firewall and make the main database available on the internet. Cons: security implications Scenario 3 seems the most elegant to me. It avoids having to set up some sort of replication/copying scheme and having the same data stored in two different places. But we are understandably nervous about hanging that main db out there on the internet. So, I'm looking for the best recipe to minimize risk. Here's what I've thought about so far: - all 10,000 clients can get a separate Pg user account. performance issues? can we then restrict to a certain user/IP combo? can we restrict what actions they can take, what tables they can see, or just whether or not they have access to the db? does this even help? - SSL? is this even possible? The db client on those 10,000 machines is going to be a very lightweight C program out of necessity (perl and other languages is not supported, these machines are old and often we don't have permission to install new languages on them anyway) - the sensitive data fields can be encrypted in some reversible but secure fashion when we store them in the database - we can use things like tripwire, etc... to detect any unauthorized access to the db server machine - i have a nagging feeling i'm not seeing the big picture. does postgres have some other built-in security features that would help secure the box? revers lookups, maybe? or something else? I'm really interested in seeing what other people have done to alleviate these types of concerns, and what if anything I am missing as I approach the problem. Thanks for your time, Fran
Mitch Vincent wrote: > I think you've got most of your problem solved -- at least in theory. > > The answer to your questions of SSL and IPs is yes, and yes - check the > manual for details.. Yeah, I know that it's straightforward to compile the SSL into the server, but the question on the SSL is more of how likely is it that we are going to be able to roll our own SSL communications from our lightweight C client running on a hodgepodge of AIX and SCO machines. I'm not the author of the C program, so I really can't speak with authority, but I imagine it's not trivial. I also have seen some of the Pg folks (Momjian for one, maybe Lane) post with some skepticism about the state of the SSL support, but I don't recall the details at the moment. > 10,000 clients eh'. I hope you have some beefy hardware :-) It's 10,000 clients, but talking to us only once every 15 minutes to an hour, and at very known intervals so we can kind of control the flow. I was just wondering how well Pg would manage that many user accounts. But yes, should we need to beef up the hardware, we do at least have the ability to do that quite liberally. Thanks for the input thus far! I wish Perl was an option, I think I might have been able to leverage its DBI::ProxyServer to isolate the physical db from the internet a bit more. Ah well, c'est la vie. -Fran
Fran, Is it truly necessary for your 10000 client programs to interact *directly* with the database? If their mode of operation is going to be relatively simple (send this structured upload, receive this structured download) would it not be better to use a web-based script as the interface to the clients, and have that script do the actual database manipulations? The clients could POST upload data to the script (as an XML fragment, for example) and download data from the script. The script would be exposted to the outside world, not the database, and could have some data validation routines to ensure that nasty people are not hacking at your interface. And if it gets compromised, it's running under web server permissions, not database permissions. Also, the database can then be placed back behind the firewall, with the script playing middleman. The other nice thing about this scenario is that if you decide in 18 months that PostgreSQL 8 or whatever is the greatest thing since sliced bread, but the PG wire protocol has changed, you can upgrade your server without having to upgrade your clients (the PG team have been good about not monkeying with the wire protocol though, so this is probably moot). Paul Fran Fabrizio wrote: > > Hi, > > I apologize in advance for the long post but I'm faced with a thorny > problem and my lack of experience is showing. We are trying to figure > out the best arrangement for our Pg server. We have what appears to me > to be a difficult-to-satisfy set of security requirements/implications. > Here's our setup... > > We have a Pg database here at the central office. We have two main > groups of clients that need to talk to this database. > > In the field, we have/need to plan for upwards of 10,000 client sites > that need to both put data into (log messages) and take data out of > (downloading patches) this database. These 10,000 clients are talking > to us over the internet, not a private network of any sort. > > We also have a couple of dozen people that we need to access a different > section of the db through a web interface. > > Some of the data in the database is of a sensitive nature (IP addresses, > account names and passwords to connect to the client sites). > > So, the challenge is to provide access to this db from the internet > while making it reasonably hard to allow the wrong people to get > access. We've been tossing over a lot of different scenarios, and > here's what we've come up with so far: > > Scenario 1: We put a Pg server outside our firewall, and another one > behind it. The outer database contains only a subset of the total db > schema, just enough to receive the log messages and provide the patches > that need to be available for download. The internet clients connect to > this outer database. The internal database contains the full db > schema. Then, the log and patch tables are replicated over to the > internal, main database. > > Cons: replication doesn't seem to be a solid product yet, would require > two-way replication (log messages need to be moved internally, new > available patches need to be moved onto the outer db), means we have two > databases to maintain > > Scenario 2: Same hardware setup as Scenario 1 but instead of > replication we have a cron'ed perl script or psql script or something > similar select from one db and insert into the other, and vice versa. > > Cons: still have two seperate databases, not real time, seems like a > hack to me > > Scenario 3: Punch a hole through the firewall or move the main Pg > database outside of the firewall and make the main database available on > the internet. > > Cons: security implications > > Scenario 3 seems the most elegant to me. It avoids having to set up > some sort of replication/copying scheme and having the same data stored > in two different places. But we are understandably nervous about > hanging that main db out there on the internet. So, I'm looking for the > best recipe to minimize risk. Here's what I've thought about so far: > > - all 10,000 clients can get a separate Pg user account. performance > issues? can we then restrict to a certain user/IP combo? can we > restrict what actions they can take, what tables they can see, or just > whether or not they have access to the db? does this even help? > > - SSL? is this even possible? The db client on those 10,000 machines > is going to be a very lightweight C program out of necessity (perl and > other languages is not supported, these machines are old and often we > don't have permission to install new languages on them anyway) > > - the sensitive data fields can be encrypted in some reversible but > secure fashion when we store them in the database > > - we can use things like tripwire, etc... to detect any unauthorized > access to the db server machine > > - i have a nagging feeling i'm not seeing the big picture. does > postgres have some other built-in security features that would help > secure the box? revers lookups, maybe? or something else? > > I'm really interested in seeing what other people have done to alleviate > these types of concerns, and what if anything I am missing as I approach > the problem. > > Thanks for your time, > Fran > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
I think you've got most of your problem solved -- at least in theory. The answer to your questions of SSL and IPs is yes, and yes - check the manual for details.. 10,000 clients eh'. I hope you have some beefy hardware :-) > Scenario 3 seems the most elegant to me. It avoids having to set up > some sort of replication/copying scheme and having the same data stored > in two different places. But we are understandably nervous about > hanging that main db out there on the internet. So, I'm looking for the > best recipe to minimize risk. Here's what I've thought about so far:
Sorry about that, You can use Stunnel. There is info here: http://stage.linuxports.com/projects/postgres/x5621.htm and here: http://www.stunnel.org On Tue, 31 Jul 2001, Fran Fabrizio wrote: > Mitch Vincent wrote: > > > I think you've got most of your problem solved -- at least in theory. > > > > The answer to your questions of SSL and IPs is yes, and yes - check the > > manual for details.. > > Yeah, I know that it's straightforward to compile the SSL into the server, > but the question on the SSL is more of how likely is it that we are going to > be able to roll our own SSL communications from our lightweight C client > running on a hodgepodge of AIX and SCO machines. I'm not the author of the C > program, so I really can't speak with authority, but I imagine it's not > trivial. I also have seen some of the Pg folks (Momjian for one, maybe > Lane) post with some skepticism about the state of the SSL support, but I > don't recall the details at the moment. > > > 10,000 clients eh'. I hope you have some beefy hardware :-) > > It's 10,000 clients, but talking to us only once every 15 minutes to an hour, > and at very known intervals so we can kind of control the flow. I was just > wondering how well Pg would manage that many user accounts. But yes, should > we need to beef up the hardware, we do at least have the ability to do that > quite liberally. > > Thanks for the input thus far! I wish Perl was an option, I think I might > have been able to leverage its DBI::ProxyServer to isolate the physical db > from the internet a bit more. Ah well, c'est la vie. > > -Fran > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- -- <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY> <PROJECT>OpenDocs, LLC. - http://www.opendocs.org </PROJECT> <PROJECT>LinuxPorts - http://www.linuxports.com </PROJECT> <WEBMASTER>LDP - http://www.linuxdoc.org </WEBMASTER> -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
Use Stunnel On Tue, 31 Jul 2001, Fran Fabrizio wrote: > Mitch Vincent wrote: > > > I think you've got most of your problem solved -- at least in theory. > > > > The answer to your questions of SSL and IPs is yes, and yes - check the > > manual for details.. > > Yeah, I know that it's straightforward to compile the SSL into the server, > but the question on the SSL is more of how likely is it that we are going to > be able to roll our own SSL communications from our lightweight C client > running on a hodgepodge of AIX and SCO machines. I'm not the author of the C > program, so I really can't speak with authority, but I imagine it's not > trivial. I also have seen some of the Pg folks (Momjian for one, maybe > Lane) post with some skepticism about the state of the SSL support, but I > don't recall the details at the moment. > > > 10,000 clients eh'. I hope you have some beefy hardware :-) > > It's 10,000 clients, but talking to us only once every 15 minutes to an hour, > and at very known intervals so we can kind of control the flow. I was just > wondering how well Pg would manage that many user accounts. But yes, should > we need to beef up the hardware, we do at least have the ability to do that > quite liberally. > > Thanks for the input thus far! I wish Perl was an option, I think I might > have been able to leverage its DBI::ProxyServer to isolate the physical db > from the internet a bit more. Ah well, c'est la vie. > > -Fran > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- -- <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY> <PROJECT>OpenDocs, LLC. - http://www.opendocs.org </PROJECT> <PROJECT>LinuxPorts - http://www.linuxports.com </PROJECT> <WEBMASTER>LDP - http://www.linuxdoc.org </WEBMASTER> -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
On Tue, 31 Jul 2001, Fran Fabrizio wrote: > - SSL? is this even possible? The db client on those 10,000 machines > is going to be a very lightweight C program out of necessity (perl and > other languages is not supported, these machines are old and often we > don't have permission to install new languages on them anyway) > > - the sensitive data fields can be encrypted in some reversible but > secure fashion when we store them in the database > > - we can use things like tripwire, etc... to detect any unauthorized > access to the db server machine > > - i have a nagging feeling i'm not seeing the big picture. does > postgres have some other built-in security features that would help > secure the box? revers lookups, maybe? or something else? > > I'm really interested in seeing what other people have done to alleviate > these types of concerns, and what if anything I am missing as I approach > the problem. PostgreSQL can use SSL; can you write a lightwight C program that supports it well? I don't think PG has any features like reverse lookups, etc., but you could certainly set up a firewall to do so. PG <--> PG_FW <--> Internet where PG_FW is a machine/process that only allows connections to port 5432 of PG if the IP address is on an approved list, or such. Or, could you use a web app as the client program? Then, your connections are coming just from your web server, and your clients can use any kind of web authentication to connect to the web server. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Fran Fabrizio <ffabrizio@mmrd.com> writes:
> Yeah, I know that it's straightforward to compile the SSL into the server,
> but the question on the SSL is more of how likely is it that we are going to
> be able to roll our own SSL communications from our lightweight C client
> running on a hodgepodge of AIX and SCO machines.
I doubt you want to roll your own; use the OpenSSL library. Your real
question is "how portable is that library", and I have to admit I don't
know. But try it.
> I also have seen some of the Pg folks (Momjian for one, maybe
> Lane) post with some skepticism about the state of the SSL support, but I
> don't recall the details at the moment.
It was broken in 7.0, but AFAIK it's OK in 7.1.
regards, tom lane
Richard, Thanks for the feedback. I am intrigued by the idea of having an app instead of a database sitting outside the firewall. I'm a web guy, and a web app would be a neat idea, or maybe something like XML-RPC. However, I think this actually makes the clients more complicated than simpler, because then, if we use Paul's XML fragment message for example, the clients need to know how to parse and form XML messages. If they can connect directly to Pg, that buys us two things... 1. the clients just use the Pg library and standard SQL, and 2. we don't have to parse it into SQL when it gets to our end. The other problem is I think the transfer of the actual binary patches is a bit more complicated. Yes, HTTP has no problems with transfering binaries in the general sense, but I frankly don't have the foggiest idea how to write a custom web client in C that can receive a binary file. With Pg, you can use it's method of handing back large objects and that seems pretty straightforward (and more importantly, we've proof-of-concept'ed that part whereas I wouldn't know where to begin to make Apache serve and a C client receive a binary that came out of a database. =) I'm sure it's not that hard, probably involves just setting the appropriate content-type and streaming it out, but there are probably a lot of nuances on both the server and client side (the client side scares me; I'm not a C programmer, the guy writing the C client is not a programmer at all, we're just doing it out of necessity, so anything that makes the C as simple as possible makes us sleep easier. =) I guess it's all about playing to your strengths.) Having just been out at the Open Source Conference all week, I'm pretty hot on the idea of web apps/XML-RPC/Soap type services hanging out there on the net, but I'm not sure this is the right application for it. It would certainly go a long ways towards isolating the Pg database, and that is a Good Thing (tm), but then we introduce another "hop" before the data gets into/out of the database. If we can get the data into the database straight away and in a secure fashion, I think that is what we would want ultimately. Good stuff in this thread; lots of ideas not only for the current project but for the future as well. Thanks all! -Fran
Well, after aggregating all the feedback here and talking to my boss (he knows more about SSL and the like) I think we've come up with a solution that fits... The first part will be implementing SSL. Apparently, he has managed to successfully compile OpenSSL on the SCO and AIX boxes that we will be having as clients. This means that if he compiles Pg with SSL support on them, he should be able to call for a SSL connection from the Pg library from his C client. (Side note: is there any way to get client libraries onto a given machine without having to install and compile all of Pg? I remember looking for a client-library-only type download for Pg, but I did not have success and I always have to install Pg even if I just want to use it as a client) In combination with SSL, we're considering putting a linux firewall in front of the database which will indeed query the database for the known hosts and configure it's rules accordingly. Then we can do one of two things: we can forward it such that the Pg database only sees the connections as coming from the firewall and so we can restrict it to allow just that one IP and associate a username/password with that one IP, or we can forward it with the original source IPs intact and have Pg contain an account for each client and their IP (this seems like a lot of work). Well, we appear to be on the right track. Thanks for all the insight, and if anyone can shoot holes through my plan or has additional recommendations for making it more secure, I'd love to hear it. Oh, one last thing...all of these clients need SSL certificates (for another aspect of our operation). It would be great if we could leverage that fact to use SSL not only for encryption but also for authentication via the certificates. However, I don't think there's any way to get the client to just serve up the cert to Pg nor for the Pg server to do anything with it. However, if I'm wrong on that or if you can think of another way to leverage that client cert, let me know! Thanks, Fran
Fran Fabrizio wrote: > > Scenario 2: Same hardware setup as Scenario 1 but instead of > replication we have a cron'ed perl script or psql script or something > similar select from one db and insert into the other, and vice versa. > > Cons: still have two seperate databases, not real time, seems like a > hack to me I pretty much agree with everything Paul Ramsey said in his reply, use a modified version of Scenario 2 but have a separate app instead of a database outside the firewall. You can keep the clients real simple then. > - all 10,000 clients can get a separate Pg user account. performance > issues? can we then restrict to a certain user/IP combo? can we > restrict what actions they can take, what tables they can see, or just > whether or not they have access to the db? does this even help? Do this in your app. Obviously you can have 10,000 entries in a user_access table without any difficulty. > - SSL? is this even possible? The db client on those 10,000 machines > is going to be a very lightweight C program out of necessity (perl and > other languages is not supported, these machines are old and often we > don't have permission to install new languages on them anyway) Install openSSH and forward a port from the local machine to your external server. Keeps the client app nice and simple, provides security (and compression if they're on a phone-line). I'd probably just use scp to move text-files to/from the server as required. Leverage standard *nix user permissions to restrict what files/folders they have access to. You say somewhere that clients only connect every 15 mins so you'll be batching log messages anyway I presume. > - the sensitive data fields can be encrypted in some reversible but > secure fashion when we store them in the database Not necessary if you are confident enough about your communication app. > - we can use things like tripwire, etc... to detect any unauthorized > access to the db server machine Good idea anyway, but no use for people hacking the database itself. HTH - Richard Huxton
On Tue, 31 Jul 2001 16:55:07 +0000 (UTC), ffabrizio@mmrd.com (Fran Fabrizio) wrote: >Here's our setup... [huge snip] Please consider, I do not know the exact demands for you application or anything, but the following seems quite easier to maintain in my eyes: You wrote you needed a C-program, what's wrong with making it all webbased. Then your webserver could have two NICs, with the database access on the internal one. It also has the advantage, that use need only 1 account in the database, the rest is just business logic. For the webbased interfaces, unless you think about using a raw interface like PhpPgAdmin (and write it yourself), it should be safe. Ie. no need to worry about people seeing too much (as only the webserver, with is reasonably trustworthy, access the data - using scripts you control) Regards Svenne -- Mail usenet@krap.dk - svenne@krap.dk - PGP key id : 0xDF484022 ICQ: 5434480 - http://www.krap.dk - http://www.krap.net PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022
> You wrote you needed a C-program, what's wrong with making it all > webbased. The problem (not really a problem but the issue) is that the client end is a computer, not a human, so you'd still need some program to talk to the web interface. Since we don't have the luxury of something like Perl and it's LWP libraries (that make it easy to write web clients), we'd still need to write a web client in C. Certainly quite possible, but not my favorite choice (see my other responses for why I think in general a web app might not be best). > Then your webserver could have two NICs, with the database > access on the internal one. Not too worried about this, we have a very well established network here behind two firewalls, and I can drop the Pg database in front of either, in the middle, or behind both as I see fit. Even better than two NICs in a box. =) > It also has the advantage, that use need only 1 account in the > database, the rest is just business logic. Yeah, this is an attractive option. We can do the same thing by placing a firewall in front instead of a web server. My fault, though, for not specifying the parameters of my application more clearly. Thanks! -Fran
And why don't you have the luxury of using Perl? If it has to be C, I think
there's a tool or two for converting Perl code to C that might be helpful....
I can understand not wanting to make it web based if there's no human
interaction. Perl can talk directly to the database too...
Wes
Fran Fabrizio <ffabrizio%mmrd.com@interlock.lexmark.com> on 07/31/2001 03:43:51
PM
To: Svenne Krap <usenet%krap.dk@interlock.lexmark.com>
cc: pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] Re: looking for a secure
> You wrote you needed a C-program, what's wrong with making it all
> webbased.
The problem (not really a problem but the issue) is that the client end is
a computer, not a human, so you'd still need some program to talk to the
web interface. Since we don't have the luxury of something like Perl and
it's LWP libraries (that make it easy to write web clients), we'd still
need to write a web client in C. Certainly quite possible, but not my
favorite choice (see my other responses for why I think in general a web
app might not be best).
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
wsheldah@lexmark.com wrote: > And why don't you have the luxury of using Perl? If it has to be C, I think > there's a tool or two for converting Perl code to C that might be helpful.... Because the client machines are not always owned by us. In a few cases so far, we've been specifically prohibited from installing new languages. Plus, some of the client machines are rather old AIX and SCO boxes and even when we do have latitude to install perl, it's hard to either find a new enough/complete enough binary or get one to build that has all of the necessary modules that we use. > I can understand not wanting to make it web based if there's no human > interaction. Perl can talk directly to the database too... Yep. I use Perl to talk to Pg (and MySQL) extensively all the time. (Side note: Tim Bunce's Advanced DBI talk at the Open Source Conference was great last week, anyone interested should check out the slides once O'Reilly makes them available online). Just don't have the liberty of that in this case, sadly. -Fran
>>>>> "Fran" == Fran Fabrizio <ffabrizio@mmrd.com> writes: Fran> Thanks for the input thus far! I wish Perl was an option, I Fran> think I might have been able to leverage its DBI::ProxyServer to Fran> isolate the physical db from the internet a bit more. Ah well, Fran> c'est la vie. And why isn't Perl an option? Especially in a place using Pg instead of Oracle! -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!