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, Aug 7, 2018 at 03:10:09PM -0400, Tom Lane wrote:
> 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.
Great, thanks for the background. It is probably not worth trying to
enhance this further.
--
Bruce Momjian <bruce@momjian.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 +