Re: BUG #18964: `ALTER DATABASE ... RESET ...` fails to reset extension parameters that no longer exist

Nathan Bossart <nathandbossart(at)gmail(dot)com> writes:
> On Fri, Jun 20, 2025 at 12:24:20PM +0000, PG Bug reporting form wrote:
>> As a solution, I added a migration that removes the now-redundant parameter
>> with `ALTER DATABASE immich RESET vchordrq.prewarm_dim;`. However, while
>> this worked for our CI that uses Postgres 14 and for admins using 14, we
>> began getting reports from admins using 15, 16 and 17 that the migration
>> fails with the following error:
>> ```
>> PostgresError: invalid configuration parameter name "vchordrq.prewarm_dim"
>> {
>> severity_local: 'ERROR',
>> severity: 'ERROR',
>> code: '42602',
>> detail: '"vchordrq" is a reserved prefix.',
>>
>> It would be great if `RESET`ing the parameter still worked in this
>> situation, even if `SET`ing it does not.

> It seems like we could at least allow superusers and folks with existing
> privileges on removed parameters to RESET in this case.

The hazard we need to pay attention to here is someone removing a
parameter setting that they shouldn't have had privilege to do.
In particular, if a non-superuser can RESET a SUSET-or-above
parameter, that's bad.

In the case at hand, where the prefix is known but the parameter
isn't, I think we could safely assume that the setting is either
a mistake (probably installed while the extension wasn't loaded)
or the described case of a parameter the extension no longer
uses. Either way it seems safe to allow RESET with suitable
privileges on the target DB and/or role.

If the prefix is not known, then we're really flying blind.
It looks like we assume the parameter might be SUSET and therefore
allow both ALTER DB/ROLE SET and RESET only to superusers.
I'm hesitant to relax that case.

regards, tom lane