CREATE DATABASE db_audit_system_func;
\c db_audit_system_func
-- set guc parameter
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1/ -c "audit_system_object=134217727" > /dev/null 2>&1
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1/ -c "audit_function_exec=1" > /dev/null 2>&1
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1/ -c "audit_system_function_exec=1" > /dev/null 2>&1
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1/ -c "enable_cbm_tracking=on" > /dev/null 2>&1
-- 系统管理函数 配置设置函数
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
SELECT set_config('log_statement_stats', 'off', false);
set_config
------------
off
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%set_config%';
type | object_name | detail_info
----------------------+-------------------------------+-----------------------------------------------------------------------------
system_function_exec | set_config(text,text,boolean) | Execute system function(oid = 2078). args = (log_statement_stats,off,false)
(1 row)
-- 系统管理函数 服务器信号函数
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
SELECT pg_cancel_backend(139989266462464);
WARNING: PID 139989266462464 is not a gaussdb server thread
CONTEXT: referenced column: pg_cancel_backend
pg_cancel_backend
-------------------
f
(1 row)
SELECT pg_cancel_session(139856237819648, 139856237819648);
WARNING: PID 139856237819648 is not a gaussdb server thread
CONTEXT: referenced column: pg_cancel_session
pg_cancel_session
-------------------
f
(1 row)
SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
SELECT pg_rotate_logfile();
pg_rotate_logfile
-------------------
t
(1 row)
SELECT pg_terminate_session(139855736600320, 139855736600320);
WARNING: PID 139855736600320 is not a gaussdb server thread
CONTEXT: referenced column: pg_terminate_session
pg_terminate_session
----------------------
f
(1 row)
SELECT pg_terminate_backend(140298793514752);
WARNING: PID 140298793514752 is not a gaussdb server thread
CONTEXT: referenced column: pg_terminate_backend
pg_terminate_backend
----------------------
f
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_cancel_backend%';
type | object_name | detail_info
----------------------+---------------------------+---------------------------------------------------------------
system_function_exec | pg_cancel_backend(bigint) | Execute system function(oid = 2171). args = (139989266462464)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_cancel_session%';
type | object_name | detail_info
----------------------+----------------------------------+-------------------------------------------------------------------------------
system_function_exec | pg_cancel_session(bigint,bigint) | Execute system function(oid = 3991). args = (139856237819648,139856237819648)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_reload_conf%';
type | object_name | detail_info
----------------------+------------------+------------------------------------------------
system_function_exec | pg_reload_conf() | Execute system function(oid = 2621). args = ()
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_rotate_logfile%';
type | object_name | detail_info
----------------------+---------------------+------------------------------------------------
system_function_exec | pg_rotate_logfile() | Execute system function(oid = 2622). args = ()
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_terminate_session%';
type | object_name | detail_info
----------------------+-------------------------------------+-------------------------------------------------------------------------------
system_function_exec | pg_terminate_session(bigint,bigint) | Execute system function(oid = 2099). args = (139855736600320,139855736600320)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_terminate_backend%';
type | object_name | detail_info
----------------------+------------------------------+---------------------------------------------------------------
system_function_exec | pg_terminate_backend(bigint) | Execute system function(oid = 2096). args = (140298793514752)
(1 row)
-- 系统管理函数 备份恢复控制函数
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
\! @abs_bindir@/gsql -r -p @portstring@ -d db_audit_system_func -C -c "SELECT pg_create_restore_point('restore_audit');" > /dev/null 2>&1
\! @abs_bindir@/gsql -r -p @portstring@ -d db_audit_system_func -C -c "SELECT pg_start_backup('restore_audit');" > /dev/null 2>&1
\! @abs_bindir@/gsql -r -p @portstring@ -d db_audit_system_func -C -c "SELECT pg_stop_backup();" > /dev/null 2>&1
\! @abs_bindir@/gsql -r -p @portstring@ -d db_audit_system_func -C -c "SELECT pg_switch_xlog();" > /dev/null 2>&1
SELECT pg_cbm_get_merged_file('0/0', '0/0');
WARNING: Start lsn equals end lsn, nothing to merge.
CONTEXT: referenced column: pg_cbm_get_merged_file
pg_cbm_get_merged_file
------------------------
(1 row)
\! @abs_bindir@/gsql -r -p @portstring@ -d db_audit_system_func -C -c "SELECT gs_roach_switch_xlog();" > /dev/null 2>&1
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_create_restore_point%';
type | object_name | detail_info
----------------------+-------------------------------+-------------------------------------------------------------
system_function_exec | pg_create_restore_point(text) | Execute system function(oid = 3098). args = (restore_audit)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_start_backup%';
type | object_name | detail_info
----------------------+-------------------------------+-------------------------------------------------------------------
system_function_exec | pg_start_backup(text,boolean) | Execute system function(oid = 2172). args = (restore_audit,false)
(1 row)
SELECT type, object_name from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_stop_backup%';
type | object_name
----------------------+------------------
system_function_exec | pg_stop_backup()
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_switch_xlog%';
type | object_name | detail_info
----------------------+------------------+------------------------------------------------
system_function_exec | pg_switch_xlog() | Execute system function(oid = 2848). args = ()
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_cbm_get_merged_file%';
type | object_name | detail_info
----------------------+-----------------------------------+-------------------------------------------------------
system_function_exec | pg_cbm_get_merged_file(text,text) | Execute system function(oid = 4652). args = (0/0,0/0)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%gs_roach_switch_xlog%';
type | object_name | detail_info
------+-------------+-------------
(0 rows)
-- 系统管理函数 恢复控制函数
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
SELECT pg_last_xlog_receive_location();
pg_last_xlog_receive_location
-------------------------------
(1 row)
SELECT gs_pitr_clean_history_global_barriers('1489739011');
gs_pitr_clean_history_global_barriers
---------------------------------------
NULL
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_last_xlog_receive_location%';
type | object_name | detail_info
----------------------+---------------------------------+------------------------------------------------
system_function_exec | pg_last_xlog_receive_location() | Execute system function(oid = 3820). args = ()
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%gs_pitr_clean_history_global_barriers%';
type | object_name | detail_info
----------------------+------------------------------------------------+----------------------------------------------------------
system_function_exec | gs_pitr_clean_history_global_barriers(cstring) | Execute system function(oid = 4581). args = (1489739011)
(1 row)
-- 系统管理函数 双集群容灾控制函数
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
\! echo $GAUSSHOME | sed 's/^.*tmp_check\/install\///g' | xargs -I{} rm -f {}/bin/obsserver.key.cipher
\! echo $GAUSSHOME | sed 's/^.*tmp_check\/install\///g' | xargs -I{} rm -f {}/bin/obsserver.key.rand
\! echo $GAUSSHOME | sed 's/^.*tmp_check\/install\///g' | xargs -I{} @abs_bindir@/gs_guc generate -S 123456@pwd -D {}/bin -o obsserver > /dev/null 2>&1
SELECT * from pg_create_physical_replication_slot_extern('prs_audit', false, 'NAS;/data/nas/media/openGauss_uuid/dn1;0;0', false);
slotname | xlog_position
-----------+---------------
prs_audit |
(1 row)
SELECT gs_set_obs_delete_location('0/54000000');
gs_set_obs_delete_location
-----------------------------
000000010000000000000054_00
(1 row)
SELECT gs_hadr_do_switchover();
gs_hadr_do_switchover
-----------------------
f
(1 row)
SELECT gs_set_obs_delete_location_with_slotname('0/0', '0/0');
gs_set_obs_delete_location_with_slotname
------------------------------------------
000000010000000000000000_00
(1 row)
SELECT gs_streaming_dr_in_switchover();
gs_streaming_dr_in_switchover
-------------------------------
f
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_create_physical_replication_slot_extern%';
type | object_name | detail_info
----------------------+-----------------------------------------------------------------------+--------------------------------------------------------
system_function_exec | pg_create_physical_replication_slot_extern(name,boolean,text,boolean) | Execute system function(oid = 3790). args = (********)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%gs_set_obs_delete_location%';
type | object_name | detail_info
----------------------+-----------------------------------------------------------+----------------------------------------------------------
system_function_exec | gs_set_obs_delete_location(text) | Execute system function(oid = 9031). args = (0/54000000)
system_function_exec | gs_set_obs_delete_location_with_slotname(cstring,cstring) | Execute system function(oid = 9035). args = (0/0,0/0)
(2 rows)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%gs_hadr_do_switchover%';
type | object_name | detail_info
----------------------+-------------------------+------------------------------------------------
system_function_exec | gs_hadr_do_switchover() | Execute system function(oid = 9136). args = ()
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%gs_set_obs_delete_location_with_slotname%';
type | object_name | detail_info
----------------------+-----------------------------------------------------------+-------------------------------------------------------
system_function_exec | gs_set_obs_delete_location_with_slotname(cstring,cstring) | Execute system function(oid = 9035). args = (0/0,0/0)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%gs_streaming_dr_in_switchover%';
type | object_name | detail_info
----------------------+---------------------------------+------------------------------------------------
system_function_exec | gs_streaming_dr_in_switchover() | Execute system function(oid = 9140). args = ()
(1 row)
-- 系统管理函数 双集群容灾查询函数
-- 系统管理函数 咨询锁函数
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
SELECT pg_advisory_lock(123);
pg_advisory_lock
------------------
(1 row)
SELECT pg_advisory_lock_shared(123);
pg_advisory_lock_shared
-------------------------
(1 row)
SELECT pg_advisory_unlock(123);
pg_advisory_unlock
--------------------
t
(1 row)
SELECT pg_advisory_unlock_shared(123);
pg_advisory_unlock_shared
---------------------------
t
(1 row)
SELECT pg_advisory_unlock_all();
pg_advisory_unlock_all
------------------------
(1 row)
SELECT pg_advisory_xact_lock(123);
pg_advisory_xact_lock
-----------------------
(1 row)
SELECT pg_advisory_xact_lock_shared(123);
pg_advisory_xact_lock_shared
------------------------------
(1 row)
SELECT pg_try_advisory_lock(123);
pg_try_advisory_lock
----------------------
t
(1 row)
SELECT pg_try_advisory_lock_shared(123);
pg_try_advisory_lock_shared
-----------------------------
t
(1 row)
SELECT pg_try_advisory_xact_lock(123);
pg_try_advisory_xact_lock
---------------------------
t
(1 row)
SELECT pg_try_advisory_xact_lock_shared(123);
pg_try_advisory_xact_lock_shared
----------------------------------
t
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_advisory_lock%';
type | object_name | detail_info
----------------------+---------------------------------+---------------------------------------------------
system_function_exec | pg_advisory_lock(bigint) | Execute system function(oid = 2880). args = (123)
system_function_exec | pg_advisory_lock_shared(bigint) | Execute system function(oid = 2881). args = (123)
(2 rows)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_advisory_lock_shared%';
type | object_name | detail_info
----------------------+---------------------------------+---------------------------------------------------
system_function_exec | pg_advisory_lock_shared(bigint) | Execute system function(oid = 2881). args = (123)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_advisory_unlock%';
type | object_name | detail_info
----------------------+-----------------------------------+---------------------------------------------------
system_function_exec | pg_advisory_unlock(bigint) | Execute system function(oid = 2884). args = (123)
system_function_exec | pg_advisory_unlock_shared(bigint) | Execute system function(oid = 2885). args = (123)
system_function_exec | pg_advisory_unlock_all() | Execute system function(oid = 2892). args = ()
(3 rows)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_advisory_unlock_shared%';
type | object_name | detail_info
----------------------+-----------------------------------+---------------------------------------------------
system_function_exec | pg_advisory_unlock_shared(bigint) | Execute system function(oid = 2885). args = (123)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_advisory_unlock_all%';
type | object_name | detail_info
----------------------+--------------------------+------------------------------------------------
system_function_exec | pg_advisory_unlock_all() | Execute system function(oid = 2892). args = ()
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_advisory_xact_lock%';
type | object_name | detail_info
----------------------+--------------------------------------+---------------------------------------------------
system_function_exec | pg_advisory_xact_lock(bigint) | Execute system function(oid = 3089). args = (123)
system_function_exec | pg_advisory_xact_lock_shared(bigint) | Execute system function(oid = 3090). args = (123)
(2 rows)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_advisory_xact_lock_shared%';
type | object_name | detail_info
----------------------+--------------------------------------+---------------------------------------------------
system_function_exec | pg_advisory_xact_lock_shared(bigint) | Execute system function(oid = 3090). args = (123)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_try_advisory_lock%';
type | object_name | detail_info
----------------------+-------------------------------------+---------------------------------------------------
system_function_exec | pg_try_advisory_lock(bigint) | Execute system function(oid = 2882). args = (123)
system_function_exec | pg_try_advisory_lock_shared(bigint) | Execute system function(oid = 2883). args = (123)
(2 rows)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_try_advisory_lock_shared%';
type | object_name | detail_info
----------------------+-------------------------------------+---------------------------------------------------
system_function_exec | pg_try_advisory_lock_shared(bigint) | Execute system function(oid = 2883). args = (123)
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_try_advisory_xact_lock%';
type | object_name | detail_info
----------------------+------------------------------------------+---------------------------------------------------
system_function_exec | pg_try_advisory_xact_lock(bigint) | Execute system function(oid = 3091). args = (123)
system_function_exec | pg_try_advisory_xact_lock_shared(bigint) | Execute system function(oid = 3092). args = (123)
(2 rows)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%pg_try_advisory_xact_lock_shared%';
type | object_name | detail_info
----------------------+------------------------------------------+---------------------------------------------------
system_function_exec | pg_try_advisory_xact_lock_shared(bigint) | Execute system function(oid = 3092). args = (123)
(1 row)
-- 系统管理函数 段页式存储函数
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
CREATE TABLESPACE tsp_audit_sysfunc RELATIVE LOCATION 'audit_tablespace/audit_tablespace_1';
SELECT local_space_shrink('tsp_audit_sysfunc', (SELECT current_database()));
local_space_shrink
--------------------
0
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%local_space_shrink%';
type | object_name | detail_info
----------------------+-------------------------------+--------------------------------------------------------------------------------------
system_function_exec | local_space_shrink(text,text) | Execute system function(oid = 7006). args = (tsp_audit_sysfunc,db_audit_system_func)
(1 row)
-- 故障注入系统函数
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
SELECT gs_fault_inject(1,'1','1','1','1','1');
gs_fault_inject
-----------------
0
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%gs_fault_inject%';
type | object_name | detail_info
----------------------+--------------------------------------------------+-----------------------------------------------------------
system_function_exec | gs_fault_inject(bigint,text,text,text,text,text) | Execute system function(oid = 4000). args = (1,1,1,1,1,1)
(1 row)
-- 数据损坏检测修复函数
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
SELECT * from local_clear_bad_block_info();
result
--------
t
(1 row)
SELECT type, object_name, detail_info from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec' and object_name like '%local_clear_bad_block_info%';
type | object_name | detail_info
----------------------+------------------------------+------------------------------------------------
system_function_exec | local_clear_bad_block_info() | Execute system function(oid = 4568). args = ()
(1 row)
-- 非白名单系统函数 不审计
SELECT * from pg_delete_audit(current_date,current_date + interval '24 hours');
pg_delete_audit
-----------------
(1 row)
SELECT current_setting('audit_thread_num');
current_setting
-----------------
1
(1 row)
\! @abs_bindir@/gsql -r -p @portstring@ -d db_audit_system_func -C -c "SELECT pg_current_xlog_location();" > /dev/null 2>&1
\! @abs_bindir@/gsql -r -p @portstring@ -d db_audit_system_func -C -c "pg_database_size('db_audit_system_func');" > /dev/null 2>&1
SELECT count(*) from pg_query_audit(current_date, current_date + interval '24 hours') where type='system_function_exec';
count
-------
0
(1 row)
--reset guc parameter
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1/ -c "audit_system_object" > /dev/null 2>&1
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1/ -c "audit_function_exec=1" > /dev/null 2>&1
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1/ -c "audit_system_function_exec" > /dev/null 2>&1
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1/ -c "wal_level" > /dev/null 2>&1
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1/ -c "enable_cbm_tracking=on" > /dev/null 2>&1
--clean env
\! @abs_bindir@/gsql -r -p @portstring@ -d db_audit_system_func -C -c "DROP TABLESPACE IF EXISTS tsp_audit_sysfunc;" > /dev/null 2>&1
\! @abs_bindir@/gsql -r -p @portstring@ -d db_audit_system_func -C -c "SELECT * from pg_drop_replication_slot('prs_audit');" > /dev/null 2>&1
\c regression
CLEAN CONNECTION TO ALL FORCE FOR DATABASE db_audit_system_func;
DROP DATABASE db_audit_system_func;