I've looked through the PostGIS raster documentation, but can't find an indication of how to apply a convolution filter to raster data. Does this capability even exist (yet?) There only appears to be preset operators such as min, max and std deviation.
A discussion carried out in comments to the question has established that indeed PostGIS can perform convolutions by means of the ST_MapAlgebraFctNgb procedure. This provides a way to apply a user-supplied function systematically to all neighborhoods within a raster. Any function that is a weighted linear combination of the inputs (with constant weights across the raster) is a convolution, by definition.
This approach is remarkably flexible: it should be able to accommodate any kind of neighborhood statistic (mean, median, range, variance, variety, etc.) in addition to actual convolutions. The flip side is that it can be slow: there will be one neighborhood for each grid cell, so the processing time will usually be proportional to the grid size times the neighborhood size. For medium to large neighborhoods that can be onerous. Methods based on the Fast Fourier Transform can compute any convolution you want and take effort proportional to the grid size times its logarithm. For medium to large neighborhoods (typically the crossover is around 5 by 5 to 10 by 10 in size), that can be a big difference. In such cases one would seek alternative software solutions.