Webdav authentication fails after migrating from SQLite to MySQL

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

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.

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')

Hi,

Thank you very much for the explanation and the script.

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

Hello,

I’m experiencing the same issue on 11.0.9 community edition. Fresh install with MySQL, I can login fine with the contact email on seahub web and api, but WebDAV logins fail with:

20:50:56.973 - WARNING : User admin@homeserver.local doesn't exist
20:50:56.973 - WARNING : Authentication (basic) failed for user 'admin@homeserver.local', realm 'Seafile Authentication'.
20:50:56.973 - INFO    : 192.168.1.12 - (anonymous) - [2024-07-08 03:50:56] "GET " elap=0.142sec -> 401 Not Authorized

Logins with the associated *@auth.local email from ccnet_db.EmailUser work correctly, but the matching contact_email in seahub_db.profile_profile fails to work.

seafdav.log output from server start:

20:51:01.654 - INFO    : Init seahub database...
20:51:01.662 - WARNING : App wsgidav.mw.cors.Cors(None).is_disabled() returned True: skipping.
20:51:01.670 - INFO    : WsgiDAV/4.3.0 Python/3.11.9 FreeBSD-14.1-RELEASE-amd64-64bit-ELF
20:51:01.670 - INFO    : Lock manager:      LockManager(LockStorageDict)
20:51:01.670 - INFO    : Property manager:  None
20:51:01.670 - INFO    : Domain controller: SeafileDomainController()
20:51:01.670 - INFO    : Registered DAV providers by route:
20:51:01.670 - INFO    :   - '/:dir_browser': FilesystemProvider for path '/usr/local/www/haiwen/seafile-server/seahub/thirdpart/wsgidav/dir_browser/htdocs' (Read-Only)
20:51:01.670 - INFO    :   - '/seafdav': SeafileProvider for Seafile (Read-Write)
20:51:01.670 - WARNING : Basic authentication is enabled: It is highly recommended to enable SSL.
20:51:01.670 - INFO    : Running WsgiDAV/4.3.0 gunicorn/22.0.0 Python 3.11.9 ...

Running your script, I get the following output + error:

root@files:/usr/local/www/haiwen/seafile-server-latest # ./seahub.sh python-env python3.11 wsgidav-db-connection-test.py

awk: can't open file /usr/local/www/haiwen/seafile-server/pro/python/seafevents/seafevents_api.py
 source line number 1

find user 4f43ca479a694e5388063cf0829e315c@auth.local in ccnet db

Init seahub database...
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/_collections.py", line 186, in __getattr__
    return self._data[key]
           ~~~~~~~~~~^^^^^
KeyError: 'profile_profile'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/www/haiwen/seafile-server/wsgidav-db-connection-test.py", line 113, in <module>
    profile_profile = Base.classes.profile_profile
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/_collections.py", line 188, in __getattr__
    raise AttributeError(key)
AttributeError: profile_profile
Done.

This issue happens for all accounts on the server.