1343248 - (bmo-secbugstats) Migrate secbugstats scripts to bmo production

Status: RESOLVED FIXED

(bugzilla.mozilla.org :: General, defect, P1)

Description

[Dylan Hardison [:dylan] (he/him)]

This is a follow-up to conversations about taking some scripts that currently run on dveditz's mac mini and getting them to run on the bugzilla admin node.

The things we need:

1) a git repo (which will be forked into mozilla-bteam's github org)
2) instructions for configuring the thing (preferably a README file in the repo)
3) details about installation / software requirements / cron job details.

Once we have the good and I have a chance to audit it, I'll file a bmo :: Infra bug to get it running on bugzillaadmin, and we'll plan for it running in AWS when we migrate there as well.

Comment 1

[Dylan Hardison [:dylan] (he/him)]

I forgot to actually set NI because I'm a n00b.

Comment 2

[Julien Vehent]

Moving this to private infra as it might contain confidential info.

Dan: Could you please post the code, either here or on a private github repo? Dylan and I would like to review it before moving forward.
I'm wondering if this would be a good fit to run as a lambda function in cloudservices-aws-prod, where we could use encrypted configuration variables to store the access token.

Dylan: would this need to use Dan's bugzilla account or can we use some sort of service account?

Comment 3

[Julien Vehent]

The code is at https://github.com/dveditz/secbugstats/
The 'www' part isn't used. The code is in Python, so I think it could be lambdafied pretty easily.

Comment 4

[Dylan Hardison [:dylan] (he/him)]

Woops, forgot to write the comment I was thinking there. Sorry about that. 

My question here is just about code, and is also to dveditz:

Do you want the b-team to be responsible for this code, or do you want some other party responsible for it?
It seems useful to our primary customer (Firefox), and we run similar things (whines). 

If not, I'm going to close this as WONTFIX.

Comment 5

[Dylan Hardison [:dylan] (he/him)]

(In reply to Julien Vehent [:ulfr] from comment #2)
> Moving this to private infra as it might contain confidential info.

The infra group is really not appropriate for this, and I am not a member of it. 
I'm tempted to code a warning message when this group is used in my product.

> Dylan: would this need to use Dan's bugzilla account or can we use some sort
> of service account?

I'd create a service account with no password, [e-mail] comes to mind. I'll have reviewed the code by tomorrow evening.

Comment 6

[Dylan Hardison [:dylan] (he/him)]

The current code uses username/password, did a small pull request that will allow it to use API keys.

https://github.com/dveditz/secbugstats/pull/1

Comment 7

[Selena Deckelmann :selenamarie :selena]

ulfr/dylan: From your conversations, should this live in bmo infra or a lambda function? Primary concern from me is that where it runs is secure, and when it breaks more than one person notices and is able to fix it.

Comment 8

[Julien Vehent]

I am happy to let dylan make that call. If we decide to pick lambda, I'll help set it up.

Comment 9

[Daniel Veditz [:dveditz]]

(In reply to Julien Vehent [:ulfr] from comment #3)
> The code is at https://github.com/dveditz/secbugstats/

I've made some local fixups that I'll upload since people are interested in this now.

One of the key things in deploying this will be preserving the historical data that has been collected. Don't want to check that into github (a database of security bugs?) so we'll have to figure that out later. The database only stores metadata about the bugs, but the summaries can be sensitive enough. If we no longer support the www stuff I suppose we don't need the summaries anymore so we can eventually purge those, but I'd like to get the process moved first.


(In reply to Julien Vehent [:ulfr] from comment #2)
> Dylan: would this need to use Dan's bugzilla account or can we use some sort of service account?

It's currently using an api key from my account. If we switch to something else--which would be nice for continuity--it needs to have equivalent access to all client security bugs.

Comment 10

[Daniel Veditz [:dveditz]]

(In reply to Dylan Hardison [:dylan] from comment #4)
> Do you want the b-team to be responsible for this code, or do you want some
> other party responsible for it?

b-team seems appropriate. The security team will need to make requests such as to adjust the prod/comp <-> sec-group mappings, though maybe that could be built into BMO at a more fundamental level.

Comment 11

[Dylan Hardison [:dylan] (he/him)]

Considering this needs its own DB, and I have a desire to run some other functions on lambda in the future -- let's work together on that.
I'd like to be in a position to understand how we stand up utilities like this in lambda for future reference.

Chat later on this week about it?

Comment 12

[Julien Vehent]

I was asked about the state of this migration. As I understand it, this is what needs to happen:

1. The current code needs to be cleaned up and converted to a Lambda function (including packaging it properly as a zip file).
2. A service account needs to be created for the function to access the relevant bugs
3. The lambda function needs to be deployed in the dev-services prod IAM (including configuring SES to send the weekly email).

Who can take care of #1? Dan is the current maintainer of the code, so it's probably your call. Until we have dev time to improve it, we can't really make progress here.

Comment 13

[Dylan Hardison [:dylan] (he/him)]

I'm going to un-assign myself until there is a means to get this ball rolling. I am quite happy to clean up the code and make it run in a new environment if that is defined.

Comment 14

[Dylan Hardison [:dylan] (he/him)]

Okay, after some discussions (thanks Emma, for drawing my attention back to this)

1. secbugstats is being added to the bmo repo, under contrib/secbugstats/
2. it will be run as a cronjob on bmo production, adding to our existing cronjobs

Comment 15

[Dylan Hardison [:dylan] (he/him)]

Attachment: puppet-packages.patch

Is this the right place for rpms in the puppet stuff for bugzilla to go?

Comment 16

[Dylan Hardison [:dylan] (he/him)]

Attachment: PR: Initial import

This is the initial import (preserving history) of the secbugstats code into the  BMO repo

Comment 17

[Dylan Hardison [:dylan] (he/him)]

Attachment: PR: My changes

These are my changes on top of the original code to make it work in our environments - It is still a slight work in progress

Comment 18

[Dylan Hardison [:dylan] (he/him)]

Comment on attachment 8943409 [details] [review]
PR: Initial import

Let me know if this is differs in a signficant way (aside from the directory structure) from the code you're currently running. 
I based this on the current master branch of github:dveditz/secbugstats

Comment 19

[Dylan Hardison [:dylan] (he/him)]

Comment on attachment 8943410 [details] [review]
PR: My changes

I'd ask for a sanity check but I'm not sure that is reasonable giving what I'm doing here... but a look-over would be useful.


Thanks!

Comment 20

[Dylan Hardison [:dylan] (he/him)]

Updating the user story for what I have left. It will be a stretch to get this out next week as I have other issues to attend to, but we're incredibly close. :-)

Comment 21

[Dylan Hardison [:dylan] (he/him)]

Attachment: puppet-cron.patch

And I think this is what is needed for cron.

Comment 22

[Dylan Hardison [:dylan] (he/him)]

dveditz: can you please grant access to whichever groups is needed to [e-mail]?
This is a registered bot account that cannot receieve email or have a password.

c.f. https://wiki.mozilla.org/BMO/Bot_Registry

Comment 23

[Dylan Hardison [:dylan] (he/him)]

Forgot to needinfo, again. I should make tagging work like on twitter.

Anyway, I don't know per-se what groups to put that bot in.

Comment 24

[Dylan Hardison [:dylan] (he/him)]

Only thing left that I'm not blocked on is testing, so I'm doing that now.

So far I get this error:


Traceback (most recent call last):
  File "/vagrant/contrib/secbugstats/bin/curlbug.py", line 62, in <module>
    json = fetchBugzillaPage(url)
  File "/vagrant/contrib/secbugstats/bin/curlbug.py", line 27, in fetchBugzillaPage
    return opener.open(url).read()
  File "/usr/lib64/python2.6/urllib2.py", line 391, in open
    response = self._open(req, data)
  File "/usr/lib64/python2.6/urllib2.py", line 409, in _open
    '_open', req)
  File "/usr/lib64/python2.6/urllib2.py", line 369, in _call_chain
    result = func(*args)
  File "/usr/lib64/python2.6/urllib2.py", line 1198, in https_open
    return self.do_open(httplib.HTTPSConnection, req)
  File "/usr/lib64/python2.6/urllib2.py", line 1163, in do_open
    r = h.getresponse()
  File "/usr/lib64/python2.6/httplib.py", line 1049, in getresponse
    response.begin()
  File "/usr/lib64/python2.6/httplib.py", line 433, in begin
    version, status, reason = self._read_status()
  File "/usr/lib64/python2.6/httplib.py", line 397, in _read_status
    raise BadStatusLine(line)
httplib.BadStatusLine

which I suspect is zeus.

Comment 25

[Dylan Hardison [:dylan] (he/him)]

The previous error was transient, and the remaining problem was the Stats table not being populated. This is because it was MyISAM but when I made bugzilla manage the table definitions it used InnoDB.
With InnoDB, the python package MySQLdb doesn't commit transactions
unless you call con.autocommit(True). After fixing that, it seems like stats run.

Comment 26

[Dylan Hardison [:dylan] (he/him)]

note: the cron job needs to run weekly.

Comment 27

[Dylan Hardison [:dylan] (he/him)]

I've successfully generated a report. I've sent a sample to dveditz to see if it looks right.

I have one more ask: can I can a dump of the mysql db on dveditz' machine?
That way I could load the historical stats data.
If not, the first few reports might not be as useful.

Comment 28

[:dhouse]

Comment on attachment 8943408 [details] [diff] [review]
puppet-packages.patch

+1 That's the right place for rpms for puppet to install.

Comment 29

[Dylan Hardison [:dylan] (he/him)]

I've got email sending working (a trivial sendmail wrapper that feeds into the job queue table).

I'm going to go live with what I have on Feb 13th. I'll run it every monday at 6AM PST.

Before then it would be useful to have:

1) a database dump (for continuity reasons)
2) an idea on what time it should run (if 6AM PST, monday is not desired)
3) a finished review of my changes.

Comment 30

[Daniel Veditz [:dveditz]]

I've added the bot to the client-security-triage-team group which should cover all the bugs.

I wonder if it should be in the mozilla-employee-confidential group also, just in case some bug is double-grouped? I see 29 such bugs currently. Most are sec-other or sec-audit and wouldn't show up on the charts, but there two sec-moderate bugs that won't get counted unless the bot has that access level.

Are the bots immune from the process that kicks people out of the group if they haven't logged in for 6 weeks?

I generated a database dump today I can get to you. How? it's got titles from private bugs in it so I'm not going to attach it here.

Monday 6am is OK -- what timezone? Assuming Pacific/MTV time that's mid-afternoon for Europeans and after the workday for western pacific folks. Sunday morning is likelier to be a quiet period. I used to have a cron job run them Sunday 8am Pacific

Comment 31

[Dylan Hardison [:dylan] (he/him)]

(In reply to Daniel Veditz [:dveditz] from comment #30)
> I've added the bot to the client-security-triage-team group which should
> cover all the bugs.
> 
> I wonder if it should be in the mozilla-employee-confidential group also,
> just in case some bug is double-grouped? I see 29 such bugs currently. Most
> are sec-other or sec-audit and wouldn't show up on the charts, but there two
> sec-moderate bugs that won't get counted unless the bot has that access
> level.

It should be in both.

> Are the bots immune from the process that kicks people out of the group if
> they haven't logged in for 6 weeks?
I'll make them immune.

> I generated a database dump today I can get to you. How? it's got titles
> from private bugs in it so I'm not going to attach it here.

My GPG key is F7B33FB77A6496E9; email or firefox file share a file encrypted to me?

> Monday 6am is OK -- what timezone? Assuming Pacific/MTV time that's
> mid-afternoon for Europeans and after the workday for western pacific folks.
> Sunday morning is likelier to be a quiet period. I used to have a cron job
> run them Sunday 8am Pacific
Sunday 8am Pacific it is then.

Comment 32

[Dylan Hardison [:dylan] (he/him)]

Are cron times in UTC or PST/PDT?

Comment 33

[:dhouse]

They're in UTC (I checked on a few of the webservers and they are also on UTC).
```
[[e-mail]3 ~]# grep -m1 remove_idle_group /var/log/cron
Feb  4 07:00:02 bugzillaadm.private.scl3.mozilla.com CROND[6373]: (root) CMD (cd /data/bugzilla/www/bugzilla.mozilla.org; ./scripts/remove_idle_group_members.pl)
[[e-mail]3 ~]# crontab -l | grep remove_idle_group
0 7 * * * cd /data/bugzilla/www/bugzilla.mozilla.org; ./scripts/remove_idle_group_members.pl
[[e-mail]3 ~]# tail -1 /var/log/cron
Feb  7 15:14:01 bugzillaadm.private.scl3.mozilla.com crontab[13512]: (root) LIST (apache)
[[e-mail]3 ~]# date
Wed Feb  7 15:14:07 UTC 2018
```

Comment 34

[Daniel Veditz [:dveditz]]

(In reply to Dylan Hardison [:dylan] (he/him) from comment #31)
> > I wonder if [the bot] should be in the mozilla-employee-confidential group also,
> 
> It should be in both.

I can't add it to the employee-confidential group. Hopefully you can?

Comment 35

[Dylan Hardison [:dylan] (he/him)]

Attachment: puppet-cron.patch

revised puppet cron patch

Comment 36

[Dylan Hardison [:dylan] (he/him)]

Now that I've done a push, we can run this for reals.

Comment 37

[Dylan Hardison [:dylan] (he/him)]

It got so far, but failed:

Traceback (most recent call last):
  File "/data/bugzilla/www/bugzilla.mozilla.org/contrib/secbugstats/bin/curlbug.py", line 65, in <module>
    json = fetchBugzillaPage(url)
  File "/data/bugzilla/www/bugzilla.mozilla.org/contrib/secbugstats/bin/curlbug.py", line 30, in fetchBugzillaPage
    return opener.open(url).read()
  File "/usr/lib64/python2.6/urllib2.py", line 391, in open
    response = self._open(req, data)
  File "/usr/lib64/python2.6/urllib2.py", line 409, in _open
    '_open', req)
  File "/usr/lib64/python2.6/urllib2.py", line 369, in _call_chain
    result = func(*args)
  File "/usr/lib64/python2.6/urllib2.py", line 1198, in https_open
    return self.do_open(httplib.HTTPSConnection, req)
  File "/usr/lib64/python2.6/urllib2.py", line 1163, in do_open
    r = h.getresponse()
  File "/usr/lib64/python2.6/httplib.py", line 1049, in getresponse
    response.begin()
  File "/usr/lib64/python2.6/httplib.py", line 433, in begin
    version, status, reason = self._read_status()
  File "/usr/lib64/python2.6/httplib.py", line 397, in _read_status
    raise BadStatusLine(line)

Comment 38

[Dylan Hardison [:dylan] (he/him)]

Can you tell me what it was trying to do here?

Comment 39

[Dylan Hardison [:dylan] (he/him)]

Okay, the problem is the the sg_total and sg_untouched counts are returning nothing. So it's a problem with the BMO API (sort of, I mean these queries are also abusive but...)

Comment 40

[Daniel Veditz [:dveditz]]

We aren't going to support/run the associated website code ever, so why not just kill all those queries?

* remove everything but sg_critical, sg_high, sg_moderate, sg_low from the tocheck dictionary in curlbug.py

* remove everything but low,moderate,high,critical from the dictionary on line 23 of gather.sh (don't run morestats.py on files that won't exist)

Comment 41

[Dylan Hardison [:dylan] (he/him)]

Attachment: PR

Like this?

Comment 42

[Daniel Veditz [:dveditz]]

Comment on attachment 8956164 [details] [diff] [review]
PR

Review of attachment 8956164 [details] [diff] [review]:
-----------------------------------------------------------------

yup. r=dveditz