AmvTek blog

complex web systems

Accessing multiple postgres schemas from Django

One of the postgres feature we have been laking the most when working with the Django ORM is the lake of direct support for postgres schemas. In the past we tried several roads to explicitely target other schemas than public when creating or accessing the database structures required by our django applications, but those from code approaches were difficult to maintain.

It appears that this problem can be solved quite elegantly by leveraging postgres search_path parameter.

A simple example

Assumes we wish all the tables of our django project to be created in a schema called django and that our project also requires mapping/accessing a few tables in a schema called legacy. This maybe achieved very easily by fine tuning the DATABASES setting.

Let’see 2 differents ways to configure this :

Approach 1, setting search_path at connection time :

We assume that django and legacy schemas already exist in the target database and that the user we use to access it have the necessary permissions on such schemas.

On django side, we will use a search_path connection option so that we land in the correct schema. Two databases will be configured even though we are connecting to the same database.

# your project settings file

DATABASES = {

    'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'OPTIONS': {
                'options': '-c search_path=django,public'
            },
            'NAME': 'multi_schema_db',
            'USER': 'appuser',
            'PASSWORD': 'secret',
    },

    'legacy': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'OPTIONS': {
                'options': '-c search_path=legacy,public'
            },
            'NAME': 'multi_schema_db',
            'USER': 'appuser',
            'PASSWORD': 'secret',
    },
}

This is a good approach for development as it requires minimum configuration.

If you syncdb against default databases, all tables for the managed models will get created in django schema…

Approach 2, configuring various databases users :

One drawback of the first approach is that the set search_path command will be send from client to server each time a new database connection is established.

To save some milliseconds on connection time, one can preassign the desired search_path to the user used for connection…

Preassigning search_path to database user :

As postgres user in psql shell…

-- user accessing django schema...
CREATE USER django_user LOGIN PASSWORD 'secret';
GRANT appuser TO django_user;
ALTER ROLE django_user SET search_path TO django, public;

-- user accessing legacy schema...
CREATE USER legacy_user LOGIN PASSWORD 'secret';
GRANT appuser TO legacy_user;
ALTER ROLE legacy_user SET search_path TO legacy, public;

Defining DATABASES setting :

# your production project settings file

DATABASES = {

    'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'multi_schema_db',
            'USER': 'django_user',
            'PASSWORD': 'secret',
    },

    'legacy': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'multi_schema_db',
            'USER': 'legacy_user',
            'PASSWORD': 'secret',
    },
}

That’s all there is to support multiples postgres schemas from Django.

Custom Django Database Router may also be defined to automatically select the correct schema to use…