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

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...

Answer 2

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.