Permissions required to allow arbitrary sql to be executed safely

Question

I want to create an SQL sandbox that will allow users to execute arbitrary SELECT queries at a PostGIS database. Essentially, I want to allow users access to psql to play with.

Obviously this is a security disaster if write access is allowed. How can I create a system such that querying data is allowed, but there is no reasonable possibility of a user:

1. Compromising the data in the database
2. Gaining broader access to the server
3. Crippling the system with a query like SELECT * from long_table, long_table, long_table, long_table, long_table, long_table, long_table that will take a life-time to execute

Please be as specific as possible in your answer.

Answer 1

Well you just really need to CREATE a ROLE and then GRANT read only access explicitly to the things you want to allow. Anything you don't grant, they can't do (as long as they're not a superuser).

If you've granted readonly access and they're not a superuser, they shouldn't be able to gain access to the underlying system. That is not to say you shouldn't install postgres as an unprivileged user, you should - simply that it shouldn't be necessary to accomplish what you listed.

Ok, you edited your post to include issuing crazy queries. I don't believe postgres currently has a way to limit query resources per user.

Answer 2

#3 can't be prevented when the user can execute his own piece of SQL. You need a (small) application that executes predefined SQL. Not even VIEW's can help you here, everybody can join a couple of views to cripple your system.

Answer 3

You can install postgres as an unprivileged user, and run it that way. This way you take advantage of the existing system permissions to restrict what people can do with the database, including isolating their access to just their own database. See instructions at the bottom of this page:

http://www.postgresql.org/docs/current/interactive/tutorial-start.html

If you automate some part of this, say give people a 'setup postgres' command to run, voila.

Answer 4

For #3 could you write some code that monitors query activity and kill those queries you deem CRAZY based on some criteria using the system views in postgre?

Answer 5

Problems #1 and #2 are handled via explicit GRANTs and REVOKEs of permissions as others have noted.

As for #3,

ALTER ROLE <rolename> SET statement_timeout = '60000'

has always worked well for me. This terminates any query that executes for longer than 1 minute (60000 ms). We discovered this after having a couple of user-written queries in phpPgAdmin cause problems...