Re: Running untrusted sql safely?

From: Stuart McGraw <smcg2297(at)frii(dot)com>
To:
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Running untrusted sql safely?
Date: 2009-02-15 22:47:14
Message-ID: 49989B72.401@frii.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Scott Marlowe wrote:
> On Sun, Feb 15, 2009 at 3:09 PM, Stuart McGraw <smcg2297(at)frii(dot)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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2009-02-15 23:05:40 Re: Attempting to connect
Previous Message Scott Marlowe 2009-02-15 22:32:46 Re: Attempting to connect