[Solved]-Create Read-Only MySQL Database Connection in Django

7👍

As far as I know, Django does not provide any options to restrict the database connection to a “read-only” mode. However, you can do it by creating a readonly user in your MySQL database engine.

Another idea, on the Django code side, would be to create your own cursor, which throws exception if execute or executemany is called. You can look at this module django-db-readonly.

5👍

You should set permissions on the user used for the connection (instead of ‘root’).

This causes insert/update/delete queries raise errors, which you should manage in you views (in needed)

👤Don

5👍

From Django 1.2 you can specify a Router class to ‘route’ queries to different databases (https://docs.djangoproject.com/en/dev/topics/db/multi-db/).

To use one db for read and one for write you can define a Router class like this:

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 place this in your settings.py

DATABASE_ROUTERS = ['path.to.MyRouter']

This is working for me as long as I don’t use select_for_update(), which is considered a read operation, but needs write access to the database. The only workaround I found so far is to override select_for_update in a 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)
👤Oberix

0👍

A possible solution would be to make the transaction readonly when the connection gets created in Django’s connection_created signal.

See https://stackoverflow.com/a/49753667/15690 for an example with PostgreSQL.

Leave a comment