Minutes of the MiniTOP on the 2013-08-13

Setting

The MiniTOP will be held via telco 22:00 CEST (20:00 UTC)

Participants: Magu, Andreas P., BenBE, Uli, Michael, dirk

Topics

(skip to agenda)

Action items from last meeting Meeting Action Items

Software/Assessment/ActionItems

Development, Deployment, Discussion

  • OAO, Ted

    bug #943 change OA admin/assurer text

    needs 2nd test -> Fabian, Marc, Alex? {g} / needs 2nd review -> Ted, rejected

    {-}

    uli, Ted

    bug #824 Org User cert fix Case study

    Organisation User Certificates: Need UI improvement for proper production usage

    {0}

    uli, ted

    bug #823 email address removal fix

    No warning when removing e-mail address from account that certificates will be revoked
    checked by 4, needs 2nd review, deploy
    rejected

    {-}

    inopiae

    bug #920 Join - single name only (eg Indonesian)

    details under bug number

    {0}

    uli

    bug #859 admin console interface

    feature request: show activity on an account in the admin interface
    rejected, certs login doesn't modify "modified" field

    {r}

    Michael

    bug #540

    p20111113 CPS #7.1.2 "Certificate Extensions" adjustments - testing
    uli, marcus: needs full cert create tests
    duplicate report to bug#978
    tested by 3, 2nd review done, transfered
    Ken reported: still has problems, bug kept open

    {0}

    gagern, NEO

    bug #440 Problem with subjectAltName (CSR, renew certs)

    There seems to be a problem with the subjectAltName. Dupes, missing entries, and more, rejected, needs further development

    {r}

    neo

    bug #1025 Domain Dispute issue

    disputes rc and rc2 var prob
    needs work

    {r}

    dirk

    bug #1054 0001054: Review the code regarding the new point calculation

    Thawte patch part II
    needs further work

    {r}

Software Assessors: Review 1 / add to cacert-devel, add to testserver

  • Software-Assessors task

Testing

  • Testers task

    neo

    bug #1004 Stats page improvement

    tested by 2, needs 2nd review

    {0}

    neo

    Bugs #1159 it might be possible to execute commands on the signing server

    {0}

    inopiae

    bug #1065 Wrong wording when sending mails during the assurance process

    {0}

    inopiae

    bug #1162 calcutate (the passwords) hash in php instead of in mysql

    create test scenarios for the software testers /!\
    Full testing /!\

    {0}

    inopiae

    bug #0028 Wrong language for you've been assured & [CAcert.org] Client Certificate emails

    {0}

    inopiae

    bug #988 TTP cap form deployment

    {0}

Software Assessors: 2nd Review, Bundle Package to Critical Team

  • Software-Assessors task

    Ted

    bug #500 Get contact mail adress after resolving test

    tested by 3, requires review

    {0}

    Ted

    bug #1140 Show if a test is passed in learnprogress

    tested by 3, requires review

    {0}

    magu

    bug #1131 Rename _all_ Policies from .php to .html and fix all links

    global policy directory maintenance and update

    {0}

    inopiae

    bug #1010 Reorder the view on organisation certificates

    tested by 3

    {0}

Software Assessors: Bundle Package to Critical Team

  • Software-Assessors task

    inopiae

    bug #1139 Add new fields to the database

    tests through #500 and #1140, 2nd review done, requires transfer

    {0}

Awaiting Response from Critical Team

  • inopiae

    bug #411 Wrong text is made into link

    {g}

Agenda

1. Preface

2. Documentations

  1. Documentation - Review / Changes to add (relates to Policy Group SP review)
  2. Documentation - To-Do (relates to Policy Group SP review)

3. DEV on bug 1023/1054 "Thawte Patch"

4. requires transfer to production

  1. bug 663

    • dirk, 2nd review done

  2. bug 893 (reviewed 2013-08-06 by NEO) to be bundled with 1177, 1136, 1137

  3. bug 1177 (reviewed 2013-08-06 by NEO) to be bundled with 893, 1136, 1137

5. 2nd review of remaining patches

6. Patches Overview - Testing, Development

  1. needs further testing
    1. 918 .. keysize
      • Middle security mostly relates to keysize 1024, High security to 2048 but this all relates to crypto provider
      • needs testing
  2. summary - state of patches
    1. 440 needs work (NEO) (see also below)
      • Patch bug #440 was defered (timo addtl. work), but this project stalls. What to do with bug #440 ?

        gagern, neo

        bug #440 Problem with subjectAltName

        tested, needs 2nd review, rejected, new deployment getcn/getalt procedure, relates to bug #1101

        {r} 2

    2. 1004 needs work by neo
    3. 1113 needs work by benbe, transfered to cacert-devel
    4. 1017 needs work by neo
    5. 1025 needs testing

    6. bug 1123, magu test done

    7. Bugs #1023 re-opened

      • Bugs #1112 Exchange the text on the TTP page according to the new TTP programm, deployed 2013-04-24

      • needs update of patch 1023 (new points calculation routine)

      • Bugs #1023 re-opened

  3. Policy text and Arbitration ruling bug# fixes
    1. Policy text changes
    2. Arbitration ruling text fixes

      1. bug #879

        • CAcert must update the web page on disputes, and include an explanation how to file a dispute (a20091206.1)

  4. bug #1004 Stats page improvement

    • neo, BenBe

      bug #1004 Stats page improvement

      tested by 2, needs 2nd review

      {0}

    • stats, Marcus + Uli did some tests, one problem identified, fixed 2012-08-25 by NEO
    • fully re-tested by 2: 2012-08-25 (at froscon)
    • needs 2nd review
    • moved out to cron job routine

    • -> BenBe, assigned

    • 1004 ... on review by BenBe

    • checked BenBe

    • work done by NEO, pushed to cacert-devel, transfered to testserver
    • needs 2nd review, tested
    • current state:
    • open issues
      1. How are deleted users handled?
      2. Isn't "verified_certs" misleading as the affected tables also contain certs that failed to be signed?
      3. User Statistics don't take removed assurances into account (???)
      4. Why not calculate backwards in the year-dependent loop from the already known values? The loop runs backwards already anyway.
    • the latter is still open

  5. bug #1025 Domain Dispute issue

    • BenBe will pickup for 2nd review

    • needs further testing

    • magu, inopiae, u60 -> testing https://bugs.cacert.org/view.php?id=1025

      • several test accounts, variations of one or more email addresses, 0 or 1 domain added
      • test the full disputes procedure for all variations
      • tested by u60

  6. bug #1054, test 1054.3.6, bug #1035

    • create several types of certs (client certs, server certs, org client certs, org server certs) and analyse the content of the certs -> subjectAltName and CN with single SAN and multiple SANs

    • renew the certs

    • addtl. tests ? Marcus? Magu? BenBe?

    • 2012-10-02 dirk: problems with git push #1054, got fixed
    • DEV on bug 1023/1054 "Thawte Patch"

    • see reference notes note 3225 on bug #1101 and note 3245 on bug #1101

  7. bug #1017 {o} , relates also to bug #1054, test 1054.3.6 - Chrome certificate enrollement (relates to #964 "Black Jack") bug #964

    • create client certs, go to signing routine
    • new routine with 3 different potential signed public key download routines /account.php?id=6 list 3 options
      1. Install the certificate into your browser (tested)
      2. Download the certificate in PEM format
      3. Download the certificate in DER format

    • bug #1017 Chrome certificate enrollement

      • BenBe will pickup

      • bug #1017, doing some more tests?

        • new routine with 3 different potential signed public key download routines /account.php?id=6 list 3 options
          1. Install the certificate into your browser (tested)
          2. Download the certificate in PEM format
          3. Download the certificate in DER format
        • Alex, Marcus doing some more tests
    • BenBE to review
    • review bug #964 by Michael
    • bug #964 transfered, still open: bug #1017

      • bug #1017 Chrome certificate enrollement

      • needs testing (lost by transfer to testserver stable)
      • commited 2012-09-04
      • new commited 2013-02-13
      • 964 create cert works, install into browser doesn't work
  8. Marcus Bugs list

    • see Software/BugsOverview

    • according to Bugs # 976

      • 0000976: List of update request for webdb database structure upgrade with tables / fields

      • addtl_notes table hasn't been added in patch bug 976 on 2011-11-25

      • OU info from Org cert not stored

      • addtl_notes table hasn't been added in patch bug 976 on 2011-11-25

      • extend org certs table ? new bug?
      • OU in subject?
      • includes/account.php (17)
      • in org certs it is in subject
      • addtl. field ou ? new bug# ?
      • used bug #1010

  9. new bug #1095 "Problems with creating server sertificate where the csr is created with Java SDK Tools"

    • cmdline sample: keytool -genkey -alias test.test.net -keyalg RSA -keystore test.test.net.ks -validity 1095
    • NEO couldn't reproduce the problem using keytool, tested against production and testserver
    • identified as weak key usage: csr used MD2 encryption, not or no longer supported by openssl, add new error message

  10. bug #440, bug #1101 (extract CSR) (back under development)

    • ASN.1 format
    • CSR extract: needed for signing: email address, hostname
    • Timo will write a CSR parser
    • Current:
      • CN will be parsed
      • some information about public key
    • ASN.1 php library
    • Whats about UTF-8 ?
    • IDN's

      • Policy: p20091108 CPS to drop assurer critieria and allow IDN certificates in specified TLD or single script character sets

      • FAQ Privileges

      • CPS 3.1.7

      • Assurance Handbook - Some more Information 

        • Code signing and IDN certificates
If you are an Assurer, you can get certificates signed/issued by CAcert for code signing and IDNs (International Domain Names).
Due to the increased possibilities for abuse those certificates have additional requirements. The CPS states that this requires Assurer level, which you meet if you are reading this Handbook. However note that as of 20091106, there is a move to reduce these requirements. Watch this space.
    • current only client and server certs, other options currently not selectable, except Code Signing
      • extensions currently not supported eg jabber

      • bug #530 XMPP extension not present after renewal

      • bug #87 Issuing certificates for Jabber servers/users

    • parameters: domains, current first becomes CN, others SANs
    • rebuild subject routine ... to check
    • Michael: shall we enforce cn from csr?
      • optional?
      • enforce copy cn to SAN

    • asn1 parse procedure, http://lapo.it/asn1js/

      • getcn, getalt procedure

      • docs für extractit() und getcn(): general.php line.230

      • felicitus: how someone get "CN" from "commonName"? where is it documented that "CN" is "commonName"?
      • OID of commonName is 2.5.4.3, but there is nothing about "CN"
        • BenBE: see Header of OpenSSL-Header
    • Patch bug #440 was defered (timo's addtl. work), but this project stalls. What to do with bug #440 ?
    • ASN.1 parser - planned: incorporate asn.1 from openssl

  11. bug #1101 refactoring getalt getcn (Timo)

    • might 1101 comment c3225

    • tries to build a php library for openssl parsing replacement
      1. asn.1 parsing, own library
      2. ???
    • openssl does escaping (per man page) (input? output?)
    • library test thru unit tests
    • openssl command for multiple san's ?
  12. GPG bugs

    1. delete/revoke GPG keys (eg bug #1079 )

      • trust signatures can be revoked
      • CRL's have to be added to keyservers, but no one will check
      • revocation: 5 reasons given
      • should be possible, but project needs a developer
    2. GPG bugs
      • OpenGPG parser project, reviewed by Michael last weekend
      • Michael remark: using 3x = (===) instead of 2x = (==)
      • unpack (N) 32bit unsigned may become a problem
      • relates to hardware platforms, signer has been replaced about 2 years ago, but needs to be used on both sides (webserver + signer). Webserver upgrade is WIP
      • in principle ok
    3. BenBE: GPG/PGP parser
      • revoke gpg keys implemented

      • 1181

  13. bug #279 bad domains

    • .*top.*
    • regexp list
    • database table exist
    • update procedure?
    • whats about recuring distribution of update files via cabforum?
    • arbitration?
    • SE console for update?
    • critical admins?
    • check routine on add-domain
    • add domain under OA should be possible ...
    • one-time check of current existing domains ?
      1. first time check against full filter list
      2. individual check in event add domain
      3. global check in event add entry to filter list
      4. replace/update full filter list (case 1 + 4)
    • meta infos:
      1. datasources
      2. attributes (?)
      3. creation date
      4. delete entry / revocation date

  14. bug #1135 SE activity audit tables

    • addtl. recording of arbitration numbers to members
    • results in long discussions

      • requirements, thought cases (eg name change request while another arbitration is running (-> uncritical))

      • delete account requests handled under precedent case a20111128.3), one "critical" case (certs misusage) is turned in procedure: arbitrator has to follow "emergency case" procedure and to keep track of open "delete account" cases

      • interferance/interaction of 2 of the 3 powers (executive, judicate) (arbitration has to act as executive to forward all new cases to support team with list of open/running arbitration cases)
    • all ends on (arbitration) "critical" cases

    • "critical" cases will be handled under Arbitration eg. a20111128.3 within reasonable (eg 48 hours) window

    • discussion defered

    • 1135 (BenBe) 2nd review by another SA before moving to testserver

    • Michael to review

      1. bug #1135 Extend database table AdminLog et al

  15. bug #1136 SE console, delete all certs of a member (instead of highjack an account)

    • probably 1 requirement: addtl. verification step
    • 2013-02-26:
      • bug #1136 - revoke certs doesn't work
      • server log shows no errors
      • and the fix: cacert-devel: testserver-stable 90bdd8cb Timestamp: 2013-02-26 23:32:07
    • added to testers portal, needs testing, 2nd review
    • doesn't work as expected, needs work
    • bug-1136 ready to test. Error fixed
      • tested by magu

    • bug 1136 review tests

      • u60: requires some rework report #4112

      • inopiae: patch delivered, merged into testserver
      • re-tests started

  16. bug #893 Extend Delete account feature for support

    • inopiae

      bug #893 Delete account rev 3 procedure

      needs testing and 2nd review

      {0}

      • test 893 (delete account) with existing server certs, also gpg certs
      • gpg revocation is currently not avail ...
        • manual procedure: currently we hadn't such a case in manualy procedure
        • proposal: in production: set on hold until gpg key expires

        • for testing: gpg keys not expired -> stop procedure if remaining gpg keys not expired

      • if account is locked -> no special exception

      • org admin flag set -> procedure stop (includes Org certs avail)

      • related dispute bugs 1136? 1045?
      • from meeting 2013-04-30
        • requires regexp to check validity: /^[a-z]\d{8}\.\d+\.\d+$/i
        • several tests and updates made

  17. bug #1137 Record the CCA acception for entering an assurance

    • inopiae

      bug #1137 Record the CCA acception for entering an assurance

      needs testing and 2nd review

      {0}

    • 1137 "Record the CCA acception for entering an assurance" needs review testing
    • bug-1137 ready to test. Error fixed

    • (2013-06-11) bug #1137 pushed to test server by BenBE

    • Testing 2013-07-16
      • after finishing assure someone - results in wot.php blank page
      • problem commit 93742459589b515a232d3ec4b717e4b7caa9de85

      • testserver-stable~43..testserver-stable~44 (bad -> good) - dublicated code

      • 43.php inclusion -> notary.inc is most current

      • to depricate: wot.inc
      • inopiae will pickup the merge process upcoming weekend

  18. bug #1162 pushed to test server by BenBE

  19. bug #1141 If i delete Domains, no Servercerts for this domains are listet, even not the revoked

    • moved to testserver

      NEO

      bug #1141 If i delete Domains, no Servercerts for this domains are listet, even not the revoked

      needs testing

      {0}

    • discusssions: arb case? privacy (eg PP 10.), data retention (-> Australien DPA)

    • Marcus to contact Benedikt

  20. what to do with bug #1143 Web site doesn't scale vertically

  21. Advertisement
    • permission review script doesn't include ADadmin

    • relates to bug #1003 and Arbitration case a20110118.1

    • board motion? treasurer? adadmin?

    • Answers given by Intermediate ruling #7 under a20110118.1

    • Michael to pickup
    • 2013-06-11: reminder for NEO

    • Bug #1003 permissions review script

    • next run: 2013-06-30
    • patch transfered to testserver, initiated schedule, mails sent
    • still problems?

  22. bug #901 Renewal of certificate with WIN 7 and IE8

  23. Marcus: bug #1160 "Unable to import personal cert/key into Tunderbird or Evolution, hence unable to encrypt mail with CACert certificates" - needs feedback

    • does this have to do with the last patch install ?

      • Since install of patch bug #964 (Black Jack) automatic client cert installation and renew into FF doesn't work (install to IE5 button doesn't work)

      • signed public client cert will be presented in ascii for copy and paste into a file, but this cert doesn't include the private key part, so the signed public key has to be marriaged with the private key

      • see also FAQ client certs Renew Client Certs under FF

      • patch bug #1017 includes an automatic install into Mozilla keystore, current code on production doesn't

  24. Marcus: server.pl - bug #1159 - it might be possible to execute commands on the signing server

    • answered by Wytze
    • NEO tries a patch

    • server.pl issue .. review by Ben finished, ready to deploy bug #1159

  25. bug #1172 MySQL -> transactional, move isam to innodb

    • Switch MySQL to MariaDB ?

    • 2013-06-11: bug #1172 Move the database engine from myISAM to InnoDB - and other plans for DB migrations

    • also long term project: "sql class project"
    • ongoing discussions about using stored procedures or not
      • voted: result: 1 aye, 4 naye, 1 abstain

  26. bug #1094 Wrong information shown when disputing a domain that is part of a organisation account - Review by Michael

    • Review by Michael + Test by Magu 1094 - OK.

  27. bug #28 Wrong language for you've been assured & [CAcert.org] Client Certificate emails - Review by Michael

    • Review 28 by Michael doesn't work.
    • Additional work required: Doc Comments in include/lib/l10n.php
    • Repaired by Benny for bug 28
    • Ported patch by Marcus
    • Fixed parameter name and class refs in include/lib/l10n.php

  28. bug #872 Discuss over Software changes for PoJAM Policy

    1. (BenBE)
      • UI with checkbox for PoJAM seen
      • for old cases take "not seen" as default
      • do mass-mailing for all PoJAM related to ask assurers to confirm they saw the Parental Consent form
      • ignore points from assurances under PoJAM (even after 18th birthday) when calculating permissions if no confirmation is present
    2. Marcus
      • check only one case of PoJAM acceptions per user
      • once one is present count all assurances as valid
    3. Michael
      • 2 or more Checked PoJAM Assurances for CAcert High Products
    4. SQL query to critical:

      • Users below 18th birthday grouped by date -> counts of assurance points

      • From arb a20091221.1

    5. Uli (AO)
    6. bug 872 for statiscs for PoJAM
      • file a dispute - SQLquery 
           1 SELECT 
   2     count( `temp`.`no` ) as AffectedUsers, 
   3     sum( `temp`.`assurances` ) as AffectedAssurances, 
   4     if(points = 0, "No points", IF(points < 50, "1 < x < 50", IF(points < 100, "50 <= x < 100", "100 <= x"))) as ReceivedPoints 
   5 FROM ( 
   6     SELECT 1 AS no, count( 1 ) AS assurances, sum( `notary`.`points` ) AS points 
   7     FROM `users`, `notary` 
   8     WHERE YEAR(`users`.`dob`)>=1995 and `users`.`id`=`notary`.`to` 
   9     GROUP BY `users`.`id` 
  10     ) AS `temp` 
  11 group by ReceivedPoints

  29. bug #1140 needs testing

    • Ted

      bug #1140 Show if a test is passed in learnprogress

      tested by 3, requires review

      {0}

  30. bug #500 needs testing

    • Ted

      bug #500 Get contact mail adress after resolving test

      requires testing

      tested by 3, requires review

      {0}

  31. bug #1139 moved to ready to deploy

    • inopiae

      bug #1139 Add new fields to the database

      tests through #500 and #1140, 2nd review done, requires transfer

      {0}

  32. 2013-06-11: Bug #1064 and Bug #1045 results in merge conflict in www/wot.php - postponed, Various patches

    • merge conflicts, in www/wot.php

  33. Updated Bug #569 had been pushed to testserver already

  34. bug 1183 prepared patch by magu

  35. bug 372 - relates to fixed bug 922 but 922 only covers missing expired certs notifications - 372 requires deeper review regarding domlink table (in short: deprication of domlink table isn't possible)

  36. Ted: bug 1191 proposal to upgrade to Wheezy

    • do we have a testserver? if no - can cats1 be upgraded?
    • requires no update for 1191
    • ca-mgr1 update will be done by NEO
    • Markus W initiated Zend on ca-mgr1, NEO continued

  37. bug 1190 re-opened

    • magu

      bug #1190 News does not display teaser

      re-opened, again

      {0}

      • Magu dropped fix, depatched entities
      • Mario: looks like wp bug

  38. bug 1185 register globals

    • simple fix; Should be solved now because bug 1176 is in production

  39. bug 1193

    • per CPS 4.2.2 this is an allowed variant

    • CPS 4.2.2

    • section Domain verification

7. Long Term Projects

  1. NEO: "BlackJack" bug #964

    • NEO: "BlackJack" bug #964 testing from last week -> error codes

      • started implementing

    • how does bug #1017 relate to this bug?

      • cert signing routine
      • ie5 ie6 automatic storage of signed key in local keystore
      • doesn't work under vista, win7
      • msi package is to download and import the keys to the local keystore under vista, win7

      • relates to bug #1099 but is quite different

      • neo sent msi package for testing to u60, benbe; test successful passed
      • bug #964 passed, #1017 still open
      • bug 964, has been passed to production, key generation works, transfer into browser not

      • BenBE: reviewed bug #1099 roots installer

      • Root Certificates

      • displays: "Windows installer package for browsers that use the Windows certificate store (for example Internet Explorer, Chrome on Windows and Safari on Windows)"
      • some ideas to move the installer to own section

      • Michael: reworks bug #1099 (roots download page), deployed

  2. Marek's sql class project:
    • is working on charset replacement
  3. api project, Carsten continues with portal project not waiting for vendor-api to be delivered
    • vendor-api delayed
      • no coders
      • other projects
      • related to sql class project
    • portal project continues with a workaround, needs an assurer
      • arbitration case on locations database orders outsourcing of find-an-assurer asap
      • with portal function, update of data is possible vs. update of data on critical system is difficult (keep data current for assurers)
      • relation to location database
        1. website find an assurer
        2. scripted mailing for ATE invitations
      • user check that data is still valid eg every 1 year
        • notification at login upto 6 months not online
        • notification by email if not logged in within last 6 months
  4. Automated testing system
  5. Timo: monitoring signer, not yet done
    • Probably Wytze monitors the systems externaly ?!?

    • see Systems overview

    • monitoring system eg Zabbix instead of Nagios?
    • BenBE: Icinga as alternate?
    • Zabbix agents: requires to be the same revision as server
  6. TLS project
    • BenBe/Wytze talked @ fosdem
    • risks fairly low, awaiting fix

  7. secure boot project (required steps?) (also relates to New Roots & Escrow)

    • we have
      • risk analyze
      • new roots procedure
    • required steps?
      • Escrow method to select
      • subroot under eg. org++
      • cps changes
      • new roots?
      • new signer?
      • indirect crl's
  8. tk-server / testserver system hosting
    • plans for moving testserver over from current location over to BIT Ede, NL new non-critical infrastructure?
    • What are the testserver host requirements? as current non-critical infrastructure runs on LXC and testserver runs under VMware esxi 3.5
      1. piped serial interface configuration
      2. isolating port 25 for testservers (local firewall?)
    • LXC and serial interfaces
      • possible solutions: using a. serial interface b. named pipes?

      • alternates: VirtualBox

      • To link serial port ttyS0 to another serial port: 
        •        socat /dev/ttyS0,raw,echo=0,crnl /dev/ttyS1,raw,echo=0,crnl
      • Server is currently locate by Sebastian and is planned for Non-Critical Infrastructure
      • another plan: reducing rackspace, removal of old hardware?

      • IP Addresses see IP List

        • cacert1 + secure1 -> 1 IP

        • ca-mgr1 + cats1 -> 1 IP

        • git-cacert -> 1 IP

        • TVERIFY is disabled
        • dirk: shall contact Sebastian, transfer to Wytze, Wytze will continue preparation offsite
        • secure-u project (signatures) is decoupled from tk server project

    • Plans are: using VirtualBox or LXC (preferred)

    • planned Infrastructure upgrades

      • several systems bugs, mantis, lists and many others (current Infrastructure systems keys list (see meeting minutes 2013-07-16))

      • several firewall outbound rules for blog, web (community portal) and others
    • Update 2013-07-23
      • dirk: transfer to BIT Ede, NL, around week 33 (2013-08-12 ff.)

8. next meeting

Minutes

1. Preface

  1. Introduction Andreas
  2. Arb case a20130810.1 sql queries
    • Michael: also other provider closed down: Silent Circle

    • https://silentcircle.wordpress.com/2013/08/09/to-our-customers/

    • sql1, tested on local testserver: ok (u60) 
      • select count(users.id) from users where users.email like "%@lavabit.%" and users.deleted = 0
    • sql2, tested on local testserver: ok (u60) 
      • select count(distinct email.memid) from email inner join users on email.memid = users.id  where users.email like '%@lavabit.%' and users.deleted = 0 and email.deleted=0 and email.email not like '%@lavabit.%';
    • sql3a - assurances given to sql1 
      • select count(distinct notary.from) from notary, users where notary.deleted = 0 and notary.from = users.id and users.email like "%@lavabit.%" and users.deleted = 0;
      • optimized, tested on local testserver: ok (u60) 
        select count(distinct notary.from) from (select distinct users.id from users where users.email like "%@lavabit.%" and users.deleted = 0) as interesting_users inner join notary on interesting_users.id = notary.from where notary.deleted = 0;
    • sql4a - assurances given to sql2 
      • select count(distinct notary.from) from notary, users, email where notary.deleted = 0 and notary.from = users.id and users.id = email.memid and users.email like '%@lavabit.%' and users.deleted = 0 and email.deleted=0 and email.email not like '%@lavabit.%';
      • optimized, tested on local testserver: ok (u60) 
        select count(distinct notary.from) from (select distinct users.id from email inner join users on email.memid = users.id  where users.email like '%@lavabit.%' and users.deleted = 0 and email.deleted=0 and email.email not like '%@lavabit.%') as interesting_users inner join notary on interesting_users.id = notary.from where notary.deleted = 0;
    • sql3b1 - client certs active given to sql1, tested on local testserver: ok (u60) 
      • select count(distinct emailcerts.memid ) from (select distinct users.id from users where users.email like "%@lavabit.%" and users.deleted = 0) as interesting_users inner join emailcerts on interesting_users.id = emailcerts.memid where emailcerts.expire > NOW() and revoked = 0;
    • sql3b2 - server certs active given to sql1, tested on local testserver: ok (u60) 
      • select count(distinct domains.memid ) from (select distinct users.id from users where users.email like "%@lavabit.%" and users.deleted = 0) as interesting_users inner join domains on interesting_users.id = domains.memid inner join domaincerts on domains.id = domaincerts.domid where domaincerts.expire > NOW() and domaincerts.revoked = 0;
    • sql3 combined, tested on local testserver: ok (u60) 
      • select count(*) from (
    select notary.from as memid
        from (select users.id from users where users.email like "%@lavabit.%" and users.deleted = 0) as interesting_users inner join notary on interesting_users.id = notary.from
        where notary.deleted = 0
    
    union distinct
    
    select emailcerts.memid as memid
        from (select users.id from users where users.email like "%@lavabit.%" and users.deleted = 0) as interesting_users inner join emailcerts on interesting_users.id = emailcerts.memid
        where emailcerts.expire > NOW() and revoked = 0
    
    union distinct
    
    select domains.memid as memid
        from (select users.id from users where users.email like "%@lavabit.%" and users.deleted = 0) as interesting_users inner join domains on interesting_users.id = domains.memid inner join domaincerts on domains.id = domaincerts.domid
        where domaincerts.expire > NOW() and domaincerts.revoked = 0
) as  critical_users;
    • sql4 combined, tested on local testserver: ok (u60) 
      • select count(*) from (
    select notary.from as memid
         from (select users.id from email inner join users on email.memid = users.id  where users.email like '%@lavabit.%' and users.deleted = 0 and email.deleted=0 and email.email not like '%@lavabit.%') as interesting_users inner join notary on  interesting_users.id = notary.from
        where notary.deleted = 0
    
    union distinct
    
    select emailcerts.memid as memid
         from (select users.id from email inner join users on email.memid = users.id  where users.email like '%@lavabit.%' and users.deleted = 0 and email.deleted=0 and email.email not like '%@lavabit.%') as interesting_users inner join emailcerts on  interesting_users.id = emailcerts.memid
        where emailcerts.expire > NOW() and revoked = 0
    
    union distinct
    
    select domains.memid as memid
         from (select users.id from email inner join users on email.memid = users.id  where users.email like '%@lavabit.%' and users.deleted = 0 and email.deleted=0 and email.email not like '%@lavabit.%') as interesting_users inner join domains on  interesting_users.id = domains.memid inner join domaincerts on  domains.id = domaincerts.domid
        where domaincerts.expire > NOW() and domaincerts.revoked = 0
) as  critical_users;
  3. tk-server
    • proposed 33 kw, no update yet
    • proposed that Sebastian transfers server over to dirk, dirk transfers one day over to NL

2. Bugs

  1. BenBE prepared patch bundle from

  2. bug 1200

    • BenBE: ready to deploy

      • Ask Critical for installation of package mktemp in executable path of webserver for GPG signing to work

  3. NEO found probably bug in notary.inc regarding server certs mysql query
    • running id=50

  4. bug 1190 re-opened

    • wp bug
      • fix in webdb code requires deeper inspection -or-
      • search bug in wp and fix it

3. Documentations

  1. BenBE updated page 4 of testers welcome pack

4. Long term projects

  1. BenBE proposal ...
    • to centralize sql queries in the code base
    • project has been started by Marek, but didn't made much progress
    • see agenda item 7.2

Fixed Action Items since last or within meeting

Action Items New

Action items: Meeting Action Items

Software/Assessment/20130813-S-A-MiniTOP (last edited 2013-08-14 00:04:42 by UlrichSchroeter)