Re: Restricting queries by the presence of a WHERE clause

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