From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nathan Bossart <nathandbossart@gmail.com>
cc: mert@futo.org, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18964: `ALTER DATABASE ... RESET ...` fails to reset
 extension parameters that no longer exist
In-reply-to: <aFW8R6Qlgtbp7TOk@nathan>
References: <18964-ba09dea8c98fccd6@postgresql.org> <aFW8R6Qlgtbp7TOk@nathan>
Comments: In-reply-to Nathan Bossart <nathandbossart@gmail.com>
	message dated "Fri, 20 Jun 2025 14:53:43 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2474667.1750451081.1@sss.pgh.pa.us>
Date: Fri, 20 Jun 2025 16:24:41 -0400
Message-ID: <2474668.1750451081@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2474668.1750451081%40sss.pgh.pa.us>
Precedence: bulk

Nathan Bossart <nathandbossart@gmail.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