Re: Running untrusted sql safely?

Scott Marlowe wrote:
> On Sun, Feb 15, 2009 at 3:09 PM, Stuart McGraw <smcg2297@frii.com> wrote:
>> John R Pierce wrote:
>>>
>>> Stuart McGraw wrote:
>>>>
>>>> What is the best way to run an arbitrary query received from an untrusted
>>>> source, safely?
>>>> (I want a web page form with a textbox that
>>>> a user can enter an arbitrary sql statement,
>>>> then run it .....
>>>
>>> just keep http://xkcd.com/327/ in mind.
>>
>> Yes, exactly what I would like some advice on avoiding! :-)
>
> Your first idea, to allow it to connect via a read only user is a good
> start.  Another thing you can do is explain the query, then see what
> the cost is according to first line in the explain output that has it.
> explain select * from a;
>                       QUERY PLAN
> ------------------------------------------------------
>  Seq Scan on a  (cost=0.00..29.40 rows=1940 width=12)
>
> Grep out that first line, look for the number on the right of the ..
> and if it's over some predetermined threshold then refuse to run it.

The "29.40"?
That's an interesting idea that would not have
occurred to me, thanks!

> It's like herding cats.  There's only so much you can do to prevent
> someone who's running sql on your database from DOSing the server.

In my case access to arbitrary sql statements will
be limited to a relatively small set of authenticated
users so a social/administrative approach to DoS
problems will be OK I think.  But for protection
against data deletion/corruption I would like
a stronger guarantee.

I just hoping for some confirmation that the permissions
based approach did not have some holes in it that I am
not seeing.