本文的目的一是为了备忘，二是为了抛砖引玉，希望有更多的人来研究如何使用好PostgreSQL的hook机制。
在研究pg_stat_statments的源代码的时候，发现其中使用了hook机制：例如其中提到了如下几种hook：
void
_PG_fini(void)
{
/* Uninstall hooks. */
shmem_startup_hook = prev_shmem_startup_hook;
ExecutorStart_hook = prev_ExecutorStart;
ExecutorRun_hook = prev_ExecutorRun;
ExecutorFinish_hook = prev_ExecutorFinish;
ExecutorEnd_hook = prev_ExecutorEnd;
ProcessUtility_hook = prev_ProcessUtility;
}
hook机制，是一把双刃剑：
它十分强大，可以让用户有机会切入到PostgreSQL的内部运行机制中，完成用户自定义的控制登录过程、查看系统状态、收集数据库活动的统计信息，甚至控制数据库中特定活动的执行。
当然，一般而言要求hook函数用C语言来编写，如果稍有不慎可能造成数据库系统失常乃至崩溃。
如果按照如下的日文网站的列举，其种类繁多，功能丰富：
http://postgresql.g.hatena.ne.jp/pgsql/20090325
遗憾的是，PostgreSQL的官方文档中没有讲如何使用hook的。只是在release note 中隐约提及。
目前，最权威的资料，来自于：http://wiki.postgresql.org/images/e/e3/Hooks_in_postgresql.pdf
为了进行初步的学习，参考了如下的URL的例子：
http://michael.otacoo.com/postgresql-2/hooks-in-postgres-super-superuser-restrictions/
下面描述建立hook的过程：
此处使用的是 ProcessUtility_hook，作为了例子，当我们从psql等发起 drop database xxx命令的时候。
本hook被激活，然后进行检查，如果被删除的数据库名为 fooddb，那么只有用户foo才有机会删除它。
步骤1：建立contrib目录：
[root@server contrib]# pwd
/soft/postgresql-9.1.2/contrib
[root@server contrib]# mkdir dbrestrict
[root@server contrib]# cd dbrestrict
步骤2：编写程序代码：
需要指出的是，由于我用的是postgresql9.1.2，和上述URL中所用的数据库版本有所不同，故 standard_ProcessUtility 的入口参数有所不同。
[root@server dbrestrict]# pwd
/soft/postgresql-9.1.2/contrib/dbrestrict
[root@server dbrestrict]# cat dbrestrict.c
/*
* dbrestrict.c
* Restrict drop of a given database to a given super-superuser only.
* Michael Paquier, 2013
* Under license "do whatever you want with that"
*/
#include "postgres.h"
#include "miscadmin.h"
#include "tcop/utility.h"
PG_MODULE_MAGIC;
void _PG_init(void);
void _PG_fini(void);
static char *undroppabledb = "foodb";
static char *supersuperuser = "foo";
static ProcessUtility_hook_type prev_utility_hook = NULL;
static void dbrestrict_utility(Node *parsetree, const char *queryString, ParamListInfo params,bool isTopLevel, DestReceiver *dest,char *completionTag);
/*
* Personal process for DB drop restriction
*/
static
void dbrestrict_utility(Node *parsetree,const char *queryString,ParamListInfo params,bool isTopLevel,DestReceiver *dest,char *completionTag)
{
/* Do our custom process on drop database */
switch (nodeTag(parsetree))
{
case T_DropdbStmt:
{
DropdbStmt *stmt = (DropdbStmt *) parsetree;
char *username = GetUserNameFromId(GetUserId());
/*
* Check that only the authorized superuser foo can
* drop the database undroppable_foodb.
*/
if (strcmp(stmt->dbname, undroppabledb) == 0 &&
strcmp(username, supersuperuser) != 0)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("Only super-superuser \"%s\" can drop database \"%s\"",
supersuperuser, undroppabledb)));
break;
}
default:
break;
}
/* Fallback to normal process */
standard_ProcessUtility(parsetree, queryString, params, isTopLevel,dest,completionTag);
}
/*
* _PG_init
* Install the hook.
*/
void
_PG_init(void)
{
prev_utility_hook = ProcessUtility_hook;
ProcessUtility_hook = dbrestrict_utility;
}
/*
* _PG_fini
* Uninstall the hook.
*/
void
_PG_fini(void)
{
ProcessUtility_hook = prev_utility_hook;
}
[root@server dbrestrict]#
步骤3：建立Makefile:
[root@server dbrestrict]# cat Makefile
# contrib/dbrestrict/Makefile
MODULES = dbrestrict
OBJS = dbrestreict.so
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/dbrestrict
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
[root@server dbrestrict]#
步骤4：编译和安装:
[root@server dbrestrict]# make
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -c -o dbrestrict.o dbrestrict.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -L../../src/port -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags -shared -o dbrestrict.so dbrestrict.o
rm dbrestrict.o
[root@server dbrestrict]# ls
Makefile dbrestrict.c dbrestrict.so
[root@server dbrestrict]# make install
/bin/mkdir -p '/usr/local/pgsql/lib'
/bin/sh ../../config/install-sh -c -m 755 dbrestrict.so '/usr/local/pgsql/lib/'
[root@server dbrestrict]#
步骤5：配置postgresql.conf文件：把编译好的名为dbrestrict的动态库，追加到 shared_preload_libraries中。
[postgres@server data]$ vim postgresql.conf
[postgres@server data]$ cat postgresql.conf | grep 'preload'
shared_preload_libraries = 'dbrestrict' # (change requires restart)
#local_preload_libraries = ''
[postgres@server data]$
这中 shared_preload_libraries 机制还是不错的，事实上EnterpriseDB PPAS的postgresql.conf的此行是这样的：
shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen'
步骤6：运行验证：
重新启动postgresql
[postgres@server pgsql]$ ./bin/pg_ctl -D ./data start
server starting
[postgres@server pgsql]$ LOG: loaded library "dbrestrict"
LOG: database system was shut down at 2013-06-19 21:05:17 CST
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
可以看到，对用户 foo 和 goo，他们意图drop database foodb时候，得到的信息是不同的；如果foo是super user，就已经可以删除数据库了：
postgres=# create database foodb;
CREATE DATABASE
postgres=# create role foo;
CREATE ROLE
postgres=# \c postgres foo;
FATAL: role "foo" is not permitted to log in
Previous connection kept
postgres=# drop role foo;
DROP ROLE
postgres=# create role foo login;
CREATE ROLE
postgres=# \c postgres foo;
You are now connected to database "postgres" as user "foo".
postgres=> drop database foodb;
ERROR: must be owner of database foodb
postgres=>
postgres=# create role goo login;
CREATE ROLE
postgres=# \c postgres goo;
You are now connected to database "postgres" as user "goo".
postgres=> drop database foodb;
ERROR: Only super-superuser "foo" can drop database "foodb"
postgres=>
结束！如果有可能，希望更进一步地学习PostgreSQL的各种hook，如果有人可以提供更加详细的信息，十分感谢！
磨砺技术珠矶，践行数据之道，追求卓越价值
本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/p/3259147.html，如需转载请自行联系原作者
