加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

PostgreSQL hook机制编写插件限制超级用户权限

发布时间：2020-12-13 16:49:10 所属栏目：百科 来源：网络整理
导读：PG版本：PostgreSQL 9.6.5 由于版本不同代码会有所差别，本例在PostgreSQL 9.6.5编写而来，其他版本未经测试(PostgreSQL 10beta3测试不能直接用 )。 1.安装插件前 sun@sun:~/PG/PG9.6.5/bin$ ./createdb testdbsun@sun:~/PG/PG9.6.5/bin$ ./createuser testu

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:

Hook 初始版本 说明
check_password_hook 9.0 处理用户密码时调用的hook，可以对用户的密码进行限制，增加密码的规范。
ClientAuthentication_hook 9.1 处理连接时调用的hook，可以对连接进行管理。
ExecutorStart_hook 8.4 处理查询执行开始时调用的hook
ExecutorRun_hook 8.4 处理查询执行时调用的hook
ExecutorFinish_hook 8.4 处理查询结束时调用的hook
ExecutorEnd_hook 8.4 处理查询完成后调用的hook
ExecutorCheckPerms_hook 9.1 处理访问权限时调用的hook
ProcessUtility_hook 9.0 通用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,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,errmsg("relation with OID %u does not exist",class_oid)));

	ownerId = ((Form_pg_class) GETSTRUCT(tuple))->relowner;

	ReleaseSysCache(tuple);
//	printf("User = %dn",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 = %dn",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)

参考：

http://www.52php.cn/article/p-zxerrakd-bor.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同