9 BitBlog

This is where I blog about apps I built and technical subjects.

Database level security in webapps

Jan. 3, 2012

One often underestimated challenge of good a webapp design and implementation is security. Most webframeworks nowadays have measures at some level to protect against common security problems. Though it is nice that they take at least some work away from the app developer they still leave a big problem on the table; design and implementation bugs.

The security of a system is only as good as it's weakest link. This means that only one badly coded webpage can severely weaken the system as a whole. Because most webapp security is handled on the code / app layer such a bug can be easily introduced.

In most applications it might be possible to limit the impact of such a bug. This can be done by having an additional layer of security at the datamodel level. Modern database systems provide facilities for limiting what a (database) user can do.

This means you can assign on a table (or column) level if the database user can select, insert, update or delete records. Combining this with a setup where a webapp does not use just one single database user but several an additional layer of security can be had with very little work.

I recently wanted to use database level security for a project. The app allowed for a clean separation of super users and normal end users. It also had several tables that where always going to be off-limits to normal users.

Another nice aspect, security wise, of this webapp is that normal user accounts are only managed by the super users. Under no circumstance did they need any other than select priviliges on the user table.

So to develop te app I planned to split it up into two distinct applications. One for normal users and one for super users. Both would connect with different users to the PostgreSQL database. The database could limit the normal user based on limited granted permissions.

This sounded good until I met with the reality of my webapp. I had built it using the Django framework. Django has a feature that updates the user table each time someone logs-in. The feature got in the way of limiting write access for normal users to the user table. This means that normal users would get update rights, which is something I did not want.

I tried to enable only updates on the last_login column, a feature which PostgreSQL seemed to support. Unfortunately Django uses it's ORM to update the user record. And the ORM updates the whole record, regardless of what has been changed.

Since this app did not need the last login timestamp I just disabled it. For those interested, the following code can be placed in a urls.py to disable it in your own project:

from django.contrib.auth import models as auth_models
from django.contrib.auth.signals import user_logged_in

This little bit of code did the trick for me. By having Django connect to the database with different users depending on the functionality needed I can now feel a little more sure that the database will disallow at least some of the issues that might be introduced.

Because this concept is not Django specific I hope that more app developers start thinking about if, and how, they can leverage this simple but sometimes very effective extra security layer.