Making Django’s Database Connection More Secure for Migrations

Since Django 1.7 (September, 2014) and the introduction of schema migrations, it has always bugged me that Django needs to connect to the database with a user that has pretty close to ALL privileges. That is because when migrate runs it needs to be able to make changes to the schema with commands like CREATE TABLE and ALTER TABLE.

99% of Django database configuration examples I see online show a single default database connection:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'HOST': 'myhost',
        'USER': 'django_user',
        'PASSWORD': 'XYZ*****'
    }
}

This configuration implies django_user would need at least ALTER and CREATE permissions for migrate to work.

What typically happens is new comers set it up like the above, then run into a permissions error the first time they run migrate. That leads to google searches where the “solution” is to run a GRANT ALL for django_user. That solves the problem but it is a big shortcut and opens a security hole. The ALL permission includes potentially destructive commands like DROP TABLE and EXECUTE.  MySQL offers a number of problematic permissions that Postgres doesn’t including FILE, LOCK TABLES and SHUTDOWN.

Ordinarily, connecting to the database with a user that has ALL rights would only be a problem if:

1. There were a SQL injection vulnerability in Django’s ORM.
   or
2. The application used poorly coded raw SQL functions (without escaping inputs properly).

In my opinion it is better to grant each layer of the system only the access it needs. When fulfilling standard web requests Django should only be able to SELECT, INSERT, UPDATE and DELETE the tables it works with. The only time it needs the extra rights is when it runs the actual migrate step during a deploy.

The Solution Is Two Database Connections That Differ By User:

Turns out there is a little known argument to the migrate command (--database) that lets you specify which database connection to use.

To take advantage of that enter two connections under DATABASES in the settings file. They both point to the same database. One uses the basic user that is more locked down. The other is used when running the migrate command.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'HOST': 'myhost',
        'USER': 'django_user',
        'PASSWORD': 'XYZ*****'
    }, 
   'default_with_migration_rights': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'HOST': 'myhost',
        'USER': 'django_migration_user',
        'PASSWORD': 'ABC***'
    }
}

The migrate command then accepts a database argument like so:

python manage.py migrate --database=default_with_migration_rights

NOTE! This example shows the password being entered directly into the config file (and presumable checked into source control).  It is better to store passwords / secrets on the server as an environment variable or in a properties file. This stack overflow page  has examples.

Setting up the database user:

Configuring the database user will vary by platform.

• Postgres GRANT syntax
• Postgres default privileges
• MySQL Privileges Reference

Example for Postgres (we have to worry about connecting, and the sequences):

GRANT CONNECT, SELECT, UPDATE, INSERT, DELETE 
   ON ALL TABLES IN SCHEMA db_name 
   TO django_user;
GRANT USAGE, SELECT 
   ON ALL SEQUENCES IN SCHEMA db_name
   TO django_user;
GRANT ALL PRIVILEGES 
   ON ALL TABLES IN SCHEMA db_name 
   TO django_migration_user; 
GRANT ALL PRIVILEGES
   ON ALL SEQUENCES IN SCHEMA db_name
   TO django_migration_user;

Example for MySQL:

GRANT SELECT, INSERT, UPDATE, DELETE ON db_name.* 
   TO 'django_user'@'127.0.0.1' 
   IDENTIFIED BY 'XYZ***';

GRANT ALL ON db_name.* 
  TO 'django_migration_user'@'127.0.0.1' 
  IDENTIFIED BY 'ABC***';

FLUSH PRIVILEGES;

Is This Really Worth It?

In reality, even with the extra precaution, if there were a SQL injection vulnerability in your Django app, you are so screwed. 

Unfettered UPDATE access could be used to mix up record ownership, inject content, change prices, etc.

SELECT may be the most dangerous as it would allow hackers to harvest your entire database.

Those would both would require guessing table names though.

But at least they couldn’t change the schema!