Create Read-Only MySQL Database Connection in Django

Question

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

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 didn't find anything simple.

Maxime Lorant · Accepted Answer · 2014-07-25T07:53:24.507

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.

edited Jul 25 '14 at 07:53

answered Jul 25 '14 at 07:18

Maxime Lorant

34,607
19
87
97

score 5 · Answer 2 · answered Jul 25 '14 at 07:20

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)

answered Jul 25 '14 at 07:20

Don

16,928
12
63
101

1

Thanks Don! However I prefer Maxime solution as it keeps me inside Django (and therefore inside Git) – YardenST Jul 25 '14 at 07:36

score 5 · Answer 3 · answered Oct 22 '14 at 13:03

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)

Actually I notice that the select_for_update issue is solved in Django 1.7 by considering a select_for_update a write operation. — Oberix, Oct 22 '14 at 16:30

score 0 · Answer 4 · answered Apr 10 '18 at 12:30

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.

answered Apr 10 '18 at 12:30

blueyed

27,102
4
75
71

Create Read-Only MySQL Database Connection in Django

4 Answers4