Re: BUG #15271: Documentation / Error reporting on GUC parameterchange - Mailing list pgsql-bugs

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@gmail.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@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 +