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

Do I have a small database on the webserver, that is then synch'd with a
"master" database inside our main firewall.  Is this practical?

Does the webserver work directly with one database via VPN?

Do I put everything on the webserver?  I would guess this last would be
least safe.

>
> 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

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

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:r.ted.byers@rogers.com]
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

> 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

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 <mfeller@mgako.com> 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:r.ted.byers@rogers.com]
> 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 majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>


--
Jonel Rienton
mailto:jonel@rientongroup.com
powered by: google

On 3/27/06, Merlin Moncure <mmoncure@gmail.com> 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:jonel@rientongroup.com
powered by: google

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

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/28/06, Alex Turner <armtuk@gmail.com> 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