Database:ify the list of security patches

This finally moves the patches into the db, which makes it a lot easier
to filter patches in the views.

It also adds the new way of categorising patches, which is assigning
them a CVSSv3 score.

For now, there are no public views to this, and the old static pages
remain. This is so we can backfill all existing security patches before
we make it public.

6 files changed, 270 insertions, 0 deletions

diff --git a/pgweb/security/__init__.py b/pgweb/security/__init__.py
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/pgweb/security/__init__.py
diff --git a/pgweb/security/admin.py b/pgweb/security/admin.py
new file mode 100644
index 00000000..7abe19c7
--- /dev/null
+++ b/pgweb/security/admin.py
@@ -0,0 +1,69 @@
+from django.contrib import admin
+from django import forms
+from django.db import models
+from django.core.validators import ValidationError
+from django.conf import settings
+
+from pgweb.core.models import Version
+from pgweb.news.models import NewsArticle
+from models import SecurityPatch, SecurityPatchVersion
+
+class VersionChoiceField(forms.ModelChoiceField):
+	def label_from_instance(self, obj):
+		return obj.numtree
+
+class SecurityPatchVersionAdminForm(forms.ModelForm):
+	model = SecurityPatchVersion
+	version = VersionChoiceField(queryset=Version.objects.filter(tree__gt=0), required=True)
+
+class SecurityPatchVersionAdmin(admin.TabularInline):
+	model = SecurityPatchVersion
+	extra = 2
+	form = SecurityPatchVersionAdminForm
+
+class SecurityPatchForm(forms.ModelForm):
+	model = SecurityPatch
+	newspost = forms.ModelChoiceField(queryset=NewsArticle.objects.filter(org=settings.PGDG_ORG_ID), required=False)
+
+	def clean(self):
+		d = super(SecurityPatchForm, self).clean()
+		vecs = [v for k,v in d.items() if k.startswith('vector_') and k != 'vector_other']
+		empty = [v for v in vecs if v == '']
+		if len(empty) != len(vecs) and len(empty) != 0:
+			for k in d.keys():
+				if k.startswith('vector_') and k != 'vector_other':
+					self.add_error(k, 'Either specify all vector values or none')
+		if d['vector_other'] and len(empty) > 0:
+			self.add_error('vector_other', 'Cannot specify other vectors without base vectors')
+		return d
+
+class SecurityPatchAdmin(admin.ModelAdmin):
+	form = SecurityPatchForm
+	exclude = ['cvenumber', ]
+	inlines = (SecurityPatchVersionAdmin, )
+	list_display = ('cve', 'public', 'cvssscore', 'legacyscore', 'cvssvector', 'description')
+	actions = ['make_public', 'make_unpublic']
+
+	def cvssvector(self, obj):
+		if not obj.cvssvector:
+			return ''
+		return '<a href="https://nvd.nist.gov/vuln-metrics/cvss/v3-calculator?vector={0}">{0}</a>'.format(
+			obj.cvssvector)
+	cvssvector.allow_tags = True
+	cvssvector.short_description = "CVSS vector link"
+
+	def cvssscore(self, obj):
+		return obj.cvssscore
+	cvssscore.short_description = "CVSS score"
+
+	def make_public(self, request, queryset):
+		self.do_public(queryset, True)
+	def make_unpublic(self, request, queryset):
+		self.do_public(queryset, False)
+	def do_public(self, queryset, val):
+		# Intentionally loop and do manually, so we generate change notices
+		for p in queryset.all():
+			p.public=val
+			p.save()
+
+admin.site.register(SecurityPatch, SecurityPatchAdmin)
diff --git a/pgweb/security/migrations/0001_initial.py b/pgweb/security/migrations/0001_initial.py
new file mode 100644
index 00000000..105002e7
--- /dev/null
+++ b/pgweb/security/migrations/0001_initial.py
@@ -0,0 +1,56 @@
+# -*- coding: utf-8 -*-
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+import pgweb.security.models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('news', '0003_news_tags'),
+        ('core', '0001_initial'),
+    ]
+
+    operations = [
+        migrations.CreateModel(
+            name='SecurityPatch',
+            fields=[
+                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
+                ('public', models.BooleanField(default=False)),
+                ('cve', models.CharField(blank=True, max_length=32, validators=[pgweb.security.models.cve_validator])),
+                ('cvenumber', models.IntegerField(db_index=True)),
+                ('detailslink', models.URLField(blank=True)),
+                ('description', models.TextField()),
+                ('component', models.CharField(help_text=b'If multiple components, choose the most critical one', max_length=32, choices=[(b'core server', b'Core server product'), (b'client', b'Client library or application only'), (b'contrib module', b'Contrib module only'), (b'client contrib module', b'Client contrib module only'), (b'packaging', b'Packaging, e.g. installers or RPM'), (b'other', b'Other')])),
+                ('vector_av', models.CharField(blank=True, max_length=1, verbose_name=b'Attack Vector', choices=[('N', 'Network'), ('A', 'Adjacent'), ('L', 'Local'), ('P', 'Physical')])),
+                ('vector_ac', models.CharField(blank=True, max_length=1, verbose_name=b'Attack Complexity', choices=[('L', 'Low'), ('H', 'High')])),
+                ('vector_pr', models.CharField(blank=True, max_length=1, verbose_name=b'Privileges Required', choices=[('N', 'None'), ('L', 'Low'), ('H', 'High')])),
+                ('vector_ui', models.CharField(blank=True, max_length=1, verbose_name=b'User Interaction', choices=[('N', 'None'), ('R', 'Required')])),
+                ('vector_s', models.CharField(blank=True, max_length=1, verbose_name=b'Scope', choices=[('C', 'Changed'), ('U', 'Unchanged')])),
+                ('vector_c', models.CharField(blank=True, max_length=1, verbose_name=b'Confidentiality Impact', choices=[('H', 'High'), ('L', 'Low'), ('N', 'None')])),
+                ('vector_i', models.CharField(blank=True, max_length=1, verbose_name=b'Integrity Impact', choices=[('H', 'High'), ('L', 'Low'), ('N', 'None')])),
+                ('vector_a', models.CharField(blank=True, max_length=1, verbose_name=b'Availability Impact', choices=[('H', 'High'), ('L', 'Low'), ('N', 'None')])),
+                ('legacyscore', models.CharField(blank=True, max_length=1, verbose_name=b'Legacy score', choices=[(b'A', b'A'), (b'B', b'B'), (b'C', b'C'), (b'D', b'D')])),
+                ('newspost', models.ForeignKey(blank=True, to='news.NewsArticle', null=True)),
+            ],
+            options={
+                'ordering': ('-cvenumber',),
+                'verbose_name_plural': 'Security patches',
+            },
+        ),
+        migrations.CreateModel(
+            name='SecurityPatchVersion',
+            fields=[
+                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
+                ('fixed_minor', models.IntegerField()),
+                ('patch', models.ForeignKey(to='security.SecurityPatch')),
+                ('version', models.ForeignKey(to='core.Version')),
+            ],
+        ),
+        migrations.AddField(
+            model_name='securitypatch',
+            name='versions',
+            field=models.ManyToManyField(to='core.Version', through='security.SecurityPatchVersion'),
+        ),
+    ]
diff --git a/pgweb/security/migrations/__init__.py b/pgweb/security/migrations/__init__.py
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/pgweb/security/migrations/__init__.py
diff --git a/pgweb/security/models.py b/pgweb/security/models.py
new file mode 100644
index 00000000..e4ec6563
--- /dev/null
+++ b/pgweb/security/models.py
@@ -0,0 +1,111 @@
+from django.db import models
+from django.core.validators import ValidationError
+
+import re
+
+from pgweb.core.models import Version
+from pgweb.news.models import NewsArticle
+
+import cvss
+
+vector_choices = {k:list(v.items()) for k,v in cvss.constants3.METRICS_VALUE_NAMES.items()}
+
+component_choices = (
+	('core server', 'Core server product'),
+	('client', 'Client library or application only'),
+	('contrib module', 'Contrib module only'),
+	('client contrib module', 'Client contrib module only'),
+	('packaging', 'Packaging, e.g. installers or RPM'),
+	('other', 'Other'),
+)
+
+re_cve = re.compile('^(\d{4})-(\d{4,5})$')
+def cve_validator(val):
+	if not re_cve.match(val):
+		raise ValidationError("Enter CVE in format 0000-0000 without the CVE text")
+
+def other_vectors_validator(val):
+	if val != val.upper():
+		raise ValidationError("Vector must be uppercase")
+
+	try:
+		for vector in val.split('/'):
+			k,v = vector.split(':')
+			if not cvss.constants3.METRICS_VALUES.has_key(k):
+				raise ValidationError("Metric {0} is unknown".format(k))
+			if k in ('AV', 'AC', 'PR', 'UI', 'S', 'C', 'I', 'A'):
+				raise ValidationError("Metric {0} must be specified in the dropdowns".format(k))
+			if not cvss.constants3.METRICS_VALUES[k].has_key(v):
+				raise ValidationError("Metric {0} has unknown value {1}. Valind ones are: {2}".format(
+					k,v,
+					", ".join(cvss.constants3.METRICS_VALUES[k].keys()),
+				))
+	except ValidationError, ve:
+		raise
+	except Exception, e:
+		raise ValidationError("Failed to parse vectors: %s" % e)
+
+class SecurityPatch(models.Model):
+	public = models.BooleanField(null=False, blank=False, default=False)
+	newspost = models.ForeignKey(NewsArticle, null=True, blank=True)
+	cve = models.CharField(max_length=32, null=False, blank=True, validators=[cve_validator,])
+	cvenumber = models.IntegerField(null=False, blank=False, db_index=True)
+	detailslink = models.URLField(null=False, blank=True)
+	description = models.TextField(null=False, blank=False)
+	component = models.CharField(max_length=32, null=False, blank=False, help_text="If multiple components, choose the most critical one", choices=component_choices)
+
+	versions = models.ManyToManyField(Version, through='SecurityPatchVersion')
+
+	vector_av = models.CharField(max_length=1, null=False, blank=True, verbose_name="Attack Vector", choices=vector_choices['AV'])
+	vector_ac = models.CharField(max_length=1, null=False, blank=True, verbose_name="Attack Complexity", choices=vector_choices['AC'])
+	vector_pr = models.CharField(max_length=1, null=False, blank=True, verbose_name="Privileges Required", choices=vector_choices['PR'])
+	vector_ui = models.CharField(max_length=1, null=False, blank=True, verbose_name="User Interaction", choices=vector_choices['UI'])
+	vector_s = models.CharField(max_length=1, null=False, blank=True, verbose_name="Scope", choices=vector_choices['S'])
+	vector_c = models.CharField(max_length=1, null=False, blank=True, verbose_name="Confidentiality Impact", choices=vector_choices['C'])
+	vector_i = models.CharField(max_length=1, null=False, blank=True, verbose_name="Integrity Impact", choices=vector_choices['I'])
+	vector_a = models.CharField(max_length=1, null=False, blank=True, verbose_name="Availability Impact", choices=vector_choices['A'])
+	legacyscore = models.CharField(max_length=1, null=False, blank=True, verbose_name='Legacy score', choices=(('A', 'A'),('B','B'),('C','C'),('D','D')))
+
+	purge_urls = ('/support/security/', )
+
+	def save(self, force_insert=False, force_update=False):
+		# Calculate a number from the CVE, that we can use to sort by. We need to
+		# do this, because CVEs can have 4 or 5 digit second parts...
+		if self.cve == '':
+			self.cvenumber = 0
+		else:
+			m = re_cve.match(self.cve)
+			if not m:
+				raise ValidationError("Invalid CVE, should not get here!")
+			self.cvenumber = 100000 * int(m.groups(0)[0]) + int(m.groups(0)[1])
+		super(SecurityPatch, self).save(force_insert, force_update)
+
+	def __unicode__(self):
+		return self.cve
+
+	@property
+	def cvssvector(self):
+		if not self.vector_av:
+			return None
+		s = 'AV:{0}/AC:{1}/PR:{2}/UI:{3}/S:{4}/C:{5}/I:{6}/A:{7}'.format(
+			self.vector_av, self.vector_ac, self.vector_pr, self.vector_ui,
+			self.vector_s, self.vector_c, self.vector_i, self.vector_a)
+		return s
+
+	@property
+	def cvssscore(self):
+		try:
+			c = cvss.CVSS3("CVSS:3.0/" + self.cvssvector)
+			return c.base_score
+		except Exception, e:
+			return -1
+
+	class Meta:
+		verbose_name_plural = 'Security patches'
+		ordering = ('-cvenumber',)
+
+class SecurityPatchVersion(models.Model):
+	patch = models.ForeignKey(SecurityPatch, null=False, blank=False)
+	version = models.ForeignKey(Version, null=False, blank=False)
+	fixed_minor = models.IntegerField(null=False, blank=False)
+
diff --git a/pgweb/security/views.py b/pgweb/security/views.py
new file mode 100644
index 00000000..0dfb8f0d
--- /dev/null
+++ b/pgweb/security/views.py
@@ -0,0 +1,34 @@
+from django.shortcuts import render_to_response, get_object_or_404
+
+from pgweb.util.contexts import NavContext
+
+from pgweb.core.models import Version
+from models import SecurityPatch
+
+def _list_patches(request, filt):
+	patches = SecurityPatch.objects.raw("SELECT p.*, array_agg(CASE WHEN v.tree >= 10 THEN v.tree::int ELSE v.tree END ORDER BY v.tree) AS affected, array_agg(CASE WHEN v.tree >= 10 THEN v.tree::int ELSE v.tree END || '.' || fixed_minor ORDER BY v.tree) AS fixed FROM security_securitypatch p INNER JOIN security_securitypatchversion sv ON p.id=sv.patch_id INNER JOIN core_version v ON v.id=sv.version_id WHERE p.public AND {0} GROUP BY p.id".format(filt))
+
+	return render_to_response('security/security.html', {
+		'patches': patches,
+		'supported': Version.objects.filter(supported=True),
+		'unsupported': Version.objects.filter(supported=False, tree__gt=0),
+	}, NavContext(request, 'support'))
+
+def index(request):
+	# Show all supported versions
+	return _list_patches(request, "v.supported")
+
+def version(request, numtree):
+	version = get_object_or_404(Version, tree=numtree)
+	# It's safe to pass in the value since we get it from the module, not from
+	# the actual querystring.
+	return _list_patches(request, "v.id={0}".format(version.id))
+
+	patches = SecurityPatch.objects.filter(public=True, versions=version).distinct()
+
+	return render_to_response('security/security.html', {
+		'patches': patches,
+		'supported': Version.objects.filter(supported=True),
+		'unsupported': Version.objects.filter(supported=False, tree__gt=0),
+		'version': version,
+	}, NavContext(request, 'support'))