How can I safely let users query my database using (Postgre)SQL?

Question

I'm currently writing a web app which would largely be used by developers, and I figured (from personal experience) that there would be times where it would be handy to run custom searches in an unrestricted way. I would like to let my users run arbitrary multi-statement SQL searches on their personal data (for an extra fee), so they can retrieve the data that's relevant to their question at the time.

Obviously, this is something that needs to be done with extreme caution, so I would like to make sure I'm going to tackle this the right way.

As I see it, the main points of concern are:

• A malicious user could run a DOS (can track this via logging and remove their permissions)
• Someone could run a function in a malicious way
• Someone could access/modify data that doesn't belong to them (including database schema)
• Someone could delete or modify data in a query (I would prefer they do that in a controlled manner)

What would be the safest way to go about providing this kind of ability to users safely?

Answer 1

This is dangerous territory (and I strongly recommend you weigh up this requirement carefully due to the obvious dangers you will be exposing yourself to), however I will try to give you the safest way to proceed if you must.

The only assumption I am making here is that you are running a current version of PostgreSQL and that you require users to remotely connect to the server (using their own tools) to execute their custom queries. Even if they will be entering them into a webpage, most of the same techniques will still apply as long as they each have a separate user log in for the database server.

First, (as NoBugs pointed out) to prevent users executing obvious malicious statements (like UPDATES, DELETES, DROPS, etc) you need to ensure that the user account connecting to the server has only SELECT permissions on the db(s) and table(s) they should be able to read from. Have a look in manual to see how to define roles for users, and grant specific permissions to those roles.

http://www.postgresql.org/docs/9.0/static/user-manag.html http://www.postgresql.org/docs/9.0/static/database-roles.html

Note that you can only limit a user down to a particular table. If users each need to be given access to different parts of a table, then PostgreSQL (and nearly all DBMS's) will not support this out of the box. Your only option would be to try and create some kind of SQL/TCP proxy that intercepts requests, and modifies them somehow to limit query results, before passing on to the database server. This would be extremely difficult even for a very experienced developer!

To prevent (or at least detect) DOS attacks, you will need an external script or process to keep an eye on the resource usage of the database (and/or the entire server) every few seconds, and possibly build in a mechanism to restart the PostgreSQL service if it is maxed-out.

You will need to experiment with how long before you should intervene carefully, as it is quite possible for a legitimate query to max things for a few seconds.

As you mentioned, you would need to keep a careful log of who was trying to execute what, & when so, if necessary you can work backwards from a failure, to find out the culprit. You can really only rely on the system logs for this, which can be configured to write out to files, CSV, or Syslog.

I would suggest you pre-create some tools to help you quickly search these logs to find what you need before you need to try and find it (pun intended).

Finally you should also try to follow the other standard best practices for administration and security (all of which can be found in the manuals) including:

• Only allow access for your users from specific ip's/hosts (dont give the general public any chance at connecting to your server. Your customers will need static IP's to access the system, but this is certainly worth considering to mitigate risks.
• Keep a close eye on all of your standard administrative tasks for the server (especially backups, disk space, log file maintenance, index usage, etc.)