PostgreSQL中利用pgaudit审计扩展记录SQL审计，以及使用fdw分析审计日志

1，pg_audit编译安装

参考：https://github.com/pgaudit/pgaudit

1. --pgaudit 编译安装
3. git clone https://github.com/pgaudit/pgaudit.git
5. cd pgaudit/
7. git checkout REL_16_STABLE --这里checkout出来postgresql16版本，中间的数字是PostgreSQL的大版本号，REL_15_STABLE，REL_16_STABLE，REL_17_STABLE等
9. --如下是编译安装后相关文件的位置
11. root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package/pgaudit# make install USE_PGXS=1 PG_CONFIG=/usr/local/pgsql16/server/bin/pg_config
12. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/usr/local/pgsql16/server/include/server -I/usr/local/pgsql16/server/include/internal  -D_GNU_SOURCE   -c -o pgaudit.o pgaudit.c
13. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o pgaudit.so pgaudit.o  -L/usr/local/pgsql16/server/lib    -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql16/server/lib',--enable-new-dtags  -fvisibility=hidden
14. /usr/bin/mkdir -p '/usr/local/pgsql16/server/lib'
15. /usr/bin/mkdir -p '/usr/local/pgsql16/server/share/extension'
16. /usr/bin/mkdir -p '/usr/local/pgsql16/server/share/extension'
17. /usr/bin/install -c -m 755  pgaudit.so '/usr/local/pgsql16/server/lib/pgaudit.so'
18. /usr/bin/install -c -m 644 .//pgaudit.control '/usr/local/pgsql16/server/share/extension/'
19. /usr/bin/install -c -m 644 .//pgaudit--16.1.sql .//pgaudit--16.0--16.1.sql  '/usr/local/pgsql16/server/share/extension/'
20. root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package/pgaudit# ^C
21. root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package/pgaudit#
24. #postgresql.conf中添加动态库
25. shared_preload_libraries = 'pg_stat_statements, pg_cron, pgaudit'        # (change requires restart)
27. #重启postgresql实例
28. systemctl restart postgresql9300
30. #检查动态库是否生效
31. show shared_preload_libraries;
34. #检查相关扩展
35. select name,setting from pg_settings where name like '%audit%';
37. name                          |setting|
38. ------------------------------+-------+
39. pgaudit.log                   |none   |
40. pgaudit.log_catalog           |on     |
41. pgaudit.log_client            |off    |
42. pgaudit.log_level             |log    |
43. pgaudit.log_parameter         |off    |
44. pgaudit.log_parameter_max_size|0      |
45. pgaudit.log_relation          |off    |
46. pgaudit.log_rows              |off    |
47. pgaudit.log_statement         |on     |
48. pgaudit.log_statement_once    |off    |
49. pgaudit.role                  |       |

复制代码

 
2，pg_audit扩展创建 

1. --#创建pg_audit扩展
2. create extension pgaudit;
4. drop extension pgaudit;
6. set pgaudit.log = '';
7. alter system set pgaudit.role = '';
9. select pg_reload_conf();
13. -- 基于库级别的审计
14. ALTER DATABASE db01 SET pgaudit.log = 'read, write, ddl';
16. -- 审计用户级别的审计
17. alter system set pgaudit.role = 'appuser1,appuser2';
22. select pg_reload_conf();
23. select name,setting from pg_settings where name like '%audit%';

复制代码

3，pg_audit开启审计

查了网站的资料，基本上都是翻译pg_audit github上官方的文档，都没有说清楚到底怎么审计。
比如最常见的两种审计：1，记录某个库下的所有的操作；2，记录某个数据库用户的所有的操作
 
3.1 记录数据库用户的审计 

记录某个用户的所有操作行为

1. 3.1. 基于数据库用户的审计：
2. ALTER ROLE *** SET pgaudit.log = '***';
5. --创建测试用户
6. CREATE ROLE appuser WITH LOGIN PASSWORD '******';
9. -- 授予连接权限
10. GRANT CONNECT ON DATABASE db03 TO appuser;
11. -- 授权使用某 schema(切换到DB03下执行)
12. GRANT usage,create ON SCHEMA public TO appuser;
13. alter default privileges in schema public GRANT all ON TABLES TO appuser;
16. -- 授予连接权限
17. GRANT CONNECT ON DATABASE db04 TO appuser;
18. -- 授权使用某 schema（切换到DB04下执行）
19. GRANT usage,create ON SCHEMA public TO appuser;
20. alter default privileges in schema public GRANT all ON TABLES TO appuser;
23. 对appuser开启审计，记录其所有操作（任务数据库下执行）
24. ALTER ROLE appuser SET pgaudit.log = 'all';
27. 2025-08-02 13:49:52.433 CST [374923] user=,db=,app=,host= LOG:  checkpoint starting: time
28. 2025-08-02 13:49:52.855 CST [374923] user=,db=,app=,host= LOG:  checkpoint complete: wrote 7 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.405 s, sync=0.005 s, total=0.423 s; sync files=6, longest=0.003 s, average=0.001 s; distance=9 kB, estimate=9 kB; lsn=0/1D002558, redo lsn=0/1D002520
29. 2025-08-02 13:51:06.017 CST [374997] user=appuser,db=postgres,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
30. 2025-08-02 13:51:06.029 CST [374997] user=appuser,db=postgres,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
31. 2025-08-02 13:51:06.040 CST [374997] user=appuser,db=postgres,app=DBeaver 25.1.0 - Read PostgreSQL database list,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - Read PostgreSQL database list',<not logged>
32. 2025-08-02 13:51:06.053 CST [374997] user=appuser,db=postgres,app=DBeaver 25.1.0 - Read PostgreSQL database list,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT db.oid,db.* FROM pg_catalog.pg_database db WHERE 1 = 1 AND datallowconn AND NOT datistemplate OR db.datname =$1
33.         ORDER BY db.datname",<not logged>
34. 2025-08-02 13:51:06.065 CST [374997] user=appuser,db=postgres,app=DBeaver 25.1.0 - Read PostgreSQL database list,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,READ,SELECT,,,SELECT current_database(),<not logged>
35. 2025-08-02 13:51:06.140 CST [374998] user=appuser,db=postgres,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
36. 2025-08-02 13:51:06.151 CST [374998] user=appuser,db=postgres,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
37. 2025-08-02 13:51:06.162 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - Main <postgres>',<not logged>
38. 2025-08-02 13:51:06.173 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
39. 2025-08-02 13:51:06.186 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,READ,SELECT,,,"SELECT n.oid,n.*,d.description FROM pg_catalog.pg_namespace n
40.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
41.          ORDER BY nspname",<not logged>
42. 2025-08-02 13:51:06.198 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,"SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = $1",<not logged>
43. 2025-08-02 13:51:06.211 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,"SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid   FROM pg_catalog.pg_type   LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE pg_type.oid = $1  ORDER BY sp.r, pg_type.oid DESC",<not logged>
44. 2025-08-02 13:51:06.222 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,MISC,SHOW,,,SHOW search_path,<not logged>
45. 2025-08-02 13:51:06.296 CST [374999] user=appuser,db=postgres,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
46. 2025-08-02 13:51:06.308 CST [374999] user=appuser,db=postgres,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
47. 2025-08-02 13:51:06.322 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - Metadata <postgres>',<not logged>
48. 2025-08-02 13:51:06.335 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
49. 2025-08-02 13:51:06.346 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,MISC,SHOW,,,SHOW search_path,<not logged>
50. 2025-08-02 13:51:06.358 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,select * from pg_catalog.pg_settings where name=$1,<not logged>
51. 2025-08-02 13:51:06.371 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,"select string_agg(word, ',') from pg_catalog.pg_get_keywords() where word <> ALL ('{a,abs,absolute,action,ada,add,admin,after,all,allocate,alter,always,and,any,are,array,as,asc,asensitive,assertion,assignment,asymmetric,at,atomic,attribute,attributes,authorization,avg,before,begin,bernoulli,between,bigint,binary,blob,boolean,both,breadth,by,c,call,called,cardinality,cascade,cascaded,case,cast,catalog,catalog_name,ceil,ceiling,chain,char,char_length,character,character_length,character_set_catalog,character_set_name,character_set_schema,characteristics,characters,check,checked,class_origin,clob,close,coalesce,cobol,code_units,collate,collation,collation_catalog,collation_name,collation_schema,collect,column,column_name,command_function,command_function_code,commit,committed,condition,condition_number,connect,connection_name,constraint,constraint_catalog,constraint_name,constraint_schema,constraints,constructors,contains,continue,convert,corr,corresponding,count,covar_pop,covar_samp,create,cross,cube,cume_dist,current,current_collation,current_date,current_default_transform_group,current_path,current_role,current_time,current_timestamp,current_transform_group_for_type,current_user,cursor,cursor_name,cycle,data,date,datetime_interval_code,datetime_interval_precision,day,deallocate,dec,decimal,declare,default,defaults,deferrable,deferred,defined,definer,degree,delete,dense_rank,depth,deref,derived,desc,describe,descriptor,deterministic,diagnostics,disconnect,dispatch,distinct,domain,double,drop,dynamic,dynamic_function,dynamic_function_code,each,element,else,end,end-exec,equals,escape,every,except,exception,exclude,excluding,exec,execute,exists,exp,external,extract,false,fetch,filter,final,first,float,floor,following,for,foreign,fortran,found,free,from,full,function,fusion,g,general,get,global,go,goto,grant,granted,group,grouping,having,hierarchy,hold,hour,identity,immediate,implementation,in,including,increment,indicator,initially,inner,inout,input,insensitive,insert,instance,instantiable,int,integer,intersect,intersection,interval,into,invoker,is,isolation,join,k,key,key_member,key_type,language,large,last,lateral,leading,left,length,level,like,ln,local,localtime,localtimestamp,locator,lower,m,map,match,matched,max,maxvalue,member,merge,message_length,message_octet_length,message_text,method,min,minute,minvalue,mod,modifies,module,month,more,multiset,mumps,name,names,national,natural,nchar,nclob,nesting,new,next,no,none,normalize,normalized,not,""null"",nullable,nullif,nulls,number,numeric,object,octet_length,octets,of,old,on,only,open,option,options,or,order,ordering,ordinality,others,out,outer,output,over,overlaps,overlay,overriding,pad,parameter,parameter_mode,parameter_name,parameter_ordinal_position,parameter_specific_catalog,parameter_specific_name,parameter_specific_schema,partial,partition,pascal,path,percent_rank,percentile_cont,percentile_disc,placing,pli,position,power,preceding,precision,prepare,preserve,primary,prior,privileges,procedure,public,range,rank,read,reads,real,recursive,ref,references,referencing,regr_avgx,regr_avgy,regr_count,regr_intercept,regr_r2,regr_slope,regr_sxx,regr_sxy,regr_syy,relative,release,repeatable,restart,result,return,returned_cardinality,returned_length,returned_octet_length,returned_sqlstate,returns,revoke,right,role,rollback,rollup,routine,routine_catalog,routine_name,routine_schema,row,row_count,row_number,rows,savepoint,scale,schema,schema_name,scope_catalog,scope_name,scope_schema,scroll,search,second,section,security,select,self,sensitive,sequence,serializable,server_name,session,session_user,set,sets,similar,simple,size,smallint,some,source,space,specific,specific_name,specifictype,sql,sqlexception,sqlstate,sqlwarning,sqrt,start,state,statement,static,stddev_pop,stddev_samp,structure,style,subclass_origin,submultiset,substring,sum,symmetric,system,system_user,table,table_name,tablesample,temporary,then,ties,time,timestamp,timezone_hour,timezone_minute,to,top_level_count,trailing,transaction,transaction_active,transactions_committed,transactions_rolled_back,transform,transforms,translate,translation,treat,trigger,trigger_catalog,trigger_name,trigger_schema,trim,true,type,uescape,unbounded,uncommitted,under,union,unique,unknown,unnamed,unnest,update,upper,usage,user,user_defined_type_catalog,user_defined_type_code,user_defined_type_name,user_defined_type_schema,using,value,values,var_pop,var_samp,varchar,varying,view,when,whenever,where,width_bucket,window,with,within,without,work,write,year,zone}'::text[])",<not logged>
52. 2025-08-02 13:51:06.386 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,READ,SELECT,,,SELECT version(),<not logged>
53. 2025-08-02 13:51:06.397 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,9,1,READ,SELECT,,,SELECT * FROM pg_catalog.pg_enum WHERE 1<>1 LIMIT 1,<not logged>
54. 2025-08-02 13:51:06.409 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,10,1,READ,SELECT,,,SELECT reltype FROM pg_catalog.pg_class WHERE 1<>1 LIMIT 1,<not logged>
55. 2025-08-02 13:51:06.422 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,11,1,READ,SELECT,,,"SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description
56.         FROM pg_catalog.pg_type t
57.         LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
58.         LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
59.         LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
60.         WHERE t.typname IS NOT NULL
61.         AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')",<not logged>
62. 2025-08-02 13:51:06.487 CST [374999] user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,12,1,READ,SELECT,,,SELECT * FROM pg_catalog.pg_enum,<not logged>
63. 2025-08-02 13:51:06.593 CST [375000] user=appuser,db=db03,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
64. 2025-08-02 13:51:06.604 CST [375000] user=appuser,db=db03,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
65. 2025-08-02 13:51:06.616 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - SQLEditor <Script-8.sql>',<not logged>
66. 2025-08-02 13:51:06.627 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
67. 2025-08-02 13:51:06.709 CST [375001] user=appuser,db=db03,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
68. 2025-08-02 13:51:06.721 CST [375001] user=appuser,db=db03,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
69. 2025-08-02 13:51:06.733 CST [375001] user=appuser,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - Main <db03>',<not logged>
70. 2025-08-02 13:51:06.745 CST [375001] user=appuser,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
71. 2025-08-02 13:51:06.759 CST [375001] user=appuser,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,READ,SELECT,,,"SELECT n.oid,n.*,d.description FROM pg_catalog.pg_namespace n
72.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
73.          ORDER BY nspname",<not logged>
74. 2025-08-02 13:51:06.772 CST [375001] user=appuser,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,"SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = $1",<not logged>
75. 2025-08-02 13:51:06.785 CST [375001] user=appuser,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,"SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid   FROM pg_catalog.pg_type   LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE pg_type.oid = $1  ORDER BY sp.r, pg_type.oid DESC",<not logged>
76. 2025-08-02 13:51:06.797 CST [375001] user=appuser,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,MISC,SHOW,,,SHOW search_path,<not logged>
77. 2025-08-02 13:51:06.892 CST [375002] user=appuser,db=db03,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
78. 2025-08-02 13:51:06.913 CST [375002] user=appuser,db=db03,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
79. 2025-08-02 13:51:06.925 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - Metadata <db03>',<not logged>
80. 2025-08-02 13:51:06.938 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
81. 2025-08-02 13:51:06.950 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,MISC,SHOW,,,SHOW search_path,<not logged>
82. 2025-08-02 13:51:06.965 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,"SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description
83.         FROM pg_catalog.pg_type t
84.         LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
85.         LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
86.         LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
87.         WHERE t.typname IS NOT NULL
88.         AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')",<not logged>
89. 2025-08-02 13:51:07.017 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,SELECT * FROM pg_catalog.pg_enum,<not logged>
90. 2025-08-02 13:51:07.026 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,MISC,SHOW,,,SHOW search_path,<not logged>
91. 2025-08-02 13:51:07.767 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
92.         FROM pg_catalog.pg_class c
93.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
94.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
95. 2025-08-02 13:51:07.781 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,9,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
96.         FROM pg_catalog.pg_class c
97.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
98.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
99. 2025-08-02 13:51:07.797 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,10,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
100.         FROM pg_catalog.pg_attribute a
101.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
102.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
103.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
104.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
105.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
106.         ORDER BY a.attnum",<not logged>
107. 2025-08-02 13:51:07.813 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,11,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
108.         FROM pg_catalog.pg_class c
109.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
110.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
111. 2025-08-02 13:51:07.826 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,12,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
112.         FROM pg_catalog.pg_class c
113.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
114.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
115. 2025-08-02 13:51:07.839 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,13,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
116.         FROM pg_catalog.pg_class c
117.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
118.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
119. 2025-08-02 13:51:07.879 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,14,1,READ,SELECT,,,"SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = $1",<not logged>
120. 2025-08-02 13:51:07.892 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,15,1,READ,SELECT,,,"SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid   FROM pg_catalog.pg_type   LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE pg_type.oid = $1  ORDER BY sp.r, pg_type.oid DESC",<not logged>
121. 2025-08-02 13:51:18.677 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,16,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
122.         FROM pg_catalog.pg_attribute a
123.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
124.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
125.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
126.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
127.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
128.         ORDER BY a.attnum",<not logged>
129. 2025-08-02 13:51:22.015 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,DDL,DROP TABLE,,,"
130.         drop table t01",<not logged>
131. 2025-08-02 13:51:26.653 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,DDL,CREATE TABLE,,,create table t01 (c1 int),<not logged>
132. 2025-08-02 13:51:27.878 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,WRITE,INSERT,,,insert into t1 values(1),<not logged>
133. 2025-08-02 13:51:29.013 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,9,1,READ,SELECT,,,"select * from t1
134.         ",<not logged>
135. 2025-08-02 13:51:29.027 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,10,1,READ,SELECT,,,"SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16880 AS oid , 1 AS attnum) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) ",<not logged>
136. 2025-08-02 13:51:29.042 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,17,1,READ,SELECT,,,"SELECT i.*,i.indkey as keys,c.relname,c.relnamespace,c.relam,c.reltablespace,tc.relname as tabrelname,dsc.description,pg_catalog.pg_get_expr(i.indpred, i.indrelid) as pred_expr,pg_catalog.pg_get_expr(i.indexprs, i.indrelid, true) as expr,pg_catalog.pg_relation_size(i.indexrelid) as index_rel_size,pg_catalog.pg_stat_get_numscans(i.indexrelid) as index_num_scans
137.         FROM pg_catalog.pg_index i
138.         INNER JOIN pg_catalog.pg_class c ON c.oid=i.indexrelid
139.         INNER JOIN pg_catalog.pg_class tc ON tc.oid=i.indrelid
140.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON i.indexrelid=dsc.objoid
141.         WHERE  i.indrelid=$1 ORDER BY tabrelname, c.relname",<not logged>
142. 2025-08-02 13:51:29.057 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,18,1,READ,SELECT,,,"SELECT c.oid,c.*,t.relname as tabrelname,rt.relnamespace as refnamespace,d.description, case when c.contype='c' then ""substring""(pg_get_constraintdef(c.oid), 7) else null end consrc_copy
143.         FROM pg_catalog.pg_constraint c
144.         INNER JOIN pg_catalog.pg_class t ON t.oid=c.conrelid
145.         LEFT OUTER JOIN pg_catalog.pg_class rt ON rt.oid=c.confrelid
146.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_constraint'::regclass
147.         WHERE c.conrelid=$1
148.         ORDER BY c.oid",<not logged>
149. 2025-08-02 13:51:29.070 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,19,1,READ,SELECT,,,"SELECT i.*,c.relnamespace FROM pg_catalog.pg_inherits i,pg_catalog.pg_class c WHERE i.inhrelid=$1 AND c.oid=i.inhparent ORDER BY i.inhseqno",<not logged>
150. 2025-08-02 13:51:31.377 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,11,1,READ,SELECT,,,select count(1) from t1,<not logged>
151. 2025-08-02 13:51:32.343 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,12,1,WRITE,DELETE,,,"delete from t1
152.         ",<not logged>
153. 2025-08-02 13:51:33.859 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,13,1,READ,SELECT,,,"
154.         select count(1) from t1",<not logged>
155. 2025-08-02 13:51:35.578 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,14,1,DDL,CREATE TABLE,,,"create table t02 (c1 int)
156.         ",<not logged>
157. 2025-08-02 13:51:37.162 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:  relation "t2" does not exist at character 15
158. 2025-08-02 13:51:37.162 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:  select * from t2
159.        
160. 2025-08-02 13:51:39.968 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:  relation "t2" does not exist at character 13
161. 2025-08-02 13:51:39.968 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:  insert into t2 values(1)
162. 2025-08-02 13:51:42.789 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:  relation "t02" already exists
163. 2025-08-02 13:51:42.789 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:  
164.         create table t02 (c1 int)
165.        
166. 2025-08-02 13:51:46.868 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,15,1,READ,SELECT,,,"SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in('r','v','m','f','p') AND pc.relname ILIKE $1 AND pc.relnamespace IN ($2,$3) ORDER BY pc.relname LIMIT 10",<not logged>
167. 2025-08-02 13:51:46.880 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,16,1,READ,SELECT,,,"SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3) ORDER BY pp.proname LIMIT 10",<not logged>
168. 2025-08-02 13:51:48.946 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,17,1,READ,SELECT,,,"SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in('r','v','m','f','p') AND pc.relname ILIKE $1 AND pc.relnamespace IN ($2,$3) ORDER BY pc.relname LIMIT 10",<not logged>
169. 2025-08-02 13:51:48.957 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,18,1,READ,SELECT,,,"SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3) ORDER BY pp.proname LIMIT 9",<not logged>
170. 2025-08-02 13:51:50.598 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,19,1,READ,SELECT,,,"select * from t02
171.         ",<not logged>
172. 2025-08-02 13:51:50.612 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,20,1,READ,SELECT,,,"SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16895 AS oid , 1 AS attnum) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) ",<not logged>
173. 2025-08-02 13:51:51.808 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,21,1,WRITE,DELETE,,,"
174.         delete from t02",<not logged>
175. 2025-08-02 13:51:54.331 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,22,1,READ,SELECT,,,"SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in('r','v','m','f','p') AND pc.relname ILIKE $1 AND pc.relnamespace IN ($2,$3) ORDER BY pc.relname LIMIT 10",<not logged>
176. 2025-08-02 13:51:54.342 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,23,1,READ,SELECT,,,"SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3) ORDER BY pp.proname LIMIT 10",<not logged>
177. 2025-08-02 13:51:55.996 CST [375000] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,24,1,READ,SELECT,,,select count(1) from t02,<not logged>
178. 2025-08-02 13:52:01.258 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,9,1,READ,SELECT,,,select description from pg_shdescription join pg_database on objoid = pg_database.oid where datname = $1,<not logged>
179. 2025-08-02 13:52:01.270 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,10,1,READ,SELECT,,,select description from pg_shdescription join pg_database on objoid = pg_database.oid where datname = $1,<not logged>
180. 2025-08-02 13:52:01.282 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,11,1,READ,SELECT,,,select description from pg_shdescription join pg_database on objoid = pg_database.oid where datname = $1,<not logged>
181. 2025-08-02 13:52:01.293 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,12,1,READ,SELECT,,,select description from pg_shdescription join pg_database on objoid = pg_database.oid where datname = $1,<not logged>
182. 2025-08-02 13:52:01.305 CST [374998] user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:  AUDIT: SESSION,13,1,READ,SELECT,,,select description from pg_shdescription join pg_database on objoid = pg_database.oid where datname = $1,<not logged>
183. 2025-08-02 13:52:02.836 CST [375011] user=appuser,db=db04,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
184. 2025-08-02 13:52:02.847 CST [375011] user=appuser,db=db04,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
185. 2025-08-02 13:52:02.858 CST [375011] user=appuser,db=db04,app=DBeaver 25.1.0 - Main <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - Main <db04>',<not logged>
186. 2025-08-02 13:52:02.870 CST [375011] user=appuser,db=db04,app=DBeaver 25.1.0 - Main <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
187. 2025-08-02 13:52:02.884 CST [375011] user=appuser,db=db04,app=DBeaver 25.1.0 - Main <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,READ,SELECT,,,"SELECT n.oid,n.*,d.description FROM pg_catalog.pg_namespace n
188.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
189.          ORDER BY nspname",<not logged>
190. 2025-08-02 13:52:02.896 CST [375011] user=appuser,db=db04,app=DBeaver 25.1.0 - Main <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,"SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = $1",<not logged>
191. 2025-08-02 13:52:02.909 CST [375011] user=appuser,db=db04,app=DBeaver 25.1.0 - Main <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,"SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid   FROM pg_catalog.pg_type   LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE pg_type.oid = $1  ORDER BY sp.r, pg_type.oid DESC",<not logged>
192. 2025-08-02 13:52:02.920 CST [375011] user=appuser,db=db04,app=DBeaver 25.1.0 - Main <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,MISC,SHOW,,,SHOW search_path,<not logged>
193. 2025-08-02 13:52:02.998 CST [375012] user=appuser,db=db04,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
194. 2025-08-02 13:52:03.015 CST [375012] user=appuser,db=db04,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
195. 2025-08-02 13:52:03.028 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - Metadata <db04>',<not logged>
196. 2025-08-02 13:52:03.042 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
197. 2025-08-02 13:52:03.055 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,MISC,SHOW,,,SHOW search_path,<not logged>
198. 2025-08-02 13:52:03.072 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,"SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description
199.         FROM pg_catalog.pg_type t
200.         LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
201.         LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
202.         LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
203.         WHERE t.typname IS NOT NULL
204.         AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')",<not logged>
205. 2025-08-02 13:52:03.126 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,SELECT * FROM pg_catalog.pg_enum,<not logged>
206. 2025-08-02 13:52:03.983 CST [375013] user=appuser,db=db04,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
207. 2025-08-02 13:52:03.994 CST [375013] user=appuser,db=db04,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
208. 2025-08-02 13:52:04.006 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - SQLEditor <Script-8.sql>',<not logged>
209. 2025-08-02 13:52:04.018 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
210. 2025-08-02 13:52:04.029 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,MISC,SHOW,,,SHOW search_path,<not logged>
211. 2025-08-02 13:52:04.599 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
212.         FROM pg_catalog.pg_class c
213.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
214.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
215. 2025-08-02 13:52:04.612 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,9,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
216.         FROM pg_catalog.pg_class c
217.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
218.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
219. 2025-08-02 13:52:04.626 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,10,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
220.         FROM pg_catalog.pg_class c
221.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
222.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
223. 2025-08-02 13:52:04.663 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,11,1,READ,SELECT,,,"SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = $1",<not logged>
224. 2025-08-02 13:52:04.677 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,12,1,READ,SELECT,,,"SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid   FROM pg_catalog.pg_type   LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE pg_type.oid = $1  ORDER BY sp.r, pg_type.oid DESC",<not logged>
225. 2025-08-02 13:52:04.696 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,13,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
226.         FROM pg_catalog.pg_attribute a
227.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
228.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
229.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
230.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
231.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
232.         ORDER BY a.attnum",<not logged>
233. 2025-08-02 13:52:09.425 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:  relation "t1" does not exist at character 13
234. 2025-08-02 13:52:09.425 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:  insert into t1 values(1)
235. 2025-08-02 13:52:10.441 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:  relation "t1" does not exist at character 15
236. 2025-08-02 13:52:10.441 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:  select * from t1
237. 2025-08-02 13:52:14.358 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,"SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in('r','v','m','f','p') AND pc.relname ILIKE $1 AND pc.relnamespace IN ($2,$3) ORDER BY pc.relname LIMIT 10",<not logged>
238. 2025-08-02 13:52:14.371 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,"SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3) ORDER BY pp.proname LIMIT 10",<not logged>
239. 2025-08-02 13:52:15.679 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,READ,SELECT,,,"SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in('r','v','m','f','p') AND pc.relname ILIKE $1 AND pc.relnamespace IN ($2,$3) ORDER BY pc.relname LIMIT 10",<not logged>
240. 2025-08-02 13:52:15.691 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,9,1,READ,SELECT,,,"SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3) ORDER BY pp.proname LIMIT 10",<not logged>
241. 2025-08-02 13:52:20.776 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:  table "t01" does not exist
242. 2025-08-02 13:52:20.776 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:  drop table t01
243. 2025-08-02 13:52:22.245 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,10,1,DDL,CREATE TABLE,,,create table t01 (c1 int),<not logged>
244. 2025-08-02 13:52:23.508 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,11,1,WRITE,INSERT,,,"insert into t01 values(1)
245.         ",<not logged>
246. 2025-08-02 13:52:24.364 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,12,1,READ,SELECT,,,"select * from t01
247.         ",<not logged>
248. 2025-08-02 13:52:24.378 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,13,1,READ,SELECT,,,"SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16898 AS oid , 1 AS attnum) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) ",<not logged>
249. 2025-08-02 13:52:25.298 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,14,1,WRITE,DELETE,,,delete from t01,<not logged>
250. 2025-08-02 13:52:26.301 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:  relation "t1" does not exist at character 22
251. 2025-08-02 13:52:26.301 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:  select count(1) from t1
252. 2025-08-02 13:52:29.853 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,15,1,READ,SELECT,,,"SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in('r','v','m','f','p') AND pc.relname ILIKE $1 AND pc.relnamespace IN ($2,$3) ORDER BY pc.relname LIMIT 10",<not logged>
253. 2025-08-02 13:52:29.865 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,16,1,READ,SELECT,,,"SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3) ORDER BY pp.proname LIMIT 10",<not logged>
254. 2025-08-02 13:52:30.486 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,17,1,READ,SELECT,,,"SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in('r','v','m','f','p') AND pc.relname ILIKE $1 AND pc.relnamespace IN ($2,$3) ORDER BY pc.relname LIMIT 10",<not logged>
255. 2025-08-02 13:52:30.498 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,18,1,READ,SELECT,,,"SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3) ORDER BY pp.proname LIMIT 9",<not logged>
256. 2025-08-02 13:52:31.882 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,19,1,READ,SELECT,,,select count(1) from t01,<not logged>
257. 2025-08-02 13:52:33.158 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:  relation "t02" already exists
258. 2025-08-02 13:52:33.158 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:  create table t02 (c1 int)
259.        
260. 2025-08-02 13:52:34.933 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,20,1,WRITE,INSERT,,,insert into t02 values(1),<not logged>
261. 2025-08-02 13:52:36.036 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,21,1,READ,SELECT,,,select * from t02,<not logged>
262. 2025-08-02 13:52:36.048 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,22,1,READ,SELECT,,,"SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16889 AS oid , 1 AS attnum) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) ",<not logged>
263. 2025-08-02 13:52:36.061 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,14,1,READ,SELECT,,,"SELECT i.*,i.indkey as keys,c.relname,c.relnamespace,c.relam,c.reltablespace,tc.relname as tabrelname,dsc.description,pg_catalog.pg_get_expr(i.indpred, i.indrelid) as pred_expr,pg_catalog.pg_get_expr(i.indexprs, i.indrelid, true) as expr,pg_catalog.pg_relation_size(i.indexrelid) as index_rel_size,pg_catalog.pg_stat_get_numscans(i.indexrelid) as index_num_scans
264.         FROM pg_catalog.pg_index i
265.         INNER JOIN pg_catalog.pg_class c ON c.oid=i.indexrelid
266.         INNER JOIN pg_catalog.pg_class tc ON tc.oid=i.indrelid
267.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON i.indexrelid=dsc.objoid
268.         WHERE  i.indrelid=$1 ORDER BY tabrelname, c.relname",<not logged>
269. 2025-08-02 13:52:36.075 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,15,1,READ,SELECT,,,"SELECT c.oid,c.*,t.relname as tabrelname,rt.relnamespace as refnamespace,d.description, case when c.contype='c' then ""substring""(pg_get_constraintdef(c.oid), 7) else null end consrc_copy
270.         FROM pg_catalog.pg_constraint c
271.         INNER JOIN pg_catalog.pg_class t ON t.oid=c.conrelid
272.         LEFT OUTER JOIN pg_catalog.pg_class rt ON rt.oid=c.confrelid
273.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_constraint'::regclass
274.         WHERE c.conrelid=$1
275.         ORDER BY c.oid",<not logged>
276. 2025-08-02 13:52:36.088 CST [375012] user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:  AUDIT: SESSION,16,1,READ,SELECT,,,"SELECT i.*,c.relnamespace FROM pg_catalog.pg_inherits i,pg_catalog.pg_class c WHERE i.inhrelid=$1 AND c.oid=i.inhparent ORDER BY i.inhseqno",<not logged>
277. 2025-08-02 13:52:36.963 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,23,1,WRITE,DELETE,,,"delete from t02",<not logged>
278. 2025-08-02 13:52:38.284 CST [375013] user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,24,1,READ,SELECT,,,"select count(1) from t02",<not logged>
282. -- role级别规则查看
283. select
284.   rolname,
285.   rolconfig
286. from pg_roles where rolname = 'appuser';
287.        
288.        

复制代码

 
3.2 基于数据库级别的审计

记录某个库上的所有操作： ALTER DATABASE dbname SET pgaudit.log = '***';

1. 1. 记录某个库上的所有操作：
2. ALTER DATABASE dbname SET pgaudit.log = '***';
5. ALTER DATABASE DB03 SET pgaudit.log = 'read, write, ddl';
7. 以上开启DB03库的审计后，不管用什么用户操作，只要是DB03库，所有的操作日志都会记录下来，比如这的postgres用户和appuser用户的所有操作都记录了下来。
9. -- 从这里开始，记录DB03库上的所有操作
10. 2025-08-02 14:03:47.860 CST [375154] user=appuser,db=db03,app=[unknown],host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
11. 2025-08-02 14:03:47.873 CST [375154] user=appuser,db=db03,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
12. 2025-08-02 14:03:47.886 CST [375154] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET application_name = 'DBeaver 25.1.0 - SQLEditor <Script-8.sql>',<not logged>
13. 2025-08-02 14:03:47.899 CST [375154] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
14. 2025-08-02 14:03:47.913 CST [375154] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,MISC,SHOW,,,SHOW search_path,<not logged>
15. 2025-08-02 14:03:52.387 CST [375155] user=postgres,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
16. 2025-08-02 14:03:52.399 CST [375155] user=postgres,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT n.oid,n.*,d.description FROM pg_catalog.pg_namespace n
17.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
18.          ORDER BY nspname",<not logged>
19. 2025-08-02 14:03:52.410 CST [375155] user=postgres,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,"SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = $1",<not logged>
20. 2025-08-02 14:03:52.421 CST [375155] user=postgres,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid   FROM pg_catalog.pg_type   LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE pg_type.oid = $1  ORDER BY sp.r, pg_type.oid DESC",<not logged>
21. 2025-08-02 14:03:52.552 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
22. 2025-08-02 14:03:52.580 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description
23.         FROM pg_catalog.pg_type t
24.         LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
25.         LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
26.         LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
27.         WHERE t.typname IS NOT NULL
28.         AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')",<not logged>
29. 2025-08-02 14:03:52.633 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,SELECT * FROM pg_catalog.pg_enum,<not logged>
30. 2025-08-02 14:03:53.260 CST [375157] user=postgres,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-6.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,"SELECT current_schema(),session_user",<not logged>
31. 2025-08-02 14:04:01.451 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
32.         FROM pg_catalog.pg_class c
33.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
34.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
35. 2025-08-02 14:04:01.464 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
36.         FROM pg_catalog.pg_class c
37.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
38.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
39. 2025-08-02 14:04:01.478 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,"SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key
40.         FROM pg_catalog.pg_class c
41.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
42.         WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
43. 2025-08-02 14:04:01.515 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,"SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = $1",<not logged>
44. 2025-08-02 14:04:01.529 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,READ,SELECT,,,"SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid   FROM pg_catalog.pg_type   LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE pg_type.oid = $1  ORDER BY sp.r, pg_type.oid DESC",<not logged>
45. 2025-08-02 14:04:01.544 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,9,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
46.         FROM pg_catalog.pg_attribute a
47.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
48.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
49.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
50.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
51.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
52.         ORDER BY a.attnum",<not logged>
53. 2025-08-02 14:04:04.693 CST [375157] user=postgres,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-6.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,2,1,READ,SELECT,,,"select name,setting from pg_settings where name like '%audit%'",<not logged>
54. 2025-08-02 14:04:04.711 CST [375157] user=postgres,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-6.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,"SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 12104 AS oid , 1 AS attnum UNION ALL SELECT 12104, 2) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) ",<not logged>
55. 2025-08-02 14:04:14.669 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,10,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
56.         FROM pg_catalog.pg_attribute a
57.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
58.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
59.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
60.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
61.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
62.         ORDER BY a.attnum",<not logged>
63. 2025-08-02 14:04:14.683 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,11,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
64.         FROM pg_catalog.pg_attribute a
65.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
66.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
67.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
68.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
69.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
70.         ORDER BY a.attnum",<not logged>
71. 2025-08-02 14:04:14.931 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,12,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
72.         FROM pg_catalog.pg_attribute a
73.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
74.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
75.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
76.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
77.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
78.         ORDER BY a.attnum",<not logged>
79. 2025-08-02 14:04:14.955 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,13,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
80.         FROM pg_catalog.pg_attribute a
81.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
82.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
83.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
84.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
85.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
86.         ORDER BY a.attnum",<not logged>
87. 2025-08-02 14:04:22.622 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,14,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
88.         FROM pg_catalog.pg_attribute a
89.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
90.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
91.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
92.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
93.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
94.         ORDER BY a.attnum",<not logged>
95. 2025-08-02 14:04:25.190 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,15,1,READ,SELECT,,,"SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid
96.         FROM pg_catalog.pg_attribute a
97.         INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
98.         LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
99.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
100.         LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
101.         WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
102.         ORDER BY a.attnum",<not logged>
103. 2025-08-02 14:04:27.307 CST [375157] user=postgres,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-6.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,"
104.         select * from t01
105.         ",<not logged>
106. 2025-08-02 14:04:27.323 CST [375157] user=postgres,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-6.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,5,1,READ,SELECT,,,"SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16892 AS oid , 1 AS attnum) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) ",<not logged>
107. 2025-08-02 14:04:27.338 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,16,1,READ,SELECT,,,"SELECT i.*,i.indkey as keys,c.relname,c.relnamespace,c.relam,c.reltablespace,tc.relname as tabrelname,dsc.description,pg_catalog.pg_get_expr(i.indpred, i.indrelid) as pred_expr,pg_catalog.pg_get_expr(i.indexprs, i.indrelid, true) as expr,pg_catalog.pg_relation_size(i.indexrelid) as index_rel_size,pg_catalog.pg_stat_get_numscans(i.indexrelid) as index_num_scans
108.         FROM pg_catalog.pg_index i
109.         INNER JOIN pg_catalog.pg_class c ON c.oid=i.indexrelid
110.         INNER JOIN pg_catalog.pg_class tc ON tc.oid=i.indrelid
111.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON i.indexrelid=dsc.objoid
112.         WHERE  i.indrelid=$1 ORDER BY tabrelname, c.relname",<not logged>
113. 2025-08-02 14:04:27.352 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,17,1,READ,SELECT,,,"SELECT c.oid,c.*,t.relname as tabrelname,rt.relnamespace as refnamespace,d.description, case when c.contype='c' then ""substring""(pg_get_constraintdef(c.oid), 7) else null end consrc_copy
114.         FROM pg_catalog.pg_constraint c
115.         INNER JOIN pg_catalog.pg_class t ON t.oid=c.conrelid
116.         LEFT OUTER JOIN pg_catalog.pg_class rt ON rt.oid=c.confrelid
117.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_constraint'::regclass
118.         WHERE c.conrelid=$1
119.         ORDER BY c.oid",<not logged>
120. 2025-08-02 14:04:27.366 CST [375156] user=postgres,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,18,1,READ,SELECT,,,"SELECT i.*,c.relnamespace FROM pg_catalog.pg_inherits i,pg_catalog.pg_class c WHERE i.inhrelid=$1 AND c.oid=i.inhparent ORDER BY i.inhseqno",<not logged>
121. 2025-08-02 14:04:35.211 CST [375154] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,select * from t01,<not logged>
122. 2025-08-02 14:04:35.228 CST [375154] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,"SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16892 AS oid , 1 AS attnum) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) ",<not logged>
123. 2025-08-02 14:04:35.243 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,20,1,READ,SELECT,,,"SELECT i.*,i.indkey as keys,c.relname,c.relnamespace,c.relam,c.reltablespace,tc.relname as tabrelname,dsc.description,pg_catalog.pg_get_expr(i.indpred, i.indrelid) as pred_expr,pg_catalog.pg_get_expr(i.indexprs, i.indrelid, true) as expr,pg_catalog.pg_relation_size(i.indexrelid) as index_rel_size,pg_catalog.pg_stat_get_numscans(i.indexrelid) as index_num_scans
124.         FROM pg_catalog.pg_index i
125.         INNER JOIN pg_catalog.pg_class c ON c.oid=i.indexrelid
126.         INNER JOIN pg_catalog.pg_class tc ON tc.oid=i.indrelid
127.         LEFT OUTER JOIN pg_catalog.pg_description dsc ON i.indexrelid=dsc.objoid
128.         WHERE  i.indrelid=$1 ORDER BY tabrelname, c.relname",<not logged>
129. 2025-08-02 14:04:35.257 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,21,1,READ,SELECT,,,"SELECT c.oid,c.*,t.relname as tabrelname,rt.relnamespace as refnamespace,d.description, case when c.contype='c' then ""substring""(pg_get_constraintdef(c.oid), 7) else null end consrc_copy
130.         FROM pg_catalog.pg_constraint c
131.         INNER JOIN pg_catalog.pg_class t ON t.oid=c.conrelid
132.         LEFT OUTER JOIN pg_catalog.pg_class rt ON rt.oid=c.confrelid
133.         LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_constraint'::regclass
134.         WHERE c.conrelid=$1
135.         ORDER BY c.oid",<not logged>
136. 2025-08-02 14:04:35.270 CST [375002] user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:  AUDIT: SESSION,22,1,READ,SELECT,,,"SELECT i.*,c.relnamespace FROM pg_catalog.pg_inherits i,pg_catalog.pg_class c WHERE i.inhrelid=$1 AND c.oid=i.inhparent ORDER BY i.inhseqno",<not logged>
137. 2025-08-02 14:04:36.766 CST [375154] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,8,1,WRITE,INSERT,,,insert into t01 values(1),<not logged>
138. 2025-08-02 14:04:38.508 CST [375154] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,9,1,READ,SELECT,,,"
139.         select * from t01",<not logged>
140. 2025-08-02 14:04:39.290 CST [375154] user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,10,1,READ,SELECT,,,"
141.         select count(1) from t01
142.         ",<not logged>
143. 2025-08-02 14:04:41.537 CST [375157] user=postgres,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-6.sql>,host=***.***.***.*** LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,"
144.        
145.         select * from t01",<not logged>
148. -- 库级别规则查看
149. select d.datname, r.*,unnest(r.setconfig) AS config
150. FROM pg_db_role_setting r JOIN
151.     pg_database d ON d.oid = r.setdatabase;
152.    

复制代码

 
4，pgaudit审计规则查看

1. -- 库级别规则查看
2. select d.datname, r.*,unnest(r.setconfig) AS config
3. FROM pg_db_role_setting r JOIN
4.     pg_database d ON d.oid = r.setdatabase;
5.    
6. datname|setdatabase|setrole|setconfig                       |config                      |
7. -------+-----------+-------+--------------------------------+----------------------------+
8. db01   |      16442|      0|{"pgaudit.log=read, write, ddl"}|pgaudit.log=read, write, ddl|
9. db02   |      16870|      0|{"pgaudit.log=read, write, ddl"}|pgaudit.log=read, write, ddl|
10. db03   |      16874|      0|{"pgaudit.log=read, write, ddl"}|pgaudit.log=read, write, ddl|
13. -- role级别规则查看
14. select
15.   rolname,
16.   rolconfig
17. from pg_roles where rolname = 'appuser';
20. rolname|rolconfig        |
21. -------+-----------------+
22. appuser|{pgaudit.log=all}|

复制代码

 
5，file_fwd分析日志

使用file_fdw分析导入数据库中年分析日志

1. SELECT * FROM pg_available_extensions where name = 'file_fdw';
3. name    |default_version|installed_version|comment                                  |
4. --------+---------------+-----------------+-----------------------------------------+
5. file_fdw|1.0            |1.0              |foreign-data wrapper for flat file access|
9. CREATE EXTENSION file_fdw;
12. SELECT * FROM pg_extension;
14. oid  |extname           |extowner|extnamespace|extrelocatable|extversion|extconfig                |extcondition |
15. -----+------------------+--------+------------+--------------+----------+-------------------------+-------------+
16. 12758|plpgsql           |      10|          11|false         |1.0       |NULL                     |NULL         |
17. 16909|pgaudit           |      10|        2200|true          |16.1      |NULL                     |NULL         |
18. 16914|file_fdw          |      10|        2200|true          |1.0       |NULL                     |NULL         |
19. 16410|pg_stat_statements|      10|        2200|true          |1.10      |NULL                     |NULL         |
20. 16812|pg_cron           |      10|          11|false         |1.6       |{16815,16814,16834,16833}|{"","","",""}|
23. CREATE SERVER pg_file_server foreign data wrapper file_fdw;
26. CREATE FOREIGN TABLE pg_log_csv
27. (
28.           log_time timestamp(3) with time zone,
29.           user_name text,
30.           database_name text,
31.           process_id integer,
32.           connection_from text,
33.           session_id text,
34.           session_line_num bigint,
35.           command_tag text,
36.           session_start_time timestamp with time zone,
37.           virtual_transaction_id text,
38.           transaction_id bigint,
39.           error_severity text,
40.           sql_state_code text,
41.           message text,
42.           detail text,
43.           hint text,
44.           internal_query text,
45.           internal_query_pos integer,
46.           context text,
47.           query text,
48.           query_pos integer,
49.           location text,
50.           application_name text,
51.           backend_type text,
52.           leader_pid integer,
53.           query_id bigint
54. )
55. SERVER pg_file_server OPTIONS(filename '/usr/local/pgsql16/pg9300/data/log/postgresql-2025-08-01_134452.csv',format 'csv');
58. SELECT * FROM pg_log_csv;
60. log_time                     |user_name|database_name|process_id|connection_from   |session_id    |session_line_num|command_tag   |session_start_time           |virtual_transaction_id|transaction_id|error_severity|sql_state_code|message                                                                                                                                                                                                                                                        |detail|hint|internal_query|internal_query_pos|context|query                                                                                                                          |query_pos|location|application_name                              |backend_type    |leader_pid|query_id            |
61. -----------------------------+---------+-------------+----------+------------------+--------------+----------------+--------------+-----------------------------+----------------------+--------------+--------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+----+--------------+------------------+-------+-------------------------------------------------------------------------------------------------------------------------------+---------+--------+----------------------------------------------+----------------+----------+--------------------+
62. 2025-08-02 03:44:52.909 +0800|         |             |    374918|                  |688c5454.5b886|               1|              |2025-08-02 03:44:52.000 +0800|                      |             0|LOG           |00000         |starting PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit                                                                                                                                                 |      |    |              |                  |       |                                                                                                                               |         |        |                                              |postmaster      |          |                   0|
63. 2025-08-02 03:44:52.909 +0800|         |             |    374918|                  |688c5454.5b886|               2|              |2025-08-02 03:44:52.000 +0800|                      |             0|LOG           |00000         |listening on IPv4 address "0.0.0.0", port 9300                                                                                                                                                                                                                 |      |    |              |                  |       |                                                                                                                               |         |        |                                              |postmaster      |          |                   0|
64. 2025-08-02 03:44:52.909 +0800|         |             |    374918|                  |688c5454.5b886|               3|              |2025-08-02 03:44:52.000 +0800|                      |             0|LOG           |00000         |listening on IPv6 address "::", port 9300                                                                                                                                                                                                                      |      |    |              |                  |       |                                                                                                                               |         |        |                                              |postmaster      |          |                   0|
65. 2025-08-02 03:44:52.914 +0800|         |             |    374918|                  |688c5454.5b886|               4|              |2025-08-02 03:44:52.000 +0800|                      |             0|LOG           |00000         |listening on Unix socket "/tmp/.s.PGSQL.9300"                                                                                                                                                                                                                  |      |    |              |                  |       |                                                                                                                               |         |        |                                              |postmaster      |          |                   0|
66. ……

复制代码

 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！