Sync LDAP users with MySQL

waelaltaqi · 09-05-2008, 09:48 AM

I'm trying to get the below script to work. It is supposed to sync ldap users to MySQL users with passwords and attributes. I followed instructions inhttp://mailwatch.sourceforge.net/dok...pcryptauthsync and here is the script:

Code:

#!/usr/bin/perl
 
#
# Gets mail users from an ldapsearch and creates the user in the mailscanner
# mysql database if they not yet exist.  Updates the full name and password
# values for all existing users when run as well.  The script simply drops
# and re-adds the users-filters entries for all users.
#
# - Leah Cunningham <leah@heinous.org>
#
# INCLUDES
#
use DBI;
use MIME::Base64::Perl;
use Data::Dumper;
use Net::LDAP;
#
# CONFIGURATION VARIABLES
#
ReadConfig("ldap_conf.pl");
my $LDAPPASS = command_output("cat /etc/ldap.secret");
my $ADM_GROUP = "useradmins";
my $SQL_DB_HOST = "localhost";
my $SQL_DB_PORT = "3306";
my $SQL_DB_USER = "root";
my $SQL_DB_PASS = "secret";
my $SQL_DB_NAME = "mailscanner";
#
# GLOBAL VARIABLES
#
my @ADMINS;
my @USERS_QUEUE;
my @USERS_UPDATE_QUEUE;
my @USER_FILTERS_QUEUE;
my $DBH = DBI->connect( "dbi:mysql:database=$SQL_DB_NAME;host=$SQL_DB_HOST;port=$SQL_DB_PORT",
        $SQL_DB_USER,$SQL_DB_PASS, {
                RaiseError => 1,
                AutoCommit => 0,
        }
) || die "Database connection not made: $DBI::errstr";
my $LDAP = LDAP_Bind($Config::ldap_server,$Config::ldap_admin,$LDAPPASS);
 
&main;
$DBH->disconnect();
 
sub main {
        #&drop_users;
        &drop_user_filters;
        &fetch_admins;
        my @users = command_output_array(
                "ldapsearch -x -D $Config::ldap_admin -w$LDAPPASS uid=*@*.* mail | awk '/uid/ {print \$2}' | awk -F, '{print \$1}' | awk -Fuid= '{print \$2}'"
        );
        foreach my $user (@users) {
                next if ($user !~ m/\S@\S/) or ($user =~ m/.*@.*@/);
                my ($username,$fqdomain) = split(/@/, $user);
                my ($domain,$ext) = split(/\./, $fdomain);
                #
                # Getting the user settings from LDAP
                #
                my $filter = "(uid=$username\@$fqdomain)";
                my $mesg = $LDAP->search(filter=>$filter,
                        base=>$Config::usersdn,
                        attrs=> ["uid","userPassword","sn","givenName","mail"] );
                my @entries = $mesg->entries;
                my $sn = $entries[0]->get_value("sn");
                my $givenName = $entries[0]->get_value("givenName");
                my $password = $entries[0]->get_value("userPassword");
                my @mail = $entries[0]->get_value("mail");
                my $fullname = $givenName . ' ' . $sn;
                # print "username: $username, fdomain: $fdomain, domain: $domain, ext: $ext\n";
                if (! check_exists($username . '@' . $fqdomain)) {
                        #print "user: $username\@$fqdomain not yet in database ... queuing for addition\n";
                        #
                        # Now we need to queue up the user for addition
                        #
                        queue_user($username . '@' . $fqdomain,$password,$fullname,@mail);
                        #print "username: $username, password: $password\n";
                } else {
                        ### MAYBE SOME UPDATE CODE HERE ###
                        queue_user_update($username . '@' . $fqdomain,$password,$fullname,@mail);
                        #print "user: $username\@$fqdomain is in the database ... skipping\n";
                }
        }
        if (@USERS_QUEUE) {
                &create_users;
        } else { print "No users to add $USERS_QUEUE\n"; print Dumper(@USERS_QUEUE); }
        if (@USERS_UPDATE_QUEUE) {
                &update_users;
        } else { print "No users to update $USERS_UPDATE_QUEUE\n"; print Dumper(@USERS_QUEUE); }
        if (@USER_FILTERS_QUEUE) {
                &create_user_filters;
        } else { print "No user filter entries to add @USER_FILTERS_QUEUE\n"; }
}
sub drop_users {
        my $drop = "DROP TABLE `users`";
        my $create = "CREATE TABLE `users` (   `username` varchar(60) NOT NULL default '',   `password` varchar(32) default NULL,   `fullname` varchar(50) NOT NULL default '',   `type` enum('A','D','U','R','H') default NULL,   `quarantine_report` tinyint(1) default '0',   `spamscore` tinyint(4) default '0',   `highspamscore` tinyint(4) default '0',   `noscan` tinyint(1) default '0',   `quarantine_rcpt` varchar(60) default NULL,   PRIMARY KEY  (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
        $DBH->do($drop);
        $DBH->do($create);
}
sub update_users {
 
        foreach my $user (@USERS_UPDATE_QUEUE) {
                #[$user,$pass,$fullname]
                my $username = $DBH->quote($user->[0]);
                my $password = $DBH->quote($user->[1]);
                my $fullname = $DBH->quote($user->[2]);
                #UPDATE `users` SET `password` = '{CRYPT}IssbC.vCx0TzI', `fullname` = 'Leah R. M. Cunningham' WHERE CONVERT(`username` USING utf8) = 'leahc@iplink.net' LIMIT 1;
                my $update = "UPDATE `users` SET `password` = $password, `fullname` = $fullname WHERE CONVERT(`username` USING utf8) = $username LIMIT 1;";
                $DBH->do($update);
        }
}
sub create_users {
        my $sql = qq{ INSERT INTO users VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) };
        my $sth = $DBH->prepare($sql);
        for(@USERS_QUEUE) {
                eval {
                        $sth->bind_param( 1, @$_->[0]);
                        $sth->bind_param( 2, @$_->[1]);
                        $sth->bind_param( 3, @$_->[2]);
                        $sth->bind_param( 4, @$_->[3]);
                        $sth->bind_param( 5, @$_->[4]);
                        $sth->bind_param( 6, @$_->[5]);
                        $sth->bind_param( 7, @$_->[6]);
                        $sth->bind_param( 8, @$_->[7]);
                        $sth->bind_param( 9, @$_->[8]);
                };
                $sth->execute();
                $DBH->commit();
                if( $@ ) {
                        warn "Database error: $DBI::errstr\n";
                        $dbh->rollback(); #just die if rollback is failing
                }
        }
}
sub drop_user_filters {
        my $drop = "DROP TABLE `user_filters`";
        my $create = "CREATE TABLE `user_filters` (   `username` varchar(60) NOT NULL default '',   `filter` text,   `verify_key` varchar(32) NOT NULL default '',   `active` enum('N','Y') default 'N',   KEY `user_filters_username_idx` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
        $DBH->do($drop);
        $DBH->do($create);
}
sub create_user_filters {
        my $sql = qq{ INSERT INTO user_filters VALUES ( ?, ?, ?, ? ) };
        my $sth = $DBH->prepare($sql);
        for(@USER_FILTERS_QUEUE) {
                eval {
                        $sth->bind_param( 1, @$_->[0]);
                        $sth->bind_param( 2, @$_->[1]);
                        $sth->bind_param( 3, @$_->[2]);
                        $sth->bind_param( 4, @$_->[3]);
                };
                $sth->execute();
                $DBH->commit();
                if( $@ ) {
                        warn "Database error: $DBI::errstr\n";
                        $dbh->rollback(); #just die if rollback is failing
                }
        }
}
sub queue_user {
        my ($user,$pass,$fullname,@mail) = @_;
        chomp($user,$pass,$fullname,@mail);
        my @users_entry;
        my $is_admin = 'no';
        foreach my $admin (@ADMINS) {
                chomp( $admin );
                if ($admin =~ m/$user/) {
                        @users_entry = [$user,$pass,$fullname,'A','0','0','0','0','0'];
                        $is_admin = 'yes';
                        print "$admin equals $user\n";
                }
        }
        if ($is_admin =~ m/^no$/ ) {
                @users_entry = [$user,$pass,$fullname,'U','0','0','0','0','0'];
        }
        #print Dumper(@users_entry);
        my $rand = rand();
        foreach $mail (@mail) {
                my @user_filters_entry = [$user,$mail,$rand,'Y'];
                push(@USER_FILTERS_QUEUE,@user_filters_entry);
        }
        #print Dumper(@user_filters_entry);
        push(@USERS_QUEUE,@users_entry);
        #print Dumper(@USERS_QUEUE);
}
sub queue_user_update {
        my ($user,$pass,$fullname,@mail) = @_;
        chomp($user,$pass,$fullname,@mail);
        my $users_entry = [$user,$pass,$fullname];
        my $rand = rand();
        foreach $mail (@mail) {
                my @user_filters_entry = [$user,$mail,$rand,'Y'];
                push(@USER_FILTERS_QUEUE,@user_filters_entry);
        }
        #print Dumper(@user_filters_entry);
        push(@USERS_UPDATE_QUEUE,$users_entry);
        #print Dumper(@USERS_QUEUE);
}
sub check_exists {
        my ($user) = @_;
        chomp($user);
        my $sql = qq{ SELECT * FROM users where USERNAME LIKE ?};
        my $sth = $DBH->prepare($sql);
        $sth->bind_param(1, $user);
        $sth->execute;
        while ( my @rows = $sth->fetchrow ) {
                if ($rows[0]) {
                        return 1;
                } else {
                        return 0;
                }
        }
}
sub run_command {
        my (@command) = @_;
        system("@command 2>/dev/null") == 0
                or return "system command: \"@command\"\n\tfailed: $?";
}
sub command_output_array {
        my (@command) = @_;
        open(HANDLE, "@command|");
        my @output = <HANDLE>;
        close(HANDLE);
        my $count = 0;
        my $n_entries = $#output;
        for ( $count = $count; $count <= $n_entries; $count++ ) {
                chomp($output[$count]);
        }
        return @output;
}
sub command_output {
        my (@command) = @_;
        open(HANDLE, "@command|");
        my $output = <HANDLE>;
        close(HANDLE);
        chomp $output;
        return $output;
}
sub ReadConfig {
        my ($readfile) = @_;
        if ((! -r "$ENV{PWD}/$readfile") and (! -r "/etc/$readfile")) {
                die "        Error reading " . $readfile . " from working directory or /etc\n";
                } elsif (-r "$ENV{PWD}/$readfile") {
                        $readfile="$ENV{PWD}/$readfile";
                        package Config;
                        do $readfile;
                } else {
                        $readfile="/etc/$readfile";
                        package Config;
                        do $readfile;
        }
}
#
# Returns a connection to the ldap server
#
sub LDAP_Bind {
        my ($ldap_server,$ldap_admin,$ldap_pass) = @_;
        my $ldap = Net::LDAP->new($ldap_server) or die "Unable to connect to $ldap_server: $@\n";
        $ldap->bind     ("$ldap_admin",
                         password => "$ldap_pass") or die "Unable to bind: $@\n";
        return $ldap;
}
sub fetch_admins {
        #
        # Getting the user settings from LDAP
        #
        my $filter = "(cn=$ADM_GROUP)";
        my $mesg = $LDAP->search(filter=>$filter,
                base=>$Config::groupdn,
                attrs=> ["memberUid"] );
        my @entries = $mesg->entries;
        @ADMINS = $entries[0]->get_value("memberUid");
}

When i run the script it comes back with the following error:

Code:

[root@server]# ./ldapsync
#Can't call method "get_value" on an undefined value at ./ldapsync line #272.

Line 272 is the last line in the code
what does this error mean? Am I missing a module?
Any help would be appreciated.

acid_kewpie · 09-05-2008, 10:45 AM

Please post your thread in only one forum. Posting a single thread in the most relevant forum will make it easier for members to help you and will keep the discussion in one place. This thread is being closed because it is a duplicate.