[Django]-How to configure database permissions for a Django app?

10👍

From the django docs:

https://docs.djangoproject.com/en/dev/topics/install/

If you plan to use Django’s manage.py syncdb command to automatically create database tables for your models (after first installing Django and creating a project), you’ll need to ensure that Django has permission to create and alter tables in the database you’re using; if you plan to manually create the tables, you can simply grant Django SELECT, INSERT, UPDATE and DELETE permissions. On some databases, Django will need ALTER TABLE privileges during syncdb but won’t issue ALTER TABLE statements on a table once syncdb has created it. After creating a database user with these permissions, you’ll specify the details in your project’s settings file, see DATABASES for details.

👤Rob

10👍

I’ve just tested initial setup with MySQL. For python manage.py migrate at least you need following grants for simple operation (if yo use db-preparation):

  1. CREATE, ALTER, INDEX
  2. SELECT, UPDATE, INSERT, DELETE

And, by the way – security matters. You can reduce attack impact by limiting your system exposure. In this case – you can restrict ‘DROP’ – which is fairly huge plus. If you leave some tricky hole with ability to SQL-inject – you probably reduce the damage. I will research in the future if it will not do any harm to remove DELETE keyword – that would limit potential threats as well. Just because we all leave bugs from time to time 🙂

👤Dagaz

8👍

I usually:

grant all privileges on my_db.* to my_user@localhost identified by 'my_user_pass'
grant all privileges on test_my_db.* to my_user@localhost identified by 'my_user_pass'

I suppose if there were a bug in django, you might be opening your database up to terrible things, but you’d have other problems if there were that big of a security hole in django.

django minimally needs select, insert, update, and delete, to operate. If you’re using test or syncdb at all, you’ll also need to be able to create tables, and indexes (and maybe the file permission for loading sql fixtures).

So, for a mysql db, I’d guess the optimal set of permissions might be select, insert, update, delete, create, index, and file. If you wanted to get real nitty-gritty, you could selectively grant these permissions as appropriate on the table level (rather than the db level).

Personally, I find grant all ... easier to type.

👤Seth

0👍

You need to create custom migrations and use raw SQL for any fine-tuning. A simple example below.


  1. First create an empty migration file
python manage.py makemigrations --empty myapp
  1. Rename to something meaningful
mv 0002_* 0002_allow_workers_access_to_user_table.py
  1. Edit your migration file
from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ("myapp_user", "0001_initial"),
    ]

    operations = [

        # Allow web workers access to the user table
        migrations.RunSQL(
            sql="""
                GRANT SELECT ON TABLE myapp_user TO "web-worker";
            """,
            reverse_sql="""
                REVOKE SELECT ON TABLE myapp_user FROM "web-worker";
            """,
        ),
    ]

-3👍

What’s the purpose of configuring permissions on DB level? If your server is compromised then the attacker will be able to do anything with your database (because he has the login/pass) and permissons won’t help. If your server is secured then permissions are useless.

Permissions can make sense if your DB server is available from the outer world, but it is not a good idea to do so.

Leave a comment