Specifying Readonly access for Django.db connection object

Question

I have a series of integration-level tests that are being run as a management command in my Django project. These tests are verifying the integrity of a large amount of weather data ingested from external sources into my database. Because I have such a large amount of data, I really have to test against my production database for the tests to be meaningful. What I'm trying to figure out is how I can define a read-only database connection that is specific to that command or connection object. I should also add that these tests can't go through the ORM, so I need to execute raw SQL.

The structure of my test looks like this

class Command(BaseCommand):
    help = 'Runs Integration Tests and Query Tests against Prod Database'

    def handle(self,*args, **options):
        suite = unittest.TestLoader().loadTestsFromTestCase(TestWeatherModel)
        ret = unittest.TextTestRunner().run(suite)
        if(len(ret.failures) != 0):
            sys.exit(1)
        else:
            sys.exit(0)

class TestWeatherModel(unittest.TestCase):
    def testCollectWeatherDataHist(self):
        wm = WeatherManager()
        wm.CollectWeatherData()
        self.assertTrue(wm.weatherData is not None)

And the WeatherManager.CollectWeatherData() method would look like this:

def CollecWeatherData(self):
    cur = connection.cursor()
    cur.execute(<Raw SQL Query>)
    wm.WeatherData = cur.fetchall()
    cur.close()

I want to somehow idiot-proof this, so that someone else (or me) can't come along later and accidentally write a test that would modify the production database.

Jenaejenda

in my experience, that's done on the database-side. In other words, the tests should connect to the database with test-specific read-only user credentials. your tests also probably shouldn't have to hit a live DB, but that's a larger architectural issue. – Own

I know that I shouldn't have these tests hitting the production DB, I'm going to eventually move all the ingestion and verification to a staging DB, and then sync to the production DB, but I haven't gotten it done yet. – Jenaejenda

Answer 1

Man, once again, I should read the docs more carefully before I post questions here. I can define a readonly connection to my production database in the settings file, and then straight from the docs:

If you are using more than one database, you can use django.db.connections to obtain the connection (and cursor) for a specific database. django.db.connections is a dictionary-like object that allows you to retrieve a specific connection using its alias:

from django.db import connections
cursor = connections['my_db_alias'].cursor()
# Your code here...

Jenaejenda

Answer 2

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

Source: https://nejc.saje.info/django-postgresql-readonly.html

Freeliving

Answer 3

You can achieve this by hooking into Django's connection_created signal, and then making the transaction read-only.

The following works for PostgreSQL:

from django.db.backends.signals import connection_created


class MyappConfig(AppConfig):
    def ready(self):
        def connection_created_handler(connection, **kwargs):
            with connection.cursor() as cursor:
                cursor.execute('SET default_transaction_read_only = true;')
        connection_created.connect(connection_created_handler, weak=False)

This can be useful for some specific Django settings (e.g. to run development code with runserver against the production DB), where you do not want to create a real read-only DB user.

Vanden

Answer 4

If you add a serializer for you model, you could specialized in the serializer that is working in readonly mode

class AccountSerializer(serializers.ModelSerializer):
    class Meta:
        model = Account
        fields = ('id', 'account_name', 'users', 'created')
        read_only_fields = ('account_name',)

from http://www.django-rest-framework.org/api-guide/serializers/#specifying-read-only-fields

Slaty

Anyway, create a read-only user on the DB is the best and most secure solution – Slaty