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, 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)