----------------------------------------------------------
-- The common user should not have the permission.
---------------------------------------------------------
DROP ROLE IF EXISTS alter_system_testuser;
CREATE USER alter_system_testuser PASSWORD 'test@1233';
SET SESSION AUTHORIZATION alter_system_testuser PASSWORD 'test@1233';
show use_workload_manager;
ALTER SYSTEM SET use_workload_manager to off;
show io_limits;
ALTER SYSTEM SET io_limits to 100;
select pg_sleep(1); -- wait to reload postgres.conf file
show use_workload_manager;
show io_limits;
\c
DROP user alter_system_testuser;
------------------------------------------------------
-- there is a black list of guc for sysadmin.
------------------------------------------------------
DROP ROLE IF EXISTS alter_system_testadmin;
CREATE USER alter_system_testadmin with sysadmin PASSWORD 'test@1233';
SET SESSION AUTHORIZATION alter_system_testadmin PASSWORD 'test@1233';
show modify_initial_password;
show enable_copy_server_files;
ALTER SYSTEM SET modify_initial_password to on;
ALTER SYSTEM SET enable_copy_server_files to on;
show modify_initial_password;
show enable_copy_server_files;
\c
DROP user alter_system_testadmin;
-------------------------------------------------------
-- make sure that audit is on.
-------------------------------------------------------
show audit_enabled;
show audit_set_parameter;
show audit_user_violation;
--------------------------------------------------------
-- for POSTMASTER GUC
--------------------------------------------------------
SHOW enable_thread_pool;
ALTER SYSTEM SET enable_thread_pool to off;
ALTER SYSTEM SET enable_thread_pool to on;
------------------------------------------------------
-- for SIGHUP GUC
------------------------------------------------------
-- change
SHOW password_lock_time;
ALTER SYSTEM SET password_lock_time to 1.1;
SHOW autovacuum;
ALTER SYSTEM SET autovacuum to off;
SHOW log_destination;
ALTER SYSTEM SET log_destination to 'stderr,csvlog';
SHOW autovacuum_mode;
ALTER SYSTEM SET autovacuum_mode to 'analyze';
SHOW io_control_unit;
ALTER SYSTEM SET io_control_unit TO 10000;
SHOW vacuum_defer_cleanup_age;
ALTER SYSTEM SET vacuum_defer_cleanup_age TO 50000;
show enable_copy_server_files;
ALTER SYSTEM SET enable_copy_server_files to on;
select pg_sleep(2); -- wait to reload postgres.conf file
-- check result and change back.
SHOW password_lock_time;
ALTER SYSTEM SET password_lock_time to 1;
SHOW autovacuum;
ALTER SYSTEM SET autovacuum to on;
SHOW log_destination;
ALTER SYSTEM SET log_destination to 'stderr';
SHOW autovacuum_mode;
ALTER SYSTEM SET autovacuum_mode to mix;
SHOW io_control_unit;
ALTER SYSTEM SET io_control_unit TO 6000;
SHOW vacuum_defer_cleanup_age;
ALTER SYSTEM SET vacuum_defer_cleanup_age TO 0;
show enable_copy_server_files;
ALTER SYSTEM SET enable_copy_server_files to off;
select pg_sleep(2); -- wait to reload postgres.conf file
SHOW password_lock_time;
SHOW autovacuum;
SHOW log_destination;
SHOW autovacuum_mode;
SHOW io_control_unit;
SHOW enable_copy_server_files;
-- some err case
ALTER SYSTEM SET password_lock_time to true;
ALTER SYSTEM SET autovacuum to 'lalala';
ALTER SYSTEM SET log_destination to 'abcdefg';
ALTER SYSTEM SET autovacuum_mode to 123;
ALTER SYSTEM SET autovacuum_mode to lalala;
ALTER SYSTEM SET io_control_unit TO -100;
ALTER SYSTEM SET io_control_unit TO 1.1;
ALTER SYSTEM SET vacuum_defer_cleanup_age TO 5.1;
ALTER SYSTEM SET vacuum_defer_cleanup_age TO '8#@da%';
select pg_sleep(2); -- wait to reload postgres.conf file
SHOW password_lock_time;
SHOW autovacuum;
SHOW log_destination;
SHOW autovacuum_mode;
SHOW io_control_unit;
------------------------------------------------------
-- FOR BACKEND GUC
------------------------------------------------------
show ignore_system_indexes;
ALTER SYSTEM SET ignore_system_indexes TO on;
show ignore_system_indexes;
ALTER SYSTEM SET ignore_system_indexes TO off;
show ignore_system_indexes;
----------------------------------------------------
-- for USERSET GUC
----------------------------------------------------
show io_limits;
ALTER SYSTEM SET io_limits to 100;
show io_limits;
ALTER SYSTEM SET io_limits to 0;
show io_limits;
-----------------------------------------------------
-- for SUSET GUC
----------------------------------------------------
show autoanalyze;
ALTER SYSTEM SET autoanalyze to on;
show autoanalyze;
ALTER SYSTEM SET autoanalyze to off;
show autoanalyze;
-----------------------------------------------------
-- UNSUPPORT SET TO DEFAULT
-- NOTICE: we change io_control_unit here and not recover.
-----------------------------------------------------
SHOW io_control_unit;
ALTER SYSTEM SET io_control_unit TO 10000;
ALTER SYSTEM SET io_control_unit TO default;
select pg_sleep(1);
SHOW io_control_unit;
------------------------------------------------------
-- exception in configure file
-- 1.duplicate parameter
-- 2.mixed case
------------------------------------------------------
\! echo "# io_control_Unit = 50000" >> @abs_srcdir@/tmp_check/datanode1/postgresql.conf
\! echo "io_control_uNit = 50000" >> @abs_srcdir@/tmp_check/datanode1/postgresql.conf
\! echo "io_control_unIt = 60000" >> @abs_srcdir@/tmp_check/datanode1/postgresql.conf
\! echo "# io_control_uniT = 80000" >> @abs_srcdir@/tmp_check/datanode1/postgresql.conf
ALTER SYSTEM SET "io_control_UNit" TO 90000;
\! grep "io_control_" @abs_srcdir@/tmp_check/datanode1/postgresql.conf
select pg_sleep(1);
SHOW io_control_unit;
-------------------------------------------------------
-- exception: configure file does not exist.
-- expect: read bak file.
-- NOTICE: we recover io_control_unit here.
-------------------------------------------------------
\! rm @abs_srcdir@/tmp_check/datanode1/postgresql.conf
ALTER SYSTEM SET "io_control_unIT" TO 6000;
select pg_sleep(1);
SHOW io_control_unit;
\! grep "io_control_" @abs_srcdir@/tmp_check/datanode1/postgresql.conf
\! grep "io_control_" @abs_srcdir@/tmp_check/datanode1/postgresql.conf.bak
-------------------------------------------------------
-- can not in a transaction
-------------------------------------------------------
BEGIN;
SHOW autovacuum;
ALTER SYSTEM SET autovacuum to off;
SHOW autovacuum;
ALTER SYSTEM SET autovacuum to on;
SHOW autovacuum;
END;
-------------------------------------------------------
-- shoule be audited.
-------------------------------------------------------
SELECT type,result,userid,database,client_conninfo,object_name,detail_info FROM pg_query_audit('2000-01-01 08:00:00','9999-01-01 08:00:00') where detail_info like 'ALTER SYSTEM SET %';