Hi hackers!
In our Cloud we have a patch, which allows non-superuser role ('mdb_admin') to do some superuser things.
In particular, we have a patch that allows mdb admin to cancel the autovacuum process and some other processes (processes with application_name = 'MDB'), see the attachment.
This is needed to allow non-superuser roles to run pg_repack and to cancel pg_repack.
We need to cancel running autovac to run pg_repack (because of locks), and we need to cancel pg_repack sometimes also.
I want to reduce our internal patch size and transfer this logic to extension or to core.
I have found similar threads [1] and [2], but, as far as I understand, they do not solve this particular case.
I see 2 possible ways to implement this. The first one is to have hool in pg_signal_backend, and define a hook in extension which can do the thing.
The second one is to have a predefined role. Something like a `pg_signal_autovacuum` role which can signal running autovac to cancel. But I don't see how we can handle specific `application_name` with this solution.