Project

General

Profile

[Solved] Unable to use DBI / SQLite3 for authentication

Added by sneekm 3 months ago

Hi All,

We are attempting to run lighttpd/1.4.59 with a SQLite database via DBI to do directory level authentication on Ubuntu 20.04.02 LTS.
Initially we started with MySQL via DBI which worked fine.

Using the same environment we have not the been able to switch to SQLite.

When verifying the configuration we get these messages:

lighttpd -tt -f /etc/lighttpd/lighttpd.conf

2021-05-13 10:34:11: mod_authn_dbi.c.84) dbi_conn_connect(): 14: could not open database
2021-05-13 10:34:11: mod_authn_dbi.c.84) dbi_conn_connect(): -3: libdbi could not establish a connection
2021-05-13 10:34:11: mod_authn_dbi.c.84) dbi_conn_connect(): -3: libdbi could not establish a connection
2021-05-13 10:34:11: mod_authn_dbi.c.84) dbi_conn_connect(): -3: libdbi could not establish a connection
2021-05-13 10:34:11: mod_authn_dbi.c.84) dbi_conn_connect(): -3: libdbi could not establish a connection
2021-05-13 10:34:11: mod_authn_dbi.c.84) dbi_conn_connect(): -3: libdbi could not establish a connection
2021-05-13 10:34:11: mod_authn_dbi.c.84) dbi_conn_connect(): -3: libdbi could not establish a connection
2021-05-13 10:34:11: server.c.1517) Configuration of plugins failed. Going down.

The implementation details for this instance:

lighttpd -V

lighttpd/1.4.59 (ssl) - a light and fast webserver

Event Handlers:

+ select (generic)
+ poll (Unix)
+ epoll (Linux)
- /dev/poll (Solaris)
- eventports (Solaris)
- kqueue (FreeBSD)
- libev (generic)

Network handler:

+ linux-sendfile
- freebsd-sendfile
- darwin-sendfile
- solaris-sendfilev
+ writev
+ write
- mmap support

Features:

- IPv6 support
+ zlib support
- zstd support
- bzip2 support
- brotli support
+ crypt support
+ OpenSSL support
- mbedTLS support
- NSS crypto support
- GnuTLS support
- WolfSSL support
- Nettle support
+ PCRE support
- MySQL support
- PgSQL support
+ DBI support
- Kerberos support
- LDAP support
- PAM support
- memcached support
- FAM support
- LUA support
- xml support
- SQLite support
- GDBM support

lighttpd -p -tt -f /etc/lighttpd/lighttpd.conf

config {
    var.PID                      = 2128
    var.CWD                      = "/etc/lighttpd/conf-enabled" 
    server.network-backend       = "linux-sendfile" 
    server.document-root         = "/data/lighttpd/downloads" 
    server.upload-dirs           = ("/data/lighttpd/uploads")
    server.errorlog              = "/var/log/lighttpd/error.log" 
    server.pid-file              = "/run/lighttpd.pid" 
    server.username              = "www-data" 
    server.groupname             = "www-data" 
    server.http-parseopts        = (
        "header-strict"            => "enable",
        "host-strict"              => "enable",
        "host-normalize"           => "enable",
        "url-normalize-unreserved" => "enable",
        "url-normalize-required"   => "enable",
        # 5
        "url-ctrls-reject"         => "enable",
        "url-path-2f-decode"       => "enable",
        "url-path-dotseg-remove"   => "enable",
        # 8
    )
    index-file.names             = ()
    deflate.cache-dir            = "/var/cache/lighttpd/compress/" 
    server.bind                  = "192.168.206.131" 
    server.port                  = 8443
    auth.backend.dbi             = (
        "sql"          => "SELECT password FROM users WHERE username='?' AND realm='?'",
        "dbtype"       => "sqlite3",
        "dbname"       => "auth.sqlite",
        "sqlite_dbdir" => "/data/db/",
        # 4
    )
    auth.backend                 = "dbi" 
    server.dir-listing           = "disable" 
    accesslog.filename           = "/var/log/lighttpd/access.log" 
    ssl.cipher-list              = "HIGH" 
    ssl.engine                   = "enable" 
    ssl.pemfile                  = "/etc/lighttpd/server.pem" 
    auth.require                 = (
        "" => (
            "method"    => "digest",
            "realm"     => "Authorized users only",
            "algorithm" => "SHA-256",
            "require"   => "valid-user",
            # 4
        ),
    )
    status.status-url            = "/server-status" 
    status.config-url            = "/server-config" 
    status.statistics-url        = "/server-statistics" 
    debug.log-request-handling   = "enable" 
    debug.log-condition-handling = "enable" 
    server.modules               = (
        "mod_access",
        "mod_alias",
        "mod_redirect",
        "mod_setenv",
        "mod_authn_dbi",
        "mod_auth",
        "mod_accesslog",
        "mod_openssl",
        "mod_status",
        "mod_deflate",
        "mod_dirlisting",
        "mod_staticfile",
        # 12
    )

}

apt list --installed|grep sqlite

libdbd-sqlite3-perl/focal,now 1.64-1build1 amd64 [installed]
libdbd-sqlite3/focal,now 0.9.0-8ubuntu1 amd64 [installed]
libsqlite3-0/focal,now 3.35.0-1-17377~202104021520~202104041725~ubuntu20.04.1 amd64 [installed,automatic]
libsqlite3-dev/focal,now 3.35.0-1-17377~202104021520~202104041725~ubuntu20.04.1 amd64 [installed]
sqlite3/focal,now 3.35.0-1-17377~202104021520~202104041725~ubuntu20.04.1 amd64 [installed]
sqlitebrowser/focal,now 3.12.1-3~3048~202104041634~ubuntu20.04.1 amd64 [installed]

Please let me know if i missed something.

Thanks,


Replies (6)

RE: Unable to use DBI / SQLite3 for authentication - Added by gstrauss 3 months ago

If you strace the lighttpd process, what happens before the error trace is produced:
2021-05-13 10:34:11: mod_authn_dbi.c.84) dbi_conn_connect(): 14: could not open database

Does the database exist? Does lighttpd have appropriate read/write permission for DBI and SQLite?

RE: Unable to use DBI / SQLite3 for authentication - Added by gstrauss 3 months ago

Aside: please read mod_auth Warning At the moment, I am aware of client support only by Opera for "algorithm" => "SHA-256", and not other major browsers.

RE: Unable to use DBI / SQLite3 for authentication - Added by sneekm 3 months ago

Thank you for looking into this!

I attached the strace output for reference but it appears to be looking in "the wrong" directory

lstat("/usr/var/lib/libdbi/sqlite3/auth.sqlite", 0x7ffdafb632b0) = -1 ENOENT (No such file or directory)

It appears to be ignoring the sqlite_dbdir parameter. I just took that from the documentation.

This is what i have configured.

auth.backend.dbi += (
   "sql" => "SELECT password FROM users WHERE username='?' AND realm='?'",
   "dbtype" => "sqlite3",
   "dbname" => "auth.sqlite",
   "sqlite_dbdir" => "/data/db/" 
)

creating that directory structure and putting the file there (/usr/var/lib/libdbi/sqlite3/auth.sqlite) allows me to start so i guess it is just an issue with the configuration directive / code. Is this something you will be able to verify?

light_trace (49.1 KB) light_trace Lighttpd with dbi sqllite issue

RE: Unable to use DBI / SQLite3 for authentication - Added by gstrauss 3 months ago

This does not appear to be an issue with lighttpd.
This works as intended (for me) on Fedora 34.
The sqlite3_dbdir option is passed as-is by lighttpd to the DBI DBD driver for sqlite3.
http://libdbi-drivers.sourceforge.net/docs/dbd_sqlite3/c98.html

RE: Unable to use DBI / SQLite3 for authentication - Added by sneekm 3 months ago

Thanks for that link. The configuration should read sqlite3_dbdir, not sqlite_dbdir

I changed it and all is good.

Thanks for the quick responses!

RE: Unable to use DBI / SQLite3 for authentication - Added by gstrauss 3 months ago

Sorry about the spelling mistake in the mod_auth doc. It will be fixed momentarily.

    (1-6/6)