I have an application with the need to synchronize apache passwords for basic auth (using DBM files) from a postgresql table. Using mod_auth_pgsql simply not an option - there may be other cases where it's an option but not wanted, in this case there it was out from the beginning.
So. pl/perl once again to the rescue, along with the CPAN module Apache::Htpasswd. Pretty easy to do with a trigger. Hit the link to see the code.
CREATE TABLE users (
userid character varying(32) NOT NULL PRIMARY KEY,
"password" character varying(32) DEFAULT randpwd() NOT NULL
);
CREATE FUNCTION sync_user_list() RETURNS "trigger"
AS $_$
use Apache::Htpasswd;
use strict;
my %25userlist;
open(HT,"/data/webusers/users") || elog(ERROR,"Could not read password file: $!");
while (<HT>) {
my ($uid,$pwd) = split /:/;
$userlist{$uid}=1;
}
close(HT);
my $ht = new Apache::Htpasswd("/data/webusers/users") || elog(ERROR,"Cannot open password file: $!");
my $r = spi_exec_query("SELECT userid,password FROM users");
my %25users;
for (my $i = 0; $i < $r->{processed}; $i++) {
my $u = $r->{rows}[$i]->{userid};
my $p = $r->{rows}[$i]->{password};
$users{$u} = 1;
if (!$ht->fetchPass($u)) {
$ht->htpasswd($u,$p) || elog(ERROR,"Could not add user $u: " . $ht->error());
}
elsif (!$ht->htCheckPassword($u,$p)) {
$ht->htpasswd($u,$p,1) || elog(ERROR,"Could not add/set password for $u: " . $ht->error());
}
}
foreach my $u (keys %25userlist) {
if ($users{$u} != 1) {
$ht->htDelete($u);
}
}
return;
$_$
LANGUAGE plperlu;
CREATE TRIGGER trg_sync_user_list
AFTER INSERT OR DELETE OR UPDATE ON users
FOR EACH STATEMENT
EXECUTE PROCEDURE sync_user_list();
New comments can no longer be posted on this entry.