Webdav authentication fails after migrating from SQLite to MySQL

[ChristopGH]

Hello,

I migrated my Seafile server (community version) from 10 to 11 last week (actually, I installed a new instance and re-synchronized all my data because the upgrading failed).

By the way, I migrated from SQLite to MySQL (I create new users).

Now, I have some problems to use Webdav because of the different users’ designations, *@auth.local and email address.

In my comprehension, users are managed in two tables :

• ccnet_db → EmailUser (email = *@auth.local)
• seahub_db → profile_profile (user = *@auth.local / contact_email = real email)

I can login with the real email (via both navigator and Seafile client).

But I cannot login by using Webdav, I get these lines on logs/seafdav.log file :

12:29:58.681 - INFO    : 127.0.0.1 - (anonymous) - [2024-01-02 11:29:58] "PROPFIND /" depth=1, elap=0.000sec -> 401 Not Authorized
12:29:58.701 - WARNING : User <email_address> doesn't exist
12:29:58.701 - WARNING : Authentication (basic) failed for user '<email_address>', realm 'Seafile Authentication'.
12:29:58.701 - INFO    : 127.0.0.1 - (anonymous) - [2024-01-02 11:29:58] "PROPFIND /" depth=1, elap=0.003sec -> 401 Not Authorized

By replacing *@auth.local by the email address in ccnet_db.EmailUser, it worked one day but not anymore. I replaced *@auth.local by the email address in seahub_db.profile_profile but it didn’t work.

I recreate a new user, did not manually change the database and tried to login (in Webdav) with *@auth.local (in place of the email) but it didn’t work :

12:42:26.028 - INFO    : 127.0.0.1 - (anonymous) - [2024-01-02 11:42:26] "PROPFIND /" depth=1, elap=0.000sec -> 401 Not Authorized
12:42:26.185 - INFO    : 127.0.0.1 - *@auth.local - [2024-01-02 11:42:26] "PROPFIND /" depth=1, elap=0.030sec -> 207 Multi-Status
12:42:26.494 - INFO    : 127.0.0.1 - (anonymous) - [2024-01-02 11:42:26] "PROPFIND /*****" depth=1, elap=0.000sec -> 401 Not Authorized
12:42:26.513 - WARNING : User *@auth.local doesn't exist
12:42:26.513 - WARNING : Authentication (basic) failed for user '*@auth.local', realm 'Seafile Authentication'.
12:42:26.514 - INFO    : 127.0.0.1 - (anonymous) - [2024-01-02 11:42:26] "PROPFIND /*****" depth=1, elap=0.003sec -> 401 Not Authorized
12:42:26.825 - INFO    : 127.0.0.1 - (anonymous) - [2024-01-02 11:42:26] "PROPFIND /" depth=1, elap=0.000sec -> 401 Not Authorized
12:42:26.863 - WARNING : User *@auth.local doesn't exist
12:42:26.863 - WARNING : Authentication (basic) failed for user '*@auth.local', realm 'Seafile Authentication'.
12:42:26.863 - INFO    : 127.0.0.1 - (anonymous) - [2024-01-02 11:42:26] "PROPFIND /" depth=1, elap=0.003sec -> 401 Not Authorized

Do anyone have encounter this problem ?
Is there any solution ?

Thank you for advance.

Christophe

[lian]

Hi, I just tested it on our demo site demo.seafile.com, which is Seafile 11 pro version with mysql.

I created a new user webdav@seafile.com (6851b8d4c61e4f7091f10aa744473c22@auth.local), password is webdav, and I can login webdav via both emails.

I took a closer look at the code, and the reason for error User xxx doesn't exist is that the webdav program cannot find the user when querying the database based on the email entered.

If this email does exist in the database, then the problem is probably with the database connection. I will write a webdav database connection test script in the next two days, you can use this script to test it then.

[lian]

Hello, you can put the following script (wsgidav-db-connection-test.py) to the seafile-server-latest folder, update CCNET_EMAIL, CONTACT_EMAIL, and DATABASES,

then run:

./seahub.sh python-env python3 wsgidav-db-connection-test.py

wsgidav-db-connection-test.py

from seaserv import ccnet_api as api
from urllib.parse import quote_plus

from sqlalchemy import create_engine
from sqlalchemy.event import contains as has_event_listener, listen as add_event_listener
from sqlalchemy.exc import DisconnectionError
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import Pool
from sqlalchemy.ext.automap import automap_base

Base = automap_base()

# email in ccnet_db → EmailUser
CCNET_EMAIL = ''

# contact_email in seahub_db → profile_profile
CONTACT_EMAIL = ''

# DATABASES config in seahub_settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': '',
        'USER': '',
        'PASSWORD': '',
        'HOST': '127.0.0.1',
        'PORT': '3306',
        'OPTIONS': {
            'init_command': 'SET storage_engine=INNODB',
            'charset': 'utf8mb4',
        }
    }
}


def init_db_session_class():
    try:
        print('Init seahub database...')
        engine = create_seahub_db_engine()
        Base.prepare(engine, reflect=True)
        Session = sessionmaker(bind=engine)
        return Session
    except ImportError:
        return None
    except Exception as e:
        print('Failed to init seahub db: %s.', e)
        return None


def create_seahub_db_engine():
    db_infos = DATABASES['default']
    if db_infos.get('ENGINE') != 'django.db.backends.mysql':
        print.warning('Failed to init seahub db, only mysql db supported.')
        return

    db_host = db_infos.get('HOST', '127.0.0.1')
    db_port = int(db_infos.get('PORT', '3306'))
    db_name = db_infos.get('NAME')
    if not db_name:
        print('Failed to init seahub db, db name is not set.')
        return
    db_user = db_infos.get('USER')
    if not db_user:
        print('Failed to init seahub db, db user is not set.')
        return
    db_passwd = db_infos.get('PASSWORD')

    db_url = "mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8" % (db_user, quote_plus(db_passwd), db_host, db_port, db_name)

    kwargs = dict(pool_recycle=300, echo=False, echo_pool=False)

    engine = create_engine(db_url, **kwargs)
    if not has_event_listener(Pool, 'checkout', ping_connection):
        # We use has_event_listener to double check in case we call create_engine
        # multipe times in the same process.
        add_event_listener(Pool, 'checkout', ping_connection)

    return engine


# This is used to fix the problem of "MySQL has gone away" that happens when
# mysql server is restarted or the pooled connections are closed by the mysql
# server beacause being idle for too long.
#
# See http://stackoverflow.com/a/17791117/1467959
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    cursor = dbapi_connection.cursor()
    try:
        cursor.execute("SELECT 1")
        cursor.close()
    except Exception as e:
        print('fail to ping database server, disposing all cached connections')
        print(e)
        connection_proxy._pool.dispose()

        # Raise DisconnectionError so the pool would create a new connection
        raise DisconnectionError()


# get user from db
ccnet_user_obj = api.get_emailuser(CCNET_EMAIL)
if ccnet_user_obj:
    print(f'\nfind user {ccnet_user_obj.email} in ccnet db\n')

# get user from seahub db
seahub_db_session_class = init_db_session_class()
profile_profile = Base.classes.profile_profile
seahub_db_session = seahub_db_session_class()
q = seahub_db_session.query(profile_profile.user).filter(profile_profile.contact_email==CONTACT_EMAIL)
res = q.first()
if res:
    print(f'\nfind user {res[0]} in seahub db\n')

[ChristopGH]

Hi,

Thank you very much for the explanation and the script.

I will run it in the next days and let you know.