CAcert Code Development list.

[Michael Tänzer <michael.taenzer AT cacert.org>]

Hi folks,

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

-- 
Have a nice day,
Michael Tänzer


diff --git a/includes/lib/account.php b/includes/lib/account.php
index f7a24fa..c7697ce 100644
--- a/includes/lib/account.php
+++ b/includes/lib/account.php
@@ -19,6 +19,8 @@
 
 function fix_assurer_flag($userID)
 {
+	// If requirements for assurers are modified see also scripts/cron/updatesort.php
+
 	// Update Assurer-Flag on users table if 100 points.
 	// Should the number of points be SUM(points) or SUM(awarded)?
 	$query = mysql_query('UPDATE `users` AS `u` SET `assurer` = 1 WHERE '.
@@ -29,11 +31,11 @@ function fix_assurer_flag($userID)
 		'(SELECT SUM(`points`) FROM `notary` AS `n` WHERE `n`.`to` = `u`.`id` '.
 			'AND (`n`.`expire` > now() OR `n`.`expire` IS NULL)) >= 100');
 	// Challenge has been passed and non-expired points >= 100
-	
+
 	if (!$query) {
 		return false;
 	}
- 
+
 	// Reset flag if requirements are not met
 	$query = mysql_query('UPDATE `users` AS `u` SET `assurer` = 0 WHERE '.
 		'`u`.`id` = \''.(int)intval($userID).'\' AND '.
@@ -42,10 +44,10 @@ function fix_assurer_flag($userID)
 			'AND `cp`.`user_id` = `u`.`id`) OR '.
 		'(SELECT SUM(`points`) FROM `notary` AS `n` WHERE `n`.`to` = `u`.`id` '.
 			'AND (`n`.`expire` > now() OR `n`.`expire` IS NULL)) < 100)');
-	
+
 	if (!$query) {
 		return false;
 	}
-	
+
 	return true;
 }
\ No newline at end of file
diff --git a/scripts/cron/permissionreview.php b/scripts/cron/permissionreview.php
new file mode 100755
index 0000000..572c1fd
--- /dev/null
+++ b/scripts/cron/permissionreview.php
@@ -0,0 +1,102 @@
+#!/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');
diff --git a/scripts/cron/removedead.php b/scripts/cron/removedead.php
new file mode 100755
index 0000000..aadda81
--- /dev/null
+++ b/scripts/cron/removedead.php
@@ -0,0 +1,85 @@
+#!/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]);
+	}
+?>
diff --git a/scripts/cron/updatesort.php b/scripts/cron/updatesort.php
new file mode 100755
index 0000000..498eda2
--- /dev/null
+++ b/scripts/cron/updatesort.php
@@ -0,0 +1,111 @@
+#!/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);
+	}
+
+
+?>
diff --git a/scripts/cron/warning.php b/scripts/cron/warning.php
new file mode 100755
index 0000000..18e89da
--- /dev/null
+++ b/scripts/cron/warning.php
@@ -0,0 +1,100 @@
+#!/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);
+		}
+	}
+?>
diff --git a/scripts/removedead.php b/scripts/removedead.php
deleted file mode 100755
index 23c4cd9..0000000
--- a/scripts/removedead.php
+++ /dev/null
@@ -1,85 +0,0 @@
-#!/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
-*/
-
-	include_once("/home/cacert/www/includes/mysql.php");
-	require_once('/home/cacert/www/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]);
-	}
-?>
diff --git a/scripts/updatesort.php b/scripts/updatesort.php
deleted file mode 100755
index 4d36bfc..0000000
--- a/scripts/updatesort.php
+++ /dev/null
@@ -1,77 +0,0 @@
-#!/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
-*/
-	include_once("../includes/mysql.php");
-
-
-
-	//mysql_query("update users set assurer=0");
-	$query = "select notary.`to` as uid from notary group by notary.`to` having sum(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);
-	}
-
-
-	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);
-	}
-
-
-
-
-?>
diff --git a/scripts/warning.php b/scripts/warning.php
deleted file mode 100755
index b578c09..0000000
--- a/scripts/warning.php
+++ /dev/null
@@ -1,100 +0,0 @@
-#!/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
-*/
-
-	include_once("/home/cacert/www/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);
-		}
-	}
-?>

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