Thread: Intervening in Parser -> Planner Stage
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.
"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
> 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
> > 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.