Thread: Intervening in Parser -> Planner Stage

Intervening in Parser -> Planner Stage

From
"Arguile"
Date:
A few questions on the parser -> planner stage.

I'm currently pursuing a line of thought on resource limiting, and I'd like
some opinions on whether it's possible/probable.


I need to give community access directly to the database, so I need to
impose some sane controls. As I can't hold any of that class of users
accountable, I need to impose the limits in the software.

I'd like to try hooking in right after the parser produces it's tree and
modifying limitCount based on a few rules, then handing it back to normal
flow. After that I'd also like to hook in before the planner hands the plan
to the executor, evaluate estimated cost, and accept/deny the query based on
that.

I realise cost is just simply a number for comparison, but I'm only looking
to cap excessively high costs due to inexperience (lots of cartesians
products by accident) or maliscious intent. It would be set based on a
refference set of queries run on the individual system.

At the same time processes will be monitored (probably using Bruce's tool)
at the same time and killing anything that might slip by.

The concept (rewriting the query and limiting cost) seems to work well. At
current though it's horribly expensive and buggy as I'm rewriting the query
using regexeps (no grammar rules), running an explain on it, parsing and
evaluating the explain output for cost, then finally running the query.

As a related issue I've been hunting about for ways to limit classes of
users to certain commands (such as only allow SELECT on a database). I've
only begun to play with the debug output but so far it's my understanding
that the :command node from the parse tree identifies the operation being
performed. Since I plan to be intervening after the parser anyways, I
thought it would be opertune to check a permissions table and see if that
user/group has permission to run that command class on the database.



At the moment I'm just looking for opinions on the attemp and, if it's not
an obvious dead end, a few pointers on where to start. This is a learning
project (as my C skills are horrid) so any suggestions are appreciated.




Re: Intervening in Parser -> Planner Stage

From
Tom Lane
Date:
"Arguile" <arguile@lucentstudios.com> writes:
> I'm currently pursuing a line of thought on resource limiting, and I'd like
> some opinions on whether it's possible/probable.

These seem like fairly indirect ways of limiting resource usage.  Why
not instead set a direct limit on the amount of runtime allowed?

Postgres doesn't use the ITIMER_VIRTUAL (CPU time) timer, so about all
you'd need issetitimer(ITIMER_VIRTUAL, &itimer, NULL);
to a suitable limit before starting query execution, and cancel it again
at successful query end.  Set the SIGVTALRM signal handler to
QueryCancelHandler (same as SIGINT), and voila.  A direct solution in
about ten lines of code ...
        regards, tom lane


Re: Intervening in Parser -> Planner Stage

From
Christopher Kings-Lynne
Date:
> I need to give community access directly to the database, so I need to
> impose some sane controls. As I can't hold any of that class of users
> accountable, I need to impose the limits in the software.

For comparison, I know that MySQL allows you to set the max queries per
hour for a particular user...

Chris



Re: Intervening in Parser -> Planner Stage

From
"Rod Taylor"
Date:
> > I need to give community access directly to the database, so I
need to
> > impose some sane controls. As I can't hold any of that class of
users
> > accountable, I need to impose the limits in the software.
>
> For comparison, I know that MySQL allows you to set the max queries
per
> hour for a particular user...

Which is completely useless.  A single table with a few thousand
records joined to itself 40 or so times as a cartesian product will
completely wipe out most MySQL boxes and thats only 1 query :)
PostgreSQL has the same problem..

What would be interesting is the ability to limit a query to a maximum
of X cost.  This requires knowing the cost of a function call which is
currently evaluated as nothing.

Perhaps even 'nice' processeses running for certain users / groups.
Offline or background processes could be niced to 20 to allow the
interactive sessions to run unnoticed.  Not so good if the background
process locks a bunch of stuff though -- so that may be more trouble
than good.

Either way the user can simply attempt to make thousands of database
connections which would effectivly block anyone else from connecting
at all.