审计触发器 c
来自 PostgreSQL wiki
跳转到导航跳转到搜索
触发器
CREATE SCHEMA log;
CREATE TABLE log.audits
(
id bigserial NOT NULL,
owner_role character varying(64) NOT NULL,
owner_id character varying(64) NOT NULL,
operation character(1) NOT NULL,
fields character varying(64)[],
data text[],
transaction bigint,
CONSTRAINT audits_pkey PRIMARY KEY (id)
);
CREATE OR REPLACE FUNCTION auditc() RETURNS trigger AS
'audit.so', 'auditc'
LANGUAGE c VOLATILE COST 1;
演示/示例代码
CREATE TABLE teste_table
(
id serial NOT NULL,
teste_char VARCHAR(16) NOT NULL,
teste_int integer NOT NULL,
CONSTRAINT teste_table_pkey PRIMARY KEY (id)
);
CREATE TRIGGER audit_teste_table
BEFORE INSERT OR DELETE OR UPDATE ON teste_table
FOR EACH ROW EXECUTE PROCEDURE auditc();
insert into teste_table (id, teste_char, teste_int) values(10, 'Hello', 123);
insert into teste_table (id, teste_char, teste_int) values(20, 'Good', 456);
update teste_table set teste_char = 'World' where id = 10;
update teste_table set teste_char = 'Bye' where id = 20;
select id, owner_role, owner_id, operation, data, fields from log.audits where owner_role = 'public.teste_table' order by owner_id;
Result:
id | owner_role | owner_id | operation | data | fields
-------+--------------------+----------+-----------+---------------+---------------------------
1 | public.teste_table | 10 | I | {123,Hello,10} | {teste_int,teste_char,id}
3 | public.teste_table | 10 | U | {World} | {teste_char}
2 | public.teste_table | 20 | I | {456,Good,20} | {teste_int,teste_char,id}
4 | public.teste_table | 20 | U | {Bye} | {teste_char}
库 audit.c
#include "postgres.h"
#include "executor/spi.h"
#include "commands/trigger.h"
#include "utils/array.h"
#include "utils/rel.h"
#include "utils/builtins.h"
#include "catalog/pg_type.h"
#include "access/htup_details.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
//#define DEBUGAUDIT
#define OID_VARCHAR_ARRAY 1015
extern Datum auditc(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(auditc);
/**
* Structs
**/
typedef char* string;
typedef struct {
string* fields;
string* data;
string* pks;
uint32 pk_length;
int size;
} diffret;
typedef struct {
string key;
uint32 pk_length;
string *values;
} entryset;
static entryset* primaryKeys = 0;
static int tableUsed = 0;
static int tableAmount = 0;
/**
* Audit function
**/
Datum
auditc(PG_FUNCTION_ARGS)
{
if (SPI_connect() == SPI_OK_CONNECT)
{
/** **************************** BEGIN singleton *****************************************/
if (!tableAmount)
{
SPIPlanPtr prep = SPI_prepare("select count(tablename) from pg_tables where schemaname = 'public'", 0, NULL);
if (prep)
{
if (SPI_execute_plan(prep, NULL, NULL, true, 0))
{
uint32 pk_length = SPI_processed;
if (pk_length > 0)
{
tableAmount = atoi(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1));
}
}
}
primaryKeys = malloc(tableAmount * sizeof(entryset));
}
/** ---------------------------- END singleton ---------------------------- **/
TriggerData *trigdata = (TriggerData *) fcinfo->context;
HeapTuple oldtuple;
HeapTuple newtuple;
#ifdef DEBUGAUDIT
display(trigdata);
#endif
char operation;
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
{
operation = 'I';
oldtuple = 0;
newtuple = trigdata->tg_trigtuple;
} else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
{
operation = 'U';
oldtuple = trigdata->tg_trigtuple;
newtuple = trigdata->tg_newtuple;
} else {
operation = 'D';
oldtuple = trigdata->tg_trigtuple;
newtuple = trigdata->tg_trigtuple;
}
diffret ret;
TupleDesc description = trigdata->tg_relation->rd_att;
ret.size = 0;
ret.pks = 0;
ret.pk_length = 0;
uint32 colunsSize = HeapTupleHeaderGetNatts(newtuple->t_data);
if (colunsSize > 0)
{
int pos;
ret.fields = SPI_palloc(colunsSize * sizeof(string));
ret.data = SPI_palloc(colunsSize * sizeof(string));
string *pks = 0;
/** **************************** GET PKS *****************************************/
#ifdef DEBUGAUDIT
displayconstraints(trigdata->tg_relation);
elog(INFO, "_____CHECK_PRIMARY_KEYS________");
#endif
/** **************************** CHECK PKS IN singleton *****************************************/
{
int row;
for (row = 0; row < tableUsed; row++)
{
#ifdef DEBUGAUDIT
elog(INFO, "pk: %s", primaryKeys[row].key);
#endif
if (strcmp(primaryKeys[row].key, SPI_getrelname(trigdata->tg_relation)) == 0)
{
ret.pk_length = primaryKeys[row].pk_length;
pks = primaryKeys[row].values;
}
}
}
/** ---------------------------- END CHECK PKS IN singleton ---------------------------- **/
/** **************************** CHECK PKS IN Data BASE *****************************************/
if (!pks)
{
#ifdef DEBUGAUDIT
elog(INFO, "_____LOADING_PRIMARY_KEYS______");
#endif
Oid oids[1] = {VARCHAROID};
SPIPlanPtr prep = SPI_prepare("SELECT a.attname AS keys FROM pg_class c, pg_attribute a, pg_index i"
" WHERE c.relname = $1 AND c.oid=i.indrelid AND a.attnum > 0"
" AND a.attrelid = i.indexrelid AND i.indisprimary='t' ORDER BY a.attname ASC", 1, oids);
if (prep)
{
Datum * parameters = (Datum *) SPI_palloc(sizeof(Datum));
parameters[0] = CStringGetDatum(cstring_to_text(SPI_getrelname(trigdata->tg_relation)));
if (SPI_execute_plan(prep, parameters, NULL, true, 0))
{
ret.pk_length = SPI_processed;
if (ret.pk_length > 0)
{
pks = malloc(ret.pk_length * sizeof(string));
int row;
for (row = 0; row < ret.pk_length; row++)
{
string value = SPI_getvalue(SPI_tuptable->vals[row], SPI_tuptable->tupdesc, 1);
int size = strlen(value) + 1;
pks[row] = malloc(size);
memcpy( pks[row], value, size);
SPI_pfree(value);
#ifdef DEBUGAUDIT
elog(INFO, "pk: %s", pks[row]);
#endif
}
}
if (tableUsed < tableAmount)
{
char* tableName = SPI_getrelname(trigdata->tg_relation);
int size = strlen(tableName) + 1;
primaryKeys[tableUsed].key = malloc(size);
memcpy( primaryKeys[tableUsed].key, tableName, size);
primaryKeys[tableUsed].pk_length = ret.pk_length;
primaryKeys[tableUsed].values = pks;
tableUsed++;
}
SPI_freetuptable(SPI_tuptable);
}
}
}
/** ---------------------------- END CHECK PKS IN Data BASE ---------------------------- **/
if (ret.pk_length == 0)
{
elog(ERROR,"auditc.so: Erro find primary key");
}
/** ---------------------------- GET PKS ---------------------------- **/
int pk_pos;
ret.pks = SPI_palloc(ret.pk_length * sizeof(string));
/** **************************** DIFF *****************************************/
string column;
string newValue;
for(pos = colunsSize; pos > 0; pos--)
{
column = SPI_fname(description, pos);
newValue = SPI_getvalue(newtuple, description, pos);
for(pk_pos = 0; pk_pos < ret.pk_length; pk_pos++)
{
if (strcmp(column, pks[pk_pos]) == 0)
{
ret.pks[pk_pos] = newValue;
}
}
if (oldtuple != 0)
{
string oldValue = SPI_getvalue(oldtuple, description, pos);
if ((oldValue == 0 && newValue == 0)
|| (
!(oldValue == 0 && newValue != 0)
&& !(oldValue != 0 && newValue == 0)
&& !strcmp(oldValue, newValue)))
{
continue;
}
}
ret.fields[ret.size] = column;
ret.data[ret.size] = newValue;
ret.size++;
}
/** ---------------------------- END DIFF ---------------------------- **/
} else {
ret.size = 0;
}
#ifdef DEBUGAUDIT
displaydiff(&ret);
#endif
/** **************************** SAVE IN LOG *****************************************/
if (ret.size > 0 || operation == 'D')
{
Oid oids[6] = {VARCHAROID, VARCHAROID, CHAROID, OID_VARCHAR_ARRAY, TEXTARRAYOID, INT4OID};
SPIPlanPtr prep = SPI_prepare("INSERT INTO log.audits"
" (transaction, owner_role, owner_id, operation, fields, data)"
" VALUES (txid_current(), $1, $2, $3, $4, $5)", 5, oids);
if (prep)
{
//OWNER_ROLE COLUMN
char *schema = SPI_getnspname(trigdata->tg_relation);
char *table = SPI_getrelname(trigdata->tg_relation);
char *st = (char *) SPI_palloc(strlen(schema) + strlen(table) + 2); /* +1 for null character */
strcpy(st, schema);
strcat(st, ".");
strcat(st, table);
//OWNER_ID COLUMN
int size = strlen(ret.pks[0]);
int pk_pos;
for(pk_pos = 1; pk_pos < ret.pk_length; pk_pos++)
{
size += strlen(ret.pks[pk_pos]) + 1;
}
char *pkIds = (char *) SPI_palloc(size * sizeof(char) + 1); /* +1 for null character */
strcpy(pkIds, ret.pks[0]);
for(pk_pos = 1; pk_pos < ret.pk_length; pk_pos++)
{
char *val= ret.pks[pk_pos];
strcat(pkIds, ",");
strcat(pkIds, val);
}
//DATA COLUMN and FIELDS COLUMN
Datum *fields = SPI_palloc(sizeof(Datum) * ret.size);
Datum *values = SPI_palloc(sizeof(Datum) * ret.size);
bool *valuesNulls = SPI_palloc(sizeof(bool) * ret.size);
{
int pk_pos;
for(pk_pos = 0; pk_pos < ret.size; pk_pos++)
{
fields[pk_pos] = CStringGetDatum(cstring_to_text(ret.fields[pk_pos]));
if (ret.data[pk_pos]) {
values[pk_pos] = CStringGetDatum(cstring_to_text(ret.data[pk_pos]));
valuesNulls[pk_pos] = false;
} else {
values[pk_pos] = 0;
valuesNulls[pk_pos] = true;
}
}
}
//PARAMETERS For statement
Datum *parameters = SPI_palloc(sizeof(Datum) * 6);
parameters[0] = CStringGetDatum(cstring_to_text(st));
parameters[1] = CStringGetDatum(cstring_to_text(pkIds));
parameters[2] = CharGetDatum(operation);
parameters[3] = (Datum) construct_array(fields, ret.size, VARCHAROID, -1, false, 'i');
{
int * dims = SPI_palloc(sizeof(int));
dims[0] = ret.size;
int * lbs = SPI_palloc(sizeof(int));
lbs[0] = 1;
parameters[4] = (Datum) construct_md_array(values, valuesNulls, 1, dims, lbs, VARCHAROID, -1, false, 'i');
}
//NULL VALUES
char *nulls = SPI_palloc(sizeof(char) * 6);
nulls[0] = ' ';
nulls[1] = ' ';
nulls[2] = ' ';
nulls[3] = ret.size ? ' ' : 'n';
nulls[4] = ret.size ? ' ' : 'n';
//EXECUTE QUERY
SPI_execute_plan(prep, parameters, nulls, false, 0);
//CLEAN VARIABLES
SPI_pfree(st);
SPI_pfree(pkIds);
SPI_pfree(parameters);
SPI_pfree(nulls);
} else {
elog(ERROR,"auditc.so: Erro to create preparar: %d", SPI_result);
}
}
/** ---------------------------- END IN LOG ---------------------------- **/
//CLEAN VARIABLES
SPI_pfree(ret.pks);
SPI_pfree(ret.data);
SPI_pfree(ret.fields);
if (SPI_finish() != SPI_OK_FINISH)
{
elog(ERROR,"auditc.so: Erro to close connection");
}
return PointerGetDatum(newtuple);
} else {
elog(ERROR,"auditc.so: Erro not get Connection");
}
}
/**
* Debug functions
**/
#ifdef DEBUGAUDIT
display(TriggerData *trigdata)
{
TupleDesc tupdesc = trigdata->tg_relation->rd_att;
elog(INFO,"_________TRIGGER_INFO___________");
elog(INFO,"nspname(schema) = %s", SPI_getnspname(trigdata->tg_relation));
elog(INFO,"relname(table) = %s", SPI_getrelname(trigdata->tg_relation));
elog(INFO,"_________TRIG_TUPLE_____________");
displayvars(trigdata->tg_trigtuple, tupdesc);
if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
{
elog(INFO,"_________TRIG_NEW_______________");
displayvars(trigdata->tg_newtuple, tupdesc);
}
}
displayvars(HeapTuple tupe, HeapTuple tupdesc)
{
if (!tupe) return;
uint32 coluns = HeapTupleHeaderGetNatts(tupe->t_data);
elog(INFO,"colums size: %d",coluns);
if (coluns > 0)
{
bool isnull = false;
int pos;
for(pos = coluns; pos > 0; pos--)
{
char* value = SPI_getvalue(tupe, tupdesc, pos);
elog(INFO,"column %s value: %s",SPI_fname(tupdesc, pos), (value ? value : "(null)"));
}
}
}
displaydiff(diffret *ret)
{
elog(INFO,"_________DIFF_VALUES____________");
elog(INFO,"colums diff: %d",ret->size);
if (ret->size < 1) return;
int pos;
for(pos = 0; pos < ret->size; pos++)
{
elog(INFO,"column %s diff: %s", ret->fields[pos], ret->data[pos]);
}
}
displayconstraints(Relation relation)
{
elog(INFO, "______CONSTRAINTS_OIDs_________");
ListCell *i;
foreach(i, relation->rd_indexlist)
{
Oid oid = lfirst(i);
elog(INFO, "Oid: %d", oid);
}
elog(INFO, "______CONSTRAINTS_INDEX________");
elog(INFO, "rd_index: %d", relation->rd_index);
if (relation->rd_index)
{
elog(INFO, "indkey.ndim: %d", relation->rd_index->indkey.ndim);
elog(INFO, "indkey.vl_len_: %d", relation->rd_index->indkey.vl_len_);
}
}
#endif
构建命令
编译：cc -fpic -I/usr/include/pgsql/server/ -c audit.c
创建共享库：cc -shared -o audit.so audit.o
将 "audit.so" 移动到 postgres 库文件夹
限制
需要表上的主键
