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.

10 files changed, 422 insertions, 0 deletions

diff --git a/media/css/layout.css b/media/css/layout.css
index 7850f028..345348e7 100644
--- a/media/css/layout.css
+++ b/media/css/layout.css
@@ -694,3 +694,7 @@ TABLE.pgGenericFormTable TR TD UL {
 img {
   border: 0;
 }
+
+span.cvssvector {
+   font-size: smaller;
+}
diff --git a/pgweb/core/models.py b/pgweb/core/models.py
index d9aeb380..20269bcb 100644
--- a/pgweb/core/models.py
+++ b/pgweb/core/models.py
@@ -72,6 +72,7 @@ class Version(models.Model):
 	def purge_urls(self):
 		yield '/$'
 		yield '/support/versioning'
+		yield '/support/security'
 		yield '/docs/$'
 		yield '/docs/manuals'
 		yield '/about/featurematrix/$'
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'))
diff --git a/pgweb/settings.py b/pgweb/settings.py
index c79ca50f..91a8bede 100644
--- a/pgweb/settings.py
+++ b/pgweb/settings.py
@@ -110,6 +110,7 @@ INSTALLED_APPS = [
     'pgweb.contributors',
     'pgweb.profserv',
     'pgweb.lists',
+    'pgweb.security',
     'pgweb.sponsors',
     'pgweb.survey',
     'pgweb.misc',
@@ -159,6 +160,7 @@ LIST_ACTIVATORS=()								       # Servers that can activate lists
 ARCHIVES_SEARCH_SERVER="archives.postgresql.org"       # Where to post REST request for archives search
 FRONTEND_SMTP_RELAY="magus.postgresql.org"             # Where to relay user generated email
 OAUTH={}                                               # OAuth providers and keys
+PGDG_ORG_ID=-1                                         # id of the PGDG organisation entry
 
 # Load local settings overrides
 from settings_local import *
diff --git a/templates/security/security.html b/templates/security/security.html
new file mode 100644
index 00000000..330de4f4
--- /dev/null
+++ b/templates/security/security.html
@@ -0,0 +1,145 @@
+{%extends "base/page.html"%}
+{%block title%}Security Information{%endblock%}
+{%block contents%}
+
+<h1>Security Information</h1>
+
+<p>
+If you wish to report a new security vulnerability in PostgreSQL, please
+send an email to
+<a href="mailto:security@postgresql.org">security@postgresql.org</a>.
+For reporting non-security bugs, please see the <a href="/account/submitbug">Report a Bug</a> page.
+</p>
+
+{%if version and not version.supported%}
+<h1>UNSUPPORTED VERSION</h1>
+<p>
+You are currently viewing security issues for an unsupported version. If
+you are still using PostgreSQL version {{version}}, you should upgrade as
+soon as possible!
+</p>
+{%else%}
+<p>
+
+The PostgreSQL Global Development Group (PGDG) takes security seriously,
+allowing our users to place their trust in the web sites and applications
+built around PostgreSQL. Our approach covers fail-safe configuration options,
+a secure and robust database server as well as good integration with other
+security infrastructure software.
+</p>
+
+<p>
+PostgreSQL security updates are primarily made available as <a href="/support/versioning">minor version</a>
+upgrades. You are always advised to use the latest minor version available,
+as it will likely also contain other non-security related fixes. All known
+security issues are always fixed in the next major release, when it comes out.
+</p>
+
+<p>
+PGDG believes that accuracy, completeness and availability of security
+information is essential for our users. We choose to pool all information on
+this one page, allowing easy searching for vulnerabilities by a range of
+criteria.
+</p>
+
+<p>
+Vulnerabilities list which major releases they were present
+in, and which version they are fixed in for each. If the vulnerability
+was exploitable without a valid login, this is also stated. They also
+list a vulnerability class, but we urge all users to read the description
+to determine if the bug affects specific installations or not.
+</p>
+
+{%endif%}
+
+<h2>Known security issues in {%if version%}version {{version.numtree}}{%else%}all supported versions{%endif%}</h2>
+<p>
+You can filter the view of patches to show just the version:<br/>
+{%for v in supported%}
+<a href="/support/security/{{v.numtree}}/">{{v.numtree}}</a>{%if not forloop.last%} -{%endif%}
+{%endfor%}
+- <a href="/support/security/">all</a>
+</p>
+
+<div class="tblBasic">
+ <table border="0" cellpadding="0" cellspacing="0" class="tblBasicGrey">
+  <tr>
+   <th class="colFirst">Reference</th>
+   <th class="colMid">Affected<br/>versions</th>
+   <th class="colMid">Fixed in</th>
+   <th class="colMid" align="center"><a href="#comp">Component</a> and<br/>CVSS v3 Base Score</th>
+   <th class="colLast">Description</th>
+  </tr>
+{%for p in patches%}
+  <tr valign="top">
+    <td class="colFirst">
+{%if p.cve%}<nobr><a href="https://access.redhat.com/security/cve/CVE-{{p.cve}}">CVE-{{p.cve}}</a></nobr><br/>{%endif%}
+{%if p.newspost%}<a href="/about/news/{{p.newspost.id}}/">Announcement</a><br/>{%endif%}
+    </td>
+    <td class="colMid">{{p.affected|join:", "}}</td>
+    <td class="colMid">{{p.fixed|join:", "}}</td>
+    <td class="colMid" align="center">
+{{p.component}}<br/>
+{%if p.cvssscore >= 0%}<a href="https://nvd.nist.gov/vuln-metrics/cvss/v3-calculator?vector={{p.cvssvector}}">{{p.cvssscore}}</a><br/><span class="cvssvector">{{p.cvssvector}}</span>
+{%else%}Legacy: {{p.legacyscore}}{%endif%}</td>
+    <td class="colLast">{{p.description}}{%if p.detailslink%}<br/><br/><a href="{{p.detailslink}}">more details</a>{%endif%}</td>
+  </tr>
+{%endfor%}
+
+ </table>
+</div>
+
+<h3>Unsupported versions</h3>
+<p>
+  You can also view archived security patches for unsupported versions:<br/>
+{%for v in unsupported%}
+<a href="/support/security/{{v.numtree}}/">{{v.numtree}}</a>{%if not forloop.last%} -{%endif%}
+{%endfor%}
+</p>
+
+
+<a name="comp"></a>
+<h2>Components</h2>
+<p>
+The following component references are used in the above table:
+</p>
+
+<div class="tblBasic">
+ <table border="0" cellpadding="0" cellspacing="0" class="tblBasicGrey">
+  <tr>
+   <th class="colFirst">Component</th>
+
+   <th class="colLast">Description</th>
+  </tr>
+
+  <tr valign="top">
+   <td class="colFirst">core server</td>
+   <td class="colLast">This vulnerability exists in the core server product.</td>
+  </tr>
+
+  <tr valign="top">
+   <td class="colFirst">client</td>
+   <td class="colLast">This vulnerability exists in a client library or client application only.</td>
+  </tr>
+
+  <tr valign="top">
+   <td class="colFirst">contrib module</td>
+   <td class="colLast">This vulnerability exists in a contrib module. Contrib modules are not installed by default when PostgreSQL is installed from source. They may be installed by binary packages.</td>
+
+  </tr>
+
+  <tr valign="top">
+   <td class="colFirst">client contrib module</td>
+   <td class="colLast">This vulnerability exists in a contrib module used on the client only.</td>
+  </tr>
+
+  <tr valign="top" class="lastrow">
+   <td class="colFirst">packaging</td>
+   <td class="colLast">This vulnerability exists in PostgreSQL binary packaging, e.g. an installer or RPM.</td>
+  </tr>
+
+ </table>
+</div>
+
+
+{%endblock%}