Creating read-only database connections in Django

Question

Is it possible to add a connection to the database, so only queries are allowed when using it?

Something like this would be great:

DATABASES = { #can do update, insert, etc... 'default': { 'ENGINE': 'django.db.backends.mysql', 'USER': 'root', 'PASSWORD': '12345', } #select only 'default_readonly': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'mydb', 'PASSWORD': '12345', 'READONLY': True, } } 

I did not find anything simple.

Answer 1

As far as I know, Django does not provide any parameters to limit the connection of the database to read-only mode. However, you can do this by creating a read-only user in your MySQL database engine.

Another idea on the Django code side would be to create a custom cursor that throws an exception if execute or executemany . You can look at this django-db-readonly module .

Answer 2

You must set permissions for the user used to connect (instead of "root").

This causes insert / update / delete requests to cause errors that you must manage in your views (if necessary)

Answer 3

From Django 1.2, you can specify the class of the router for routing queries to various databases ( https://docs.djangoproject.com/en/dev/topics/db/multi-db/ ).

To use one bit for reading and one for writing, you can define the Router class as follows:

 Class MyRouter(object): def db_for_read(self, model, **hints): return 'default_readonly' def db_for_write(self, model, **hints): return 'default' def allow_syncdb(self, db, model): return db == 'default' 

And put it in your settings.py

 DATABASE_ROUTERS = ['path.to.MyRouter'] 

This works for me until I use select_for_update (), which is considered a read operation but requires write access to the database. The only workaround I have found so far is to override select_for_update in the manager class in a similar way:

 class MyManager(Manager): def select_for_update(self, *args, **kwargs): return super(MyManager, self).using('default').select_for_update(*args, **kwargs)