On 21 Dec 2010, at 10:57, Stefan Keller wrote:
> You answered:
>>> 1. Filter out all SQL commands which are *not* read-only (no DROP
>> Most people do this using permissions.
>
> Oh, yes: forgot to mention that; that's obvious. What I also looked
> for was the PL/pgSQL's "EXECUTE command-string".
I'm not sure what you're getting at here, but if you're saying that you have to catch SQL commands called from EXECUTE
separatelyI think you're wrong. I wouldn't expect Postgres to not apply permissions in such cases. A simple test-case
canprove that.
>>> 2. Get the estimated time (units) from PostgreSQL planner in a
>>> reliable way (if possible standard/ANSI).
>
> Ok; again keep in mind that I have a read-only database. Therefore the
> statistics should be up-to-date (after a vacuum analyse).
Up-to-date? Probably. Correct or adequate? That depends, as I pointed out in my previous message.
> See below my attempt to write such a function I called
> "secure_execute(text)". It's still not functioning and I have
> indicated two problems there. What do you think?
Your problems seem to stem from a lack of experience with set-returning functions. You best look up the documentation
forthose, it explains it better than I could. Make sure you look at the docs for the versions of Postgres that you're
usingor expect to use, as there's a relatively new feature in this domain returning a set as a table.
> I like the idea letting abandon the query if it's obviously(!) wrong
> or if the planner alerts me about very high costs?
> Or should I rather abandon the idea of such a function and simply rely
> on read-only privileges and a session statement_timeout?
Although I don't think what you're attempting is wrong in any way, I'd first see whether it's necessary to do so. You
canuse the built-in features (permissions & statement_timeout) and see whether that's adequate for your use-case. If
it'snot, then it's time to look into tightening things up.
Of course, having some experience with the solution through experimentation can't hurt if you can afford to.
What you're doing would by many on this list be pointed out as "premature optimisation", although that usually involves
queryperformance ;)
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4d10881c802651631920626!
