Re: [Bulk] General advice on database/web applications - Mailing list pgsql-general

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