Re: [Bulk] [GENERAL] General advice on database/web applications
On 3/28/06, Alex Turner <[EMAIL PROTECTED]> wrote: > The solution that I have seen typical is to have both webserver and database > machine behind a firewall both NATed, with only HTTP and HTTPS ports open on > the webserver. SSH is not open, as trusted clients connect via the VPN in > the firewall. The database machine, unlike the webserver, will not have a > static NAT entry, only a private IP address, and will only permit > connections from the webserver via postgres conf. right. that's pretty secure imo. Having read your postings for a couple of years now I know you know your stuff... > If your webserver is compromised, it's only a matter of time before they get > to through to your DB, so going through the hassle of replication is not > worth it IMHO as they can still compromise the DB on the front and cause > havoc. one small point here: you don't have to replicate the entire database, and replication is one direction only. I think replication approach is better than a data transfer on cron. It will scale much better and limit time differential issues. Generally, though, I think the whole idea is just a big headache. > You can put business objects on your database server as someone suggested, > but typicaly most people want all their RAM available for the database, and > don't really want memory hungry objects cluttering up memory, which they > inevitably do. This is also an increase in complexity, and will increase > development time significantly if you have to access all data through remote > object calls instead of simple SQL. Typicaly this is not regarded as a big agree 100%. IMO, business objects stretch your database and makes it moe complex by an order of magnitude. Also there is no reason to believe the business objects are any more secure than your database. They might be less. I'd submit that an application with business objects logging into database as db root is much less seucure than direct connect all logging in as properly set up db user. One last point I'd like to make is that if you know what you are doing, pg can be extremely secure. PostgreSQL can be armored and to the point where it is quite safe to back a public website, even with critical data, if: 1. database users and permissions are carefully thought out and set up 2. make liberal use of functions and use DEFINER,etc 3. use paramaterized query routines, or use driver technology that does 4. understand the information that pg gives out to unpriv. user account and how to take it away. I'd start with 'revoke select on pg_proc from public' and go on from there ;) Merlin ---(end of broadcast)--- TIP 1: 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: [Bulk] [GENERAL] General advice on database/web applications
The solution that I have seen typical is to have both webserver and database machine behind a firewall both NATed, with only HTTP and HTTPS ports open on the webserver. SSH is not open, as trusted clients connect via the VPN in the firewall. The database machine, unlike the webserver, will not have a static NAT entry, only a private IP address, and will only permit connections from the webserver via postgres conf. If your webserver is compromised, it's only a matter of time before they get to through to your DB, so going through the hassle of replication is not worth it IMHO as they can still compromise the DB on the front and cause havoc. I would suggest that if you want another layer, put a reverse proxy for the clients to get webpages from, then put your webserver behind that. That way no client has direct access to your webserver.You can put business objects on your database server as someone suggested, but typicaly most people want all their RAM available for the database, and don't really want memory hungry objects cluttering up memory, which they inevitably do. This is also an increase in complexity, and will increase development time significantly if you have to access all data through remote object calls instead of simple SQL. Typicaly this is not regarded as a big enough improvement in security to warrant the extra development hassle (at least not to people who want their websites yestarday, which I have found is virtualy everyone). Alex.On 3/27/06, Mark Feller <[EMAIL PROTECTED]> wrote: The webserver runs linux and I also have iptables on that server filteringout all but HTTP and SSH traffic.I have not yet implemented the database, and I am VERY reluctant to put thefull db outside our "main" firewall because of the need to protect sensitive info. So my question, is how do the applications on the webserver interfacewith the database? My one thought for a solution is to have a more limiteddatabase hosted on the same machine as the webserver that would have customer account number, price lists, and product lists--enough for an orderto be taken. Credit info, etc. is stored someplace more secure. After anorder is taken, the webserver/database/something then forwards an "order placed" type of message to the main database. Maybe a synch is done betweenwebserver database and main database every five minutes, where the maindatabase pulls any new orders, and pushes any updated part lists, pricing etc. to the webserver db?My question, is would such a scheme be practical, or is there a "bestpractices" type of approach that I should consider instead, such as thesuggestion in your next-to-last paragraph? Thanks.--Mark-Original Message-From: Ted Byers [mailto:[EMAIL PROTECTED]]Sent: Monday, March 27, 2006 2:54 PMTo: Mark Feller; pgsql-general@postgresql.orgSubject: Re: [Bulk] [GENERAL] General advice on database/webapplications>> I am developing a small web application. Currently, our web server is> sitting outside our firewall (running its own firewall), and the > application> being developed would let users do things like place orders.>> My question is...what and where is the database for this?>What do you mean when you say your web server is running its own firewall? I could well be wrong, but I am not aware of a web server that can run afirewall; web servers and firewalls are, as I understand them, quitedifferent kinds of software, though I am aware of some hardware that have built in firewalls.Your question, though, doesn't make sense. If, as you say explicitly inyour first sentence, that you're developing a small web application, theneither you don't have a database and need to create it, or you have already created your database and know both where and what it is. If you haven'tcreated it already, then you can create it and you have absolute controlover where to put it and what RDBMS to use. The only circumstance in which I could imagine you having a database back end for your application but notknowing about it is if you bought hosting services from a company thatprovides such services. But if that's the case, then you ought to be asking that company about it. But if that's the case, they probably already have aready made virtual store application for you to use, which makes developingyour own unnecessary unless you're planning to do your own hosting, and that takes us back to you having complete control over what you use and where youput it.If I were to create such a web application as you describe, I'd create adatabase using PostgreSQL or something similar and have it live inside the firewall, configured to respond only to applications running behind thefirewall. Under no circumstances would I want it to accept connectionsacross the firewall. Similarly, I'd have my application server and my httpd server behind the firewall and configured to accept connections across thefirewall but only from proxy servers set up in a DMZ.Since you are dealing with sensitive information such
Re: [Bulk] [GENERAL] General advice on database/web applications
On Mar 27, 2006, at 4:23 PM, Mark Feller wrote: Maybe a synch is done between webserver database and main database every five minutes, where the main database pulls any new orders, and pushes any updated part lists, pricing etc. to the webserver db? I have implemented a design like this and it seems to work very well. The main disadvantage, as already mentioned, is that it is a lot more work than having one database. In my case, the internal database was not PostgreSQL so I was going to have separate databases anyway. Syncing is time based (internal database contacts the PostgreSQL web database, never the other way around), but you could also setup a persistent connection and use LISTEN/NOTIFY to handle events immediately. In addition to some possible security security benefits, the other advantage is redundancy. If the internal system is undergoing maintenance or is down for some other reason, the web system is not impacted. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [Bulk] [GENERAL] General advice on database/web applications
On 3/27/06, Merlin Moncure <[EMAIL PROTECTED]> wrote: > > I have not yet implemented the database, and I am VERY reluctant to put the > > full db outside our "main" firewall because of the need to protect sensitive > > this is natural. One solution is to put 2nd nic on the web server > (carefully firewalled) for connections to the database. Theres lots > of solutions to the problem. a second nic would only introduce more headaches don't you think? it's like opening another can of woop a$$ that'll eventually bite you later. > > > info. So my question, is how do the applications on the webserver interface > > with the database? My one thought for a solution is to have a more limited > > database hosted on the same machine as the webserver that would have > > customer account number, price lists, and product lists--enough for an order > > I personally don't think this is a very good solution. You are > complicating your architecture where database user accounts are much > more natural and appropriate. create a web user and explicitly grant > permisions to that user. This gives you the flexibility to do real > stuff when you want to via functions...pg functions can operate under > a elevated security when you want them to (check out create > function...invoker/definer) > > > to be taken. Credit info, etc. is stored someplace more secure. After an > > order is taken, the webserver/database/something then forwards an "order > > placed" type of message to the main database. Maybe a synch is done between > > webserver database and main database every five minutes, where the main > > database pulls any new orders, and pushes any updated part lists, pricing > > etc. to the webserver db? > > again, I don't like this. you have to maintain the syncing proces and > you are introducing timing issues, as well as greatly complicating > constring checking. Factor in the complexity and the load. I would > suggest doing this only as a last resort. If you must do this, I > would suggest using the slony replicator. > > > My question, is would such a scheme be practical, or is there a "best > > practices" type of approach that I should consider instead, such as the > > suggestion in your next-to-last paragraph? > > My suggestion would be to familiarize yourself with database security. > If using postgres, this means reading over the administration > chapters very carefully, as well as grant/revoke usage, etc. > > Merlin > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- Jonel Rienton mailto:[EMAIL PROTECTED] powered by: google ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [Bulk] [GENERAL] General advice on database/web applications
I would normally put the database inside my LAN and only accesible from boxes in the DMZ through certain ports (remoting). I usually not let the web application access the database directly. I use business objects through remoting and only have those business objects available to the web application and not the data directly. regards, Jonel On 3/27/06, Mark Feller <[EMAIL PROTECTED]> wrote: > The webserver runs linux and I also have iptables on that server filtering > out all but HTTP and SSH traffic. > > I have not yet implemented the database, and I am VERY reluctant to put the > full db outside our "main" firewall because of the need to protect sensitive > info. So my question, is how do the applications on the webserver interface > with the database? My one thought for a solution is to have a more limited > database hosted on the same machine as the webserver that would have > customer account number, price lists, and product lists--enough for an order > to be taken. Credit info, etc. is stored someplace more secure. After an > order is taken, the webserver/database/something then forwards an "order > placed" type of message to the main database. Maybe a synch is done between > webserver database and main database every five minutes, where the main > database pulls any new orders, and pushes any updated part lists, pricing > etc. to the webserver db? > > My question, is would such a scheme be practical, or is there a "best > practices" type of approach that I should consider instead, such as the > suggestion in your next-to-last paragraph? > > Thanks. > > --Mark > > -Original Message- > From: Ted Byers [mailto:[EMAIL PROTECTED] > Sent: Monday, March 27, 2006 2:54 PM > To: Mark Feller; pgsql-general@postgresql.org > Subject: Re: [Bulk] [GENERAL] General advice on database/web > applications > > > > > > I am developing a small web application. Currently, our web server is > > sitting outside our firewall (running its own firewall), and the > > application > > being developed would let users do things like place orders. > > > > My question is...what and where is the database for this? > > > What do you mean when you say your web server is running its own firewall? > I could well be wrong, but I am not aware of a web server that can run a > firewall; web servers and firewalls are, as I understand them, quite > different kinds of software, though I am aware of some hardware that have > built in firewalls. > > Your question, though, doesn't make sense. If, as you say explicitly in > your first sentence, that you're developing a small web application, then > either you don't have a database and need to create it, or you have already > created your database and know both where and what it is. If you haven't > created it already, then you can create it and you have absolute control > over where to put it and what RDBMS to use. The only circumstance in which > I could imagine you having a database back end for your application but not > knowing about it is if you bought hosting services from a company that > provides such services. But if that's the case, then you ought to be asking > that company about it. But if that's the case, they probably already have a > ready made virtual store application for you to use, which makes developing > your own unnecessary unless you're planning to do your own hosting, and that > takes us back to you having complete control over what you use and where you > put it. > > If I were to create such a web application as you describe, I'd create a > database using PostgreSQL or something similar and have it live inside the > firewall, configured to respond only to applications running behind the > firewall. Under no circumstances would I want it to accept connections > across the firewall. Similarly, I'd have my application server and my httpd > server behind the firewall and configured to accept connections across the > firewall but only from proxy servers set up in a DMZ. > > Since you are dealing with sensitive information such as financial data, you > are going to have to design security into your application from start to > finish, and then harden your entire network inside and out, including > especially your firewall and each machine individually. You have some legal > responsibilities to protect your clients' data. I'm told, by folk who ought > to know, that you could face major problems if you fail to exercise due > diligence in protecting your clients' data. > > Cheers, > > Ted > > > > ---(end of broadcast)--- > TIP
Re: [Bulk] [GENERAL] General advice on database/web applications
> I have not yet implemented the database, and I am VERY reluctant to put the > full db outside our "main" firewall because of the need to protect sensitive this is natural. One solution is to put 2nd nic on the web server (carefully firewalled) for connections to the database. Theres lots of solutions to the problem. > info. So my question, is how do the applications on the webserver interface > with the database? My one thought for a solution is to have a more limited > database hosted on the same machine as the webserver that would have > customer account number, price lists, and product lists--enough for an order I personally don't think this is a very good solution. You are complicating your architecture where database user accounts are much more natural and appropriate. create a web user and explicitly grant permisions to that user. This gives you the flexibility to do real stuff when you want to via functions...pg functions can operate under a elevated security when you want them to (check out create function...invoker/definer) > to be taken. Credit info, etc. is stored someplace more secure. After an > order is taken, the webserver/database/something then forwards an "order > placed" type of message to the main database. Maybe a synch is done between > webserver database and main database every five minutes, where the main > database pulls any new orders, and pushes any updated part lists, pricing > etc. to the webserver db? again, I don't like this. you have to maintain the syncing proces and you are introducing timing issues, as well as greatly complicating constring checking. Factor in the complexity and the load. I would suggest doing this only as a last resort. If you must do this, I would suggest using the slony replicator. > My question, is would such a scheme be practical, or is there a "best > practices" type of approach that I should consider instead, such as the > suggestion in your next-to-last paragraph? My suggestion would be to familiarize yourself with database security. If using postgres, this means reading over the administration chapters very carefully, as well as grant/revoke usage, etc. Merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [Bulk] [GENERAL] General advice on database/web applications
The webserver runs linux and I also have iptables on that server filtering out all but HTTP and SSH traffic. I have not yet implemented the database, and I am VERY reluctant to put the full db outside our "main" firewall because of the need to protect sensitive info. So my question, is how do the applications on the webserver interface with the database? My one thought for a solution is to have a more limited database hosted on the same machine as the webserver that would have customer account number, price lists, and product lists--enough for an order to be taken. Credit info, etc. is stored someplace more secure. After an order is taken, the webserver/database/something then forwards an "order placed" type of message to the main database. Maybe a synch is done between webserver database and main database every five minutes, where the main database pulls any new orders, and pushes any updated part lists, pricing etc. to the webserver db? My question, is would such a scheme be practical, or is there a "best practices" type of approach that I should consider instead, such as the suggestion in your next-to-last paragraph? Thanks. --Mark -Original Message- From: Ted Byers [mailto:[EMAIL PROTECTED] Sent: Monday, March 27, 2006 2:54 PM To: Mark Feller; pgsql-general@postgresql.org Subject: Re: [Bulk] [GENERAL] General advice on database/web applications > > I am developing a small web application. Currently, our web server is > sitting outside our firewall (running its own firewall), and the > application > being developed would let users do things like place orders. > > My question is...what and where is the database for this? > What do you mean when you say your web server is running its own firewall? I could well be wrong, but I am not aware of a web server that can run a firewall; web servers and firewalls are, as I understand them, quite different kinds of software, though I am aware of some hardware that have built in firewalls. Your question, though, doesn't make sense. If, as you say explicitly in your first sentence, that you're developing a small web application, then either you don't have a database and need to create it, or you have already created your database and know both where and what it is. If you haven't created it already, then you can create it and you have absolute control over where to put it and what RDBMS to use. The only circumstance in which I could imagine you having a database back end for your application but not knowing about it is if you bought hosting services from a company that provides such services. But if that's the case, then you ought to be asking that company about it. But if that's the case, they probably already have a ready made virtual store application for you to use, which makes developing your own unnecessary unless you're planning to do your own hosting, and that takes us back to you having complete control over what you use and where you put it. If I were to create such a web application as you describe, I'd create a database using PostgreSQL or something similar and have it live inside the firewall, configured to respond only to applications running behind the firewall. Under no circumstances would I want it to accept connections across the firewall. Similarly, I'd have my application server and my httpd server behind the firewall and configured to accept connections across the firewall but only from proxy servers set up in a DMZ. Since you are dealing with sensitive information such as financial data, you are going to have to design security into your application from start to finish, and then harden your entire network inside and out, including especially your firewall and each machine individually. You have some legal responsibilities to protect your clients' data. I'm told, by folk who ought to know, that you could face major problems if you fail to exercise due diligence in protecting your clients' data. Cheers, Ted ---(end of broadcast)--- TIP 1: 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: [Bulk] [GENERAL] General advice on database/web applications
Ted Byers wrote: I am developing a small web application. Currently, our web server is sitting outside our firewall (running its own firewall), and the application being developed would let users do things like place orders. My question is...what and where is the database for this? What do you mean when you say your web server is running its own firewall? I could well be wrong, but I am not aware of a web server that can run a firewall; web servers and firewalls are, as I understand them, quite different kinds of software, though I am aware of some hardware that have built in firewalls. He is probably running Linux, which has apache and iptables and thus is a webserver that is running its own firewall. Joshua D. Drake ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Bulk] [GENERAL] General advice on database/web applications
I am developing a small web application. Currently, our web server is sitting outside our firewall (running its own firewall), and the application being developed would let users do things like place orders. My question is...what and where is the database for this? What do you mean when you say your web server is running its own firewall? I could well be wrong, but I am not aware of a web server that can run a firewall; web servers and firewalls are, as I understand them, quite different kinds of software, though I am aware of some hardware that have built in firewalls. Your question, though, doesn't make sense. If, as you say explicitly in your first sentence, that you're developing a small web application, then either you don't have a database and need to create it, or you have already created your database and know both where and what it is. If you haven't created it already, then you can create it and you have absolute control over where to put it and what RDBMS to use. The only circumstance in which I could imagine you having a database back end for your application but not knowing about it is if you bought hosting services from a company that provides such services. But if that's the case, then you ought to be asking that company about it. But if that's the case, they probably already have a ready made virtual store application for you to use, which makes developing your own unnecessary unless you're planning to do your own hosting, and that takes us back to you having complete control over what you use and where you put it. If I were to create such a web application as you describe, I'd create a database using PostgreSQL or something similar and have it live inside the firewall, configured to respond only to applications running behind the firewall. Under no circumstances would I want it to accept connections across the firewall. Similarly, I'd have my application server and my httpd server behind the firewall and configured to accept connections across the firewall but only from proxy servers set up in a DMZ. Since you are dealing with sensitive information such as financial data, you are going to have to design security into your application from start to finish, and then harden your entire network inside and out, including especially your firewall and each machine individually. You have some legal responsibilities to protect your clients' data. I'm told, by folk who ought to know, that you could face major problems if you fail to exercise due diligence in protecting your clients' data. Cheers, Ted ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org