">

Postgresql DDL Audit Mekanizması

Postgresql DDL Audit Mekanizması

Merhaba,

Postgresql DDL audit mekanizması için aşağıdaki yolu izleyebiliriz.

Aşağıdaki tablo oluşturulur logları tutmak için.

create table DDL_EVENT(classid bigint,objid bigint,objsubid integer, command_tag text, object_type text,schema_name text,object_identity text, in_extension boolean,current_query text,user_ text,client_id text,time_ timestamp);

Fonksiyonu için aşağıdaki script çalıştırılır

CREATE OR REPLACE FUNCTION alter_event() RETURNS event_trigger AS $$

DECLARE r RECORD;

BEGIN

    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP

        --RAISE NOTICE 'caught % event on % , classid % ', r.command_tag, r.object_identity, r.classid;

RAISE WARNING 'event= % , object = % , subid = %, objid =%',r.command_tag, r.object_identity,r.objsubid, r.objid;

INSERT INTO DDL_EVENT values (r.classid,r.objid,r.objsubid,r.command_tag,r.object_type,r.schema_name,r.object_identity,r.in_extension,current_query(),session_user::TEXT,inet_client_addr(),current_timestamp);

    END LOOP;

/*

FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP

        --RAISE NOTICE 'caught % event on % , classid % ', r.command_tag, r.object_identity, r.classid;

RAISE WARNING 'event= % , object = % , subid = %, objid =%',r.command_tag, r.object_identity,r.objsubid, r.objid;

INSERT INTO DDL_DML_EVENT values (r.classid,r.objid,r.objsubid,r.command_tag,r.object_type,r.schema_name,r.object_identity,r.in_extension,current_query(),session_user::TEXT,inet_client_addr());

    END LOOP;

*/

END;

$$

LANGUAGE plpgsql; 

 

Son olarak trigger oluşturulur,

 

CREATE EVENT TRIGGER tr_eng_alter_table

  ON ddl_command_end WHEN TAG IN ('ALTER TABLE','CREATE TABLE','ALTER AGGREGATE','ALTER COLLATION','ALTER CONVERSION',

  'ALTER DOMAIN','ALTER EXTENSION','ALTER FOREIGN DATA WRAPPER','ALTER FOREIGN TABLE','ALTER FUNCTION','ALTER LANGUAGE','ALTER OPERATOR','ALTER OPERATOR CLASS',

  'ALTER OPERATOR FAMILY','ALTER POLICY','ALTER SCHEMA','ALTER SEQUENCE','ALTER SERVER','ALTER TRIGGER','ALTER TYPE','ALTER USER MAPPING','ALTER VIEW','CREATE INDEX',

  'CREATE FUNCTION','CREATE SEQUENCE','CREATE TABLE AS','CREATE TRIGGER','CREATE VIEW','COMMENT')

  EXECUTE PROCEDURE alter_event();

 
Umarım faydalı olmuştur..
 

Yorumlar

Henüz yorum yapılmadı.

Yorum Yapın