Thread: Restricting queries by the presence of a WHERE clause
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
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 > > > > >
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
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
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.
> > 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.
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