Thread: Running untrusted sql safely?
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 but I want to prevent therm from changing anything or escaping postgresql and executing system commands. I.e., it is intended to allow for searching only. I understand and accept that resource hogging queries could submitted constituting a DoS attack but I will deal with that in other ways.) I am thinking the running the query on a connection with a role that gives only select privileges might be sufficient. Is it? Any things I need to watch out for? Any other or better ways to do this?
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.
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! :-)
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. 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.
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.
On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote: > I just hoping for some confirmation that the permissions based > approach did not have some holes in it that I am > not seeing. Another possibility is to create a set of functions that contain the query operations you would like to allow, isolate those in a schema, and make that schema the only thing accessible to the (semi-)trusted users. Generally, I try to design things so that web servers and other clients who could potentially be compromised don't execute full- function SQL directly, but go through functions instead.
On Sun, Feb 15, 2009 at 4:39 PM, Christophe <xof@thebuild.com> wrote: > > On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote: > >> I just hoping for some confirmation that the permissions based approach >> did not have some holes in it that I am >> not seeing. > > Another possibility is to create a set of functions that contain the query > operations you would like to allow, isolate those in a schema, and make that > schema the only thing accessible to the (semi-)trusted users. I can see that getting complex real fast in a big operation, but for a database that runs a few big reporting queries every day or sits on an intranet would be workable. Another option is to create preferred views. These server two purposes, one they make life easier for your users, because they don't have to join 7 tables to look at the data anymore, the view does that for them, or whatever makes the queries ugly. They don't have to worry about accidentally creating an unconstrained join by accident unless they step outside the views. The users who know how to writer bigger and better queries and test them with explain analyze are given view creation ability, and it's a self sustaining environment. I've found users faced with lots of tables very receptive to views to make their job simpler, so there's usually a pretty good buy in on it.
Scott Marlowe wrote: > On Sun, Feb 15, 2009 at 4:39 PM, Christophe <xof@thebuild.com> wrote: >> On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote: >> >>> I just hoping for some confirmation that the permissions based approach >>> did not have some holes in it that I am >>> not seeing. >> Another possibility is to create a set of functions that contain the query >> operations you would like to allow, isolate those in a schema, and make that >> schema the only thing accessible to the (semi-)trusted users. > > I can see that getting complex real fast in a big operation, but for a > database that runs a few big reporting queries every day or sits on an > intranet would be workable. ... And to actually answer Christophes question: yes, granting only SELECT to a few tables is enough to prevent them doing anything else in the database. But watch out for the default permissions on the public schema of all the databases the users are able to connect to. Regards Tino