Your message dated Thu, 6 Jun 2019 21:56:55 +0200 with message-id <[🔎] 20190606195655.GA12735@eldamar.local> and subject line Re: Bug#507303: security-tracker: please provide a per-maintainer report has caused the Debian Bug report #507303, regarding security-tracker: add per-maintainer page (with half-baked patch) to be marked as done. This means that you claim that the problem has been dealt with. If this is not the case it is now your responsibility to reopen the Bug report if necessary, and/or fix the problem forthwith. (NB: If you are a system administrator and have no idea what this message is talking about, this may indicate a serious mail system misconfiguration somewhere. Please contact owner@bugs.debian.org immediately.) -- 507303: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=507303 Debian Bug Tracking System Contact owner@bugs.debian.org with problems
--- Begin Message ---
- To: Debian Bug Tracking System <submit@bugs.debian.org>
- Subject: security-tracker: add per-maintainer page (with half-baked patch)
- From: Paul Wise <pabs@debian.org>
- Date: Fri, 17 Feb 2012 17:36:00 +0800
- Message-id: <1329471360.7159.64.camel@chianamo>
Package: security-tracker Severity: wishlist The attached patch implements a first pass at a per-maintainer page of security issues. It involves some database schema changes to it will require a full reimport of all the data. My SQL knowledge isn't great, so there are some deficiencies: I'm not sure if the adding another table is the right way to go, nor if I used the right table name. I'm not sure if the getBugsForMaintainer is correct, especially wrt version numbers/releases/etc. I am not sure how to implement a getDSAsForMaintainer function to add DSAs related to the maintainer at the bottom of the page. -- bye, pabs http://wiki.debian.org/PaulWiseIndex: lib/python/security_db.py =================================================================== --- lib/python/security_db.py (revision 18462) +++ lib/python/security_db.py (working copy) @@ -38,6 +38,7 @@ import sys import types import zlib +import email.utils import debian_support import dist_config @@ -123,6 +124,9 @@ # Enable WAL. This means that updates will not block readers. c.execute("PRAGMA journal_mode = WAL") + # Enable foreign keys + c.execute("PRAGMA foreign_keys=ON") + self.schema_version = 22 self._initFunctions() @@ -198,15 +202,23 @@ cursor.execute( """CREATE TABLE source_packages - (name TEXT NOT NULL, + (id INTEGER, + name TEXT NOT NULL, release TEXT NOT NULL, subrelease TEXT NOT NULL, archive TEXT NOT NULL, version TEXT NOT NULL, version_id INTEGER NOT NULL DEFAULT 0, - PRIMARY KEY (name, release, subrelease, archive))""") + UNIQUE (name, release, subrelease, archive), + PRIMARY KEY(id ASC))""") cursor.execute( + """CREATE TABLE source_package_maintainers + (source_package_id INTEGER NOT NULL, + maintainer TEXT NOT NULL, + FOREIGN KEY(source_package_id) REFERENCES source_packages(id) ON DELETE CASCADE)""") + + cursor.execute( """CREATE TABLE binary_packages (name TEXT NOT NULL, release TEXT NOT NULL, @@ -348,14 +360,14 @@ AND sidp.release = 'sid' AND sidp.subrelease = '' AND sidp.archive = sp.archive AND sidst.bug_name = st.bug_name - AND sidst.package = sidp.rowid) AS unstable_vulnerable, + AND sidst.package = sidp.id) AS unstable_vulnerable, COALESCE((SELECT NOT vulnerable FROM source_packages AS tsecp, source_package_status AS tsecst WHERE tsecp.name = sp.name AND tsecp.release = 'wheezy' AND tsecp.subrelease = 'security' AND tsecp.archive = sp.archive AND tsecst.bug_name = st.bug_name - AND tsecst.package = tsecp.rowid), 0) AS testing_security_fixed, + AND tsecst.package = tsecp.id), 0) AS testing_security_fixed, (SELECT range_remote FROM nvd_data WHERE cve_name = st.bug_name) AS remote, (EXISTS (SELECT * FROM package_notes_nodsa AS pnd @@ -363,7 +375,7 @@ AND pnd.package = sp.name AND pnd.release = 'wheezy')) AS no_dsa FROM source_package_status AS st, source_packages AS sp - WHERE st.vulnerable > 0 AND sp.rowid = st.package + WHERE st.vulnerable > 0 AND sp.id = st.package AND sp.release = 'wheezy' AND sp.subrelease = '' ORDER BY sp.name, st.urgency, st.bug_name""") @@ -380,7 +392,7 @@ AND pnd.package = sp.name AND pnd.release = '%s')) AS no_dsa FROM source_package_status AS st, source_packages AS sp - WHERE st.vulnerable > 0 AND sp.rowid = st.package + WHERE st.vulnerable > 0 AND sp.id = st.package AND sp.release = '%s' AND sp.subrelease = '' AND NOT COALESCE((SELECT NOT vulnerable FROM source_packages AS secp, source_package_status AS secst @@ -388,7 +400,7 @@ AND secp.release = '%s' AND secp.subrelease = 'security' AND secp.archive = sp.archive AND secst.bug_name = st.bug_name - AND secst.package = secp.rowid), 0) + AND secst.package = secp.id), 0) ORDER BY sp.name, urgency_to_number(urgency), st.bug_name""" % (name, nickname, nickname, nickname)) @@ -465,6 +477,7 @@ for pkg in packages: pkg_name = None pkg_version = None + pkg_maintainers = [] pkg_arch = None pkg_source = None pkg_source_version = None @@ -473,6 +486,8 @@ pkg_name = contents elif name == "Version": pkg_version = contents + elif name in ("Maintainer", "Uploaders"): + pkg_maintainers += [mail for (name, mail) in email.utils.getaddresses([contents])] elif name == "Source": match = re_source.match(contents) if match is None: @@ -482,6 +497,7 @@ (pkg_source, pkg_source_version) = match.groups() elif name == "Architecture": pkg_arch = contents + pkg_maintainers = list(set(pkg_maintainers)) if pkg_name is None: raise SyntaxError\ ("package record does not contain package name") @@ -497,10 +513,10 @@ oversion = debian_support.Version(data[pkg_name][0]) if oversion < debian_support.Version(pkg_version): data[pkg_name] = (pkg_version, pkg_arch, - pkg_source, pkg_source_version) + pkg_source, pkg_source_version, pkg_maintainers) else: data[pkg_name] = (pkg_version, pkg_arch, - pkg_source, pkg_source_version) + pkg_source, pkg_source_version, pkg_maintainers) return data @@ -546,6 +562,7 @@ print " reading source packages" for filename in glob.glob(directory + '/*_Sources'): + print filename match = re_sources.match(filename) if match is None: raise ValueError, "invalid file name: " + `filename` @@ -561,18 +578,26 @@ (release, subrelease, archive)) self._clearVersions(cursor) - def gen(): - for name in parsed.keys(): - (version, archs, source, source_version) = parsed[name] - assert source is None - assert source_version is None - yield name, release, subrelease, archive, version - cursor.executemany( - """INSERT INTO source_packages - (name, release, subrelease, archive, version) - VALUES (?, ?, ?, ?, ?)""", - gen()) - + for name in parsed.keys(): + (version, archs, source, source_version, maintainers) = parsed[name] + assert source is None + assert source_version is None + cursor.execute( + """INSERT INTO source_packages + (name, release, subrelease, archive, version) + VALUES (?, ?, ?, ?, ?)""", + (name, release, subrelease, archive, version)) + last_id = self.db.last_insert_rowid() + def gen(): + for maintainer in maintainers: + yield last_id, maintainer + cursor.executemany( + """INSERT INTO source_package_maintainers + (source_package_id, maintainer) + VALUES (?, ?)""", + gen()) + print 'done importing' + def _readBinaryPackages(self, cursor, directory): """Reads from a directory with binary package files.""" @@ -618,7 +643,7 @@ (unch, parsed) = self._parseFile(cursor, filename) unchanged = unchanged and unch for name in parsed.keys(): - (version, arch, source, source_version) = parsed[name] + (version, arch, source, source_version, maintainer) = parsed[name] if source is None: source = name if source_version is None: @@ -1001,7 +1026,7 @@ cursor.execute( """INSERT INTO source_package_status - SELECT n.bug_name, p.rowid, + SELECT n.bug_name, p.id, CASE WHEN n.fixed_version == 'undetermined' THEN 2 ELSE CASE WHEN n.fixed_version IS NULL THEN 1 ELSE CASE WHEN p.version_id < n.fixed_version_id THEN 1 @@ -1017,7 +1042,7 @@ print " source packages (qualified)" cursor.execute( """INSERT OR REPLACE INTO source_package_status - SELECT n.bug_name, p.rowid, + SELECT n.bug_name, p.id, CASE WHEN n.fixed_version == 'undetermined' THEN 2 ELSE CASE WHEN n.fixed_version IS NULL THEN 1 ELSE CASE WHEN p.version_id < n.fixed_version_id THEN 1 @@ -1081,7 +1106,7 @@ """SELECT DISTINCT sp.name, st.vulnerable, n.urgency FROM source_package_status AS st, source_packages AS sp, package_notes AS n - WHERE st.bug_name = ? AND sp.rowid = st.package + WHERE st.bug_name = ? AND sp.id = st.package AND sp.release = 'sid' AND n.bug_name = st.bug_name AND n.package = sp.name ORDER BY sp.name""", @@ -1145,7 +1170,7 @@ st.vulnerable, n.urgency FROM source_package_status AS st, source_packages AS sp, package_notes AS n - WHERE st.bug_name = ? AND sp.rowid = st.package + WHERE st.bug_name = ? AND sp.id = st.package AND sp.release = ? AND sp.subrelease IN ('', 'security') AND n.bug_name = st.bug_name AND n.package = sp.name ORDER BY sp.name""", @@ -1279,7 +1304,7 @@ AND p.release = ? AND p.subrelease IN ('', 'security') AND st.bug_name = ? - AND st.package = p.rowid + AND st.package = p.id ORDER BY p.version COLLATE version DESC""" else: fix_available_sql = '' @@ -1615,7 +1640,7 @@ release_name(p.release, p.subrelease, p.archive) AS release, p.version AS version, s.vulnerable AS vulnerable FROM source_package_status AS s, source_packages AS p - WHERE s.bug_name = ? AND p.rowid = s.package + WHERE s.bug_name = ? AND p.id = s.package AND release in ('lenny', 'squeeze', 'wheezy', 'sid', 'experimental') ORDER BY release_to_number(p.release), p.subrelease) GROUP BY package, version, vulnerable @@ -1661,14 +1686,14 @@ source_package_status AS st2 WHERE sp2.name = sp.name AND sp2.release = sp.release AND sp2.subrelease = 'security' AND sp2.archive = sp.archive - AND st2.package = sp2.rowid AND st2.bug_name = st.bug_name + AND st2.package = sp2.id AND st2.bug_name = st.bug_name ORDER BY st2.vulnerable DESC), 1)) AS vulnerable, st.urgency = 'unimportant' OR NOT vulnerable AS unimportant FROM source_packages AS sp, source_package_status AS st, bugs WHERE sp.name = ? AND sp.release IN ('lenny', 'squeeze', 'wheezy', 'sid') AND sp.subrelease <> 'security' - AND st.package = sp.rowid + AND st.package = sp.id AND bugs.name = st.bug_name AND bugs.name NOT LIKE 'DSA-%' GROUP BY bugs.name, bugs.description, sp.name) @@ -1683,6 +1708,33 @@ AND bugs.name LIKE 'DSA-%' AND p.package = ?""", (package,)) + def getBugsForMaintainer(self, cursor, maintainer, vulnerable, unimportant): + """Returns a generator for a list of (BUG, DESCRIPTION) pairs + which have the requested status. Only bugs affecting supported + releases are returned.""" + return cursor.execute( + """SELECT DISTINCT pkg, name, description + FROM (SELECT sp.name AS pkg, bugs.name AS name, bugs.description AS description, + MAX(st.vulnerable + AND COALESCE((SELECT st2.vulnerable FROM source_packages AS sp2, + source_package_status AS st2 + WHERE sp2.name = sp.name AND sp2.release = sp.release + AND sp2.subrelease = 'security' AND sp2.archive = sp.archive + AND st2.package = sp2.id AND st2.bug_name = st.bug_name + ORDER BY st2.vulnerable DESC), 1)) AS vulnerable, + st.urgency = 'unimportant' OR NOT vulnerable AS unimportant + FROM source_packages AS sp, source_package_maintainers AS m, source_package_status AS st, bugs + WHERE m.maintainer = ? + AND m.source_package_id = sp.id + AND sp.release IN ('lenny', 'squeeze', 'wheezy', 'sid') + AND sp.subrelease <> 'security' + AND st.package = sp.id + AND bugs.name = st.bug_name + AND bugs.name NOT LIKE 'DSA-%' + GROUP BY bugs.name, bugs.description, sp.name) + WHERE vulnerable = ? AND unimportant = ? + ORDER BY name""", (maintainer, vulnerable, unimportant)) + def getTODOs(self, cursor=None, hide_check=False): """Returns a list of pairs (BUG-NAME, DESCRIPTION).""" if cursor is None: Index: bin/tracker_service.py =================================================================== --- bin/tracker_service.py (revision 18462) +++ bin/tracker_service.py (working copy) @@ -132,6 +132,7 @@ self.register('*', self.page_object) self.register('redirect/*', self.page_redirect) self.register('source-package/*', self.page_source_package) + self.register('maintainer/*', self.page_maintainer) self.register('status/release/oldstable', self.page_status_release_oldstable) self.register('status/release/stable', self.page_status_release_stable) @@ -278,6 +279,9 @@ if self.db.isSourcePackage(c, obj): return RedirectResult(self.url_source_package(url, obj, full=True)) + if '@' in obj: + return RedirectResult(self.url_maintainer(url, obj, full=True)) + return self.page_not_found(url, obj) def page_bug(self, url, name, redirect): @@ -576,6 +580,49 @@ replacement='No known security announcements.') ]) + def page_maintainer(self, path, params, url): + maintainer = path[0] + + def gen_versions(): + for (releases, version) in self.db.getSourcePackageVersions( + self.db.cursor(), maintainer): + yield ', '.join(releases), version + def gen_bug_list(lst): + for (pkg, bug, description) in lst: + yield self.make_xref(url, pkg), self.make_xref(url, bug), description + + caption=('Package','Bug', 'Description') + return self.create_page( + url, 'Information on maintainer ' + maintainer, + [make_menu(lambda x: x, + (self.url_ddpo(url, maintainer), + maintainer + ' in the Debian Developer\'s Packages Overview'), + (self.url_debian_bug_maintainer(url, maintainer), + maintainer + ' in the Bug Tracking System'), + (self.url_buildd(url, maintainer), + maintainer + ' in the Buildd status page'), + (self.url_portfolio(url, maintainer), + maintainer + ' in the Debian Member Portfolio Service')), + + H2('Open issues'), + make_table(gen_bug_list(self.db.getBugsForMaintainer + (self.db.cursor(), maintainer, True, False)), + caption=caption, + replacement='No known open issues.'), + + H2('Open unimportant issues'), + make_table(gen_bug_list(self.db.getBugsForMaintainer + (self.db.cursor(), maintainer, True, True)), + caption=caption, + replacement='No known unimportant issues.'), + + H2('Resolved issues'), + make_table(gen_bug_list(self.db.getBugsForMaintainer + (self.db.cursor(), maintainer, False, True)), + caption=caption, + replacement='No known resolved issues.') + ]) + def page_status_release_stable_oldstable(self, release, params, url): assert release in ('stable', 'oldstable') @@ -1181,7 +1228,7 @@ status=status) def make_search_button(self, url): - return FORM("Search for package or bug name: ", + return FORM("Search for maintainer email or package or bug name: ", INPUT(type='text', name='query', onkeyup="onSearch(this.value)", onmousemove="onSearch(this.value)"), @@ -1223,17 +1270,34 @@ def url_debian_bug_pkg(self, url, debian): return url.absolute("http://bugs.debian.org/cgi-bin/pkgreport.cgi";, pkg=debian) + def url_debian_bug_maintainer(self, url, maintainer): + return url.absolute("http://bugs.debian.org/cgi-bin/pkgreport.cgi";, + maint=maintainer) def url_pts(self, url, package): return url.absolute("http://packages.qa.debian.org/common/index.html";, src=package) def url_testing_status(self, url, package): return url.absolute("http://release.debian.org/migration/testing.pl";, package=package) + def url_portfolio(self, url, maintainer): + return url.absolute("http://portfolio.debian.net/result";, + email=maintainer) + def url_ddpo(self, url, maintainer): + return url.absolute("http://qa.debian.org/developer.php";, + email=maintainer) + def url_buildd(self, url, maintainer): + return url.absolute("https://buildd.debian.org/status/package.php";, + p=maintainer, compact='compact', comaint='yes') def url_source_package(self, url, package, full=False): if full: return url.scriptRelativeFull("source-package/" + package) else: return url.scriptRelative("source-package/" + package) + def url_maintainer(self, url, maintainer, full=False): + if full: + return url.scriptRelativeFull("maintainer/" + maintainer) + else: + return url.scriptRelative("maintainer/" + maintainer) def make_xref(self, url, name): return A(url.scriptRelative(name), name)Attachment: signature.asc
Description: This is a digitally signed message part
--- End Message ---
--- Begin Message ---
- To: Raphael Geissert <atomo64@gmail.com>, 507303@bugs.debian.org
- Cc: 507303-done@bugs.debian.org
- Subject: Re: Bug#507303: security-tracker: please provide a per-maintainer report
- From: Salvatore Bonaccorso <carnil@debian.org>
- Date: Thu, 6 Jun 2019 21:56:55 +0200
- Message-id: <[🔎] 20190606195655.GA12735@eldamar.local>
- In-reply-to: <200811291510.32926.atomo64@gmail.com>
- References: <200811291510.32926.atomo64@gmail.com>
Hi Raphael, On Sat, Nov 29, 2008 at 03:10:21PM -0600, Raphael Geissert wrote: > Package: security-tracker > Severity: wishlist > > It would be great to provide such report, as to have a link to it on > the DDPO. While looking at some open bugs for the security-tracker I encountered this one. I think the Debian maintainer dashboard might be a better option to include this overview (actually it does schon already open security issues in one maintainers view). Regards, Salvatore
--- End Message ---