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

On Tue, Jul 10, 2018 at 08:59:03AM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15271
> Logged by: Akos Vandra
> Email address: axos88(at)gmail(dot)com
> PostgreSQL version: 10.4
> Operating system: Mac OS X, Linux
> Description:
>
> I am using the pg_trgm extension, and would like to change the
> similarity_threshold GUC parameter default value.
>
> Seems like when trying to alter a GUC parameter of an extension that was not
> yet loaded into session memory, the ALTER DATABASE command returns with an
> unexpected message, `ERROR: permission denied to set parameter
> "pg_trgm.similarity_threshold"`, although that is NOT the problem.
>
> I understand this may have sever implications, but obviously the expected
> behaviour would be to be able to set that GUC parameter regardless if the
> extension has been loaded into session memory (and probably load it if
> not).
>
> Workaround:
> Before the `alter database` command issue a command such as `select
> show_limit();` to load the extension into session memory.
>
> Repro:
> 1. CONNECT as superuser
> 1. CREATE USER test PASSWORD 'test';
> 2. CREATE DATABASE test OWNER test;
> 3. DISCONNECT AND CONNECT as test user
> 4. ALTER DATABASE test SET pg_trgm.similarity_threshold = 0.42;
>
> Expected:
> Successful alter
>
> Actual:
> ERROR: permission denied to set parameter
> "pg_trgm.similarity_threshold"
>
> Workaround:
>
> test=> alter database test set pg_trgm.similarity_threshold = 0.42;
> ERROR: permission denied to set parameter "pg_trgm.similarity_threshold"
> test=> select show_limit();
> show_limit
> ------------
> 0.2
> (1 row)
>
> test=> alter database test set pg_trgm.similarity_threshold = 0.42;
> ALTER DATABASE
>
> Workaround effect:
>
> test=> select show_limit();
> show_limit
> ------------
> 0.2
> (1 row)
>
> test=> \q
> $ psql -U test -d test
> psql (10.4)
> Type "help" for help.
>
> test=> select show_limit();
> show_limit
> ------------
> 0.42
> (1 row)

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:

$ psql -U postgres postgres
psql (10.5)
Type "help" for help.

postgres=> CREATE USER test PASSWORD 'test';
CREATE ROLE
postgres=> CREATE DATABASE test OWNER test;
CREATE DATABASE

postgres=> \c test test
You are now connected to database "test" as user "test".
test=> ALTER DATABASE test SET pg_trgm.similarity_threshold = 0.42;
--> ERROR: permission denied to set parameter "pg_trgm.similarity_threshold"
test=> ALTER DATABASE test SET work_mem = '200MB';
--> ALTER DATABASE
test=> SET x.y = 0;
--> SET

test=> \c test postgres
You are now connected to database "test" as user "postgres".
test=> ALTER DATABASE test SET pg_trgm.similarity_threshold = 0.42;
--> ALTER DATABASE

The pastern I see is that non-superusers can't set custom GUCs via ALTER
DATABASE, though they can via plain SET. Our ALTER DATABASE
documentation has vague wording wording about this:

Only the database owner or a superuser can change the session defaults
for a database. Certain variables cannot be set this way, or can only be
set by a superuser.

I am not sure how we could improve this.

--
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 +