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

Pycon Sweden
May 9-10, 2016
Stockholm, Sweden
PGCon
May 17-21, 2016
Ottawa, Canada
Stockholm PUG 2016/3
Jun 16, 2016
Stockholm, Sweden
PGDay UK 2016
Jul 05, 2016
London, UK
PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
Postgres Open
Sep 13-16, 2016
Dallas, USA
PGConf.EU 2016
Nov 1-Jan 4, 2016
Tallinn, Estonia

Past

Stockholm PUG 2016/2
Apr 26, 2016
Stockholm, Sweden
PGConf.US
Apr 18-20, 2016
New York, USA
pgDay Paris
Mar 31, 2016
Paris, France
Nordic PGDay
Mar 17, 2016
Helsinki, Finland
ConFoo
Feb 24-26, 2016
Montreal, Canada
More past conferences