Zarafa DB to LDAP user plugin conversion

From Zarafa wiki

(Difference between revisions)
Jump to: navigation, search
Line 12: Line 12:
<pre>
<pre>
#!/usr/bin/perl -w
#!/usr/bin/perl -w
-
 
use strict;
use strict;
-
use MIME::Base64;
 
use DBI;
use DBI;
 +
use Net::LDAP;
-
if(@ARGV != 7) {
+
sub readconfig($) {
-
        print "Usage: $0 <mysqluser> <mysqlpass> <database> <ldaphost> <ldapbinduser> <ldapbindpass> <ldap_base_dn>\n";
+
my ($fn) = @_;
-
        exit(1);
+
my %options;
 +
 
 +
open(CFG, $fn) or die("unable to open ".$fn." config file");
 +
while (<CFG>) {
 +
if ($_ =~ /^\s*[#!]/) {
 +
next;
 +
}
 +
if ($_ =~ /^\s*(\S+)\s*=\s*([^\r]+)\r?$/) {
 +
my $idx = $1;
 +
my $val = $2;
 +
chomp($val);
 +
$val =~ s/\s+$//;
 +
$options{$idx} = $val;
 +
}
 +
}
 +
close(CFG);
 +
return %options;
}
}
-
my ($dbuser, $dbpass, $db, $ldaphost, $ldapuser, $ldappass, $ldapbase) = @ARGV;
+
# @todo, differentiate between users/groups/etc.
 +
sub get_extern_id($$$) {
 +
my ($lo, $ldap, $db_username) = @_;
 +
my $mesg = $ldap->search(filter => "($lo->{ldap_loginname_attribute}=$db_username)",
 +
base  => "$lo->{ldap_search_base}",
 +
attrs  => ["$lo->{ldap_loginname_attribute}", "$lo->{ldap_user_unique_attribute}"],
 +
scope  => "sub");
-
my $dbh = DBI->connect("DBI:mysql:database=$db;host=localhost",
+
my @entries = $mesg->entries;
-
                                $dbuser, $dbpass,
+
# if $mesg->count != 1 fallback on emailaddress lookup?
-
                                {'RaiseError' => 1});
+
if ( $mesg->count == 0 ) {
-
 
+
print "Error updating $db_username. No entry found in ldap for ($lo->{ldap_loginname_attribute} = $db_username)\n";
-
open INPUT, "ldapsearch -x -H ldap://$ldaphost -b \"$ldapbase\" -D \"$ldapuser\" -w \"$ldappass\"|";
+
return ($mesg->count, "");
-
 
+
}
-
my %names;
+
if ( $mesg->count > 1 ) {
-
my $objectsid;
+
print "Error updating $db_username. Multiple entries found in ldap for ($lo->{ldap_loginname_attribute} = $db_username)\n";
-
 
+
return ($mesg->count, "");
-
while(<INPUT>) {
+
}
-
        if(/sAMAccountName: (\S+)/) {
+
-
#              print "name: $1\n";
+
return ($mesg->count, $entries[0]->get_value($lo->{ldap_user_unique_attribute}));
-
                $names{$1}->{"objectsid"} = $objectsid;
+
-
        }
+
-
        if(/objectSid:: (\S+)/) {
+
-
#              print "objectSid: " . $dbh->quote(decode_base64($1)) . "\n";
+
-
                $objectsid = decode_base64($1);
+
-
        }
+
}
}
-
my $name;
 
-
foreach $name (keys %names) {
 
-
        my $q = $dbh->prepare("SELECT objectid FROM objectproperty WHERE propname='loginname' AND value='$name'");
 
-
        $q->execute;
 
-
        my $rows = $q->rows;
+
my $servercfg = $ARGV[0];
 +
$servercfg = "/etc/zarafa/server.cfg" if (!defined($servercfg));
-
        if($rows == 1) {
+
my %serveropt = readconfig($servercfg);
-
                # Found a user with username in the database, so now we know the user id
+
if (!defined($serveropt{user_plugin})) {
-
                $names{$name}->{"userid"} = ($q->fetchrow_array())[0];
+
print "First argument must be the server configuration file\n";
-
        }
+
exit(0);
 +
}
 +
if ($serveropt{user_plugin} ne "ldap") {
 +
print "You only can change the unique attribute for the ldap user plugin, found: '".$serveropt{user_plugin}."'\n";
 +
exit(0);
 +
}
 +
my %ldapopt = readconfig($serveropt{user_plugin_config});
-
my ($dbuser, $dbpass, $db, $ldaphost, $ldapuser, $ldappass, $ldapbase) = @ARGV;
+
my $ldapuri = $ldapopt{ldap_protocol}."://".$ldapopt{ldap_host}.":".$ldapopt{ldap_port};
 +
my $ldap = Net::LDAP->new($ldapuri) or die ("LDAP connection failed");
 +
my $msg = $ldap->bind($ldapopt{ldap_bind_user}, password => $ldapopt{ldap_bind_passwd});
 +
$msg->code && die $msg->error;
-
my $dbh = DBI->connect("DBI:mysql:database=$db;host=localhost",
+
my ($dbh, $query, $sth, $upd, $udh);
-
                                $dbuser, $dbpass,
+
my @row;
-
                                {'RaiseError' => 1});
+
-
open INPUT, "ldapsearch -x -H ldap://$ldaphost -b \"$ldapbase\" -D \"$ldapuser\" -w \"$ldappass\"|";
+
$dbh = DBI->connect("dbi:mysql:database=".$serveropt{mysql_database}.";host=".$serveropt{mysql_host}, $serveropt{mysql_user}, $serveropt{mysql_password})
 +
or die $DBI::errstr;
-
my %names;
+
$query = "SELECT u.id AS user_id, op.value AS user_name, o.id FROM users AS u JOIN object AS o ON u.externid=o.externid JOIN objectproperty AS op ON o.id=op.objectid AND op.propname='loginname'";
-
my $objectsid;
+
$sth = $dbh->prepare($query) or die $DBI::errstr;
 +
$sth->execute();
-
while(<INPUT>) {
+
$dbh->begin_work();
-
        if(/sAMAccountName: (\S+)/) {
+
$dbh->{AutoCommit} = 0;
-
#              print "name: $1\n";
+
-
                $names{$1}->{"objectsid"} = $objectsid;
+
-
        }
+
-
        if(/objectSid:: (\S+)/) {
+
-
#              print "objectSid: " . $dbh->quote(decode_base64($1)) . "\n";
+
-
                $objectsid = decode_base64($1);
+
-
        }
+
-
}
+
-
my $name;
+
while (@row = $sth->fetchrow_array) {
 +
my $db_user_id = $row[0];
 +
my $db_user_name = $row[1];
 +
my $db_object_id = $row[2];
 +
print "Found user '$db_user_name' in database with user_id '$db_user_id'\n";
-
foreach $name (keys %names) {
+
my ($retval, $extern_id) = get_extern_id(\%ldapopt, $ldap, $db_user_name);
-
        my $q = $dbh->prepare("SELECT objectid FROM objectproperty WHERE propname='loginname' AND value='$name'");
+
#print "$retval - $extern_id\n";
-
        $q->execute;
+
#print "$db_user_id - $db_user_name\n";
-
 
+
if ($retval == 1) {
-
        my $rows = $q->rows;
+
$query = "UPDATE users SET externid=? WHERE id=?";
-
 
+
$upd = $dbh->prepare($query)
-
        if($rows == 1) {
+
or die $DBI::errstr;
-
                # Found a user with username in the database, so now we know the user id
+
$upd->execute($extern_id, $db_user_id);
-
                $names{$name}->{"userid"} = ($q->fetchrow_array())[0];
+
$query = "UPDATE object SET externid=? WHERE id=?";
-
        }
+
$upd = $dbh->prepare($query)
 +
or die $DBI::errstr;
 +
$upd->execute($extern_id, $db_object_id);
 +
}
}
}
-
foreach $name (keys %names) {
+
print "Committing changes to database\n";
-
        print "# $name\n";
+
$dbh->commit();
-
        print "UPDATE users SET externid=" . $dbh->quote($names{$name}->{"objectsid"}) . " WHERE id=" . $names{$name}->{"userid"} . ";\n";
+
$ldap->unbind;
-
 
+
print "Done.\n";
</pre>
</pre>

Revision as of 08:53, 16 August 2011

The following script allows you to convert the mailboxes from a local Zarafa DB plugin to an Active Directory environment.


Requirements:

  • Make sure all local Zarafa users are also available in the Active Directory
  • Install the perl module MIME::Base64 and DBI
  • Make sure you have a successful backup of the Zarafa database


Note: this is only possible with version before 6.30! 

#!/usr/bin/perl -w
use strict;
use DBI;
use Net::LDAP;

sub readconfig($) {
	my ($fn) = @_;
	my %options;

	open(CFG, $fn) or die("unable to open ".$fn." config file");
	while (<CFG>) {
		if ($_ =~ /^\s*[#!]/) {
			next;
		}
		if ($_ =~ /^\s*(\S+)\s*=\s*([^\r]+)\r?$/) {
			my $idx = $1;
			my $val = $2;
			chomp($val);
			$val =~ s/\s+$//;
			$options{$idx} = $val;
		}
	}
	close(CFG);
	return %options;
}

# @todo, differentiate between users/groups/etc.
sub get_extern_id($$$) {
	my ($lo, $ldap, $db_username) = @_;
	my $mesg = $ldap->search(filter => "($lo->{ldap_loginname_attribute}=$db_username)",
							 base   => "$lo->{ldap_search_base}",
							 attrs  => ["$lo->{ldap_loginname_attribute}", "$lo->{ldap_user_unique_attribute}"],
							 scope  => "sub");

	my @entries = $mesg->entries;
	# if $mesg->count != 1 fallback on emailaddress lookup?
	if ( $mesg->count == 0 ) {
		print "Error updating $db_username. No entry found in ldap for ($lo->{ldap_loginname_attribute} = $db_username)\n";
		return ($mesg->count, "");
	}
	if ( $mesg->count > 1 ) {
		print "Error updating $db_username. Multiple entries found in ldap for ($lo->{ldap_loginname_attribute} = $db_username)\n";
		return ($mesg->count, "");
	}
	
	return ($mesg->count, $entries[0]->get_value($lo->{ldap_user_unique_attribute}));
}



my $servercfg = $ARGV[0];
$servercfg = "/etc/zarafa/server.cfg" if (!defined($servercfg));

my %serveropt = readconfig($servercfg);
if (!defined($serveropt{user_plugin})) {
	print "First argument must be the server configuration file\n";
	exit(0);
}
if ($serveropt{user_plugin} ne "ldap") {
	print "You only can change the unique attribute for the ldap user plugin, found: '".$serveropt{user_plugin}."'\n";
	exit(0);
}
my %ldapopt = readconfig($serveropt{user_plugin_config});

my $ldapuri = $ldapopt{ldap_protocol}."://".$ldapopt{ldap_host}.":".$ldapopt{ldap_port};
my $ldap = Net::LDAP->new($ldapuri) or die ("LDAP connection failed");
my $msg = $ldap->bind($ldapopt{ldap_bind_user}, password => $ldapopt{ldap_bind_passwd});
$msg->code && die $msg->error;

my ($dbh, $query, $sth, $upd, $udh);
my @row;

$dbh = DBI->connect("dbi:mysql:database=".$serveropt{mysql_database}.";host=".$serveropt{mysql_host}, $serveropt{mysql_user}, $serveropt{mysql_password})
	or die $DBI::errstr;

$query = "SELECT u.id AS user_id, op.value AS user_name, o.id FROM users AS u JOIN object AS o ON u.externid=o.externid JOIN objectproperty AS op ON o.id=op.objectid AND op.propname='loginname'";
$sth = $dbh->prepare($query) or die $DBI::errstr;
$sth->execute();

$dbh->begin_work();
$dbh->{AutoCommit} = 0;

while (@row = $sth->fetchrow_array) {
	my $db_user_id = $row[0];
	my $db_user_name = $row[1];
	my $db_object_id = $row[2];
	print "Found user '$db_user_name' in database with user_id '$db_user_id'\n";

	my ($retval, $extern_id) = get_extern_id(\%ldapopt, $ldap, $db_user_name);
	#print "$retval - $extern_id\n";
	#print "$db_user_id - $db_user_name\n";
	if ($retval == 1) {
		$query = "UPDATE users SET externid=? WHERE id=?";
		$upd = $dbh->prepare($query)
			or die $DBI::errstr;
		$upd->execute($extern_id, $db_user_id);
		$query = "UPDATE object SET externid=? WHERE id=?";
		$upd = $dbh->prepare($query)
			or die $DBI::errstr;
		$upd->execute($extern_id, $db_object_id);
	}
}

print "Committing changes to database\n";
$dbh->commit();
$ldap->unbind;
print "Done.\n";
Retrieved from "http://www.zarafa.com/wiki/index.php/Zarafa_DB_to_LDAP_user_plugin_conversion"
Personal tools