Nejc Saje   

blog | about

Django PostgreSQL Read-Only Connection

A while ago I was in a situation where I had to perform some alterations on Django ORM objects and dump the modified objects to a JSON file. But I also had to make sure that the changes I performed did not get saved into the original database.

At first, I thought I'd simply use the pre_save signal to catch any erroneous save() calls. As it turned out, simply not calling save() on objects was not sufficient - Django performs save() automatically on certain operations like modifying Many-To-Many fields.

My next step was to use a transaction and then roll it back at the end, but that still hasn't left me feeling safe enough. So in addition to that, I decided to also use a read-only PostgreSQL transaction, which throws an error when any modifications to the database are attempted.

Since Django doesn't support read-only connections out of the box, I resorted to reading some PostgreSQL documentation and some Psycopg and Django source code, and finally I managed to create a read-only connection with the following configuration:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mydb',
        'USER': 'myusername',
        'PASSWORD': 'mypassword',
        'HOST': 'myhost',
        'OPTIONS': {
            'options': '-c default_transaction_read_only=on'
        }
    }
}

But I wanted a read-only connection in only a part of my application (in a single management command, in fact), so I created a decorator that temporarily adds a read-only option to my connection settings. After the decorated function exits, the settings are reset to their original state.

Disclaimer: only use this decorator in single-threaded applications, otherwise unintended parts of the application might get a read-only connection!

def postgres_read_only(using='default'):
    def decorator(func):
        @functools.wraps(func)
        def f(*args, **kwargs):
            db_settings = settings.DATABASES[using]
            db_options = db_settings.setdefault('OPTIONS', {})
            old_pg_options = db_options.setdefault('options', '')
            readonly_option = '-c default_transaction_read_only=on'
            db_options['options'] = ' '.join([readonly_option, old_pg_options])
            try:
                ret = func(*args, **kwargs)
            finally:
                db_options['options'] = old_pg_options
                connections[using].close()
            return ret
        return f
    return decorator

Example usage:

from django.core.management.base import BaseCommand
import myapp.models

class Command(BaseCommand):

    # put subsequent connections in read-only mode
    @postgres_read_only(using='default')
    def handle(self, *args, **options):
        user = myapp.models.User.objects.get(pk=1)
        user.name = 'X'
        user.save() # raises exception
comments powered by Disqus