Re: [PATCH] New default role allowing to change per-role/database settings

Hi,

Am Mittwoch, dem 08.09.2021 um 07:38 +0000 schrieb
Shinya11(dot)Kato(at)nttdata(dot)com:
> > Thanks for letting me know, I've attached a rebased v4 of this
> > patch, no other changes.

Please find attached another rebase, sorry it took so long.

I tried it, but when I used set command, tab completion did not work >
properly and an error occurred.

It's been a while, but I believe the patch only changes the ALTER ROLE
command, not the SET/SHOW commands. I thought that was more generally
useful, can you explain the SET use-case?

> ---
> postgres=> \conninfo
> You are connected to database "postgres" as user "aaa" via socket in
> "/tmp" at port "5432".
> postgres=> \du
>                                            List of roles
>  Role name |                        
> Attributes                         |         Member of        
> -----------+---------------------------------------------------------
> ---+---------------------------
>  aaa      
> |                                                            |
> {pg_change_role_settings}
>  penguin   | Superuser, Create role, Create DB, Replication, Bypass
> RLS | {}
> postgres=> show log
> log_autovacuum_min_duration        log_executor_stats                
> log_min_error_statement            log_replication_commands          
> log_timezone
> log_checkpoints                    log_file_mode                     
> log_min_messages                   log_rotation_age                  
> log_transaction_sample_rate
> log_connections                    log_hostname                      
> log_parameter_max_length           log_rotation_size                 
> log_truncate_on_rotation
> log_destination                    log_line_prefix                   
> log_parameter_max_length_on_error  log_statement                     
> logging_collector
> log_disconnections                 log_lock_waits                    
> log_parser_stats                   log_statement_sample_rate         
> logical_decoding_work_mem
> log_duration                       log_min_duration_sample           
> log_planner_stats                  log_statement_stats               
> log_error_verbosity                log_min_duration_statement        
> log_recovery_conflict_waits        log_temp_files                    
> postgres=> show log_duration ;
>  log_duration
> --------------
>  off
> (1 row)
>
> postgres=> set log
> log_parameter_max_length_on_error  logical_decoding_work_mem
> postgres=> set log_duration to on;
> 2021-09-08 16:23:39.216 JST [533860] ERROR:  permission denied to set
> parameter "log_duration"
> 2021-09-08 16:23:39.216 JST [533860] STATEMENT:  set log_duration to
> on;
> ERROR:  permission denied to set parameter "log_duration"

So this would work:

postgres=> SHOW ROLE;
role
--------------
rolesetadmin
(1 row)

postgres=> \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+---------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
rolesetadmin | Cannot login | {pg_change_role_settings}
test | Cannot login | {}

postgres=> ALTER ROLE test SET log_statement='all';
ALTER ROLE
postgres=> \drds
List of settings
Role | Database | Settings
------+----------+-------------------
test | | log_statement=all
(1 row)

I am not sure if there is anything to be done about tab completion, can
you clarify here?

Michael

--
Michael Banck
Teamleiter PostgreSQL-Team
Projektleiter
Tel.: +49 2166 9901-171
E-Mail: michael(dot)banck(at)credativ(dot)de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Geoff Richardson, Peter Lilley

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz