使用C语言编写一个简易审计插件
使用C语言编写扩展
PostgreSQL是一个强大的开源关系型数据库管理系统。它使用额外的特性以扩展SQL语言。一个DBMS (数据库管理系统)并不仅仅由它的性能和开箱即用的特性所决定，还取决于是否具有 定制额外的用户功能的能力。其中一些功能以数据库中的模式或架构的形式出现，比如存储过程和函数，但是它们的范围通常局限于DBMS所公开的功能。例如，如何编写一个驻留在DBMS中的自定义查询分析应用程序。
为了支持这些选项，PostgreSQL提供了一个可插入式的架构框架，允许你安装扩展。Extensions由配置文件 (control)、一堆SQL文件和动态加载库组成。
这意味着你可以根据定义好的Extensions准则编写自己的代码，并将其插入至PostgreSQL实例中，而无需改变实际的PostgreSQL代码树。Extensions可以扩展PostgreSQL原本所能做的，但更重要的是，它提供了与外部实体交互的能力。这些外部实体可以是其他数据库管理系统，如ClickHouse、Mongo或HDFS(通常称为 foreign data wrappers外部数据包装器)，或者其他的解释器或编译器(从而允许我们使用其他语言编写数据库函数，如Java、Python、Perl或TCL等等)。Extensions的另一个潜在用处可能是“代码掩蔽”，它可以使您保护自己的机密代码不被窥视。
创建你自己的扩展
要构建你自己的扩展，并不需要一个完整的PostgreSQL基础代码。您可以使用已安装好的PostgreSQL构建和安装一个扩展(可能需要安装一个devel RPM或Debian包)。关于扩展的详细信息可以在PostgreSQL的官方文档中找到。在PostgreSQL源码的contrib目录中，有许多可用于不同特性的扩展。除了contrib目录之外，commiters也在编写可以在互联网上使用的扩展，但目前还不是PostgreSQL源代码树的一部分。其中，pg_stat_statements，PL / pgSQL和PostGIS是最出名或使用最广泛的扩展示例。
通常情况下，可用的PostgreSQL扩展可以分为四个主要类别：
添加对新语言的支持 (PL/pgSQL, PL/Python 和 PL/Java)
引入新的数据类型 (Hstore, cube 和 hstore)
各种各样的内置插件 (contrib目录下有很多各种各样的插件)
Foreign Data Wrapper (postgres_fdw, mysqldb_fdw, clickhousedb_fdw)
创建一个新的扩展需要四个基础文件：
Makefile：使用 PGXS，PostgreSQL为扩展提供的基础架构
控制文件：带有扩展的相关描述信息
SQL文件：如果一个扩展带有相关SQL代码，它可能在SQL文件中 (可选)
C代码：我们想要构建的共享对象 (可选)
Makefile
为了编译C代码，我们需要一个Makefile。这是一个非常简单的Makefile，除了PGXS之外，PGXS是PostgreSQL提供的基础架构，用于创建扩展。可以使用 pg_config --pgxs这个二进制文件查看 PGXS，这个文件的细节可以在Github上找到。
下面是一个简单的Makefile例子，可以用来编译C代码：
EXTENSION = log
MODULE_big = log
DATA = log--0.0.1.sql
OBJS = log.o
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
控制文件
这个文件的命名格式必须是 [Extensions Name].control，控制文件包含很多选项，可以在官网上找到详细信息。但在这个例子中，我只使用了一些基本的选项：
comments：关于扩展的一些描述性信息
default_version：扩展的版本信息，SQL文件的名字会在文件名中包含此类信息。
relocatable：告诉PostgreSQL是否可以将包含的对象移动到其他的模式中。
module_pathname：该信息会被替换为实际的Lib文件路径。
comment = 'PostgreSQL Utility Command Logger
'default_version = '0.0.1'
relocatable = true
module_pathname = '$libdir/log'
可以使用psql命令\dx 查看该文件的内容。
postgres=# \dx log
List of installed extensions Name | Version | Schema | Description
------------------------------------+---------+--------+----------------------------------
log
SQL文件
这是一个映射文件，用它来映射PostgreSQL中的函数和对应的C函数。无论何时调用SQL函数，都会调用相应的C函数，文件名称必须为 [Extension Name] - [default version] .sql，这与控制文件中定义的默认版本信息相同。
CREATE FUNCTION pg_all_queries(OUT query TEXT, pid OUT TEXT)
RETURNS SETOF RECORDAS 'MODULE_PATHNAME',
'pg_all_queries'
LANGUAGE C STRICT VOLATILE;
C代码
使用C代码可以编写三种类型的函数。
第一种是使用SQL文件中编写的SQL函数调用C代码函数。
第二种是回调函数。你可以通过指定函数的指针来注册回调函数。此处不需要SQL函数。当特定事件发生时，便会自动调用此函数。
第三种类型的函数是自动调用的，甚至不需要注册。这些函数会在一些事件发生时如 extension load/unload的时候被调用。
下面时包含C代码定义的C文件。对于C文件的名称或数量没有限制。
#include "postgres.h"
/* OS Includes */
/* PostgreSQL Includes */
PG_MODULE_MAGIC;
void _PG_init(void);
void _PG_fini(void);
Datum pg_all_queries(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_all_queries);
static void process_utility(PlannedStmt *pstmt, const char *queryString,ProcessUtilityContext context,ParamListInfo params,QueryEnvironment *queryEnv,DestReceiver *dest, char *completionTag);
在扩展里面需要包含postgres.h头文件。也可以按需包含其他头文件。
PG_MODULE_MAGIC是宏，需要包含在C文件中。_PG_init和 _PG_fini 分别是当安装或卸载时调用的函数。
下面是安装和卸载扩展时调用的函数示例：
void _PG_init(void)
{
/* ... C code here at time of extension loading ... */
ProcessUtility_hook = process_utility;
}
Void _PG_fini(void)
{
/* ... C code here at time of extension unloading ... */
}
下面是一个回调函数的示例，你可以在调用语句 (例如任何DDL语句) 时调用该函数。queryString变量包含实际的查询文本。
static void process_utility(PlannedStmt *pstmt,
const char *queryString,
ProcessUtilityContext context,
ParamListInfo params,
QueryEnvironment *queryEnv,DestReceiver *dest,
char *completionTag)
{
/* ... C code here ... */
standard_ProcessUtility(pstmt,
queryString,
context,
params,
queryEnv,
dest,
completionTag);
/* ... C code here ... */
}
最后，有一个通过用户定义的SQL函数调用C函数的示例。此例子在内部调用了包含在共享对象中的C函数。
Datum pg_all_queries(PG_FUNCTION_ARGS)
{
/* ... C code here ... */
tupstore = tuplestore_begin_heap(true, false, work_mem);
/* ... C code here ... */
values[0] = CStringGetTextDatum(query);
values[1] = CStringGetTextDatum(pid);
/* ... C code here ... */
tuplestore_donestoring(tupstore);
return (Datum) 0;
}
编译安装
编译前，如果系统路径下没有pg_config，需要先设置PostgreSQL bin目录的路径。
设置路径：export PATH=/usr/local/pgsql/bin:$PATH
编译插件：make USE_PGXS=1
安装插件：make USE_PGXS=1 install
输出
现在，我们可以通过一个简单的SQL查询来使用我们的扩展。下面是用C语言编写的扩展的输出：
postgres=# select * from pg_all_queries();
query | pid
--------------------------+|------
create table foo(a int); +| 8196
create table bar(a int); +| 8196
drop table foo; +| 8196
(3 rows)
我希望这个例子可以作为一个起点，让你创建更多有用的扩展，不仅可以帮助你和你的公司，还可以给你一个机会来分享和帮助PostgreSQL社区的成长。
下面是我的Case：
postgres=# select * from pg_all_queries();
query | pid
--------------------------------------+-------
create table t1(id int); +| 29615
|
drop table t1; +| 29615
|
create table t3(id int); +| 29615
|
create table t9(id int); +| 29615
|
alter table t9 drop column info ; +| 29615
|
alter table t9 add column info text;+| 29615
|
(6 rows)
附录
reference
[1] https://www.postgresql.org/docs/current/external-extensions.html
[2] https://github.com/postgres/postgres/blob/master/src/makefiles/pgxs.mk
[3] https://www.postgresql.org/docs/9.1/extend-extensions.html
[4] https://github.com/ibrarahmad/Blog-Examples/tree/master/log
C代码
/*-------------------------------------------------------------------------
*
* stat.c
*
* Copyright (c) 2008-2018, PostgreSQL Global Development Group
*
* IDENTIFICATION
* contrib/pg_log/log.c
*/
#include "postgres.h"
#include <math.h>
#include <sys/stat.h>
#include <unistd.h>
#include "access/hash.h"
#include "catalog/pg_authid.h"
#include "executor/instrument.h"
#include "funcapi.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "parser/analyze.h"
#include "parser/parsetree.h"
#include "parser/scanner.h"
#include "parser/scansup.h"
#include "pgstat.h"
#include "storage/fd.h"
#include "storage/ipc.h"
#include "storage/spin.h"
#include "tcop/utility.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
PG_MODULE_MAGIC;
void _PG_init(void);
void _PG_fini(void);
static int nested_level = 0;
static char query[1024];
static void write_file(const char *str);
static char *read_file(FILE *fp);
Datum pg_all_queries(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_all_queries);
static void process_utility(PlannedStmt *pstmt,
const char *queryString,
ProcessUtilityContext context,
ParamListInfo params,
QueryEnvironment *queryEnv,
DestReceiver *dest,
char *completionTag);
void
_PG_init(void)
{
ProcessUtility_hook = process_utility;
}
void
_PG_fini(void)
{
}
static void
write_file(const char *str)
{
FILE *fp = fopen("/tmp/log.stat", "a+");
if (fp == NULL)
elog(ERROR,"log: unable to open log file");
fputs(str, fp);
fputs("\n", fp);
fclose(fp);
}
static char *
read_file(FILE *fp)
{
char *rc = NULL;
rc = fgets(query, 1023, fp);
return rc ? query : NULL;
}
static void
process_utility(PlannedStmt *pstmt,
const char *queryString,
ProcessUtilityContext context,
ParamListInfo params,
QueryEnvironment *queryEnv,
DestReceiver *dest,
char *completionTag)
{
nested_level++;
PG_TRY();
{
standard_ProcessUtility(pstmt, queryString, context, params, queryEnv, dest, completionTag);
if(queryString)
write_file(queryString);
nested_level--;
}
PG_CATCH();
{
nested_level--;
PG_RE_THROW();
}
PG_END_TRY();
}
Datum
pg_all_queries(PG_FUNCTION_ARGS)
{
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
TupleDesc tupdesc;
Tuplestorestate *tupstore;
MemoryContext per_query_ctx;
MemoryContext oldcontext;
Datum values[2];
bool nulls[2] = {0};
char pid[25];
char *query;
FILE *fp;
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
oldcontext = MemoryContextSwitchTo(per_query_ctx);
tupstore = tuplestore_begin_heap(true, false, work_mem);
fp = fopen("/tmp/log.stat", "r");
if (fp == NULL)
{
elog(WARNING,"log: unable to open log file");
query = "no more queries";
sprintf(pid, "%s", "invalid pid");
}
else
{
sprintf(pid, "%d", (int)getpid());
query = read_file(fp);
}
while(query)
{
values[0] = CStringGetTextDatum(query);
values[1] = CStringGetTextDatum(pid);
/* Build a tuple descriptor for our result type */
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
rsinfo->returnMode = SFRM_Materialize;
rsinfo->setResult = tupstore;
rsinfo->setDesc = tupdesc;
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
if (fp == NULL)
break;
query = read_file(fp);
if (query == NULL)
break;
}
if (fp)
fclose(fp);
tuplestore_donestoring(tupstore);
MemoryContextSwitchTo(oldcontext);
return (Datum) 0;
}
控制文件
comment = 'PostgreSQL Logger Utility'
default_version = '0.0.1'
relocatable = true
module_pathname = '$libdir/log'
SQL文件
CREATE FUNCTION pg_all_queries(OUT query TEXT, pid OUT TEXT)
RETURNS SETOF RECORD
AS 'MODULE_PATHNAME', 'pg_all_queries'
LANGUAGE C STRICT VOLATILE;
Makefile
EXTENSION = log
MODULE_big = log
DATA = log--0.0.1.sql
OBJS = log.o
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
译者著：对于经常捣鼓插件的我来说，这两篇关于插件的文字可以加深对插件的理解，而并不局限于“使用”，造轮子造插件也成为了可能，这一篇是很不错的启蒙样例， 👍
原文链接：https://www.percona.com/blog/2019/04/05/writing-postgresql-extensions-is-fun-c-language/
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
