[gentoo-commits] gentoo-projects commit in forums/scripts: retire-dev-forums.pl

[Tom Knight (tomk)]

tomk        13/02/24 13:07:47

  Added:                retire-dev-forums.pl
  Log:
  Script to retire devs on the forums

Revision  Changes    Path
1.1                  forums/scripts/retire-dev-forums.pl

file : http://sources.gentoo.org/viewvc.cgi/gentoo-projects/forums/scripts/retire-dev-forums.pl?rev=1.1&view=markup
plain: http://sources.gentoo.org/viewvc.cgi/gentoo-projects/forums/scripts/retire-dev-forums.pl?rev=1.1&content-type=text/plain

Index: retire-dev-forums.pl
===================================================================
#!/usr/bin/perl

# -----------------------------------------------------------------------------
#
# retire-dev-forums.pl
#
# date        : 2013-02-18
# copyright   : Tom Knight <tomk@g.o>
# version     : 0.1
# license     : GPL2
# description : This script retires developers from the forums.
# return code : 0 on success, 1 on error, 2 on success but with action required by forums staff.
#
# -----------------------------------------------------------------------------
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation; either version 2 of the License, or (at your option) any later
# version.
#
# -----------------------------------------------------------------------------

use warnings;
use strict;
use DBI;
use File::Basename;
use Net::LDAP;
use Net::LDAP::Util qw(ldap_error_text);

my $DEBUG = 0;
#my $DEBUG = 1;
my $PRETEND = 0;
#my $PRETEND = 1;

my $LDAP_SERVER = "ldap://ldap1.gentoo.org";
my $LDAP_BASEDN = "dc=gentoo, dc=org";

my $RANK_ADMIN = 1;
my $RANK_MOD = 3;
my $RANK_DEV = 9;
my $RANK_BODHISATTVA = 14;
my $RANK_RETIRED_DEV = 15;

my $LEVEL_ADMIN = 1;
my $LEVEL_MOD = 2;
my $LEVEL_USER = 0;

my $GROUP_DEVS = 24;
my $GROUP_GLOBAL_MODS = 916;
my $GROUP_MODS = 970;
my $GROUP_BODHISATTVA = 10770;

my $EXIT_SUCCESS = 0;
my $EXIT_ERROR = 1;
my $EXIT_NOTIFY = 2;

#my $connect_string = "DBI:mysql:;mysql_read_default_file=~/.my.cnf;mysql_read_default_group=testforum_rw";
my $connect_string = "DBI:mysql:;mysql_read_default_file=~/.my.cnf;mysql_read_default_group=forum_rw";

my $username;

my $user_id;

my $mail;

my $sql;

my $user_ref;
my $users_ref;

my $new_rank;
my $new_email;
my $new_level;

my $make_includes = 0;

my $dbh;

my @rows;
my $count;

my $ldap;
my $msg;
my $filter;
my @attrs;

if (@ARGV != 1) {
    quit($dbh, $EXIT_ERROR, "usage: " . basename($0) . " username\n");
}

$username = $ARGV[0];

# get forumsUID, mail from LDAP based on passed in username

$ldap = Net::LDAP->new($LDAP_SERVER, version => 3, onerror => "die") || die "Connection Failed";

$msg  = $ldap->start_tls(verify => 'required',
                        clientcert => '/etc/openldap/ssl/star.gentoo.org.crt',
                        clientkey  => '/etc/openldap/ssl/star.gentoo.org.key',
                        cafile     => '/etc/openldap/ssl/ca.pem');

if ($msg->is_error) {
    die ldap_error_text($msg->code);
}

$msg = $ldap->bind;

if ($msg->is_error) {
    die ldap_error_text($msg->code);
}

$filter = "(uid=$username)";

$msg = $ldap->search(filter => $filter, base => $LDAP_BASEDN, attrs => ['forumsUID', 'mail']);

if ($msg->is_error) {
    die ldap_error_text($msg->code);
}

foreach my $entry ($msg->entries) {
    @attrs = $entry->attributes;

    foreach my $attr (@attrs) {
        foreach my $val (@{$entry->get_value($attr, asref => 1)}) {
            if ($attr eq "forumsUID") {
                $user_id = $val;
                last;
            } elsif ($attr eq "mail" && $val !~ /\@gentoo\.org$/) {
                $mail = $val;
                last;
            }
        }
    }
}

# if forumsUID is not set, exit
if (!defined $user_id) {
    quit($dbh, $EXIT_SUCCESS, "forumsUID not set in LDAP for $username - nothing left to do\n");
}

# connect to DB and begin transaction
$dbh = DBI->connect($connect_string, '', '', { RaiseError => 1, AutoCommit => 0 } ) || die("Could not connect to database!");

$sql = "SELECT user_id, username, user_level, user_rank, user_email FROM phpbb_users WHERE user_id = $user_id;";

$users_ref = execute_sql_select_hashref($dbh, $sql, "user_id");

# check we have a user

if (%{$users_ref}) {
    $user_ref = $users_ref->{$user_id};
} else {
    quit($dbh, $EXIT_ERROR, "forums user not found for user_id $user_id\n");
}

# username might be different on the forums

$username = get_username($username, $user_ref->{'username'});

# user rank

if ($user_ref->{'user_rank'} == $RANK_ADMIN || $user_ref->{'user_rank'} == $RANK_MOD) {
    $new_rank = $RANK_BODHISATTVA;
} elsif ($user_ref->{'user_rank'} == $RANK_DEV) {
    $new_rank = $RANK_RETIRED_DEV;
} else {
    # they already have the correct rank
    $new_rank = $user_ref->{'user_rank'};
}

# user level

if ($user_ref->{'user_level'} == $LEVEL_ADMIN || $user_ref->{'user_level'} == $LEVEL_MOD) {
    $new_level = $LEVEL_USER;
    $make_includes = 1;
} else {
    # they already have the correct level
    $new_level = $user_ref->{'user_level'};
}

# user e-mail
$new_email = $user_ref->{'user_email'};

if ($user_ref->{'user_email'} =~ '@gentoo\.org$' && defined $mail) {
    # set to alternative mail from LDAP if we have it

    $new_email = $mail;
}

# user groups

$sql = "SELECT COUNT(*) FROM phpbb_groups WHERE group_moderator = $user_id;";
@rows = execute_sql_select($dbh, $sql);
$count = $rows[0];

if ($count > 0) {
    # poke f-mods
    quit($dbh, $EXIT_NOTIFY, "$username is a group moderator for $count groups\n$username cannot be automatically retired, notify #gentoo-forums on freenode or forum-mods\@gentoo.org\n");
}

print "deleting $username from groups\n";

$sql = "DELETE FROM phpbb_user_group WHERE group_id = $GROUP_DEVS AND user_id = $user_id;";
$count = execute_sql($dbh, $sql);

if ($DEBUG) {
    if ($count > 0) {
        print "deleted $username from Developers group\n";
    } else {
        print "$username not found in Developers group\n";
    }
}

$sql = "DELETE FROM phpbb_user_group WHERE group_id = $GROUP_GLOBAL_MODS AND user_id = $user_id;";
$count = execute_sql($dbh, $sql);

if ($DEBUG) {
    if ($count > 0) {
        print "deleted $username from Global Moderators group\n";
    } else {
        print "$username not found in Global Moderators group\n";
    }
}

$sql = "DELETE FROM phpbb_user_group WHERE group_id = $GROUP_MODS AND user_id = $user_id;";
$count = execute_sql($dbh, $sql);

if ($DEBUG) {
    if ($count > 0) {
        print "deleted $username from Moderators group\n";
    } else {
        print "$username not found in Moderators group\n";
    }
}

if ($make_includes) {
    # user was a moderator/admin so add to Bodhisattva group
    $sql = "SELECT COUNT(*) FROM phpbb_user_group WHERE group_id = $GROUP_BODHISATTVA AND user_id = $user_id;";
    @rows = execute_sql_select($dbh, $sql);
    $count = $rows[0];

    if ($count == 0) {
        $sql = "INSERT INTO phpbb_user_group SET group_id = $GROUP_BODHISATTVA, user_id = $user_id, user_pending = 0;";
        execute_sql($dbh, $sql);
    }
}

# permissions

# If the user has been given individual permissions, this will be through their personal group

$sql = "SELECT group_id FROM phpbb_auth_access aa
        LEFT JOIN phpbb_groups g USING (group_id)
        LEFT JOIN phpbb_user_group ug USING (group_id)
        WHERE ug.user_id = $user_id
        AND g.group_single_user = 1;";

# if there are any then delete them from phpbb_auth_access
my @groups = execute_sql_select($dbh, $sql);

if (@groups) {
    print "deleting forum permissions for $username\n";

    foreach my $group (@groups) {
        $sql = "DELETE FROM phpbb_auth_access WHERE group_id = $group;";

        $count = execute_sql($dbh, $sql);

        if ($DEBUG) {
            if ($count > 0) {
                print "deleting $username permissions for group $group\n";
            } else {
                print "group $group not found in permissions table\n";
            }
        }
    }
}

print "updating forums status for $username\n";

# update user

$sql = "UPDATE phpbb_users SET user_rank = $new_rank, user_level = $new_level, user_email = '$new_email' WHERE user_id = $user_id;";

execute_sql($dbh, $sql);

# make includes
if ($make_includes) {
    # poke f-mods
    quit($dbh, $EXIT_NOTIFY, "$username was a forums moderator - so the forums 'make includes' function needs to be run\nnotify #gentoo-forums on freenode or forum-mods\@gentoo.org\n");
}

quit($dbh, $EXIT_SUCCESS, "done");

sub execute_sql {
    my $dbh = shift;
    my $sql = shift;

    my $rows = -1;
    my $sth;

    if ($DEBUG) {
        print "" . (caller(0))[3] . ": $sql\n";
    }

    if (!$PRETEND) {

        $sth = $dbh->prepare("$sql");
        $rows = $sth->execute();
    }
   
    return $rows;
}

sub execute_sql_select {
    my $dbh = shift;
    my $sql = shift;

    my @read_data = ();
    my @return_values = ();
    my $sth;

    if ($DEBUG) {
        print "" . (caller(0))[3] . ": $sql\n";
    }

    $sth = $dbh->prepare("$sql");
    $sth->execute();
    while (@read_data = $sth->fetchrow_array()) {
       push (@return_values, @read_data);
    }

    return @return_values;
}

sub execute_sql_select_hashref {
    my $dbh = shift;
    my $sql = shift;
    my $key_field = shift;

    my $return_values_ref;

    if ($DEBUG) {
        print "" . (caller(0))[3] . ": $sql\n";
    }

    $return_values_ref = $dbh->selectall_hashref($sql, $key_field);

    return $return_values_ref;
}

sub get_username {
    my $gentoo_username = shift;
    my $forums_username = shift;

    if ($gentoo_username ne $forums_username) {
        return $gentoo_username . " (forums username $forums_username)";
    }

    return $gentoo_username;
}

sub quit {
    my $dbh = shift;
    my $return_code = shift;
    my $message = shift;

    # commit or rollback
    if (defined $dbh) {
        if ($return_code == $EXIT_SUCCESS || $return_code == $EXIT_NOTIFY) {
            if ($DEBUG) {
                print "committing tranaction\n";
            }
            $dbh->commit();
        } elsif ($return_code == $EXIT_ERROR) {
            if ($DEBUG) {
                print "rolling back tranaction\n";
            }
            $dbh->rollback();
        }

        $dbh->disconnect();
    }

    if (defined $message) {
        print {$return_code ? *STDOUT : *STDERR} $message;
    }

    exit $return_code;
}