Re: BUG #15271: Documentation / Error reporting on GUC parameter change

Hey guys, I reported this a while back.

But the problem persists even as the database owner, I can't ALTER DATABASE
(as the db owner) before the extension is loaded into the session.
Actually that was the original issue. Non-owners or non-superusers can't
use ALTER DATABASE, and that's fine, but not even the DB OWNER can use
ALTER DB before a SELECT set_limit(); in case of pg_trgm.

Regards,
Akos

On Tue, 7 Aug 2018 at 21:36, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Tue, Aug 7, 2018 at 03:10:09PM -0400, Tom Lane wrote:
> > Bruce Momjian <bruce(at)momjian(dot)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.
>
> Great, thanks for the background. It is probably not worth trying to
> enhance this further.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Ancient Roman grave inscription +
>