Clever Geek Handbook

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

I am currently writing a web application that will be heavily used by developers and I figured (from personal experience) that there will be times when it would be convenient to do custom searches in an unlimited way. I want my users to execute arbitrary multi-user SQL queries on their personal data (additional cost) so that they can get data relevant to their question at the time.

Obviously, this is something to be done with extreme care, so I would like to make sure that I am going to solve this correctly.

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

  • An attacker could run DOS (you can track this by logging and removing their permissions)
  • Someone could run the function in a malicious manner.
  • Someone can access / modify data that does not belong to them (including the database schema)
  • Someone can delete or change the data in the request (I would prefer they do this in a controlled manner)

What would be the safest way to securely access these users?

+3


source to share


2 answers


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

The only assumption I'm making here is that you are using the current version of PostgreSQL and that you need users to be able to remotely connect to the server (using their own tools) to execute their custom queries. Even if they enter them into a web page, most of the same methods will apply as long as they each have a separate user login for the database server.

First, (as NoBugs pointed out) to prevent users from performing explicit malicious actions (like UPDATES, DELETES, DROPS, etc.), you need to ensure that the user account connecting to the server only has SELECT permissions for db (s) and the table (s) they should be able to read from. Take a look in the guide to find out how to define roles for users and grant specific permissions for 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 restrict a user to a specific table only. If each user needs to be given access to different parts of the table, then PostgreSQL (and almost all DBMSs) won't support that out of the box. The only option is to try and create some kind of SQL / TCP proxy that intercepts the requests and modifies them somehow to limit the results of the query before going to the database server. It 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 monitor your database (and / or entire server) resource usage every few seconds and possibly build in the PostgreSQL service restart mechanism if it is exceeded.



You will need to experiment with how long you need to intervene carefully, as it is entirely possible for a legitimate request to be maxed out for a few seconds.

As you mentioned, you will need to keep a close eye on who tried to accomplish something, and when that is the case, you can work from failure if necessary to find out the culprit. You can really only rely on system logs for this, which can be configured to write to files, CSV, or Syslog.

I would suggest that you create some tools to help you find these magazines quickly to find what you need before you have to try to find it (pun intended).

Finally, you should also try other standard administration and security guidelines (all of which can be found in the guides), including:

  • Only allow access to your users from specific IPs / hosts (don't give the general public any chance when connecting to your server). Your clients will need a static IP address to access the system, but this is definitely worth considering to mitigate the risks.
  • Keep track of all common administrative tasks for the server (especially backups, disk space, log file maintenance, index usage, etc.).
+8


source


Make sure the sql user is running as it only has permissions on tables / files that the user should be able to modify.

There are also some other considerations - only allow trusted input (maybe use https in your api calls?) And be aware that Mysql can access files and stuff that you would not want to allow it.



See also: http://www.greensql.com/article/protect-yourself-sqli-attacks-create-backdoor-web-server-using-mysql

0


source






More articles:


All Articles