Django with multiple databases and app specific routers, cannot add users in MSSQL

Question

We have a Django application that is connecting to multiple MS SQL database instances. There is a router.py for each app that handles routing the processes to each database.

This is my first time setting up multiple database access.

Django built-in apps are routed to the default database via this router.py:

class DjangoRouter(object):
"""
A router to control all database operations on models in the
auth application.
"""

def db_for_read(self, model, **hints):
    """
    Attempts to read auth models go to auth.
    """
    app_list = ('auth', 'admin', 'contenttypes', 'sessions',)

    if model._meta.app_label in app_list:
        return 'default'
    return None

def db_for_write(self, model, **hints):
    """
    Attempts to write auth models go to auth.
    """
    app_list = ('auth', 'admin', 'contenttypes', 'sessions',)
    if model._meta.app_label in app_list:
        return 'default'
    return None

def allow_relation(self, obj1, obj2, **hints):
    """
    Allow relations if a model in the auth app is involved.
    """
    app_list = ('auth', 'admin', 'contenttypes', 'sessions',)
    if obj1._meta.app_label in app_list and obj2._meta.app_label in app_list:
        return True
    return None

def allow_migrate(self, db, app_label, model=None, **hints):
    """
    Make sure the auth app only appears in the 'auth'
    database.
    """
    app_list = ('auth', 'admin', 'contenttypes', 'sessions',)

    if app_label in app_list:
        return db == 'default'
    return None

We are using LDAP authentication and Django's built-in authentication. The idea is that intranet users can authenticate against our AD server. External users can register, and will be authenticated with Django's authentication.

When I have the default database set to:

'default': {
    'ENGINE': 'sql_server.pyodbc',
    'NAME': 'Django',
    'USER': '',
    'PASSWORD': '',
    'HOST': 'sqlbeta',
    'PORT': '',
},

LDAP works, but I cannot add user to Django's authentication. The admin displays a "success" message, but the user is not added to the database.

If I switch the default database back to SQLLite, I am able to authenticate against AD and add Django users.

So, I don't think it is an issue with the routers.py file. I worry that it might be an issue with the 'sql_server.pyodbc' engine.

EDIT: Per request, here are the database settings:

DATABASES = {
    # 'default': {
    #    'ENGINE': 'django.db.backends.sqlite3',
    #    'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    # },
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'Django',
        'USER': '',
        'PASSWORD': '',
        'HOST': 'sqlbeta',
        'PORT': '',
    },
    'master': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'master',
        'USER': '',
        'PASSWORD': '',
        'HOST': 'sqlbeta',
        'PORT': '',
    },
    'databaseone': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'databaseone',
        'USER': '',
        'PASSWORD': '',
        'HOST': 'sqlbeta',
        'PORT': '',
    },
    'databasetwo': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'databasetwo',
        'USER': '',
        'PASSWORD': '',
        'HOST': 'sqlbeta',
        'PORT': '',
    },
}

NOTE: I think this might have to do with the way Django saves new users. Going to take a look there. I am able to use the createsuperuser command to add more superusers while both authentication backends are in place. Confirmed, I can create regular users via the shell, but not through the admin.

Follow up note: Still have not sourced the problem with the admin, but I found that I could add users via a form. I'm thinking the problem must be a bug in the admin.

Edit:

Per @AndrewSmiley's request:

Django Admin Add User View

class UserCreationForm(forms.ModelForm):
    """
    A form that creates a user, with no privileges, from the given username and
    password.
    """
    error_messages = {
        'password_mismatch': _("The two password fields didn't match."),
    }
    password1 = forms.CharField(label=_("Password"),
        widget=forms.PasswordInput)
    password2 = forms.CharField(label=_("Password confirmation"),
        widget=forms.PasswordInput,
        help_text=_("Enter the same password as above, for verification."))

    class Meta:
        model = User
        fields = ("username",)

    def clean_password2(self):
        password1 = self.cleaned_data.get("password1")
        password2 = self.cleaned_data.get("password2")
        if password1 and password2 and password1 != password2:
            raise forms.ValidationError(
                self.error_messages['password_mismatch'],
                code='password_mismatch',
            )
        return password2

    def save(self, commit=True):
        user = super(UserCreationForm, self).save(commit=False)
        user.set_password(self.cleaned_data["password1"])
        if commit:
            user.save()
        return user

UPDATE:

Per @user1797792 suggestions, I created a custom UserCreationForm to set the date_joined value, but we are still unable to add users via the Django Admin.

Relevant forms.py & admin.py entries are below:

class CustomUserCreationForm(UserCreationForm):
    now = timezone.now()

    class Meta:
        model = User
        fields = ('username',)

    def save(self, commit=True):
        user = super(CustomUserCreationForm, self).save(commit=False)
        user.date_joined = self.now
        if commit:
            user.save()
        return user

class CustomUserAdmin(UserAdmin):
    add_form = CustomUserCreationForm

admin.site.unregister(User)
admin.site.register(User, CustomUserAdmin)

Janeejaneen

I am not certain it will help, but can you post your entire databases setting? – Tnt

@TitusP, I've added the entire database config. I'm with you, not sure if that will help any. – Janeejaneen

You have the same app_list defined multiple times, why not define it in the class? And do you have admin code, or are you using Django's default admin? – Ginetteginevra

@Uri, You are right, I should define app_list in the class one time. That was just sloppy copy/pasting on my part. We are just using the default admin. – Janeejaneen

If it works with SQLLite, I think it's a bug with sql_server.pyodbc. Which version are you using and is it the latest stable version? Did you try other databases such as PosgreSQL or MySQL? – Ginetteginevra

@Uri, I have not tried it on PostgreSQL or MySQL. I haven't spun up a copy of either yet to test against. I am using django-pyodbc-azure==1.8.3.0 which depends on pyodbc==3.0.10 for sql_server.pyodbc. – Janeejaneen

Can you post the code that handles new user registration? – Turboprop

@AndrewSmiley, do you want the code from the default admin page that is causing the issue? – Janeejaneen

Please! That would be great! – Turboprop

@Turboprop I posted the add user code from Django's source and linked to their GitHub page. – Janeejaneen

Answer 1

After following all of the suggestions that were submitted via the comments and answers. We realized that the problem is an undocumented bug in the django-pyodbc-azure library.

After spinning up a PostgreSQL database, and connecting our Django app we were unable to recreate this bug. We also used FreeTDS/ODBC drivers to talk back to the MSSQL database, and were unable to recreate it.

Janeejaneen

Answer 2

I think there's a problem with nullable fields, and a bug in the UserCreationForm. I can't replicate this issue, since I don't have a MSSQL server.

https://github.com/django/django/blob/1.8.6/django/contrib/auth/models.py#L185

This function is used by the management command. It generates a timezone.now() for the date_joined property, whilst the Form in the adminpanel just uses the save() function on the object itself, which doesn't do this.

date_joined seems to be left NULL, if you create a user in the admin panel. Can you check if this column has a default value in the MSSQL database? And is NULLABLE. If it has not, and it's not nullable. Then their lies your problem.

Then you should edit the UserCreationForm. Here is a StackOverflow entry that shows you how to do this.

Mealie

You are correct. in MSSQL date_joined is not nullable, and lacks a default value. Will edit the UserCreationForm and post an update. – Janeejaneen