Bruce Momjian <bruce@momjian.us> writes:
> I looked at this report and the cause seems deeper than reported. The
> reporter states that having the extension loaded would fix it, but doing
> the ALTER DATABASE as superuser also fixes it:
Well, yeah, see guc.c's validate_option_array_item:
* There are three cases to consider:
*
* name is a known GUC variable. Check the value normally, check
* permissions normally (i.e., allow if variable is USERSET, or if it's
* SUSET and user is superuser).
*
* name is not known, but exists or can be created as a placeholder (i.e.,
* it has a prefixed name). We allow this case if you're a superuser,
* otherwise not. Superusers are assumed to know what they're doing. We
* can't allow it for other users, because when the placeholder is
* resolved it might turn out to be a SUSET variable;
* define_custom_variable assumes we checked that.
*
* name is not known and can't be created as a placeholder. Throw error,
* unless skipIfNoPermissions is true, in which case return false.
AFAICS it's behaving as designed.
Conceivably we could improve this by extending pg_db_role_setting to track
whether the assigner of a value was superuser or not, and then deciding
at value apply time whether to allow the assignment. However, that would
have downsides of its own, that you might not find out till far distant
from the mistake that you'd made a mistake.
regards, tom lane