Project

General

Profile

mod_auth_dbi - sql or a way to match the columns, password never match

Added by mckaygerhard 30 days ago

i success to made auth with lighty in newer last version.. with basic and plain also, but now i try to setup the DBI module auth in lighttpd.. i used default debian 11 package so

  • OS: debian 11
  • Ver: 1.4.59-1
  • Cnf: default

after install i just enable all the required modules:

apt-get install lighttpd lighttpd-modules-dbi

lighttpd-enable-mod accesslog auth unconfigured

the configuration are so organized and spitted in debian so i will translated for you guys in unorganized way one:

server.document-root        = "/var/www/html" 
server.upload-dirs          = ( "/var/cache/lighttpd/uploads" )
server.errorlog             = "/var/log/lighttpd/error.log" 
server.pid-file             = "/run/lighttpd.pid" 
server.username             = "www-data" 
server.groupname            = "www-data" 
server.port                 = 80
## this is just misc. so is not affected 
include_shell "/usr/share/lighttpd/use-ipv6.pl " + server.port
include_shell "/usr/share/lighttpd/create-mime.conf.pl" 
include "/etc/lighttpd/conf-enabled/*.conf" 
## modules and order is important too of course:
server.modules = (
    "mod_indexfile",
    "mod_access",
    "mod_alias",
     "mod_redirect",
    "mod_dirlisting",
    "mod_staticfile",
        "mod_auth",
        "mod_setenv",
        "mod_accesslog",
         "mod_authn_dbi",
)
dir-listing.encoding = "utf-8" 
server.dir-listing   = "enable" 
auth.backend     = "dbi" 
# setup for dbi backend
auth.backend.dbi = (
        "sql"          => "SELECT MD5(clear) as aword FROM passwd where id='?'",
        "dbtype"       => "mysql",
        "dbname"       => "dbcouriertest",
        "username"      => "root",
        "password"      => "root",
        "socket"        => "/var/run/mysqld/mysqld.sock", ## port => "3306" gives me error
        "host"          => "localhost" 
    )
auth.require = ( 
       "/protedted" => (
               "method"    => "basic",
               "realm"     => "aword",
               "algorithm" => "MD5",
               "require"   => "valid-user" 
           ),
    )
debug.log-request-handling = "enable" 

the only addition over debian/any/lighty defaults is the part of auth.backend and auth.require, i added the debug.log-request-handling .. so i dont see the sense on so detailed config but i parsed any way. . to avoid any stupid discution respect the lack of information..

i not put any line about test of config file etc cos i can put logs result so is assumed is fine,

cheking the logs i can see conection to DB is pretty fine:


2021-09-20 18:10:38: server.c.1513) server started (lighttpd/1.4.59)

i gues my problem is that i want to parse and check only the user.. not all the user:key:real part. in the sql.. so i have this on the log:

2021-09-20 18:10:49: mod_auth.c.828) password doesn't match for /protedted/ username: admin IP: 127.0.0.1
2021-09-20 18:10:53: mod_auth.c.828) password doesn't match for /protedted/ username: admin IP: 127.0.0.1

so my all the text paste is for only two simple questions:

  1. if i must use the crypted basic way.. how i must put the value in the "clear" column, noted that i m using the courier default datsabase table for users.. that have a cryopt column for crypted version and a clear column for clear text version of the same password id user..
  2. how can i setup the SQL sentence to use the plain text password column then? cos the crypt column is in this form for MD5? i mean we can modify the sql to match the columns values! as CONCAT(clear:realm:user) etc etc ..

i guess my lighttpd setup is right.. the only bad part is the SQL or the data stored.. but i need specific steps for.. thanks in advance

IMPORTANT NOTE ABOUT MD5 in bash shell, MD5 is not the same as the modified-MD5 password encryption algorithm that's used in Linux, this is different from the “md5” password hash method, so calculation from console shell is not same as in database mysql.


Replies (5)

RE: mod_auth_dbi - sql or a way to match the columns, password never match - Added by gstrauss 29 days ago

how can i setup the SQL sentence to use the plain text password column then?

You've been pointed to the documentation multiple times. Please read the 4 short paragraphs for mod_authn_dbi

RE: mod_auth_dbi - sql or a way to match the columns, password never match - Added by gstrauss 29 days ago

Additionally, the DBI section in the mod_auth Configuration template provides details on '?' substitution.

Reference the MySQL user manual for documentation about SQL syntax.
e.g. https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

RE: mod_auth_dbi - sql or a way to match the columns, password never match - Added by gstrauss 29 days ago

FYI: If you have a password in the database encoded with crypt(), then you can return the crypted password to mod_authn_dbi. If you do not have a crypted password, then you need to return password digest value.
These are both stated in the very first sentence in mod_authn_dbi

RE: mod_auth_dbi - sql or a way to match the columns, password never match - Added by mckaygerhard 29 days ago

gstrauss wrote in RE: mod_auth_dbi - sql or a way to match the columns, pas...:

FYI: If you have a password in the database encoded with crypt(), then you can return the crypted password to mod_authn_dbi. If you do not have a crypted password, then you need to return password digest value.

finally an answer to the problem .. ok that's what I asked .. and that's what I try .. change the query like this:

SELECT SHA2(CONCAT(id,':','aword',':',clear), 256) as aword FROM passwd where id='?' (remenber that aword is the real, but that is for digest.. and i want to use MD5..

also is not clear if i must rerturn only the password or more files/columns .. documentation does not pointe what values , QUERY must return right?

RE: mod_auth_dbi - sql or a way to match the columns, password never match - Added by gstrauss 29 days ago

Your reading comprehension is poor. The first sentence of the mod_authn_dbi documentation states:
The DBI backend is recommended for using a database to store username, realm, and encrypted (crypt()) or hashed (message digests) passwords.

.

also is not clear if i must rerturn only the password or more files/columns .. documentation does not pointe what values , QUERY must return right?

Your understanding of SQL is also poor, as the sample SQL statement in the document already specifies. You should try to mimic the examples provided before attempting to adapt to other database schemas. Lastly, you missed my hint to read a specific page of the MySQL documentation.

Given your poor understanding of many of these topics, it is reasonable that you are trying to learn. However, it is yet another failure on your part to assume that this site is the place that will teach you how to use things like third-party databases. This site is for the lighttpd web server.

    (1-5/5)