CAcert Code Development list.

[Wytze van der Raay <wytze AT cacert.org>]

Hi Michael,

On 28.03.2012 22:15, Michael Tänzer wrote:
> We have a fix for https://bugs.cacert.org/view.php?id=1003:
> "Provide a possibility to regularly review the permissions in the system"
> 
> The fix was reviewed by Bernhard Fröhlich (Ted) and me (NEOatNHNG) and
> tested by Marcus Mängel (INOPIAE) and Jens K. (JensK).
> 
> Diff is attached.
> 
> For this patch we have also moved the cron scripts to a sub-directory so
> the scripts directory is less cluttered (well, the scripts directory is
> still cluttered but at least we can now quickly locate the cron scripts
> as well as the db migrations). So you need to adjust the
> /etc/cron.d/cacert crontab to reflect this move.
> 
> Changed files:
> /includes/lib/account.php
> 
> New files:
> /scripts/cron/permissionreview.php
> 
> Moved with some changes:
> /scripts/removedead.php to /scripts/cron/removedead.php
> /scripts/updatesort.php to /scripts/cron/updatesort.php
> /scripts/warning.php to /scripts/cron/warning.php

The fix has been installed on the production system on March 29, 2012.
See also the attached log message sent to cacert-systemlog.

Do you have any instructions for the time and frequency at which the
new permissionreview script should be run?

Regards.
-- wytze

--- Begin Message ---

• From: Wytze van der Raay <wytze AT cacert.org>
• To: cacert-systemlog AT lists.cacert.org
• Subject: Fwd: [cvs.cacert.org checkin notification]
• Date: Thu, 29 Mar 2012 16:27:13 +0200
• Organization: CAcert

Fix for https://bugs.cacert.org/view.php?id=1003:
Provide a possibility to regularly review the permissions in the system.

In conjunction with the attached CVS changes a new tarball has been
made available incorporating all updates. The new tarball is available
through http://www.cacert.org/src-lic.php

-- end

--- Begin Message ---

• From: "root" <root AT cvs.cacert.org>
• To: critical-admin AT cacert.org
• Subject: cvs.cacert.org checkin notification
• Date: Thu, 29 Mar 2012 16:13:25 +0200 (CEST)

uid=0(root) gid=0(root) groups=0(root)
- New directory NONE NONE
Thu Mar 29 16:13:25 CEST 2012
Update of /var/lib/cvs/cacert/scripts/cron
In directory hlin:/home/cacert/www/scripts/cron

Log Message:
Directory /var/lib/cvs/cacert/scripts/cron added to the repository

===================================================================
RCS file: co: /var/lib/cvs/cacert/scripts/cron/NONE,v: No such file or 
directory
rcsdiff: /var/lib/cvs/cacert/scripts/cron/-,v: No such file or directory

--- End Message ---

--- Begin Message ---

• From: "root" <root AT cvs.cacert.org>
• To: critical-admin AT cacert.org
• Subject: cvs.cacert.org checkin notification
• Date: Thu, 29 Mar 2012 16:23:58 +0200 (CEST)

uid=0(root) gid=0(root) groups=0(root)
removedead.php 1.10 NONE updatesort.php 1.6 NONE warning.php 1.6 NONE
Thu Mar 29 16:23:58 CEST 2012
Update of /var/lib/cvs/cacert/scripts
In directory hlin:/home/cacert/www/scripts

Removed Files:
        removedead.php updatesort.php warning.php 
Log Message:
Fix for https://bugs.cacert.org/view.php?id=1003:
Provide a possibility to regularly review the permissions in the system.

rcsdiff: /var/lib/cvs/cacert/scripts/updatesort.php,v: No such file or 
directory
rcsdiff: /var/lib/cvs/cacert/scripts/warning.php,v: No such file or directory
rcsdiff: /var/lib/cvs/cacert/scripts/removedead.php,v: No such file or 
directory

--- End Message ---

--- Begin Message ---

• From: "root" <root AT cvs.cacert.org>
• To: critical-admin AT cacert.org
• Subject: cvs.cacert.org checkin notification
• Date: Thu, 29 Mar 2012 16:24:05 +0200 (CEST)

uid=0(root) gid=0(root) groups=0(root)
permissionreview.php NONE 1.1 removedead.php NONE 1.1 updatesort.php NONE 1.1 
warning.php NONE 1.1
Thu Mar 29 16:24:05 CEST 2012
Update of /var/lib/cvs/cacert/scripts/cron
In directory hlin:/home/cacert/www/scripts/cron

Added Files:
        permissionreview.php removedead.php updatesort.php warning.php 
Log Message:
Fix for https://bugs.cacert.org/view.php?id=1003:
Provide a possibility to regularly review the permissions in the system.

===================================================================
RCS file: /var/lib/cvs/cacert/scripts/cron/removedead.php,v  -->  standard 
output
revision 1.1
#!/usr/bin/php -q
<? /*
    LibreSSL - CAcert web application
    Copyright (C) 2004-2008  CAcert Inc.

    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; version 2 of the License.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301 
 USA
*/

        require_once(dirname(__FILE__).'/../../includes/mysql.php');
        require_once(dirname(__FILE__).'/../../includes/lib/l10n.php');

        $query = "select * from `users` where `users`.`verified`=0 and
                        (UNIX_TIMESTAMP(NOW()) - 
UNIX_TIMESTAMP(`users`.`created`)) >= 172800";
        $res = mysql_query($query);
        while($row = mysql_fetch_assoc($res))
        {
                mysql_query("delete from `email` where 
`memid`='".$row['id']."'");
                mysql_query("delete from `users` where 
`id`='".$row['id']."'");
        }

        $query = "delete from `domains` where `hash`!='' and
                        (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`created`)) 
>= 172800";
        mysql_query($query);

        $query = "delete from `email` where `hash`!='' and
                        (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`created`)) 
>= 172800";
        mysql_query($query);

        $query = "delete from `disputedomain` where `hash`!='' and
                        (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`created`)) 
>= 21600";
        mysql_query($query);

        $query = "delete from `disputeemail` where `hash`!='' and
                        (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`created`)) 
>= 21600";
        mysql_query($query);

        $query = "select * from `notary` where `expire`!=0 and 
`expire`<NOW()";
        $res = mysql_query($query);
        while($row = mysql_fetch_assoc($res))
        {
                $query = "select sum(`points`) as `points` from `notary` 
where `to`='$row[to]' and `expire`=0 group by `to`";
                $dres = mysql_query($query);
                $drow = mysql_fetch_assoc($dres);
                if($drow['points'] >= 150)
                {
                        $query = "update `notary` set `expire`=0, 
`points`='0' where `to`='$row[to]' and `from`='$row[from]' and 
`expire`='$row[expire]'";
                } else {
                        $newpoints = 150 - $drow['points'];
                        $query = "update `notary` set `expire`=0, 
`points`='0' where `to`='$row[to]' and `from`='$row[from]' and 
`expire`='$row[expire]'";
                        mysql_query($query);
                        $query = "insert into `notary` set `expire`=0, 
`points`='$newpoints', `to`='$row[to]', `from`='$row[from]', `when`=NOW(), 
`method`='Administrative Increase', `date`=NOW()";
                }

                $data = mysql_fetch_assoc(mysql_query("select * from `users` 
where `id`='$row[to]'"));
                $body  = sprintf("%s %s (%s) had a temporary increase, but 
this has just expired and they have been reduced to 150 points.", 
$data['fname'], $data['lname'], $data['email'])."\n\n";
                
sendmail("cacert-board AT lists.cacert.org",
 "[CAcert.org] Temporary Increase Expired.", $body, 
"website AT cacert.org",
 "", "", "CAcert Website");

                if($data['language'] != "")
                {
                        L10n::set_translation($data['language']);
                }

                $body  = _("You are receiving this email because you had a 
temporary increase to 200 points. This has since expired and you have been 
reduced to 150 points.")."\n\n";
                $body  = _("If you needed more time or any other extenuating 
circumstances you should contact us immediately so this situation can be 
dealt with immediately.")."\n\n";

                $body .= _("Best regards")."\n";
                $body .= _("CAcert Support Team");

                sendmail($data['email'], "[CAcert.org] "._("Temporary points 
increase has expired."), $body, 
"support AT cacert.org",
 "", "", "CAcert Website");

                mysql_query($query);
                fix_assurer_flag($row[to]);
        }
?>
===================================================================
RCS file: /var/lib/cvs/cacert/scripts/cron/updatesort.php,v  -->  standard 
output
revision 1.1
#!/usr/bin/php -q
<? /*
    LibreSSL - CAcert web application
    Copyright (C) 2004-2008  CAcert Inc.

    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; version 2 of the License.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301 
 USA
*/
        require_once(dirname(__FILE__).'/../../includes/mysql.php');



        /* Set assurer flag for accounts who miss it

           See also includes/lib/account.php, function 
fix_assurer_flag($userID)

           We may have some performance problems here, there are 150k 
assurances and 220k users
           in the production database. The exists-clause on cats_passed 
should be a good filter... */
           
  /* Synchronisation of assurer flag currently deactivated, see 
https://bugs.cacert.org/view.php?id=1003
     and https://bugs.cacert.org/view.php?id=1024 */
/*
        $query = "select `n`.`to` as `uid` from `notary` as `n`, `users` as 
`u` ".
                 "  where `n`.`to`=`u`.`id` and `u`.`assurer`<>'1' ".
                 "    and (`n`.`expire` > now() OR `n`.`expire` IS NULL) ".
                 "    and exists(select 1 from `cats_passed` as `cp`, 
`cats_variant` as `cv` ".
                 "                 where `cp`.`variant_id`=`cv`.`id` and 
`cv`.`type_id` = 1 and `cp`.`user_id`=`n`.`to`)".
                 "  group by `n`.`to` having sum(`n`.`points`)>=100";

        $res = mysql_query($query);
        while($row = mysql_fetch_assoc($res))
        {
                $query = "update users set `assurer`='1' where 
`id`='${row['uid']}'";
                //echo $query."\n";
                mysql_query($query);
        }
*/
        /* Remove assurer flag from accounts not eligible.

           Also a bit performance critical, but assurer flag is only set at 
5k accounts

        */
  /* Synchronisation of assurer flag currently deactivated, see 
https://bugs.cacert.org/view.php?id=1003
     and https://bugs.cacert.org/view.php?id=1024 */
/*
    $query = "select `u`.id as `uid` from `users` as `u` " .
                 "  where `u`.`assurer` = '1' ".
                 "    and (not exists(select 1 from `cats_passed` as `cp`, 
`cats_variant` as `cv` ".
                 "                     where `cp`.`variant_id`=`cv`.`id` and 
`cv`.`type_id` = 1 and `cp`.`user_id`=`u`.`id`) ".
                 "         or (select sum(`n`.`points`) from `notary` as `n` 
where `n`.`to`=`u`.`id` and (`n`.`expire` > now() OR `n`.`expire` IS NULL)) < 
100) ";
        $res = mysql_query($query);
        while($row = mysql_fetch_assoc($res))
        {
                $query = "update users set `assurer`='0' where 
`id`='${row['uid']}'";
                //echo $query."\n";
                mysql_query($query);
        }
*/

        mysql_query("update `locations` set `acount`=0");
        $query = "SELECT `users`.`locid` AS `locid`, count(*) AS `total` FROM 
`users`
                        WHERE users.assurer='1' AND `users`.`locid` != 0 and 
users.listme=1
                        GROUP BY `users`.`locid`";
        $res = mysql_query($query);
        while($row = mysql_fetch_assoc($res))
        {
                $query = "update `locations` set `acount`='${row['total']}' 
where `id`='${row['locid']}'";
                echo $query."\n";
                mysql_query($query);
        }


        mysql_query("update `regions` set `acount`=0");
        $query = "SELECT `users`.`regid` AS `regid`, count(*) AS `total` FROM 
`users`
                        WHERE users.assurer='1' AND `users`.`regid` != 0 and 
users.listme=1
                        GROUP BY `users`.`regid`";
        $res = mysql_query($query);
        while($row = mysql_fetch_assoc($res))
        {
                $query = "update `regions` set `acount`='${row['total']}' 
where `id`='${row['regid']}'";
                echo $query."\n";
                mysql_query($query);
        }




        mysql_query("update `countries` set `acount`=0");
        $query = "SELECT `users`.`ccid` AS `ccid`, count(*) AS `total` FROM 
`users`
                        WHERE users.assurer='1' AND `users`.`ccid` != 0 and 
users.listme=1
                        GROUP BY `users`.`ccid`";
        $res = mysql_query($query);
        while($row = mysql_fetch_assoc($res))
        {
                $query = "update `countries` set `acount`='${row['total']}' 
where `id`='${row['ccid']}'";
                echo $query."\n";
                mysql_query($query);
        }


?>
===================================================================
RCS file: /var/lib/cvs/cacert/scripts/cron/warning.php,v  -->  standard output
revision 1.1
#!/usr/bin/php -q
<? /*
    LibreSSL - CAcert web application
    Copyright (C) 2004-2008  CAcert Inc.

    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; version 2 of the License.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301 
 USA
*/

        require_once(dirname(__FILE__).'/../../includes/mysql.php');

        $days = array("1" => "3", "15" => "2", "30" => "1", "45" => "0");

        foreach($days as $day => $warning)
        {
                $query = "SELECT 
`emailcerts`.`id`,`users`.`fname`,`users`.`lname`,`users`.`email`,`emailcerts`.`memid`,
                                `emailcerts`.`subject`, 
`emailcerts`.`crt_name`,`emailcerts`.`CN`,
                                (UNIX_TIMESTAMP(`emailcerts`.`expire`) - 
UNIX_TIMESTAMP(NOW())) / 86400 as `daysleft`
                                FROM `users`,`emailcerts`
                                WHERE UNIX_TIMESTAMP(`emailcerts`.`expire`) - 
UNIX_TIMESTAMP(NOW()) > -7 * 86400 and
                                UNIX_TIMESTAMP(`emailcerts`.`expire`) - 
UNIX_TIMESTAMP(NOW()) < $day * 86400 and
                                `emailcerts`.`renewed`=0 and 
`emailcerts`.`warning` <= '$warning' and
                                `emailcerts`.`revoked`=0 and 
`users`.`id`=`emailcerts`.`memid`";
                $res = mysql_query($query);
                while($row = mysql_fetch_assoc($res))
                {
                        if($row['subject'] == "")
                        {
                                $row['crt_name'] = str_replace("../", "www/", 
$row['crt_name']);
                                $row['crt_name'] = 
"/home/cacert/".$row['crt_name'];
                                $subject = `openssl x509 -in '$row[crt_name]' 
-text -noout|grep Subject:`;
                                $bits = explode("/", $subject);
                                foreach($bits as $val)
                                {
                                        $sub = explode("=", trim($val));
                                        if($sub['0'] == "emailAddress")
                                        {
                                                $row['subject'] = 
"/CN=".$row['CN']."/emailAddress=".$sub['1'];
                                                break;
                                        }
                                }
                        }
                        if($row['subject'] == "")
                                $row['subject'] = "/CN=".$row['CN'];
                        $row['daysleft'] = ceil($row['daysleft']);
                        $body = sprintf(_("Hi %s"), $row['fname']).",\n\n";
                        $body .= _("You are receiving this email as you are 
the listed contact for:")."\n\n";
                        $body .= $row['subject']."\n\n";
                        $body .= sprintf(_("Your certificate is set to expire 
in approximately %s days time, you can renew this by going to the following 
URL:"), $row['daysleft'])."\n\n";
                        $body .= 
"https://www.cacert.org/account.php?id=5\n\n";;
                        $body .= _("Best Regards")."\n"._("CAcert Support");
                        sendmail($row['email'], "[CAcert.org] "._("Your 
Certificate is about to expire"), $body, 
"support AT cacert.org",
 "", "", "CAcert Support");
echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: 
".$row['memid']." Subj: ".$row['subject']." timeleft: 
".$row['daysleft'].")\n";
                        $query = "update `emailcerts` set 
`warning`='".($warning+1)."' where `id`='".$row['id']."'";
                        mysql_query($query);
                }
        }

        foreach($days as $day => $warning)
        {
                $query = "SELECT `domaincerts`.`id`, `users`.`fname`, 
`users`.`lname`, `users`.`email`,
                                `domains`.`memid`, `domaincerts`.`subject`, 
`domaincerts`.`crt_name`,
                                `domaincerts`.`CN`,
                                (UNIX_TIMESTAMP(`domaincerts`.`expire`) - 
UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft`
                                FROM `users`, `domaincerts`, `domlink`, 
`domains`
                                WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) 
- UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND
                                UNIX_TIMESTAMP(`domaincerts`.`expire`) - 
UNIX_TIMESTAMP(NOW()) < $day * 86400 AND
                                `domaincerts`.`renewed`=0 AND 
`domaincerts`.`warning` <= '$warning' AND
                                `domaincerts`.`revoked`=0 AND `users`.`id` = 
`domains`.`memid` AND
                                `domlink`.`certid` = `domaincerts`.`id` AND 
`domains`.`id` = `domlink`.`domid`";
                $res = mysql_query($query);
                while($row = mysql_fetch_assoc($res))
                {
                        if($row['subject'] == "")
                                $row['subject'] = $row['CN'];

                        $row['daysleft'] = ceil($row['daysleft']);
                        $body = sprintf(_("Hi %s"), $row['fname']).",\n\n";
                        $body .= _("You are receiving this email as you are 
the listed contact for:")."\n\n";
                        $body .= $row['subject']."\n\n";
                        $body .= sprintf(_("Your certificate is set to expire 
in approximately %s days time, you can renew this by going to the following 
URL:"), $row['daysleft'])."\n\n";
                        $body .= 
"https://www.cacert.org/account.php?id=12\n\n";;
                        $body .= _("Best Regards")."\n"._("CAcert Support");
                        sendmail($row['email'], "[CAcert.org] "._("Your 
Certificate is about to expire"), $body, 
"support AT cacert.org",
 "", "", "CAcert Support");
echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: 
".$row['memid']." Subj: ".$row['CN']." timeleft: ".$row['daysleft'].")\n";
                        $query = "update `domaincerts` set 
`warning`='".($warning+1)."' where `id`='".$row['id']."'";
                        mysql_query($query);
                }
        }
?>
===================================================================
RCS file: /var/lib/cvs/cacert/scripts/cron/permissionreview.php,v  -->  
standard output
revision 1.1
#!/usr/bin/php -q
<?php
/*
LibreSSL - CAcert web application
Copyright (C) 2004-2012  CAcert Inc.

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; version 2 of the License.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
*/

require_once(dirname(__FILE__).'/../../includes/mysql.php');

$BOARD_PRIVATE = 
'cacert-board-private AT lists.cacert.org';

$flags = array(
        'admin' => 'Support Engineer',
        'orgadmin' => 'Organisation Assurer',
        'board' => 'Board Member',
        'ttpadmin' => 'Trusted Third Party Admin',
        'tverify' => 'Tverify Admin',
        'locadmin' => 'Location Admin'
        );

$adminlist = array();

foreach ($flags as $flag => $description) {
        $query = "select `fname`, `lname`, `email` from `users` where `$flag` 
= 1";
        if(! $res = mysql_query($query) ) {
                fwrite(STDERR,
                                "MySQL query for flag $flag failed:\n".
                                "\"$query\"\n".
                                mysql_error()
                        );
                
                continue;
        }
        
        $admins = array();
        $adminlist[$flag] = "";
        
        while ($row = mysql_fetch_assoc($res)) {
                $admins[] = $row;
                $adminlist[$flag] .= "$row[fname] $row[lname] $row[email]\n";
        }
        
        foreach ($admins as $admin) {
                $message = <<<EOF
Hello $admin[fname],

you get this message, because you are listed as $description on
CAcert.org. Please review the following list of persons with the same 
privilege
and report to the responsible team leader or board
($BOARD_PRIVATE) if you spot any errors.

$adminlist[$flag]


Best Regards,
CAcert Support
EOF;
                sendmail($admin['email'], "Permissions Review", $message, 
'support AT cacert.org');
        }
}



$message = <<<EOF
Dear Board Members,

it's time for the permission review again. Here is the list of privileged 
users
in the CAcert web application. Please review them and also ask the persons 
responsible for an up-to-date copy of access lists not directly recorded in 
the
web application (critical admins, software assessors etc.) 


EOF;

foreach ($flags as $flag => $description) {
        $message .= <<<EOF
List of ${description}s:
$adminlist[$flag]

EOF;
}

$message .= <<<EOF

Best Regards,
CAcert Support
EOF;

sendmail($BOARD_PRIVATE, "Permissions Review", $message, 
'support AT cacert.org');

--- End Message ---

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature

--- End Message ---

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature