pg.ddx.io  pgsql-www@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Introduce contributor badges - v14
9+ messages / 4 participants
[nested] [flat]

* Re: Introduce contributor badges - v14
@ 2026-10-16 17:21 Christoph Berg <myon@debian.org>
  2026-10-16 17:22 ` Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
  2026-11-04 19:38 ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
  0 siblings, 2 replies; 9+ messages in thread

From: Christoph Berg @ 2026-10-16 17:21 UTC (permalink / raw)
  To: Dave Page <dpage@pgadmin.org>; Daniel Gustafsson <daniel@yesql.se>; +Cc: Magnus Hagander <magnus@hagander.net>; Jonathan S. Katz <jkatz@postgresql.org>; pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>; Melanie Plageman <melanieplageman@gmail.com>; PostgreSQL Contributors <contributors@lists.postgresql.org>

The v14 patch series addresses Dave's review, Daniels 150x150 comment
and includes Magnus feedback:

Re: Dave Page
> 1. **Removing a holder purges nothing for that person.** `BadgeForm.save`
> calls `badge.holders.remove()` (`pgweb/contributors/forms.py`). That
> deletes the through-table rows without any `post_save`, and pgweb only
> hooks `post_save` (`pgweb/util/signals.py:169`). The badge page still gets
> purged, because `simple_form` saves the badge afterwards. The removed
> person's `/community/people/<username>/` page keeps showing the badge.

This is v14-0007-Purge-badge-pages-and-holder-profiles-through-xk.patch

> 2. **Deletes never purge.** This applies to deleting a `Badge`,
> `Badgeholder` or `Contributor` in the admin. The rest of pgweb has the same
> gap, but it matters more here: a deleted contributor's public profile page,
> which can include their email, stays cached. If someone asks for their
> profile to be removed, it isn't really gone.

v14-0008-Purge-on-delete-as-well-as-on-save.patch

> 3. **Approving or editing a badge doesn't purge its holders' profile
> pages.** `Badge.purge_urls` says profiles "get refreshed when their holder
> rows are edited", but approval, unapproval, a title change and an image
> added or removed all leave the profiles stale. After unapproval, profiles
> keep linking to `/community/badge/<slug>/image/`, which now returns a 404
> that Varnish may also cache. The fix is xkeys, which the docs already
> describe as the better option: tag the badge, people and profile responses
> with something like `pgbadge_<id>` and give `Badge` a `purge_xkeys`. That
> also replaces the per-holder URL loops.

Part of v14-0007-Purge-badge-pages-and-holder-profiles-through-xk.patch

> 4. **The image URL never changes when the image does.**
> `/community/badge/<slug>/image/` has no file extension and no version in
> it. The docs say Varnish applies a longer policy to images "because they
> are considered never to change". Varnish itself is purged, but if the
> Varnish configuration (VCL) also sends long browser cache headers for
> images, browsers will keep showing the old image. The VCL isn't in this
> repo, so I can't check which behaviour applies. Adding a short hash of the
> image to the URL, as `?v=` or in the path, avoids the question.

We rejected that in the other subthread.

> ## Database and app server
> 
> 5. **The `has_image` annotation reads every image out of TOAST**,
> PostgreSQL's out-of-line storage for large values
> (`BadgeManager.with_has_image`). The comment says the value is "never
> transferred", which is true for the network, but `imagedata > ''` compares
> the full bytea, so PostgreSQL has to fetch and reassemble the whole value.
> A test of 200 rows of about 96 KB each on the local database showed the
> comparison reading about 3,000 buffers in 31 ms, whilst `octet_length(img)
> > 0` read 2 buffers in 0.04 ms, because it only reads the TOAST header.
> Switching to `Length('imagedata') > 0` is a one-line fix. It runs on every
> cache miss of the people page (up to 200 badges) and on every account
> profile page load, which is never cached.

v14-0002-Determine-has-image-without-full-detoasting.patch

> 6. **Oversized images are decoded in full (a "decompression bomb").**
> `ImageBinaryField.to_python` decodes the entire image with Pillow before
> rescaling. A PNG under 1 MB can still be about 170 megapixels before Pillow
> refuses it, which is several hundred MB of RAM plus a bicubic resize on the
> app server. `ModelForm` full-clean also re-parses the stored image on every
> badge edit, even when the image hasn't changed. Only managers of an
> approved organisation can upload, so the risk is moderate, but checking
> `img.size` before decoding would be cheap.

v14-0003-Reject-images-with-wrong-size-before-loading-the.patch
v14-0004-Don-t-re-parse-the-stored-badge-image-on-every-e.patch

> 7. **Minor, and only on cache misses:**
>    - `badge.html` uses `holders|length` and then `holders.all`, which runs
> the holder query and its two prefetches a second time.

v14-0005-Don-t-run-the-badge-holder-query-twice.patch

>    - `select_related` would do better than `prefetch_related` for the
> foreign-key lookups in `peoplelist` and `badge_view`.

v14-0006-Use-select_related-for-the-badge-holder-and-peop.patch

>    - `badge_image` fetches the image and computes its MD5 before it checks
> `If-None-Match`; storing the hash would avoid both.

v14-0009-Store-the-badge-image-md5-in-the-database.patch

Since 0009 is touching the database, we can now pull up the staged
extended badge title length:
v14-0010-Extend-badge-title-to-255-chars.patch

Both together share a migration file that is part of
v14-0011-Migrate-imagehash-and-serve-badge-images-from-it.patch

If we decide we don't want the MD5 change, we can postpone these
three.

>    - Every account profile save that has a contributor record purges
> `/community/contributors/`, `/community/people/` and all of that user's
> badge pages, even when nothing changed.

v14-0012-Don-t-re-purge-contributor-pages-on-a-no-op-acco.patch


Re: Daniel Gustafsson
> +1 on restricting the upload to 150x150 and make the user responsible for the
> badge image looking good rather than rescaling.

That's v14-0001-Require-user-supplied-badges-to-be-150x150.patch

v14-0010-Extend-badge-title-to-255-chars.patch

Christoph





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Introduce contributor badges - v14
  2026-10-16 17:21 Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
@ 2026-10-16 17:22 ` Christoph Berg <myon@debian.org>
  2026-10-16 18:22   ` Re: Introduce contributor badges - v14 Dave Page <dpage@pgadmin.org>
  1 sibling, 1 reply; 9+ messages in thread

From: Christoph Berg @ 2026-10-16 17:22 UTC (permalink / raw)
  To: Dave Page <dpage@pgadmin.org>; Daniel Gustafsson <daniel@yesql.se>; +Cc: Magnus Hagander <magnus@hagander.net>; Jonathan S. Katz <jkatz@postgresql.org>; pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>; Melanie Plageman <melanieplageman@gmail.com>; PostgreSQL Contributors <contributors@lists.postgresql.org>

Re: To Dave Page
> The v14 patch series addresses Dave's review, Daniels 150x150 comment
> and includes Magnus feedback:

*cough*

Christoph

Attachments: download all (12 files, .tar.gz)

v14-0001-Require-user-supplied-badges-to-be-150x150.patch (text/x-diff, 3.2K) download From e94420e805c7a6c32a71306a26a6a2b7e55a190b Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 15:22:20 +0200 Subject: [PATCH v14 01/12] Require user-supplied badges to be 150x150 Instead of rescaling on the server side, require the badge image to already be exactly 150x150. This avoids ugly rescaling effects, and overly large input images cannot eat memory for the rescaling operation. --- ..._alter_contributor_ctype_alter_contributor_email_and_more.py | 2 +- pgweb/contributors/models.py | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/pgweb/contributors/migrations/0005_alter_contributor_ctype_alter_contributor_email_and_more.py b/pgweb/contributors/migrations/0005_alter_contributor_ctype_alter_contributor_email_and_more.py index 7041f702..1c676489 100644 --- a/pgweb/contributors/migrations/0005_alter_contributor_ctype_alter_contributor_email_and_more.py +++ b/pgweb/contributors/migrations/0005_alter_contributor_ctype_alter_contributor_email_and_more.py @@ -82,7 +82,7 @@ class Migration(migrations.Migration): "imagedata", pgweb.util.fields.ImageBinaryField( blank=True, - help_text="Square image, will be auto-scaled to 150x150. JPEG or PNG, up to 1 MB. When left blank, the PostgreSQL logo will be used.", + help_text="Square image, must be 150x150. JPEG or PNG, up to 1 MB. When left blank, the PostgreSQL logo will be used.", max_length=1000000, null=True, verbose_name="Image", diff --git a/pgweb/contributors/models.py b/pgweb/contributors/models.py index 507cde65..62d5c3a0 100644 --- a/pgweb/contributors/models.py +++ b/pgweb/contributors/models.py @@ -102,7 +102,7 @@ class Badge(TwostateModerateModel): slug = models.SlugField(max_length=64, null=False, blank=False, unique=True, help_text='URL-friendly identifier for the badge page. Generated from the title when the badge is created, and kept fixed after that.') description = models.TextField(null=True, blank=True, help_text='What did the people do who contributed here?') url = models.URLField(max_length=100, null=True, blank=True, verbose_name='Contribution URL', help_text='URL for this contribution, e.g. the conference homepage. (Leave blank when there is no URL.)') - imagedata = ImageBinaryField(max_length=1000000, resolution=(150, 150), auto_scale=True, blank=True, null=True, verbose_name="Image", help_text="Square image, will be auto-scaled to 150x150. JPEG or PNG, up to 1 MB. When left blank, the PostgreSQL logo will be used.") + imagedata = ImageBinaryField(max_length=1000000, resolution=(150, 150), auto_scale=False, blank=True, null=True, verbose_name="Image", help_text="Square image, must be 150x150. JPEG or PNG, up to 1 MB. When left blank, the PostgreSQL logo will be used.") contact = models.CharField(max_length=100, null=True, blank=True, verbose_name='Contact address', help_text='Contact address (email, URL, other) for people who want to be added as badge holder') holders = models.ManyToManyField(User, through="Badgeholder", through_fields=("badge", "user"), blank=True) -- 2.53.0
v14-0002-Determine-has-image-without-full-detoasting.patch (text/x-diff, 1.8K) download From b303b4c1615b9dc8968c61077df6b8f53965855f Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 15:33:29 +0200 Subject: [PATCH v14 02/12] Determine "has image" without full detoasting The with_has_image function claimed it wouldn't transfer the whole image, but it was still fully reading it on the database side. Switch to looking at the length only. --- pgweb/contributors/models.py | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) diff --git a/pgweb/contributors/models.py b/pgweb/contributors/models.py index 62d5c3a0..e02a896d 100644 --- a/pgweb/contributors/models.py +++ b/pgweb/contributors/models.py @@ -4,6 +4,8 @@ import re from django.core.exceptions import ValidationError from django.db import models from django.db.models import Case, When +from django.db.models.functions import Length +from django.db.models.lookups import GreaterThan from django.contrib.auth.models import User from django.template.defaultfilters import slugify from django.utils.safestring import mark_safe @@ -87,10 +89,11 @@ class BadgeManager(models.Manager): def with_has_image(self): # Annotate the image-fallback decision so views and templates can - # avoid touching imagedata. The expression is evaluated SQL-side, - # so the (up to 1 MB) value itself is never transferred. + # avoid touching imagedata at all. LENGTH() is computed from the + # TOAST header, whereas comparing the value (imagedata > '') would + # first have to fetch and reassemble the whole (up to 1 MB) field. return self.get_queryset().annotate(has_image=Case( - When(imagedata__gt=b'', then=True), + When(GreaterThan(Length('imagedata'), 0), then=True), default=False, output_field=models.BooleanField(), )) -- 2.53.0
v14-0003-Reject-images-with-wrong-size-before-loading-the.patch (text/x-diff, 3.7K) download From baa597f19bd4a7276cd4cbfba0233f3a1d451625 Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 15:48:31 +0200 Subject: [PATCH v14 03/12] Reject images with wrong size before loading them fully The old code was fully materializing the image in memory before checking the size constraints, so a PNG "fork bomb" could still use a lot of bitmap memory even when it would be rejected at the end. Check the header first, and only then load the image. --- pgweb/util/fields.py | 52 ++++++++++++++++++++++++++------------------ 1 file changed, 31 insertions(+), 21 deletions(-) diff --git a/pgweb/util/fields.py b/pgweb/util/fields.py index baf4bed6..da99b5e7 100644 --- a/pgweb/util/fields.py +++ b/pgweb/util/fields.py @@ -5,7 +5,7 @@ from .forms import ImageBinaryFormField import io -from PIL import ImageFile +from PIL import Image from pgweb.util.image import rescale_image, apply_exif_orientation, EXIF_ORIENTATION_TAG @@ -49,32 +49,42 @@ class ImageBinaryField(models.Field): v = bytes(value) else: v = value + try: - p = ImageFile.Parser() - p.feed(v) - p.close() - img = p.image + # Opening parses only the header, so the format and dimensions can + # be checked before any pixel data is decoded. + img = Image.open(io.BytesIO(v)) except Exception as e: - raise ValidationError("Could not parse image: %s" % e) + raise ValidationError("Could not parse image") if img.format.upper() not in ('JPEG', 'PNG'): raise ValidationError("Only JPEG or PNG files are allowed") - # Bake EXIF orientation in; re-encode only when actually rotated so - # untouched JPEGs are not needlessly recompressed. - if img.getexif().get(EXIF_ORIENTATION_TAG, 1) != 1: - fmt = img.format - img = apply_exif_orientation(img) - saver = io.BytesIO() - img.save(saver, format=fmt) - value = saver.getvalue() - - if self.resolution: - if img.size[0] != self.resolution[0] or img.size[1] != self.resolution[1]: - if self.auto_scale: - value = rescale_image(img, self.resolution, centered=True) - else: - raise ValidationError("Image size must be {}x{}".format(*self.resolution)) + if self.resolution and not self.auto_scale and img.size != tuple(self.resolution): + # Reject on the header dimensions, so an oversized (or + # decompression-bomb) upload is rejected without decoding its + # pixels. + raise ValidationError("Image size must be {}x{}".format(*self.resolution)) + + try: + # Load the pixels. Only images that will be kept (possibly + # rescaled) reach this point, so the decode below is bounded. + img.load() + + # Bake EXIF orientation in; re-encode only when actually rotated so + # untouched JPEGs are not needlessly recompressed. + if img.getexif().get(EXIF_ORIENTATION_TAG, 1) != 1: + fmt = img.format + img = apply_exif_orientation(img) + saver = io.BytesIO() + img.save(saver, format=fmt) + value = saver.getvalue() + + if self.resolution and img.size != tuple(self.resolution): + # auto_scale; the not-auto_scale case was rejected above. + value = rescale_image(img, self.resolution, centered=True) + except Exception as e: + raise ValidationError("Could not parse image") return value -- 2.53.0
v14-0004-Don-t-re-parse-the-stored-badge-image-on-every-e.patch (text/x-diff, 2.2K) download From 442841d5545294c0f6ef965ceb7aecdb2691af6c Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 15:55:34 +0200 Subject: [PATCH v14 04/12] Don't re-parse the stored badge image on every edit BadgeForm includes imagedata, so ModelForm._post_clean() validated it through full_clean() on every save, and ImageBinaryField.to_python() parsed and decoded the stored image even when the edit did not touch it. Now that the field only accepts exact 150x150 images that is a small decode, but it is needless work on every badge edit, and it would be expensive for any larger row stored earlier while auto_scale was on. Exclude imagedata from model validation unless a new file was uploaded. self.files holds only newly uploaded files, which is exactly the case that needs validating. changed_data cannot be used for this: for a file field has_changed() compares the stored bytes against the empty value, so an unchanged file always counts as changed. The value is still written back through construct_instance() / save_form_data(), so edits that keep or clear the image behave as before. --- pgweb/contributors/forms.py | 10 ++++++++++ 1 file changed, 10 insertions(+) diff --git a/pgweb/contributors/forms.py b/pgweb/contributors/forms.py index 5ce44557..e73c5294 100644 --- a/pgweb/contributors/forms.py +++ b/pgweb/contributors/forms.py @@ -57,6 +57,16 @@ class BadgeForm(forms.ModelForm): # remove the holders fieldset self.fieldsets = [fs for fs in self.fieldsets if fs['id'] != 'holders'] + def _get_validation_exclusions(self): + exclude = super()._get_validation_exclusions() + # The stored image was already validated when it was uploaded, so + # don't re-parse and re-decode it on an edit that doesn't replace it. + # self.files holds only newly uploaded files, unlike changed_data + # (FileField.has_changed reports an unchanged file as changed). + if 'imagedata' not in self.files: + exclude.add('imagedata') + return exclude + def clean_add_holder(self): if self.cleaned_data['add_holder']: for u in self.cleaned_data['add_holder'].split(): -- 2.53.0
v14-0005-Don-t-run-the-badge-holder-query-twice.patch (text/x-diff, 1.7K) download From d6cc17f44a9fc42cc137e2f974fe1ef5b22fed22 Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 15:58:36 +0200 Subject: [PATCH v14 05/12] Don't run the badge holder query twice badge.html used {{ holders|length }} to choose between the compact and the list layout and then iterated {{ holders.all }}. QuerySet.all() returns a new clone, so the second use re-ran the holder query together with its two prefetches (user and user__contributor): 6 queries instead of 3 per badge page. Iterate the holders queryset directly instead. The length filter has already evaluated and cached it, so the loop reuses that cache. --- templates/contributors/badge.html | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/templates/contributors/badge.html b/templates/contributors/badge.html index 7ea0f1fd..87d4b615 100644 --- a/templates/contributors/badge.html +++ b/templates/contributors/badge.html @@ -18,12 +18,12 @@ {%if holders%} <h2>Badge Holders</h2> {%if holders|length > 50%} - {%for u in holders.all %} + {%for u in holders %} <nobr{%if u.date_retired%} class="badgeholder-retired"{%endif%}><a href="/community/people/{{u.user}}/">{{u.user.contributor.firstname}} {{u.user.contributor.lastname}}</a> {%if not forloop.last%}&middot;{%endif%}</nobr> {%endfor%} {%else%} <ul> - {%for u in holders.all%} + {%for u in holders%} <li{%if u.date_retired%} class="badgeholder-retired"{%endif%}><a href="/community/people/{{u.user}}/">{{u.user.contributor.firstname}} {{u.user.contributor.lastname}}</a>{%if u.date_retired%} (retired from this activity){%endif%}</li> {%endfor%} </ul> -- 2.53.0
v14-0006-Use-select_related-for-the-badge-holder-and-peop.patch (text/x-diff, 2.2K) download From 236bdd067aa28f8140c0e0c9b8f3e7210ffa9b5f Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 16:01:21 +0200 Subject: [PATCH v14 06/12] Use select_related for the badge holder and people lookups peoplelist() loaded Contributors with prefetch_related('user') and badge_view() loaded Badgeholders with prefetch_related('user__contributor'). Both traverse a forward foreign key (Contributor.user, Badgeholder.user) and, in badge_view(), a reverse one-to-one (user.contributor), so a join fetches them in the same query: one query instead of two for the people list, and one instead of three for a badge page. The reverse foreign key Prefetch('badge') in profile() stays a prefetch, as a reverse relation cannot be fetched with select_related(). --- pgweb/contributors/views.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/pgweb/contributors/views.py b/pgweb/contributors/views.py index 16c82b1e..3dc04a55 100644 --- a/pgweb/contributors/views.py +++ b/pgweb/contributors/views.py @@ -21,7 +21,7 @@ def peoplelist(request): # clamp lists to protect against unplanned floods. If we ever get close to # the limits, we need to make a plan on how to proceed, but we can't know # before we gain some practical experience - people = Contributor.objects.prefetch_related('user')[:2000] + people = Contributor.objects.select_related('user')[:2000] badges = Badge.objects.with_has_image().filter(approved=True)[:200] return render_pgweb(request, 'community', 'contributors/people.html', { 'people': people, @@ -33,7 +33,7 @@ def badge_view(request, slug): badge = get_object_or_404(Badge.objects.with_has_image(), slug=slug, approved=True) # badge holders are users, but we want to show only users with a contributor object here holders = Badgeholder.objects.filter(badge=badge, user__contributor__isnull=False). \ - prefetch_related('user__contributor'). \ + select_related('user', 'user__contributor'). \ order_by('user__contributor__lastname', 'user__contributor__firstname') return render_pgweb(request, 'community', 'contributors/badge.html', { 'badge': badge, -- 2.53.0
v14-0007-Purge-badge-pages-and-holder-profiles-through-xk.patch (text/x-diff, 6.2K) download From a802341468f31f28bb7f7fc1dee8dd5af6f37d80 Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 18:07:11 +0200 Subject: [PATCH v14 07/12] Purge badge pages and holder profiles through xkeys A badge change (title, image, approval, ...) has to invalidate the badge page, its image and every holder's profile page, and removing a holder has to invalidate that holder's profile. Enumerating the holder profile URLs is not practical (a badge can have hundreds of holders), so tag the responses and purge by tag instead: - badge_view and badge_image tag the response with pgbadge_<id>, - profile tags the response with pgbadge_<id> for every badge it shows, - Badge.purge_xkeys() yields pgbadge_<id>. Saving the badge then purges all of those pages. That also covers removing a holder: BadgeForm removes it and simple_form saves the badge right after. Badge.purge_urls() now only purges /community/people/: the all-people page shows every badge and every person, so it is invalidated explicitly by URL rather than tagged, since tagging it would need one tag per badge in a single response. Contributor.purge_urls() keeps purging the badge pages that list the contributor: a rename has to invalidate them, but the badge-side xkey would also clear every other holder's profile page, and tagging the badge page per holder is not an option either. --- pgweb/contributors/models.py | 20 +++++++++++++++----- pgweb/contributors/views.py | 22 +++++++++++++++++----- 2 files changed, 32 insertions(+), 10 deletions(-) diff --git a/pgweb/contributors/models.py b/pgweb/contributors/models.py index e02a896d..d3bd8eef 100644 --- a/pgweb/contributors/models.py +++ b/pgweb/contributors/models.py @@ -70,6 +70,12 @@ class Contributor(models.Model): if url: yield url if self.user_id: + # These badge pages could also be purged with an xkey, but not with + # the pgbadge_<id> one: that would clear every other holder's + # profile page as well. A per-holder pguser_<id> tag on the badge + # page is not an option either, since it would add one tag per + # holder to the response (hundreds for a popular badge). So purge + # the (few) badge pages that actually list this contributor by URL. for badge_slug in Badgeholder.objects.filter(user_id=self.user_id).values_list('badge__slug', flat=True): yield '/community/badge/%s/$' % badge_slug @@ -132,13 +138,17 @@ class Badge(TwostateModerateModel): super().save(*args, **kwargs) def purge_urls(self): - # The badge's own page and image, plus the badge grid on the - # all-people page. Holder profiles are not purged here; they get - # refreshed when their holder rows are edited. - yield '/community/badge/%s/$' % self.slug - yield '/community/badge/%s/image/$' % self.slug + # The badge page, its image and every holder profile page carry the + # pgbadge_<id> xkey (see purge_xkeys below). The all-people page shows + # every badge and has no xkey of its own, so it is purged by URL. yield '/community/people/$' + def purge_xkeys(self): + # Badge pages, badge images and holder profile pages are tagged with + # this key, so a badge change purges all of them without having to + # enumerate the holder profile URLs. + yield 'pgbadge_%d' % self.pk + @property def image_preview(self): # Rendered into the moderation preview. The image can't be served diff --git a/pgweb/contributors/views.py b/pgweb/contributors/views.py index 3dc04a55..c0faf542 100644 --- a/pgweb/contributors/views.py +++ b/pgweb/contributors/views.py @@ -35,33 +35,45 @@ def badge_view(request, slug): holders = Badgeholder.objects.filter(badge=badge, user__contributor__isnull=False). \ select_related('user', 'user__contributor'). \ order_by('user__contributor__lastname', 'user__contributor__firstname') - return render_pgweb(request, 'community', 'contributors/badge.html', { + r = render_pgweb(request, 'community', 'contributors/badge.html', { 'badge': badge, 'holders': holders, }) + # Let a badge change purge this page through its xkey. + r['xkey'] = 'pgbadge_%d' % badge.id + return r def profile(request, username): contributor = get_object_or_404(Contributor, user__username=username) badges = (Badgeholder.objects.filter(user=contributor.user, badge__approved=True) .prefetch_related(Prefetch('badge', queryset=Badge.objects.with_has_image()))) - return render_pgweb(request, 'community', 'contributors/profile.html', { + r = render_pgweb(request, 'community', 'contributors/profile.html', { 'contributor': contributor, 'badges': badges, }) + # Tag this page with every badge it shows, so a badge change (title, + # image, approval, ...) purges it. Iterating the queryset here fills its + # result cache, so the template's loop below does not run it again. + r['xkey'] = ' '.join('pgbadge_%d' % bh.badge_id for bh in badges) + return r def badge_image(request, slug): # defer(None) clears the manager's default defer: .only() on its own # would leave an already-deferred field deferred and fetch it lazily. - badge = get_object_or_404(Badge.objects.defer(None).only('slug', 'imagedata'), slug=slug, approved=True) + badge = get_object_or_404(Badge.objects.defer(None).only('id', 'slug', 'imagedata'), slug=slug, approved=True) if not badge.imagedata: raise Http404 etag = '"' + hashlib.md5(bytes(badge.imagedata)).hexdigest() + '"' + # Tag the image so a badge change purges it through its xkey. The 304 does + # not strictly need it (only the 200 is stored as a cache object), but it + # is set on both responses to keep them identical. + headers = {"ETag": etag, "xkey": "pgbadge_%d" % badge.id} if request.headers.get('If-None-Match') == etag: - return HttpResponseNotModified(headers={"ETag": etag}) + return HttpResponseNotModified(headers=headers) return HttpResponse( bytes(badge.imagedata), content_type=get_image_contenttype_from_bytes(badge.imagedata[:8]), - headers={"ETag": etag}, + headers=headers, ) -- 2.53.0
v14-0008-Purge-on-delete-as-well-as-on-save.patch (text/x-diff, 5.0K) download From 129b0c753659585993325ed987b8f8c2eb6b393f Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 18:41:54 +0200 Subject: [PATCH v14 08/12] Purge on delete as well as on save Deletes never purged anything - only post_save was hooked. This is generic infrastructure: it is shared by every model that declares purge_urls or purge_xkeys, so the fix covers all of them, not just contributor badges. News articles, events, products, professional services, organisations, versions, mailing lists, quotes and the rest all get it too. That breadth is the point, and is why it is fixed here instead of per app. The gap was identical for every item type and there is no reason for one of them to behave differently, so a single central fix means no model has to remember to purge its own deletions, and the behaviour stays consistent with the purge_urls/purge_xkeys declarations - those already describe everything that shows an object, so deletes now simply honour them as well. Doing it per app would have meant re-implementing the same receiver over and over, and, more importantly, the gap would likely reappear with the next model that gets added. It matters most where stale content is user visible or private: deleting a contributor left their public profile page, which can include their email, in the cache, and deleting a badge or a badge holder left its pages behind. Extract the purge logic into a helper and connect a post_delete handler to it as well. post_delete is used instead of overriding Model.delete() because it also fires for cascading deletes (deleting a User removes the Contributor and Badgeholder rows) and for bulk deletes from the admin, both of which bypass Model.delete(). Wrap the purge generation in a try/except: it reads related rows and can fail, for example when a cascade delete has already removed one of them, and an exception raised in a signal handler would abort the save or delete transaction. --- pgweb/util/signals.py | 51 +++++++++++++++++++++++++++++++------------ 1 file changed, 37 insertions(+), 14 deletions(-) diff --git a/pgweb/util/signals.py b/pgweb/util/signals.py index 8b69dd88..646986b0 100644 --- a/pgweb/util/signals.py +++ b/pgweb/util/signals.py @@ -1,8 +1,9 @@ -from django.db.models.signals import pre_save, post_save, pre_delete +from django.db.models.signals import pre_save, post_save, pre_delete, post_delete from django.db import models from django.conf import settings import difflib +import logging from pgweb.util.middleware import get_current_user from pgweb.util.misc import varnish_purge, varnish_purge_xkey @@ -10,6 +11,9 @@ from pgweb.util.moderation import ModerationState from pgweb.mailqueue.util import send_simple_mail +log = logging.getLogger(__name__) + + def _build_url(obj): if obj.id: return "%s/admin/%s/%s/%s/" % ( @@ -149,23 +153,42 @@ def my_pre_delete_handler(sender, **kwargs): _get_full_text_representation(instance)) +def _run_purges(instance): + # Purge generation reads related rows and can fail, for example when a + # cascade delete has already removed one of them. Swallow the error: this + # runs from a signal handler inside the save/delete transaction, and a + # purge failure must never abort that transaction. + try: + if hasattr(instance, 'purge_urls'): + if callable(instance.purge_urls): + purgelist = instance.purge_urls() + else: + purgelist = instance.purge_urls + list(map(varnish_purge, purgelist)) + if hasattr(instance, 'purge_xkeys'): + if callable(instance.purge_xkeys): + purgelist = instance.purge_xkeys() + else: + purgelist = instance.purge_xkeys + list(map(varnish_purge_xkey, purgelist)) + except Exception as e: + log.warning("Failed to purge for %s: %s", instance, e) + + def my_post_save_handler(sender, **kwargs): - instance = kwargs['instance'] - if hasattr(instance, 'purge_urls'): - if callable(instance.purge_urls): - purgelist = instance.purge_urls() - else: - purgelist = instance.purge_urls - list(map(varnish_purge, purgelist)) - if hasattr(instance, 'purge_xkeys'): - if callable(instance.purge_xkeys): - purgelist = instance.purge_xkeys() - else: - purgelist = instance.purge_xkeys - list(map(varnish_purge_xkey, purgelist)) + _run_purges(kwargs['instance']) + + +def my_post_delete_handler(sender, **kwargs): + # Deletes have to purge as well: a deleted contributor's profile page (which + # can contain their email) or a deleted badge must not stay in the cache. + # post_delete is used because it also fires for cascade and bulk deletes, + # which never call Model.delete(). + _run_purges(kwargs['instance']) def register_basic_signal_handlers(): pre_save.connect(my_pre_save_handler) pre_delete.connect(my_pre_delete_handler) post_save.connect(my_post_save_handler) + post_delete.connect(my_post_delete_handler) -- 2.53.0
v14-0009-Store-the-badge-image-md5-in-the-database.patch (text/x-diff, 2.9K) download From c2fb000b976d490e6741938f1e880be9e5d7f7cb Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 18:59:41 +0200 Subject: [PATCH v14 09/12] Store the badge image md5 in the database badge_image() reads imagedata out of TOAST and computes its md5 on every request, including when all it has to do is answer If-None-Match with a 304. Add a Badge.imagehash generated column holding the md5 of imagedata, empty when there is no image, so a revalidation can be answered from this alone. The column is used the same way as SecurityPatch.cvenumber: the database keeps it current, so nothing has to backfill it and no code path can forget to update it. The following commits add the migration and make badge_image() use it. --- pgweb/contributors/models.py | 15 ++++++++++++++- 1 file changed, 14 insertions(+), 1 deletion(-) diff --git a/pgweb/contributors/models.py b/pgweb/contributors/models.py index d3bd8eef..bf8f8b62 100644 --- a/pgweb/contributors/models.py +++ b/pgweb/contributors/models.py @@ -3,7 +3,7 @@ import re from django.core.exceptions import ValidationError from django.db import models -from django.db.models import Case, When +from django.db.models import Case, F, Func, Value, When from django.db.models.functions import Length from django.db.models.lookups import GreaterThan from django.contrib.auth.models import User @@ -112,6 +112,19 @@ class Badge(TwostateModerateModel): description = models.TextField(null=True, blank=True, help_text='What did the people do who contributed here?') url = models.URLField(max_length=100, null=True, blank=True, verbose_name='Contribution URL', help_text='URL for this contribution, e.g. the conference homepage. (Leave blank when there is no URL.)') imagedata = ImageBinaryField(max_length=1000000, resolution=(150, 150), auto_scale=False, blank=True, null=True, verbose_name="Image", help_text="Square image, must be 150x150. JPEG or PNG, up to 1 MB. When left blank, the PostgreSQL logo will be used.") + imagehash = models.GeneratedField( + # md5 of the image, computed and stored by the database, so badge_image() + # can answer If-None-Match from this alone, without reading imagedata out + # of TOAST and hashing it on every request. Empty when there is no image. + expression=Case( + When(GreaterThan(Length('imagedata'), 0), + then=Func(F('imagedata'), function='MD5', output_field=models.TextField())), + default=Value(''), + output_field=models.TextField(), + ), + output_field=models.TextField(), + db_persist=True, + ) contact = models.CharField(max_length=100, null=True, blank=True, verbose_name='Contact address', help_text='Contact address (email, URL, other) for people who want to be added as badge holder') holders = models.ManyToManyField(User, through="Badgeholder", through_fields=("badge", "user"), blank=True) -- 2.53.0
v14-0010-Extend-badge-title-to-255-chars.patch (text/x-diff, 1.7K) download From 3bfcd34aa194b804eb14b82793a91e018c463ac7 Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 19:00:17 +0200 Subject: [PATCH v14 10/12] Extend badge title to 255 chars Daniel pointed out that a reasonable badge title like "Nordic PGDay 2026 Program Committee" is already longer than the 32 chars we catered for (it's 35). Extend it, we still have moderation to reject overly long names. --- pgweb/contributors/models.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/pgweb/contributors/models.py b/pgweb/contributors/models.py index bf8f8b62..6c4330a9 100644 --- a/pgweb/contributors/models.py +++ b/pgweb/contributors/models.py @@ -107,7 +107,7 @@ class BadgeManager(models.Manager): class Badge(TwostateModerateModel): org = models.ForeignKey(Organisation, null=False, blank=False, verbose_name="Organisation", help_text=ORGANISATION_HINT_TEXT, on_delete=models.CASCADE) - badge = models.CharField(max_length=32, null=False, blank=False, unique=True, help_text='Title of this badge, e.g. "PGConf.EU 2025 Speaker".') + badge = models.CharField(max_length=255, null=False, blank=False, unique=True, help_text='Title of this badge, e.g. "PGConf.EU 2025 Speaker".') slug = models.SlugField(max_length=64, null=False, blank=False, unique=True, help_text='URL-friendly identifier for the badge page. Generated from the title when the badge is created, and kept fixed after that.') description = models.TextField(null=True, blank=True, help_text='What did the people do who contributed here?') url = models.URLField(max_length=100, null=True, blank=True, verbose_name='Contribution URL', help_text='URL for this contribution, e.g. the conference homepage. (Leave blank when there is no URL.)') -- 2.53.0
v14-0011-Migrate-imagehash-and-serve-badge-images-from-it.patch (text/x-diff, 4.3K) download From fe890525282a0a645c8df31c970fd86dc4c85567 Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 19:00:42 +0200 Subject: [PATCH v14 11/12] Migrate imagehash and serve badge images from it badge_image() now answers If-None-Match from Badge.imagehash, so a revalidation is answered with a single query and never reads imagedata (previously it read the image out of TOAST and hashed it before doing the check). The image itself is still fetched, lazily, for a 200. The migration covers both new schema changes for the contributors app: the imagehash column added two commits ago and the badge title length from the previous one. Both are unreleased, so they share a single migration. imagehash is database-generated, so there is nothing to backfill. --- .../0006_badge_imagehash_alter_badge_badge.py | 49 +++++++++++++++++++ pgweb/contributors/views.py | 13 +++-- 2 files changed, 55 insertions(+), 7 deletions(-) create mode 100644 pgweb/contributors/migrations/0006_badge_imagehash_alter_badge_badge.py diff --git a/pgweb/contributors/migrations/0006_badge_imagehash_alter_badge_badge.py b/pgweb/contributors/migrations/0006_badge_imagehash_alter_badge_badge.py new file mode 100644 index 00000000..9ce08196 --- /dev/null +++ b/pgweb/contributors/migrations/0006_badge_imagehash_alter_badge_badge.py @@ -0,0 +1,49 @@ +# Generated by Django 5.2.17 on 2026-10-16 16:54 + +import django.db.models.functions.text +import django.db.models.lookups +from django.db import migrations, models + + +class Migration(migrations.Migration): + + dependencies = [ + ( + "contributors", + "0005_alter_contributor_ctype_alter_contributor_email_and_more", + ), + ] + + operations = [ + migrations.AddField( + model_name="badge", + name="imagehash", + field=models.GeneratedField( + db_persist=True, + expression=models.Case( + models.When( + django.db.models.lookups.GreaterThan( + django.db.models.functions.text.Length("imagedata"), 0 + ), + then=models.Func( + models.F("imagedata"), + function="MD5", + output_field=models.TextField(), + ), + ), + default=models.Value(""), + output_field=models.TextField(), + ), + output_field=models.TextField(), + ), + ), + migrations.AlterField( + model_name="badge", + name="badge", + field=models.CharField( + help_text='Title of this badge, e.g. "PGConf.EU 2025 Speaker".', + max_length=255, + unique=True, + ), + ), + ] diff --git a/pgweb/contributors/views.py b/pgweb/contributors/views.py index c0faf542..fbf6b315 100644 --- a/pgweb/contributors/views.py +++ b/pgweb/contributors/views.py @@ -1,5 +1,3 @@ -import hashlib - from django.db.models import Prefetch from django.shortcuts import get_object_or_404 from django.http import HttpResponse, HttpResponseNotModified, Http404 @@ -60,12 +58,13 @@ def profile(request, username): def badge_image(request, slug): - # defer(None) clears the manager's default defer: .only() on its own - # would leave an already-deferred field deferred and fetch it lazily. - badge = get_object_or_404(Badge.objects.defer(None).only('id', 'slug', 'imagedata'), slug=slug, approved=True) - if not badge.imagedata: + # imagedata is deferred: a revalidation (If-None-Match) is answered from the + # stored imagehash alone, without reading the image out of TOAST. imagedata + # is fetched lazily below, only when the image is actually sent. + badge = get_object_or_404(Badge.objects.only('id', 'slug', 'imagehash'), slug=slug, approved=True) + if not badge.imagehash: raise Http404 - etag = '"' + hashlib.md5(bytes(badge.imagedata)).hexdigest() + '"' + etag = '"%s"' % badge.imagehash # Tag the image so a badge change purges it through its xkey. The 304 does # not strictly need it (only the 200 is stored as a cache object), but it # is set on both responses to keep them identical. -- 2.53.0
v14-0012-Don-t-re-purge-contributor-pages-on-a-no-op-acco.patch (text/x-diff, 1.7K) download From 0d26119ff31bd1c1906e006dddba3594163aac6f Mon Sep 17 00:00:00 2001 From: Christoph Berg <myon@debian.org> Date: Fri, 16 Oct 2026 19:09:12 +0200 Subject: [PATCH v14 12/12] Don't re-purge contributor pages on a no-op account save The account profile view saved the contributor form on every POST whenever the user has a contributor record, even when nothing in it had changed. That fired the contributor post_save handler, which purges the curated list, the people list, the user's own profile page and every badge page that lists them - all for a form that changed nothing. Only save the form when it actually changed. --- pgweb/account/views.py | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/pgweb/account/views.py b/pgweb/account/views.py index 986f3827..cc5a6c18 100644 --- a/pgweb/account/views.py +++ b/pgweb/account/views.py @@ -187,7 +187,9 @@ def profile(request): profileform.save() if badges and 'create_contributor_profile' in request.POST and request.POST['create_contributor_profile']: contrib = Contributor.objects.create(user=user, firstname=user.first_name, lastname=user.last_name, email=user.email) - elif contrib: + elif contrib and contribform.has_changed(): + # Skip no-op saves: the contributor post_save handler purges the + # curated list, the people list and all of the user's badge pages. contribform.save() if secondaryemailform.cleaned_data.get('email1', ''): sa = SecondaryEmail(user=request.user, email=secondaryemailform.cleaned_data['email1'], token=generate_random_token()) -- 2.53.0
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: Introduce contributor badges - v14
  2026-10-16 17:21 Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
  2026-10-16 17:22 ` Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
@ 2026-10-16 18:22   ` Dave Page <dpage@pgadmin.org>
  0 siblings, 0 replies; 9+ messages in thread

From: Dave Page @ 2026-10-16 18:22 UTC (permalink / raw)
  To: Christoph Berg <myon@debian.org>; +Cc: Daniel Gustafsson <daniel@yesql.se>; Magnus Hagander <magnus@hagander.net>; Jonathan S. Katz <jkatz@postgresql.org>; pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>; Melanie Plageman <melanieplageman@gmail.com>; PostgreSQL Contributors <contributors@lists.postgresql.org>

On Fri, 16 Oct 2026 at 18:22, Christoph Berg <myon@debian.org> wrote:

> Re: To Dave Page
> > The v14 patch series addresses Dave's review, Daniels 150x150 comment
> > and includes Magnus feedback:
>
> *cough*


I’m afk now until I get spare time during the conference I’m afraid
(whether I will actually get any remains to be seen). House full of family
until about the time we leave for the airport.

>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Introduce contributor badges - v14
  2026-10-16 17:21 Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
@ 2026-11-04 19:38 ` Magnus Hagander <magnus@hagander.net>
  2026-11-06 14:09   ` Re: Introduce contributor badges - v14 Daniel Gustafsson <daniel@yesql.se>
  1 sibling, 1 reply; 9+ messages in thread

From: Magnus Hagander @ 2026-11-04 19:38 UTC (permalink / raw)
  To: Christoph Berg <myon@debian.org>; +Cc: Dave Page <dpage@pgadmin.org>; Daniel Gustafsson <daniel@yesql.se>; Jonathan S. Katz <jkatz@postgresql.org>; pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>; Melanie Plageman <melanieplageman@gmail.com>; PostgreSQL Contributors <contributors@lists.postgresql.org>

On Fri, 16 Oct 2026 at 19:21, Christoph Berg <myon@debian.org> wrote:

> The v14 patch series addresses Dave's review, Daniels 150x150 comment
> and includes Magnus feedback:
>

I've lost track of much of this, but none of these fixes is actually
applied yet, right?

And they're just adressed in a random order here rather than from 1 and
onwards? :)

I'll start with this patch series as it's already been done, and then
review the underlying stuff later.


Re: Dave Page
> > 1. **Removing a holder purges nothing for that person.** `BadgeForm.save`
> > calls `badge.holders.remove()` (`pgweb/contributors/forms.py`). That
> > deletes the through-table rows without any `post_save`, and pgweb only
> > hooks `post_save` (`pgweb/util/signals.py:169`). The badge page still
> gets
> > purged, because `simple_form` saves the badge afterwards. The removed
> > person's `/community/people/<username>/` page keeps showing the badge.
>
> This is v14-0007-Purge-badge-pages-and-holder-profiles-through-xk.patch
>

I wonder if we shouldn't add a `badgelist_<n>` xkey and use that to purge
the pages when a contributor is edited, rather than do it url based. Url
based, particularly with looping, can generate an overhead (since
technically they are not purges even though they are called that -- they to
regexp matching agianst all incoming requests for banning).

Comments are about how the xkey is duplicated between the responses, but
not the etag? (but the comment seems to go into a level of detail we're
unusued to anyway, so we can maybe just drop that part)


> 2. **Deletes never purge.** This applies to deleting a `Badge`,
> > `Badgeholder` or `Contributor` in the admin. The rest of pgweb has the
> same
> > gap, but it matters more here: a deleted contributor's public profile
> page,
> > which can include their email, stays cached. If someone asks for their
> > profile to be removed, it isn't really gone.
>
> v14-0008-Purge-on-delete-as-well-as-on-save.patch
>

This commit message looks so painfully LLM generated that I can't read
through it all. But it also looks like a long explanation of a discussion
rather than the actual patch.

And it seems to introduce a new type of logging that's not used elsewhere,
just trapping and logging *all* exceptions as warnings completely
regardless of what they are. Doesn't seem like something that belongs in
such a patch.

And since it affects everything, not just the badges, it should probably
not just be patch 8 in a long series, it deserves looking at separtely.


> ## Database and app server
> >
> > 5. **The `has_image` annotation reads every image out of TOAST**,
> > PostgreSQL's out-of-line storage for large values
> > (`BadgeManager.with_has_image`). The comment says the value is "never
> > transferred", which is true for the network, but `imagedata > ''`
> compares
> > the full bytea, so PostgreSQL has to fetch and reassemble the whole
> value.
> > A test of 200 rows of about 96 KB each on the local database showed the
> > comparison reading about 3,000 buffers in 31 ms, whilst
> `octet_length(img)
> > > 0` read 2 buffers in 0.04 ms, because it only reads the TOAST header.
> > Switching to `Length('imagedata') > 0` is a one-line fix. It runs on
> every
> > cache miss of the people page (up to 200 badges) and on every account
> > profile page load, which is never cached.
>
> v14-0002-Determine-has-image-without-full-detoasting.patch
>
>
Looks fine, but is it really needed? Do we even (want to) support badges
without images? Whta's the point of a badge that has no image?



> > 6. **Oversized images are decoded in full (a "decompression bomb").**
> > `ImageBinaryField.to_python` decodes the entire image with Pillow before
> > rescaling. A PNG under 1 MB can still be about 170 megapixels before
> Pillow
> > refuses it, which is several hundred MB of RAM plus a bicubic resize on
> the
> > app server. `ModelForm` full-clean also re-parses the stored image on
> every
> > badge edit, even when the image hasn't changed. Only managers of an
> > approved organisation can upload, so the risk is moderate, but checking
> > `img.size` before decoding would be cheap.
>
> v14-0003-Reject-images-with-wrong-size-before-loading-the.patch
>

Why are we doing this whole exif orientation thing in the first place?
Surely we can expect people to upload badges that are not upside down?



> v14-0004-Don-t-re-parse-the-stored-badge-image-on-every-e.patch


See above comments about commit messages.




> > 7. **Minor, and only on cache misses:**
> >    - `badge.html` uses `holders|length` and then `holders.all`, which
> runs
> > the holder query and its two prefetches a second time.
>
> v14-0005-Don-t-run-the-badge-holder-query-twice.patch
>

Looks fine, but the commit message is... Yeha, see above.


>    - `select_related` would do better than `prefetch_related` for the
> > foreign-key lookups in `peoplelist` and `badge_view`.
>
> v14-0006-Use-select_related-for-the-badge-holder-and-peop.patch
>

Dito.


>    - `badge_image` fetches the image and computes its MD5 before it checks
> > `If-None-Match`; storing the hash would avoid both.
>
> v14-0009-Store-the-badge-image-md5-in-the-database.patch
>
> Since 0009 is touching the database, we can now pull up the staged
> extended badge title length:
> v14-0010-Extend-badge-title-to-255-chars.patch
>

I agree with Daniel we should do this extension.


Both together share a migration file that is part of
> v14-0011-Migrate-imagehash-and-serve-badge-images-from-it.patch
>
> If we decide we don't want the MD5 change, we can postpone these
> three.
>

This should probably be just one patch, so the model changes go along with
the migration.

0011 looks good.


>    - Every account profile save that has a contributor record purges
> > `/community/contributors/`, `/community/people/` and all of that user's
> > badge pages, even when nothing changed.
>
> v14-0012-Don-t-re-purge-contributor-pages-on-a-no-op-acco.patch
>

Looks good.


Re: Daniel Gustafsson
> > +1 on restricting the upload to 150x150 and make the user responsible
> for the
> > badge image looking good rather than rescaling.
>
> That's v14-0001-Require-user-supplied-badges-to-be-150x150.patch
>

We could perhaps just remove the autoscaling code now that we've turned it
off?

Why do we want to support both PNG and JPG? Seems a badge should be only
PNG?


//Magnus

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Introduce contributor badges - v14
  2026-10-16 17:21 Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
  2026-11-04 19:38 ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
@ 2026-11-06 14:09   ` Daniel Gustafsson <daniel@yesql.se>
  2026-11-07 11:56     ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
  0 siblings, 1 reply; 9+ messages in thread

From: Daniel Gustafsson @ 2026-11-06 14:09 UTC (permalink / raw)
  To: Magnus Hagander <magnus@hagander.net>; +Cc: Christoph Berg <myon@debian.org>; Dave Page <dpage@pgadmin.org>; Jonathan S. Katz <jkatz@postgresql.org>; pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>; Melanie Plageman <melanieplageman@gmail.com>; PostgreSQL Contributors <contributors@lists.postgresql.org>

> On 4 Nov 2026, at 20:38, Magnus Hagander <magnus@hagander.net> wrote:

> Do we even (want to) support badges without images? Whta's the point of a badge that has no image?

Requiring an image seems like a good clamp-down to reduce complexity early on,
we can revisit later if a good usecase comes up (text-only badges which are
applied with a CSS class comes to mind).

> Why are we doing this whole exif orientation thing in the first place? Surely we can expect people to upload badges that are not upside down?

Agreed, another reduction in complexity to get a base functionalty agreed upon.
Rotation is also, IMHO, most useful when accepting images taken by a smarthpone
which may have been held in all sorts up upside-down-left-center ways.
150x150px badge images will be generated by tools and less susceptible to being
upside down.

> Why do we want to support both PNG and JPG? Seems a badge should be only PNG?

For such small formats, I would prefer PNG only for now.  In the future we
could add SVG to the list, or even pure CSS based (not for upload, but a
hardcoded set defined by pgweb committers).

--
Daniel Gustafsson







^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Introduce contributor badges - v14
  2026-10-16 17:21 Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
  2026-11-04 19:38 ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
  2026-11-06 14:09   ` Re: Introduce contributor badges - v14 Daniel Gustafsson <daniel@yesql.se>
@ 2026-11-07 11:56     ` Magnus Hagander <magnus@hagander.net>
  2026-11-07 21:19       ` Re: Introduce contributor badges - v14 Dave Page <dpage@pgadmin.org>
  0 siblings, 1 reply; 9+ messages in thread

From: Magnus Hagander @ 2026-11-07 11:56 UTC (permalink / raw)
  To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Christoph Berg <myon@debian.org>; Dave Page <dpage@pgadmin.org>; Jonathan S. Katz <jkatz@postgresql.org>; pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>; Melanie Plageman <melanieplageman@gmail.com>; PostgreSQL Contributors <contributors@lists.postgresql.org>

On Fri, 6 Nov 2026 at 15:09, Daniel Gustafsson <daniel@yesql.se> wrote:

> > On 4 Nov 2026, at 20:38, Magnus Hagander <magnus@hagander.net> wrote:
>
> > Do we even (want to) support badges without images? Whta's the point of
> a badge that has no image?
>
> Requiring an image seems like a good clamp-down to reduce complexity early
> on,
> we can revisit later if a good usecase comes up (text-only badges which are
> applied with a CSS class comes to mind).
>
> > Why are we doing this whole exif orientation thing in the first place?
> Surely we can expect people to upload badges that are not upside down?
>
> Agreed, another reduction in complexity to get a base functionalty agreed
> upon.
> Rotation is also, IMHO, most useful when accepting images taken by a
> smarthpone
> which may have been held in all sorts up upside-down-left-center ways.
> 150x150px badge images will be generated by tools and less susceptible to
> being
> upside down.
>
> > Why do we want to support both PNG and JPG? Seems a badge should be only
> PNG?
>
> For such small formats, I would prefer PNG only for now.  In the future we
> could add SVG to the list, or even pure CSS based (not for upload, but a
> hardcoded set defined by pgweb committers).
>

SVG would allow for inling the images in the HTML itself, which might be a
win *iff* th eimages are small enough. Definitely something to consider for
an opitmisation as the number of badges grow. So bsaic support for SVG
would definitely be good, but let's make sure it's also verified that they
are "pure" SVGs and not just an SVG with an embeded raster image..

//Magnus

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Introduce contributor badges - v14
  2026-10-16 17:21 Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
  2026-11-04 19:38 ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
  2026-11-06 14:09   ` Re: Introduce contributor badges - v14 Daniel Gustafsson <daniel@yesql.se>
  2026-11-07 11:56     ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
@ 2026-11-07 21:19       ` Dave Page <dpage@pgadmin.org>
  2026-11-09 11:25         ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
  0 siblings, 1 reply; 9+ messages in thread

From: Dave Page @ 2026-11-07 21:19 UTC (permalink / raw)
  To: Magnus Hagander <magnus@hagander.net>; +Cc: Daniel Gustafsson <daniel@yesql.se>; Christoph Berg <myon@debian.org>; Jonathan S. Katz <jkatz@postgresql.org>; pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>; Melanie Plageman <melanieplageman@gmail.com>; PostgreSQL Contributors <contributors@lists.postgresql.org>

On Sat, 7 Nov 2026 at 11:56, Magnus Hagander <magnus@hagander.net> wrote:

> On Fri, 6 Nov 2026 at 15:09, Daniel Gustafsson <daniel@yesql.se> wrote:
>
>> > On 4 Nov 2026, at 20:38, Magnus Hagander <magnus@hagander.net> wrote:
>>
>> > Do we even (want to) support badges without images? Whta's the point of
>> a badge that has no image?
>>
>> Requiring an image seems like a good clamp-down to reduce complexity
>> early on,
>> we can revisit later if a good usecase comes up (text-only badges which
>> are
>> applied with a CSS class comes to mind).
>>
>> > Why are we doing this whole exif orientation thing in the first place?
>> Surely we can expect people to upload badges that are not upside down?
>>
>> Agreed, another reduction in complexity to get a base functionalty agreed
>> upon.
>> Rotation is also, IMHO, most useful when accepting images taken by a
>> smarthpone
>> which may have been held in all sorts up upside-down-left-center ways.
>> 150x150px badge images will be generated by tools and less susceptible to
>> being
>> upside down.
>>
>> > Why do we want to support both PNG and JPG? Seems a badge should be
>> only PNG?
>>
>> For such small formats, I would prefer PNG only for now.  In the future we
>> could add SVG to the list, or even pure CSS based (not for upload, but a
>> hardcoded set defined by pgweb committers).
>>
>
> SVG would allow for inling the images in the HTML itself, which might be a
> win *iff* th eimages are small enough. Definitely something to consider for
> an opitmisation as the number of badges grow. So bsaic support for SVG
> would definitely be good, but let's make sure it's also verified that they
> are "pure" SVGs and not just an SVG with an embeded raster image..
>
> //Magnus
>
>

Who will actually be able to upload badges? Svgs can have code embedded in
them so we need a good level of trust.

>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Introduce contributor badges - v14
  2026-10-16 17:21 Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
  2026-11-04 19:38 ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
  2026-11-06 14:09   ` Re: Introduce contributor badges - v14 Daniel Gustafsson <daniel@yesql.se>
  2026-11-07 11:56     ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
  2026-11-07 21:19       ` Re: Introduce contributor badges - v14 Dave Page <dpage@pgadmin.org>
@ 2026-11-09 11:25         ` Magnus Hagander <magnus@hagander.net>
  2026-11-09 12:04           ` Re: Introduce contributor badges - v14 Dave Page <dpage@pgadmin.org>
  0 siblings, 1 reply; 9+ messages in thread

From: Magnus Hagander @ 2026-11-09 11:25 UTC (permalink / raw)
  To: Dave Page <dpage@pgadmin.org>; +Cc: Daniel Gustafsson <daniel@yesql.se>; Christoph Berg <myon@debian.org>; Jonathan S. Katz <jkatz@postgresql.org>; pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>; Melanie Plageman <melanieplageman@gmail.com>; PostgreSQL Contributors <contributors@lists.postgresql.org>

On Sat, 7 Nov 2026 at 22:19, Dave Page <dpage@pgadmin.org> wrote:

>
>
>
> On Sat, 7 Nov 2026 at 11:56, Magnus Hagander <magnus@hagander.net> wrote:
>
>> On Fri, 6 Nov 2026 at 15:09, Daniel Gustafsson <daniel@yesql.se> wrote:
>>
>>> > On 4 Nov 2026, at 20:38, Magnus Hagander <magnus@hagander.net> wrote:
>>>
>>> > Do we even (want to) support badges without images? Whta's the point
>>> of a badge that has no image?
>>>
>>> Requiring an image seems like a good clamp-down to reduce complexity
>>> early on,
>>> we can revisit later if a good usecase comes up (text-only badges which
>>> are
>>> applied with a CSS class comes to mind).
>>>
>>> > Why are we doing this whole exif orientation thing in the first place?
>>> Surely we can expect people to upload badges that are not upside down?
>>>
>>> Agreed, another reduction in complexity to get a base functionalty
>>> agreed upon.
>>> Rotation is also, IMHO, most useful when accepting images taken by a
>>> smarthpone
>>> which may have been held in all sorts up upside-down-left-center ways.
>>> 150x150px badge images will be generated by tools and less susceptible
>>> to being
>>> upside down.
>>>
>>> > Why do we want to support both PNG and JPG? Seems a badge should be
>>> only PNG?
>>>
>>> For such small formats, I would prefer PNG only for now.  In the future
>>> we
>>> could add SVG to the list, or even pure CSS based (not for upload, but a
>>> hardcoded set defined by pgweb committers).
>>>
>>
>> SVG would allow for inling the images in the HTML itself, which might be
>> a win *iff* th eimages are small enough. Definitely something to consider
>> for an opitmisation as the number of badges grow. So bsaic support for SVG
>> would definitely be good, but let's make sure it's also verified that they
>> are "pure" SVGs and not just an SVG with an embeded raster image..
>>
>> //Magnus
>>
>>
>
> Who will actually be able to upload badges? Svgs can have code embedded in
> them so we need a good level of trust.
>
>>
Anybody can upload them, so that's a fair point. Stripping <script> tags
would be easy enough, but there is also other external entities.

Also, if you embed them with a data url as an <img> that won't happen, as
browsers prevent it. It'll be a bit less efficient, but still probably
better than PNG on account of the files being smaller?


//Magnus

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Introduce contributor badges - v14
  2026-10-16 17:21 Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
  2026-11-04 19:38 ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
  2026-11-06 14:09   ` Re: Introduce contributor badges - v14 Daniel Gustafsson <daniel@yesql.se>
  2026-11-07 11:56     ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
  2026-11-07 21:19       ` Re: Introduce contributor badges - v14 Dave Page <dpage@pgadmin.org>
  2026-11-09 11:25         ` Re: Introduce contributor badges - v14 Magnus Hagander <magnus@hagander.net>
@ 2026-11-09 12:04           ` Dave Page <dpage@pgadmin.org>
  0 siblings, 0 replies; 9+ messages in thread

From: Dave Page @ 2026-11-09 12:04 UTC (permalink / raw)
  To: Magnus Hagander <magnus@hagander.net>; +Cc: Daniel Gustafsson <daniel@yesql.se>; Christoph Berg <myon@debian.org>; Jonathan S. Katz <jkatz@postgresql.org>; pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>; Melanie Plageman <melanieplageman@gmail.com>; PostgreSQL Contributors <contributors@lists.postgresql.org>

On Mon, 9 Nov 2026 at 11:25, Magnus Hagander <magnus@hagander.net> wrote:

>
>
> On Sat, 7 Nov 2026 at 22:19, Dave Page <dpage@pgadmin.org> wrote:
>
>>
>>
>>
>> On Sat, 7 Nov 2026 at 11:56, Magnus Hagander <magnus@hagander.net> wrote:
>>
>>> On Fri, 6 Nov 2026 at 15:09, Daniel Gustafsson <daniel@yesql.se> wrote:
>>>
>>>> > On 4 Nov 2026, at 20:38, Magnus Hagander <magnus@hagander.net> wrote:
>>>>
>>>> > Do we even (want to) support badges without images? Whta's the point
>>>> of a badge that has no image?
>>>>
>>>> Requiring an image seems like a good clamp-down to reduce complexity
>>>> early on,
>>>> we can revisit later if a good usecase comes up (text-only badges which
>>>> are
>>>> applied with a CSS class comes to mind).
>>>>
>>>> > Why are we doing this whole exif orientation thing in the first
>>>> place? Surely we can expect people to upload badges that are not upside
>>>> down?
>>>>
>>>> Agreed, another reduction in complexity to get a base functionalty
>>>> agreed upon.
>>>> Rotation is also, IMHO, most useful when accepting images taken by a
>>>> smarthpone
>>>> which may have been held in all sorts up upside-down-left-center ways.
>>>> 150x150px badge images will be generated by tools and less susceptible
>>>> to being
>>>> upside down.
>>>>
>>>> > Why do we want to support both PNG and JPG? Seems a badge should be
>>>> only PNG?
>>>>
>>>> For such small formats, I would prefer PNG only for now.  In the future
>>>> we
>>>> could add SVG to the list, or even pure CSS based (not for upload, but a
>>>> hardcoded set defined by pgweb committers).
>>>>
>>>
>>> SVG would allow for inling the images in the HTML itself, which might be
>>> a win *iff* th eimages are small enough. Definitely something to consider
>>> for an opitmisation as the number of badges grow. So bsaic support for SVG
>>> would definitely be good, but let's make sure it's also verified that they
>>> are "pure" SVGs and not just an SVG with an embeded raster image..
>>>
>>> //Magnus
>>>
>>>
>>
>> Who will actually be able to upload badges? Svgs can have code embedded
>> in them so we need a good level of trust.
>>
>>>
> Anybody can upload them, so that's a fair point. Stripping <script> tags
> would be easy enough, but there is also other external entities.
>
> Also, if you embed them with a data url as an <img> that won't happen, as
> browsers prevent it. It'll be a bit less efficient, but still probably
> better than PNG on account of the files being smaller?
>
>
Seems reasonable, yes.


-- 
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com

^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2026-11-09 12:04 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-16 17:21 Re: Introduce contributor badges - v14 Christoph Berg <myon@debian.org>
2026-10-16 17:22 ` Christoph Berg <myon@debian.org>
2026-10-16 18:22   ` Dave Page <dpage@pgadmin.org>
2026-11-04 19:38 ` Magnus Hagander <magnus@hagander.net>
2026-11-06 14:09   ` Daniel Gustafsson <daniel@yesql.se>
2026-11-07 11:56     ` Magnus Hagander <magnus@hagander.net>
2026-11-07 21:19       ` Dave Page <dpage@pgadmin.org>
2026-11-09 11:25         ` Magnus Hagander <magnus@hagander.net>
2026-11-09 12:04           ` Dave Page <dpage@pgadmin.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox