Apache::AuthDBI(3pm)

okay so bestie, gather round, because this is Apache::AuthDBI(3pm) from the User Contributed Perl Documentation section, and it's the manual page for a module that lets you do authentication AND authorization straight out of a database using Perl's DBI. yes. the whole login flow. from your database. iconic behavior honestly. 💅

---

✨ NAME ✨

Apache::AuthDBI — Authentication and Authorization via Perl's DBI 🫶

---

🎀 SYNOPSIS 🎀

# Configuration in httpd.conf or startup.pl:

PerlModule Apache::AuthDBI

# Authentication and Authorization in .htaccess:

AuthName DBI
AuthType Basic

PerlAuthenHandler Apache::AuthDBI::authen
PerlAuthzHandler  Apache::AuthDBI::authz

PerlSetVar Auth_DBI_data_source   dbi:driver:dsn
PerlSetVar Auth_DBI_username      db_username
PerlSetVar Auth_DBI_password      db_password
#DBI->connect($data_source, $username, $password)

PerlSetVar Auth_DBI_pwd_table     users
PerlSetVar Auth_DBI_uid_field     username
PerlSetVar Auth_DBI_pwd_field     password
# authentication: SELECT pwd_field FROM pwd_table WHERE uid_field=$user
PerlSetVar Auth_DBI_grp_field     groupname
# authorization: SELECT grp_field FROM pwd_table WHERE uid_field=$user

require valid-user
require user   user_1  user_2 ...
require group group_1 group_2 ...

okay so quick vibe check on that 👆 — AuthType is limited to Basic, that's the whole menu, no substitutions. You can use one or more require lines, like stack them if you want. But here's the lowkey efficient part: for a single require line that says either valid-user or user user_1 user_2 ..., you only need the authentication handler. That's it. Authorization handler not even invited. periodt. ✨

---

🌸 DESCRIPTION 🌸

This module lets you do authentication and authorization against a database, using Perl's DBI. For the list of supported DBI drivers, go peek at:

http://dbi.perl.org/

---

🔐 Authentication

so here's the flow, bestie, step by step:

For the username you hand it, the password gets looked up in the cache first. And if:

• the cache isn't configured, or
• the user isn't found in the cache, or
• the given password doesn't match the cached password,

…then it goes and asks the database instead. no hard feelings, it just needs a second opinion. 🙈

If the username doesn't exist:

• and the authoritative directive is set to 'on' → the request is rejected 💀
• and the authoritative directive is set to 'off' → control gets passed on to the next module in line 🫶

If the password from the database for that username comes back empty:

• and the nopasswd directive is set to 'off' → request rejected 😭
• and the nopasswd directive is set to 'on' → any password is accepted. yes any. go off. 🔥

Then, finally, the passwords get retrieved from the database — and yeah, multiple passwords per userid are allowed, polyamorous password energy — and the result goes into the environment variable REMOTE_PASSWORDS. Then it gets compared to the password that was given. Now, the comparison style depends:

• if the encrypted directive is set to 'on', the given password gets encrypted using perl's crypt() function before comparison 🔐
• if the encrypted directive is set to 'off', the plain-text passwords are compared

If that comparison fails, the request is rejected. If it passes, the request is accepted and the password gets dropped into the environment variable REMOTE_PASSWORD. slay. 💅

The SQL-select used for retrieving the passwords is:

SELECT pwd_field FROM pwd_table WHERE uid_field = user

And if a pwd_whereclause exists, it gets appended to that SQL-select. cute little add-on.

---

Also! This module supports a simple kind of logging mechanism, and ngl it's kind of a neat trick. Whenever the handler is called and a log_string is configured, the log_field gets updated with the log_string. And depending on the database, macros like TODAY can be used inside that log_string. 👑

The SQL-select used for the logging mechanism is:

UPDATE pwd_table SET log_field = log_string WHERE uid_field = user

---

🎟️ Authorization

When the authorization handler gets called, the authentication has already happened — meaning the given username/password has already been validated. so it's just vibes from here, the hard part is done. ✅

The handler analyzes and processes the requirements line by line. The request is accepted as soon as the first requirement is fulfilled. first match wins, no waiting around.

• In the case of 'valid-user' → request accepted. that's it. done. 🎉
• In the case of one or more user-names → they get compared against the given user-name until the first match.
• In the case of one or more group-names → all groups of the given username are looked up in the cache. And if the cache isn't configured, or the user isn't in the cache, or the requested group doesn't match the cached group, then the groups get requested from the database. A comma separated list of all these groups goes into the environment variable REMOTE_GROUPS. Then those groups get compared against the required groups until the first match.

If there's no match and the authoritative directive is set to 'on', the request is rejected. 💀

But when authorization does succeed, the environment variable REMOTE_GROUP is set to the group name — which means your user scripts can grab it without hitting the database again. efficiency queen. 👑

The SQL-select used for retrieving the groups is (and which one depends on whether a grp_table exists):

SELECT grp_field FROM pwd_table WHERE uid_field = user
SELECT grp_field FROM grp_table WHERE uid_field = user

So the group-info can live either in the main users table, or in an extra table — the extra one's for when there's an m:n relationship between users and groups. From all the selected groups, a comma-separated list gets built, and that list is what gets compared against the required groups.

lowkey pro tip incoming: if you don't like normalized group records, you can put that comma-separated list of groups — no spaces, bestie, NO SPACES — directly into the grp_field instead of single groups. 🌈

And if a grp_whereclause exists, it gets appended to the SQL-select. same deal as the password one.

---

💾 Cache

The module maintains an optional ~~cache~~ "cash" for all passwords/groups — that's literally what the page says, and honestly? 🤑 same energy. See the method setCacheTime(n) for how to enable the cache.

Every server has its own cache. But optionally, the cache can be put into a shared memory segment, so it can be shared among all the servers. See the CONFIGURATION section for how to enable shared memory usage. 👀

Now, in order to stop the cache from growing indefinitely, a CleanupHandler can be initialized — it skips through the cache and deletes all the outdated entries. And the cute part? This can be done once per request, after sending the response, so it never slows down the response time to the client. main character time management fr fr. ⭐

The minimum time between two successive runs of the CleanupHandler is configurable (again, see the CONFIGURATION section). The default is 0, which means the CleanupHandler runs after every request. every single one. no breaks. 💀

---

and that's the end of part 1 bestie — there's more coming, don't go anywhere 💖

🌈 LIST OF TOKENS 🌈

okay bestie, here's the whole menu of config variables. every single one. no skips. 💅

• Auth_DBI_data_source (Authentication and Authorization) — the whole "where's the db at" situation. Syntax is 'dbi:driver:dsn', and this gets handed off to the database driver when it connects. plot twist: this one (and username and password too) can be a tilde ('~') separated list of several data_sources, and all those triples get tried until one connect actually works. so yes, backup servers, you can have them. and if you'd rather use the env var DBI_DSN instead of spelling out a data_source? then just... don't set this parameter at all. 🦋
• Auth_DBI_username (Authentication and Authorization) — passed to the database driver during connect. can also be a tilde ('~') separated list — same list rules as data_source up there. 👑
• Auth_DBI_password (Authentication and Authorization) — also passed to the driver during connect, also can be a tilde ('~') separated list. yeah, same deal as data_source. 💜
• Auth_DBI_pwd_table (Authentication and Authorization) — the table that holds at least the fields for the username and the (possibly encrypted) password. the username should be unique. no dupes, periodt.
• Auth_DBI_uid_field (Authentication and Authorization) — the name of the field in Auth_DBI_pwd_table that holds the username.
• Auth_DBI_pwd_field (Authentication only) — the name of the field in Auth_DBI_pwd_table that holds the password. 🔐
• Auth_DBI_pwd_whereclause (Authentication only) — use this when you wanna tack extra constraints onto the SQL-select.
• Auth_DBI_grp_table (Authorization only) — the table that holds at least the fields for the username and the groupname.
• Auth_DBI_grp_field (Authorization only) — the field name in Auth_DBI_grp_table that holds the groupname. 💖
• Auth_DBI_grp_whereclause (Authorization only) — extra constraints for the SQL-select, same vibe as the pwd one.
• Auth_DBI_log_field (Authentication only) — the field name in Auth_DBI_pwd_table that holds the log string.
• Auth_DBI_log_string (Authentication only) — the string that updates Auth_DBI_log_field in Auth_DBI_pwd_table. depending on your database this can be a macro situation like 'TODAY'. ✨
• Auth_DBI_authoritative < on / off> (Authentication and Authorization) — default is 'on'. when it's 'on', there's no fall-through to other authentication methods if the check fails. when you set it to 'off', control gets passed along to any other authentication modules. > bestie, be SO sure you know what you're doing before you flip this off. like genuinely.
• Auth_DBI_nopasswd < on / off > (Authentication only) — default is 'off'. when it's 'on', the password comparison gets skipped if the password pulled from the database is empty — i.e. allow any password at all. it's 'off' by default specifically so that an empty Auth_DBI_pwd_field doesn't let people log in with some random password. > again: be sure you know what you're doing when you switch this on. 🙈
• Auth_DBI_encrypted < on / off > (Authentication only) — default is 'on'. when it's 'on', the password coming out of the database is assumed to be crypted, so the incoming password gets crypted before it's compared. when it's 'off', the comparison happens directly against the plain-text password that was typed in.
• Auth_DBI_encryption_method < sha1hex/md5hex/crypt > (Authentication only) — default is blank. when you set it to one or more encryption methods, the password from the database is assumed to be crypted, so the incoming password gets crypted before comparison. it supports falling back, so specifying 'sha1hex/md5hex' would let a site that's mid-upgrade to sha1 support both methods at once. sha1 is the recommended method. 🔥
• Auth_DBI_encryption_salt < password / userid > (Authentication only) — when crypting the given password, AuthDBI uses the password selected from the database as the salt by default. set this parameter to 'userid' and it uses the userid as salt instead.
• Auth_DBI_uidcasesensitive < on / off > (Authentication and Authorization) — default is 'on'. when it's 'off', the entered userid gets converted to lower case, and the userid in the password select-statement gets converted to lower case too.
• Auth_DBI_pwdcasesensitive < on / off > (Authentication only) — default is 'on'. when it's 'off', the entered password gets converted to lower case.
• Auth_DBI_placeholder < on / off > (Authentication and Authorization) — default is 'off'. when it's 'on', the select statement is prepared using a placeholder for the username. this may improve performance on databases that support that method. ⭐

---

🎀 CONFIGURATION 🎀

bestie the module should be loaded when the Apache daemon starts up. drop this line into your httpd.conf:

PerlModule Apache::AuthDBI

a super common move is loading the module in a startup file using the PerlRequire directive. peep eg/startup.pl for an example. 🫶

there are three configurations that are server-specific, and they can all be done in a startup file:

Apache::AuthDBI->setCacheTime(0);

this sets the lifetime, in seconds, for entries in the cache. default is 0, which turns the cache off. when you set it to any value n > 0, the passwords/groups of all users get cached for at least n seconds. once the request finishes, a special handler skips through the cache and deletes all the outdated entries (the ones older than the CacheTime). 💖

Apache::AuthDBI->setCleanupTime(-1);

this sets the minimum time, in seconds, between two successive runs of the CleanupHandler, which deletes all the outdated entries from the cache. the default is -1, which disables the CleanupHandler. setting the interval to 0 runs the CleanupHandler after every request. on a heavily loaded server you should set this to a value that's a compromise between scanning a big cache that might have tons of outdated entries, and running the CleanupHandler a million times on a cache with only a few entries. balance, queen. ⚖️

Apache::AuthDBI->setProjID(1);

this sets the project ID used to create a semaphore ID for shared memory. it can be any integer from 1 to 255, otherwise it defaults to 1.

NOTE: this has to be set before calling initIPC.

if you're running multiple instances of Apache on the same server (like Apache1 and Apache2, for example), you might not want — or might not even be able — to use shared memory between them. in that case, use a different project ID on each server.

if you're reading this because you suspect a permission issue or a semaphore collision: use 'ipcs -s' to list semaphores and look for the Semaphore ID from the apache error log. if you find it, shut down Apache (all of them) and use 'ipcrm sem <semaphore key>' to remove the colliding (and hopefully unused) semaphore.

you might also wanna clear out any orphaned shared memory segments by using 'ipcs -m' and removing the orphans with ipcrm shm <shared memory id>. 🧚

Apache::AuthDBI->initIPC(50000);

this turns on shared memory for the cache. instead of every server keeping its own cache, all the servers get access to one common cache. for sites running a bunch of servers this should cut the database load considerably. the number is the size of the shared memory segment in bytes. this size is fixed — there's no dynamic allocation of more segments. rule of thumb: multiply the estimated maximum number of simultaneously cached users by 100 to get a rough idea of the size you need. values below 500 get overwritten with the default 50000.

to enable debugging, the variable $Apache::AuthDBI::DEBUG must be set. you can do this in startup.pl or in the user script. set the variable to 1 and it just reports about a cache miss. set it to 2 and you get full debug output. 🔍

---

👑 PREREQUISITES 👑

MOD_PERL 2.0

Apache::DBI version 0.96 and should work under mod_perl 2.0 RC5 and later with httpd 2.0.49 and later.

Apache::DBI versions less than 1.00 are NO longer supported. additionally, mod_perl versions less then 2.0.0 are NO longer supported. 💀

MOD_PERL 1.0

heads up: this module needs mod_perl-1.08 or higher, apache_1.3.0 or higher, and mod_perl needs to be configured with the appropriate call-back hooks:

PERL_AUTHEN=1 PERL_AUTHZ=1 PERL_CLEANUP=1 PERL_STACKED_HANDLERS=1

Apache::DBI v0.94 was the last version before dual mod_perl 2.x support was begun. it's still recommended that you use the latest version of Apache::DBI, because Apache::DBI versions less than 1.00 are NO longer supported.

---

🔥 SECURITY 🔥

in some cases it's more secure not to put the username and the password in the .htaccess file. here's an example showing a solution to that exact problem: ✨

httpd.conf:

<Perl>
my($uid,$pwd) = My::dbi_pwd_fetch();
$Location{'/foo/bar'}->{PerlSetVar} = [
    [ Auth_DBI_username  => $uid ],
    [ Auth_DBI_password  => $pwd ],
];
</Perl>

---

🌸 SEE ALSO 🌸

Apache, mod_perl, DBI

---

💜 AUTHORS 💜

• Apache::AuthDBI by Edmund Mergl; now maintained and supported by the modperl mailinglist, subscribe by sending mail to modperl-subscribe@perl.apache.org. 🫶
• mod_perl by Doug MacEachern.
• DBI by Tim Bunce <dbi-users-subscribe@perl.org>

---

🌈 COPYRIGHT 🌈

the Apache::AuthDBI module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. slay.

---

okay bestie that's the whole thing 💖 you just read the perl v5.36.0 version, dated 2022-12-11 — go forth and auth responsibly, mwah 🎀✨