Re[2]: Restricting queries by the presence of a WHERE clause

On Wed, 28 Jun 2000 11:50:25 -0400 you wrote:

> John Morton <jwm(at)plain(dot)co(dot)nz> writes:
> > What I'd like to be able to do is have the database drop any SELECT,
> > UPDATE or DELETE queries unless they have something fairly specific in
> > their WHERE clause.
>
> What about COPY?

Er.. what about it?

> Even more to the point, what about being able to back up your database?
> If you did insert some custom changes that prevented queries of this
> form, you'd be breaking pg_dump.

Only if they where indescrimanent of which user was accessing the
database. They wouldn't apply to the postgres, user for example.

> The "black hat gets root" scenario is not very convincing as a reason
> to want to hack your database server like this, anyway. Anyone with
> root can simply copy off the physical files constituting the database,
> and then inspect those at leisure (ie, set them up in another
> installation with an unrestricted server). So restricting the set
> of accepted queries doesn't help against someone who's compromised
> the underlying platform.

The database is not on the same machine as the secure web server, which
makes it vastly easier to secure (it only accepts inbound connections to
the database via SSH tunneling). I am fully aware of just how fscked I am
if they get root on the database server :-)

Getting root on the secure web server is somewhat easier (it's effectively
got a port open to the whole internet, even after it's firewalled to hell
and back) and if they achieve that, then they can connect to the database
from there using the webserver's own database username. If that user can
select from the table with the credit cards in it, so can they.

> What you might consider doing is setting up a simple proxy server that
> accepts only a very limited range of queries (perhaps not even true
> SQL, but just "give me the info for account FOO") and then sends the
> equivalent SQL to the Postgres server and returns the results. So
>
> webclient <---> proxy <---> Postgres

Ok. Pluses:

- The standalone program is easier to hack together than delving into the
database I could probably just hack on SQL Relay or something.
- The proxy can be as tightly secured as the database without losing
functionallity, as it only needs to accept a connection from the secure
web server.

Minuses:

- Another machine to buy
- Root on the proxy is as bad as root was on the secure server, so..
- The simple hack will need thorough security auditing
- It's yet another machine to secure.
- It's yet another point of failure.

What I'm really asking is is there any way of doing this with just
triggers or rules, and if not, can a function be written to examine the
where clause (or whatever it's called in the parse tree) and select
triggers be hacked into the database?

If adding select triggers and a function is a solution, it might be
cheaper than hacking a custom proxy, auditing it, building a new machine,
and securing it.

Thanks,
John.