PostgreSQL中利用pgaudit审计扩展记录SQL审计，以及使用fdw分析审计日志
1，pg_audit编译安装参考：https://github.com/pgaudit/pgaudit
--pgaudit 编译安装
git clone https://github.com/pgaudit/pgaudit.git
cd pgaudit/
git checkout REL_16_STABLE --这里checkout出来postgresql16版本，中间的数字是PostgreSQL的大版本号，REL_15_STABLE，REL_16_STABLE，REL_17_STABLE等
--如下是编译安装后相关文件的位置
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package/pgaudit# make install USE_PGXS=1 PG_CONFIG=/usr/local/pgsql16/server/bin/pg_config
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
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
/usr/bin/mkdir -p '/usr/local/pgsql16/server/lib'
/usr/bin/mkdir -p '/usr/local/pgsql16/server/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql16/server/share/extension'
/usr/bin/install -c -m 755pgaudit.so '/usr/local/pgsql16/server/lib/pgaudit.so'
/usr/bin/install -c -m 644 .//pgaudit.control '/usr/local/pgsql16/server/share/extension/'
/usr/bin/install -c -m 644 .//pgaudit--16.1.sql .//pgaudit--16.0--16.1.sql'/usr/local/pgsql16/server/share/extension/'
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package/pgaudit# ^C
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package/pgaudit#
#postgresql.conf中添加动态库
shared_preload_libraries = 'pg_stat_statements, pg_cron, pgaudit' # (change requires restart)
#重启postgresql实例
systemctl restart postgresql9300
#检查动态库是否生效
show shared_preload_libraries;
#检查相关扩展
select name,setting from pg_settings where name like '%audit%';
name |setting|
------------------------------+-------+
pgaudit.log |none |
pgaudit.log_catalog |on |
pgaudit.log_client |off |
pgaudit.log_level |log |
pgaudit.log_parameter |off |
pgaudit.log_parameter_max_size|0 |
pgaudit.log_relation |off |
pgaudit.log_rows |off |
pgaudit.log_statement |on |
pgaudit.log_statement_once |off |
pgaudit.role | |
2，pg_audit扩展创建
--#创建pg_audit扩展
create extension pgaudit;
drop extension pgaudit;
set pgaudit.log = '';
alter system set pgaudit.role = '';
select pg_reload_conf();
-- 基于库级别的审计
ALTER DATABASE db01 SET pgaudit.log = 'read, write, ddl';
-- 审计用户级别的审计
alter system set pgaudit.role = 'appuser1,appuser2';
select pg_reload_conf();
select name,setting from pg_settings where name like '%audit%';3，pg_audit开启审计
查了网站的资料，基本上都是翻译pg_audit github上官方的文档，都没有说清楚到底怎么审计。
比如最常见的两种审计：1，记录某个库下的所有的操作；2，记录某个数据库用户的所有的操作
3.1 记录数据库用户的审计
记录某个用户的所有操作行为
3.1. 基于数据库用户的审计：
ALTER ROLE *** SET pgaudit.log = '***';
--创建测试用户
CREATE ROLE appuser WITH LOGIN PASSWORD '******';
-- 授予连接权限
GRANT CONNECT ON DATABASE db03 TO appuser;
-- 授权使用某 schema(切换到DB03下执行)
GRANT usage,create ON SCHEMA public TO appuser;
alter default privileges in schema public GRANT all ON TABLES TO appuser;
-- 授予连接权限
GRANT CONNECT ON DATABASE db04 TO appuser;
-- 授权使用某 schema（切换到DB04下执行）
GRANT usage,create ON SCHEMA public TO appuser;
alter default privileges in schema public GRANT all ON TABLES TO appuser;
对appuser开启审计，记录其所有操作（任务数据库下执行）
ALTER ROLE appuser SET pgaudit.log = 'all';
2025-08-02 13:49:52.433 CST user=,db=,app=,host= LOG:checkpoint starting: time
2025-08-02 13:49:52.855 CST 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
2025-08-02 13:51:06.017 CST user=appuser,db=postgres,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 13:51:06.029 CST user=appuser,db=postgres,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 13:51:06.040 CST 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>
2025-08-02 13:51:06.053 CST 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
ORDER BY db.datname",<not logged>
2025-08-02 13:51:06.065 CST 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>
2025-08-02 13:51:06.140 CST user=appuser,db=postgres,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 13:51:06.151 CST user=appuser,db=postgres,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 13:51:06.162 CST 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>
2025-08-02 13:51:06.173 CST 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>
2025-08-02 13:51:06.186 CST 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
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
ORDER BY nspname",<not logged>
2025-08-02 13:51:06.198 CST 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>
2025-08-02 13:51:06.211 CST 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)) as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespaceWHERE pg_type.oid = $1ORDER BY sp.r, pg_type.oid DESC",<not logged>
2025-08-02 13:51:06.222 CST user=appuser,db=postgres,app=DBeaver 25.1.0 - Main <postgres>,host=***.***.***.*** LOG:AUDIT: SESSION,8,1,MISC,SHOW,,,SHOW search_path,<not logged>
2025-08-02 13:51:06.296 CST user=appuser,db=postgres,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 13:51:06.308 CST user=appuser,db=postgres,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 13:51:06.322 CST 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>
2025-08-02 13:51:06.335 CST 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>
2025-08-02 13:51:06.346 CST user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:AUDIT: SESSION,5,1,MISC,SHOW,,,SHOW search_path,<not logged>
2025-08-02 13:51:06.358 CST 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>
2025-08-02 13:51:06.371 CST 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>
2025-08-02 13:51:06.386 CST user=appuser,db=postgres,app=DBeaver 25.1.0 - Metadata <postgres>,host=***.***.***.*** LOG:AUDIT: SESSION,8,1,READ,SELECT,,,SELECT version(),<not logged>
2025-08-02 13:51:06.397 CST 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>
2025-08-02 13:51:06.409 CST 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>
2025-08-02 13:51:06.422 CST 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
FROM pg_catalog.pg_type t
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
WHERE t.typname IS NOT NULL
AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')",<not logged>
2025-08-02 13:51:06.487 CST 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>
2025-08-02 13:51:06.593 CST user=appuser,db=db03,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 13:51:06.604 CST user=appuser,db=db03,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 13:51:06.616 CST 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>
2025-08-02 13:51:06.627 CST 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>
2025-08-02 13:51:06.709 CST user=appuser,db=db03,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 13:51:06.721 CST user=appuser,db=db03,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 13:51:06.733 CST 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>
2025-08-02 13:51:06.745 CST 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>
2025-08-02 13:51:06.759 CST 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
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
ORDER BY nspname",<not logged>
2025-08-02 13:51:06.772 CST 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>
2025-08-02 13:51:06.785 CST 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)) as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespaceWHERE pg_type.oid = $1ORDER BY sp.r, pg_type.oid DESC",<not logged>
2025-08-02 13:51:06.797 CST user=appuser,db=db03,app=DBeaver 25.1.0 - Main <db03>,host=***.***.***.*** LOG:AUDIT: SESSION,8,1,MISC,SHOW,,,SHOW search_path,<not logged>
2025-08-02 13:51:06.892 CST user=appuser,db=db03,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 13:51:06.913 CST user=appuser,db=db03,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 13:51:06.925 CST 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>
2025-08-02 13:51:06.938 CST 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>
2025-08-02 13:51:06.950 CST user=appuser,db=db03,app=DBeaver 25.1.0 - Metadata <db03>,host=***.***.***.*** LOG:AUDIT: SESSION,5,1,MISC,SHOW,,,SHOW search_path,<not logged>
2025-08-02 13:51:06.965 CST 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
FROM pg_catalog.pg_type t
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
WHERE t.typname IS NOT NULL
AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')",<not logged>
2025-08-02 13:51:07.017 CST 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>
2025-08-02 13:51:07.026 CST 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>
2025-08-02 13:51:07.767 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
2025-08-02 13:51:07.781 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
2025-08-02 13:51:07.797 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 13:51:07.813 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
2025-08-02 13:51:07.826 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
2025-08-02 13:51:07.839 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
2025-08-02 13:51:07.879 CST 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>
2025-08-02 13:51:07.892 CST 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)) as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespaceWHERE pg_type.oid = $1ORDER BY sp.r, pg_type.oid DESC",<not logged>
2025-08-02 13:51:18.677 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 13:51:22.015 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,6,1,DDL,DROP TABLE,,,"
drop table t01",<not logged>
2025-08-02 13:51:26.653 CST 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>
2025-08-02 13:51:27.878 CST 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>
2025-08-02 13:51:29.013 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,9,1,READ,SELECT,,,"select * from t1
",<not logged>
2025-08-02 13:51:29.027 CST 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>
2025-08-02 13:51:29.042 CST 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
FROM pg_catalog.pg_index i
INNER JOIN pg_catalog.pg_class c ON c.oid=i.indexrelid
INNER JOIN pg_catalog.pg_class tc ON tc.oid=i.indrelid
LEFT OUTER JOIN pg_catalog.pg_description dsc ON i.indexrelid=dsc.objoid
WHEREi.indrelid=$1 ORDER BY tabrelname, c.relname",<not logged>
2025-08-02 13:51:29.057 CST 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
FROM pg_catalog.pg_constraint c
INNER JOIN pg_catalog.pg_class t ON t.oid=c.conrelid
LEFT OUTER JOIN pg_catalog.pg_class rt ON rt.oid=c.confrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_constraint'::regclass
WHERE c.conrelid=$1
ORDER BY c.oid",<not logged>
2025-08-02 13:51:29.070 CST 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>
2025-08-02 13:51:31.377 CST 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>
2025-08-02 13:51:32.343 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,12,1,WRITE,DELETE,,,"delete from t1
",<not logged>
2025-08-02 13:51:33.859 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,13,1,READ,SELECT,,,"
select count(1) from t1",<not logged>
2025-08-02 13:51:35.578 CST 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)
",<not logged>
2025-08-02 13:51:37.162 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:relation "t2" does not exist at character 15
2025-08-02 13:51:37.162 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:select * from t2
2025-08-02 13:51:39.968 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:relation "t2" does not exist at character 13
2025-08-02 13:51:39.968 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:insert into t2 values(1)
2025-08-02 13:51:42.789 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:relation "t02" already exists
2025-08-02 13:51:42.789 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:
create table t02 (c1 int)
2025-08-02 13:51:46.868 CST 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>
2025-08-02 13:51:46.880 CST 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>
2025-08-02 13:51:48.946 CST 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>
2025-08-02 13:51:48.957 CST 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>
2025-08-02 13:51:50.598 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,19,1,READ,SELECT,,,"select * from t02
",<not logged>
2025-08-02 13:51:50.612 CST 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>
2025-08-02 13:51:51.808 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,21,1,WRITE,DELETE,,,"
delete from t02",<not logged>
2025-08-02 13:51:54.331 CST 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>
2025-08-02 13:51:54.342 CST 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>
2025-08-02 13:51:55.996 CST 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>
2025-08-02 13:52:01.258 CST 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>
2025-08-02 13:52:01.270 CST 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>
2025-08-02 13:52:01.282 CST 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>
2025-08-02 13:52:01.293 CST 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>
2025-08-02 13:52:01.305 CST 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>
2025-08-02 13:52:02.836 CST user=appuser,db=db04,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 13:52:02.847 CST user=appuser,db=db04,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 13:52:02.858 CST 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>
2025-08-02 13:52:02.870 CST 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>
2025-08-02 13:52:02.884 CST 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
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
ORDER BY nspname",<not logged>
2025-08-02 13:52:02.896 CST 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>
2025-08-02 13:52:02.909 CST 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)) as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespaceWHERE pg_type.oid = $1ORDER BY sp.r, pg_type.oid DESC",<not logged>
2025-08-02 13:52:02.920 CST user=appuser,db=db04,app=DBeaver 25.1.0 - Main <db04>,host=***.***.***.*** LOG:AUDIT: SESSION,8,1,MISC,SHOW,,,SHOW search_path,<not logged>
2025-08-02 13:52:02.998 CST user=appuser,db=db04,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 13:52:03.015 CST user=appuser,db=db04,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 13:52:03.028 CST 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>
2025-08-02 13:52:03.042 CST 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>
2025-08-02 13:52:03.055 CST user=appuser,db=db04,app=DBeaver 25.1.0 - Metadata <db04>,host=***.***.***.*** LOG:AUDIT: SESSION,5,1,MISC,SHOW,,,SHOW search_path,<not logged>
2025-08-02 13:52:03.072 CST 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
FROM pg_catalog.pg_type t
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
WHERE t.typname IS NOT NULL
AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')",<not logged>
2025-08-02 13:52:03.126 CST 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>
2025-08-02 13:52:03.983 CST user=appuser,db=db04,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 13:52:03.994 CST user=appuser,db=db04,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 13:52:04.006 CST 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>
2025-08-02 13:52:04.018 CST 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>
2025-08-02 13:52:04.029 CST 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>
2025-08-02 13:52:04.599 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
2025-08-02 13:52:04.612 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
2025-08-02 13:52:04.626 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
2025-08-02 13:52:04.663 CST 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>
2025-08-02 13:52:04.677 CST 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)) as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespaceWHERE pg_type.oid = $1ORDER BY sp.r, pg_type.oid DESC",<not logged>
2025-08-02 13:52:04.696 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 13:52:09.425 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:relation "t1" does not exist at character 13
2025-08-02 13:52:09.425 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:insert into t1 values(1)
2025-08-02 13:52:10.441 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:relation "t1" does not exist at character 15
2025-08-02 13:52:10.441 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:select * from t1
2025-08-02 13:52:14.358 CST 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>
2025-08-02 13:52:14.371 CST 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>
2025-08-02 13:52:15.679 CST 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>
2025-08-02 13:52:15.691 CST 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>
2025-08-02 13:52:20.776 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:table "t01" does not exist
2025-08-02 13:52:20.776 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:drop table t01
2025-08-02 13:52:22.245 CST 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>
2025-08-02 13:52:23.508 CST 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)
",<not logged>
2025-08-02 13:52:24.364 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,12,1,READ,SELECT,,,"select * from t01
",<not logged>
2025-08-02 13:52:24.378 CST 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>
2025-08-02 13:52:25.298 CST 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>
2025-08-02 13:52:26.301 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:relation "t1" does not exist at character 22
2025-08-02 13:52:26.301 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:select count(1) from t1
2025-08-02 13:52:29.853 CST 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>
2025-08-02 13:52:29.865 CST 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>
2025-08-02 13:52:30.486 CST 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>
2025-08-02 13:52:30.498 CST 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>
2025-08-02 13:52:31.882 CST 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>
2025-08-02 13:52:33.158 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** ERROR:relation "t02" already exists
2025-08-02 13:52:33.158 CST user=appuser,db=db04,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** STATEMENT:create table t02 (c1 int)
2025-08-02 13:52:34.933 CST 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>
2025-08-02 13:52:36.036 CST 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>
2025-08-02 13:52:36.048 CST 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>
2025-08-02 13:52:36.061 CST 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
FROM pg_catalog.pg_index i
INNER JOIN pg_catalog.pg_class c ON c.oid=i.indexrelid
INNER JOIN pg_catalog.pg_class tc ON tc.oid=i.indrelid
LEFT OUTER JOIN pg_catalog.pg_description dsc ON i.indexrelid=dsc.objoid
WHEREi.indrelid=$1 ORDER BY tabrelname, c.relname",<not logged>
2025-08-02 13:52:36.075 CST 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
FROM pg_catalog.pg_constraint c
INNER JOIN pg_catalog.pg_class t ON t.oid=c.conrelid
LEFT OUTER JOIN pg_catalog.pg_class rt ON rt.oid=c.confrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_constraint'::regclass
WHERE c.conrelid=$1
ORDER BY c.oid",<not logged>
2025-08-02 13:52:36.088 CST 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>
2025-08-02 13:52:36.963 CST 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>
2025-08-02 13:52:38.284 CST 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>
-- role级别规则查看
select
rolname,
rolconfig
from pg_roles where rolname = 'appuser';
3.2 基于数据库级别的审计
记录某个库上的所有操作： ALTER DATABASE dbname SET pgaudit.log = '***';
1. 记录某个库上的所有操作：
ALTER DATABASE dbname SET pgaudit.log = '***';
ALTER DATABASE DB03 SET pgaudit.log = 'read, write, ddl';
以上开启DB03库的审计后，不管用什么用户操作，只要是DB03库，所有的操作日志都会记录下来，比如这的postgres用户和appuser用户的所有操作都记录了下来。
-- 从这里开始，记录DB03库上的所有操作
2025-08-02 14:03:47.860 CST user=appuser,db=db03,app=,host=***.***.***.*** LOG:AUDIT: SESSION,1,1,MISC,SET,,,SET extra_float_digits = 3,<not logged>
2025-08-02 14:03:47.873 CST user=appuser,db=db03,app=PostgreSQL JDBC Driver,host=***.***.***.*** LOG:AUDIT: SESSION,2,1,MISC,SET,,,SET application_name = 'PostgreSQL JDBC Driver',<not logged>
2025-08-02 14:03:47.886 CST 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>
2025-08-02 14:03:47.899 CST 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>
2025-08-02 14:03:47.913 CST 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>
2025-08-02 14:03:52.387 CST 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>
2025-08-02 14:03:52.399 CST 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
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
ORDER BY nspname",<not logged>
2025-08-02 14:03:52.410 CST 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>
2025-08-02 14:03:52.421 CST 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)) as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespaceWHERE pg_type.oid = $1ORDER BY sp.r, pg_type.oid DESC",<not logged>
2025-08-02 14:03:52.552 CST 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>
2025-08-02 14:03:52.580 CST 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
FROM pg_catalog.pg_type t
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
WHERE t.typname IS NOT NULL
AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')",<not logged>
2025-08-02 14:03:52.633 CST 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>
2025-08-02 14:03:53.260 CST 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>
2025-08-02 14:04:01.451 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') AND relname=$2",<not logged>
2025-08-02 14:04:01.464 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
2025-08-02 14:04:01.478 CST 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
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')",<not logged>
2025-08-02 14:04:01.515 CST 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>
2025-08-02 14:04:01.529 CST 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)) as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespaceWHERE pg_type.oid = $1ORDER BY sp.r, pg_type.oid DESC",<not logged>
2025-08-02 14:04:01.544 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 14:04:04.693 CST 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>
2025-08-02 14:04:04.711 CST 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>
2025-08-02 14:04:14.669 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 14:04:14.683 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 14:04:14.931 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 14:04:14.955 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 14:04:22.622 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 14:04:25.190 CST 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
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
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
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1
ORDER BY a.attnum",<not logged>
2025-08-02 14:04:27.307 CST user=postgres,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-6.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,4,1,READ,SELECT,,,"
select * from t01
",<not logged>
2025-08-02 14:04:27.323 CST 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>
2025-08-02 14:04:27.338 CST 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
FROM pg_catalog.pg_index i
INNER JOIN pg_catalog.pg_class c ON c.oid=i.indexrelid
INNER JOIN pg_catalog.pg_class tc ON tc.oid=i.indrelid
LEFT OUTER JOIN pg_catalog.pg_description dsc ON i.indexrelid=dsc.objoid
WHEREi.indrelid=$1 ORDER BY tabrelname, c.relname",<not logged>
2025-08-02 14:04:27.352 CST 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
FROM pg_catalog.pg_constraint c
INNER JOIN pg_catalog.pg_class t ON t.oid=c.conrelid
LEFT OUTER JOIN pg_catalog.pg_class rt ON rt.oid=c.confrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_constraint'::regclass
WHERE c.conrelid=$1
ORDER BY c.oid",<not logged>
2025-08-02 14:04:27.366 CST 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>
2025-08-02 14:04:35.211 CST 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>
2025-08-02 14:04:35.228 CST 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>
2025-08-02 14:04:35.243 CST 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
FROM pg_catalog.pg_index i
INNER JOIN pg_catalog.pg_class c ON c.oid=i.indexrelid
INNER JOIN pg_catalog.pg_class tc ON tc.oid=i.indrelid
LEFT OUTER JOIN pg_catalog.pg_description dsc ON i.indexrelid=dsc.objoid
WHEREi.indrelid=$1 ORDER BY tabrelname, c.relname",<not logged>
2025-08-02 14:04:35.257 CST 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
FROM pg_catalog.pg_constraint c
INNER JOIN pg_catalog.pg_class t ON t.oid=c.conrelid
LEFT OUTER JOIN pg_catalog.pg_class rt ON rt.oid=c.confrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_constraint'::regclass
WHERE c.conrelid=$1
ORDER BY c.oid",<not logged>
2025-08-02 14:04:35.270 CST 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>
2025-08-02 14:04:36.766 CST 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>
2025-08-02 14:04:38.508 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,9,1,READ,SELECT,,,"
select * from t01",<not logged>
2025-08-02 14:04:39.290 CST user=appuser,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-8.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,10,1,READ,SELECT,,,"
select count(1) from t01
",<not logged>
2025-08-02 14:04:41.537 CST user=postgres,db=db03,app=DBeaver 25.1.0 - SQLEditor <Script-6.sql>,host=***.***.***.*** LOG:AUDIT: SESSION,6,1,READ,SELECT,,,"
select * from t01",<not logged>
-- 库级别规则查看
select d.datname, r.*,unnest(r.setconfig) AS config
FROM pg_db_role_setting r JOIN
pg_database d ON d.oid = r.setdatabase;
4，pgaudit审计规则查看
-- 库级别规则查看
select d.datname, r.*,unnest(r.setconfig) AS config
FROM pg_db_role_setting r JOIN
pg_database d ON d.oid = r.setdatabase;
datname|setdatabase|setrole|setconfig |config |
-------+-----------+-------+--------------------------------+----------------------------+
db01 | 16442| 0|{"pgaudit.log=read, write, ddl"}|pgaudit.log=read, write, ddl|
db02 | 16870| 0|{"pgaudit.log=read, write, ddl"}|pgaudit.log=read, write, ddl|
db03 | 16874| 0|{"pgaudit.log=read, write, ddl"}|pgaudit.log=read, write, ddl|
-- role级别规则查看
select
rolname,
rolconfig
from pg_roles where rolname = 'appuser';
rolname|rolconfig |
-------+-----------------+
appuser|{pgaudit.log=all}|
5，file_fwd分析日志
使用file_fdw分析导入数据库中年分析日志
SELECT * FROM pg_available_extensions where name = 'file_fdw';
name |default_version|installed_version|comment |
--------+---------------+-----------------+-----------------------------------------+
file_fdw|1.0 |1.0 |foreign-data wrapper for flat file access|
CREATE EXTENSION file_fdw;
SELECT * FROM pg_extension;
oid|extname |extowner|extnamespace|extrelocatable|extversion|extconfig |extcondition |
-----+------------------+--------+------------+--------------+----------+-------------------------+-------------+
12758|plpgsql | 10| 11|false |1.0 |NULL |NULL |
16909|pgaudit | 10| 2200|true |16.1 |NULL |NULL |
16914|file_fdw | 10| 2200|true |1.0 |NULL |NULL |
16410|pg_stat_statements| 10| 2200|true |1.10 |NULL |NULL |
16812|pg_cron | 10| 11|false |1.6 |{16815,16814,16834,16833}|{"","","",""}|
CREATE SERVER pg_file_server foreign data wrapper file_fdw;
CREATE FOREIGN TABLE pg_log_csv
(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
backend_type text,
leader_pid integer,
query_id bigint
)
SERVER pg_file_server OPTIONS(filename '/usr/local/pgsql16/pg9300/data/log/postgresql-2025-08-01_134452.csv',format 'csv');
SELECT * FROM pg_log_csv;
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 |
-----------------------------+---------+-------------+----------+------------------+--------------+----------------+--------------+-----------------------------+----------------------+--------------+--------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+----+--------------+------------------+-------+-------------------------------------------------------------------------------------------------------------------------------+---------+--------+----------------------------------------------+----------------+----------+--------------------+
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|
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|
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|
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|
……
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]