PG版本：PostgreSQL 9.6.5

由于版本不同代码会有所差别，本例在PostgreSQL 9.6.5编写而来，其他版本未经测试(PostgreSQL 10beta3测试不能直接用)。

1.安装插件前

sun@sun:~/PG/PG9.6.5/bin$ ./createdb testdb sun@sun:~/PG/PG9.6.5/bin$ ./createuser testuser sun@sun:~/PG/PG9.6.5/bin$ ./psql -U testuser testdb psql (9.6.5) Type "help" for help.  testdb=> create table testtable(a int); CREATE TABLE testdb=> create table testtable2(a int); CREATE TABLE testdb=> \q sun@sun:~/PG/PG9.6.5/bin$ ./psql -U sun testdb psql (9.6.5) Type "help" for help.  testdb=# \d            List of relations  Schema |    Name    | Type  |  Owner    --------+------------+-------+----------  public | testtable  | table | testuser  public | testtable2 | table | testuser (2 rows)  testdb=# drop table testtable2; DROP TABLE testdb=# \d            List of relations  Schema |   Name    | Type  |  Owner    --------+-----------+-------+----------  public | testtable | table | testuser (1 row)

2.安装插件后

sun@sun:~/PG/PG9.6.5/bin$ ./psql -U sun testdb psql (9.6.5) Type "help" for help.  testdb=# \d            List of relations  Schema |   Name    | Type  |  Owner    --------+-----------+-------+----------  public | testtable | table | testuser (1 row)  testdb=# drop table testtable; ERROR:  Please drop with User: testuser  STATEMENT:  drop table testtable; ERROR:  Please drop with User: testuser 

3.插件安装方法：

在源码contrib下建立目录：limitsuperuserdrop，将C代码文件和Makefile放入其中，编译安装，如下：

sun@sun:~/Data/postgresql-9.6.5/contrib/limitsuperuserdrop$ ll 总用量 80 -rw------- 1 sun sun 76898 9月  13 21:04 limitsuperuserdrop.c -rw------- 1 sun sun   394 9月  13 19:57 Makefile sun@sun:~/Data/postgresql-9.6.5/contrib/limitsuperuserdrop$ make ; make install

安装完成后修改配置文件， 修改Data目录下的postgresql.conf中的shared_preload_libraries

shared_preload_libraries = 'limitsuperuserdrop'	# (change requires restart)

重启数据库。安装完成。

4.原理：

本插件使用PostgreSQL的hook机制，实现对PG数据库的超级用户的删除操作的权限限制。

常用hook:

  本例使用ProcessUtility_hook

插件实现思路：

当数据库调用ProcessUtility_hook时，首先会检测是否为NULL，不为NULL的话，则调用由我们编写的函数，否则执行标准函数。 也就是说在我们不使用插件的时候，数据库总是在调用standard_ProcessUtility。因此我们的插件代码，就是在standard_ProcessUtility中执行drop操作的地方判断要删除的表是否属于进行操作的用户，不是的话就报错。因此，本插件代码绝大部分是由src/backend/tcop/utility.c文件复制而来。

	if (ProcessUtility_hook) 		(*ProcessUtility_hook) (parsetree, queryString, 								context, params, 								dest, completionTag); 	else 		standard_ProcessUtility(parsetree, queryString, 								context, params, 								dest, completionTag);

在此提供两套代码，完整版和精简版，精简版删除了大多数的代码。 点击下载

Makefile代码：

# contrib/limitsuperuserdrop/Makefile  MODULES = limitsuperuserdrop OBJS = limitsuperuserdrop.o $(WIN32RES) PGFILEDESC = "limitsuperuserdrop"  ifdef USE_PGXS PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) else subdir = contrib/limitsuperuserdrop top_builddir = ../.. include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif  

以下是精简版的C文件代码：

/*  * limitsuperuserdrop.c  * 说明：限制超级用户删除表的操作。  */ #include "postgres.h" #include "miscadmin.h" #include "nodes/parsenodes.h" #include "nodes/pg_list.h" #include "catalog/pg_class.h" #include "executor/executor.h" #include "tcop/utility.h" #include "postgres.h"  #include "access/htup_details.h" #include "access/reloptions.h" #include "access/twophase.h" #include "access/xact.h" #include "access/xlog.h" #include "catalog/catalog.h" #include "catalog/namespace.h" #include "catalog/toasting.h" #include "commands/alter.h" #include "commands/async.h" #include "commands/cluster.h" #include "commands/comment.h" #include "commands/collationcmds.h" #include "commands/conversioncmds.h" #include "commands/copy.h" #include "commands/createas.h" #include "commands/dbcommands.h" #include "commands/defrem.h" #include "commands/discard.h" #include "commands/event_trigger.h" #include "commands/explain.h" #include "commands/extension.h" #include "commands/matview.h" #include "commands/lockcmds.h" #include "commands/policy.h" #include "commands/portalcmds.h" #include "commands/prepare.h" #include "commands/proclang.h" #include "commands/schemacmds.h" #include "commands/seclabel.h" #include "commands/sequence.h" #include "commands/tablecmds.h" #include "commands/tablespace.h" #include "commands/trigger.h" #include "commands/typecmds.h" #include "commands/user.h" #include "commands/vacuum.h" #include "commands/view.h" #include "miscadmin.h" #include "parser/parse_utilcmd.h" #include "postmaster/bgwriter.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rewriteRemove.h" #include "storage/fd.h" #include "tcop/pquery.h" #include "tcop/utility.h" #include "utils/acl.h" #include "utils/guc.h" #include "utils/syscache.h"  PG_MODULE_MAGIC;  void _PG_init(void); void _PG_fini(void);  static ProcessUtility_hook_type My_ProcessUtility_hook_type = NULL; static void ExecDropStmt(DropStmt *stmt, bool isTopLevel); Oid MyGetOwnerId(Oid class_oid); Oid MyGetrelOid(DropStmt *drop);  void My_ProcessUtility(Node *parsetree, 						const char *queryString, 						ProcessUtilityContext context, 						ParamListInfo params, 						DestReceiver *dest, 						char *completionTag) { 	bool		isTopLevel = (context == PROCESS_UTILITY_TOPLEVEL);  	switch (nodeTag(parsetree)) 	{  		case T_DropStmt: 			{ 				DropStmt   *stmt = (DropStmt *) parsetree;  				if( MyGetOwnerId(MyGetrelOid(stmt)) != GetUserId() ) 					ereport(ERROR, 									(errcode(ERRCODE_UNDEFINED_TABLE), 											errmsg("Please drop with User: %s ", GetUserNameFromId(MyGetOwnerId(MyGetrelOid(stmt)),false)) 									) 								); 				else 					ExecDropStmt(stmt, isTopLevel); 			} 			break; 	} }  static void ExecDropStmt(DropStmt *stmt, bool isTopLevel) { 	switch (stmt->removeType) 	{ 		case OBJECT_INDEX: 			if (stmt->concurrent) 				PreventTransactionChain(isTopLevel, 										"DROP INDEX CONCURRENTLY"); 			/* fall through */  		case OBJECT_TABLE: 		case OBJECT_SEQUENCE: 		case OBJECT_VIEW: 		case OBJECT_MATVIEW: 		case OBJECT_FOREIGN_TABLE: 			RemoveRelations(stmt); 			break; 		default: 			RemoveObjects(stmt); 			break; 	} }  Oid MyGetOwnerId(Oid class_oid) { 	HeapTuple	tuple; 	Oid			ownerId;  	tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(class_oid)); 	if (!HeapTupleIsValid(tuple)) 		ereport(ERROR, 				(errcode(ERRCODE_UNDEFINED_TABLE), 				 errmsg("relation with OID %u does not exist", class_oid)));  	ownerId = ((Form_pg_class) GETSTRUCT(tuple))->relowner;  	ReleaseSysCache(tuple); //	printf("User = %d\n",ownerId); 	return ownerId; }   Oid MyGetrelOid(DropStmt *drop) { 	ListCell   *cell; 	LOCKMODE	lockmode = AccessExclusiveLock; 	Oid			relOid;  	foreach(cell, drop->objects) 	{ 		RangeVar   *rel = makeRangeVarFromNameList((List *) lfirst(cell));  		relOid = RangeVarGetRelidExtended(rel, lockmode, true, 										  false, 										  NULL, 										  NULL);  	} //	printf("relOid = %d\n",relOid); 	return relOid; }   // Install Hook void _PG_init(void) {         My_ProcessUtility_hook_type = ProcessUtility_hook;         ProcessUtility_hook = My_ProcessUtility; }   // Uninstall Hook void _PG_fini(void) {         ProcessUtility_hook = My_ProcessUtility_hook_type; } 

精简后的代码运行drop操作效果一样,便于阅读学习。但是由于其他的操作(比如创建表)也会经过我们编写的代码，然而精简后的对其他操作并没有相应的实现代码，所以并不会发生真实的操作(如下，create table testtable2(a int),并没有成功创建testtable2表，因此使用精简代码进行实验的话，请在安装插件前先创建几张表备用)。

sun@sun:~/PG/PG9.6.5/bin$ ./psql -U sun testdb psql (9.6.5) Type "help" for help.  testdb=# \d            List of relations  Schema |   Name    | Type  |  Owner    --------+-----------+-------+----------  public | testtable | table | testuser (1 row)  testdb=# create table testtable2(a int); CREATE TABLE testdb=# \d            List of relations  Schema |   Name    | Type  |  Owner    --------+-----------+-------+----------  public | testtable | table | testuser (1 row)

参考：

https://my.oschina.net/Suregogo/blog/312848