On 20 Dec 2010, at 10:05, Stefan Keller wrote:
> I'd like to guard postgres from overcharged and/or malicious queries.
>
> The queries are strinctly read-only (from a SQL users perspective).
> For doing this I'd like to code two functions (preferrably pl/pgsql):
>
> 1. Filter out all SQL commands which are *not* read-only (no
> DROP/DELETE/UPDATE/TRUNCATE).
Most people do this using permissions.
> 2. Get the estimated time (units) from PostgreSQL planner in a
> reliable way (if possible standard/ANSI).
I don't think there's a way to do that directly, not without hacking the source.
What you can do is to have all users go through a SECURITY DEFINER type of function that does this for them. That
functioncan then read the output of EXPLAIN <query> for its estimates. Those aren't exactly times, but cost estimates.
Theactual time taken depends on your hardware, you would need to do some measurements to see how planned costs and
actualtime relate.
I'm not sure this is a good idea though.
Firstly, testing the query plan adds a little overhead to every query coming in. It's not a lot, but if someone fires a
lotof small fast queries it could become a problem. You would be hurting the people who're using your database
correctly,instead of the people who're "abusing" it.
Secondly, you could achieve a similar effect by limiting the amount of time a query is allowed to run. I'm pretty sure
thereare configuration options that cause long-running queries to get killed after a set time.
Thirdly... Reliable estimates??? Lol!
Seriously, as hard as Postgres tries to keep statistics that make sense, I don't think they can ever be considered
entirelyreliable. You may not be vacuuming frequently enough, your statistics target may be too small or your data
mightnot be suitable for statistical analysis (random numbers and very unbalanced distributions are good examples).
Therefore, if you plan to rely on the estimated time a query takes, you're going to be wrong sometimes.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4d0f4181802653553761881!
