Synchronizing Apache passwords

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();

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

PGDay.UK
Jul 4, 2017
London, UK
PGDay.RU
Jul 5-7, 2017
St Petersburg, Russia
PGConf.EU 2017
Oct 24-27, 2017
Warsaw, Poland

Past

PGCon 2017
May 23-26, 2017
Ottawa, Canada
FOSS-North
Apr 26, 2017
Gothenburg, Sweden
pgDay.paris 2017
Mar 23, 2017
Paris, France
Nordic PGDay 2017
Mar 21, 2017
Stockholm, Sweden
Confoo Montreal 2017
Mar 8-10, 2017
Montreal, Canada
More past conferences