Thread: Restricting queries by the presence of a WHERE clause

Restricting queries by the presence of a WHERE clause

From
John Morton
Date:
I'm working on building a system for storing customer credit card details
for reuse in an ecommerce system. The present design involves keeping the
shopping cart details, products, orders and mundane customer details in
one database, and the specific credit card details in another, more
heavily restricted database.

The heavily restricted database is only accessable from another machine
running the secure web server. Periodically customers will enter their
credit card details into that database via forms, and they will need
access to keep it up to date. They access their record via a
username/password combo that maps to a unique customer_id, which was
generated randomly from a pool of 2^92 possible combinations.

No problems so far, until some black hat gets root on the secure web
server. This doesn't necessarily imply they can get root on the database,
but it does mean they have the same access as the web server had, so the
can basically grab all the credit card details with one select query.

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. As there are no SELECT triggers, I guess it will have
to involve rules, but it doesn't look like any of them can alter or act on
what's in a WHERE clause. How deep am I going to have to hack to get this
sort of functionality working?

[And, no, limiting won't help either :-( They can just loop around a query like
this: select * from customers where primary_key_field not in
(list_of_previously_seen_keys); ]

TAI,
John

Re: Restricting queries by the presence of a WHERE clause

From
Travis Bauer
Date:
Wouldn't it be possible to create a trigger and check for the where clause
there?

----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------

On Wed, 28 Jun 2000, John Morton wrote:

> I'm working on building a system for storing customer credit card details
> for reuse in an ecommerce system. The present design involves keeping the
> shopping cart details, products, orders and mundane customer details in
> one database, and the specific credit card details in another, more
> heavily restricted database.
>
> The heavily restricted database is only accessable from another machine
> running the secure web server. Periodically customers will enter their
> credit card details into that database via forms, and they will need
> access to keep it up to date. They access their record via a
> username/password combo that maps to a unique customer_id, which was
> generated randomly from a pool of 2^92 possible combinations.
>
> No problems so far, until some black hat gets root on the secure web
> server. This doesn't necessarily imply they can get root on the database,
> but it does mean they have the same access as the web server had, so the
> can basically grab all the credit card details with one select query.
>
> 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. As there are no SELECT triggers, I guess it will have
> to involve rules, but it doesn't look like any of them can alter or act on
> what's in a WHERE clause. How deep am I going to have to hack to get this
> sort of functionality working?
>
> [And, no, limiting won't help either :-( They can just loop around a query like
> this: select * from customers where primary_key_field not in
> (list_of_previously_seen_keys); ]
>
> TAI,
> John
>
>
>
>
>


Re: Restricting queries by the presence of a WHERE clause

From
Tom Lane
Date:
John Morton <jwm@plain.co.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?

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.

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.

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

and now you don't have to hack Postgres but just maintain a simple
standalone program.  You arrange pg_hba.conf so that the Postgres
server won't accept direct connections from outside, but you can
still do pg_dump and manual database maintenance from local connections.

            regards, tom lane

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

From
John Morton
Date:
On Wed, 28 Jun 2000 07:32:11 -0500 (EST) you wrote:

 > Wouldn't it be possible to create a trigger and check for the where clause
 > there?

First of all, I've got no idea how to check a where clause inside a
trigger, which was the main reason I asked :-) I've only every built
trigger functions in PL/tcl. Guess I should have a look at the C API.

Secondly, there is no way to make a trigger for a select query (in 6.5,
anyway), and blocking selects is the most important thing I need to do.

John

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

From
John Morton
Date:
On Wed, 28 Jun 2000 11:50:25 -0400 you wrote:

 > John Morton <jwm@plain.co.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.

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

From
Lincoln Yeoh
Date:
> >     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.

If you're not too afraid you could try running the proxy server on the
database.

Check out Perl's DBD Proxy and Server. I believe you can use it to limit
the sort of queries that go through. In fact you could probably use it
write a proxy which only does a handful of queries - where the SQL is
prestored/hardcoded in the proxy, so in effect all the client can supply
are the parameters.

Now, the question is - would the DBD proxy be fast, robust and safe enough
for you...

Also another issue is you will probably need to use perl at the relevant
webapps- the client (webapp) will use DBD proxy to talk to DBD server which
will then talk to the actual database.

Cheerio,
Link.


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

From
Tom Lane
Date:
John Morton <jwm@plain.co.nz> writes:
>> webclient  <--->  proxy  <--->  Postgres

> 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??  Who said anything about another machine?  I was just
thinking another daemon process on the database server machine.

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

No.  If there were, what makes you think that it'd be easier to
security-audit it than a standalone proxy?

            regards, tom lane