本节简单介绍了如何实现钩子函数.

一、需求

删除数据库pg12db时，只能使用pg12用户删除，其他用户（包括超级用户）均不能删除此数据库。

二、实现步骤

删除数据库的命令是drop database,属于Utility命令,而PG提供了ProcessUtility_hook钩子可供使用.
实现一个钩子函数,判断SQL语句是否为T_DropdbStmt,如是,则判断数据库名称和用户名称是否匹配,如不匹配,则报错,源码如下:

[pg12@localhost hookdemo_dbrestrict]$ cat hookdemo_dbrestrict.c
/*
 * This is a hook demo.
 * 
 */
#include "postgres.h"
#include "miscadmin.h"
#include "tcop/utility.h"
PG_MODULE_MAGIC;
void _PG_init(void);
void _PG_fini(void);
static char *undroppabledb = "pg12db";
static char *hooksuperuser = "pg12";
static ProcessUtility_hook_type prev_utility_hook = NULL;
static void hookdemodbrestrict_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
                          ProcessUtilityContext context, ParamListInfo params,
                          QueryEnvironment *queryEnv,
                          DestReceiver *dest, char *completionTag)
{
    /* Do our custom process on drop database */
    switch(nodeTag(pstmt->utilityStmt))
    {   
        case T_DropdbStmt:
        {
            DropdbStmt *stmt = (DropdbStmt *)pstmt->utilityStmt;
            char *username = GetUserNameFromId(GetUserId(),false);
            /*
             * only user pg12 can drop pg12db.
            */
            if (strcmp(stmt->dbname, undroppabledb) == 0 &&
                    strcmp(username, hooksuperuser) != 0)
                ereport(ERROR,(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),errmsg("Only superuser \"%s\" can drop database \"%s\"",hooksuperuser,undroppabledb)));
            break;
        }
        default:
            break;
    }
    /*Standard process*/
    standard_ProcessUtility(pstmt, queryString, context, params, queryEnv, dest, completionTag);
}
/* _PG_init */
void
_PG_init(void)
{
    prev_utility_hook = ProcessUtility_hook;
    ProcessUtility_hook = hookdemodbrestrict_ProcessUtility;
}
/* Uninstall */
void
_PG_fini(void)
{
    ProcessUtility_hook = prev_utility_hook;
}
[pg12@localhost hookdemo_dbrestrict]$

三、实际效果

创建超级用户superx,使用该用户登录,创建pg12db数据库,删除数据库,报错.

[local:/data/run/pg12]:5120 pg12@testdb=# create user superx with superuser password 'root';
CREATE ROLE
[local:/data/run/pg12]:5120 pg12@testdb=# \q
[pg12@localhost pg122db]$ psql -U superx
Expanded display is used automatically.
psql (12.2)
Type "help" for help.
[local:/data/run/pg12]:5120 superx@testdb=# create database pg12db;
CREATE DATABASE
[local:/data/run/pg12]:5120 superx@testdb=# drop database pg12db;
ERROR:  Only superuser "pg12" can drop database "pg12db"
[local:/data/run/pg12]:5120 superx@testdb=#

四、配置文件说明

Makefile
MODULES
list of shared-library objects to be built from source files with same stem (do not include library suffixes in this list)
MODULE big
a shared library to build from multiple source files (list object files in OBJS)
PROGRAM
an executable program to build (list object files in OBJS)
EXTENSION
extension name(s); for each name you must provide an extension.control file, which will be installed into prefix/share/extension
MODULEDIR
subdirectory of prefix/share into which DATA and DOCS files should be installed (if not set, default is extension if EXTENSION is set, or contrib if not)
DATA
random files to install into prefix/share/$MODULEDIR
DATA_built
random files to install into prefix/share/$MODULEDIR, which need to be built first
DATA_TSEARCH
random files to install under prefix/share/tsearch_data
DOCS
random files to install under prefix/doc/$MODULEDIR
HEADERS
HEADERS_built
Files to (optionally build and) install under prefix/include/server/$MODULEDIR/$MODULE_big.
Unlike DATA_built, files in HEADERS_built are not removed by the clean target; if you want them removed, also add them to EXTRA_CLEAN or add your own rules to do it.
HEADERS$MODULE
HEADERS built$MODULE
Files to install (after building if specified) under prefix/include/server/$MODULEDIR/$MODULE, where $MODULE must be a module name used in MODULES or MODULE big.
Unlike DATA_built, files in HEADERS_built$MODULE are not removed by the clean target; if you want them removed, also add them to EXTRA CLEAN or add your own rules to do it.
It is legal to use both variables for the same module, or any combination, unless you have two module names in the MODULES list that differ only by the presence of a prefix built, which would cause ambiguity. In that (hopefully unlikely) case, you should use only the HEADERS built$MODULE variables.
SCRIPTS
script files (not binaries) to install into prefix/bin
SCRIPTS_built
script files (not binaries) to install into prefix/bin, which need to be built first
REGRESS
list of regression test cases (without suffix), see below
REGRESS_OPTS
additional switches to pass to pg_regress
ISOLATION
list of isolation test cases, see below for more details
ISOLATION_OPTS
additional switches to pass to pg_isolation_regress
TAP_TESTS
switch defining if TAP tests need to be run, see below
NO_INSTALLCHECK
don’t define an installcheck target, useful e.g. if tests require special configuration, or don’t use pg_regress
EXTRA_CLEAN
extra files to remove in make clean
PG_CPPFLAGS
will be prepended to CPPFLAGS
PG_CFLAGS
will be appended to CFLAGS
PG_CXXFLAGS
will be appended to CXXFLAGS
PG_LDFLAGS
will be prepended to LDFLAGS
PG_LIBS
will be added to PROGRAM link line
SHLIB_LINK
will be added to MODULE_big link line
PG_CONFIG
path to pg_config program for the PostgreSQL installation to build against (typically just pg_config to use the first one in your PATH)

control file
A control file can set the following parameters:
directory (string)
The directory containing the extension’s SQL script file(s). Unless an absolute path is given, the name is relative to the installation’s SHAREDIR directory. The default behavior is equivalent to specifying directory = ‘extension’.
default_version (string)
The default version of the extension (the one that will be installed if no version is specified in CREATE EXTENSION). Although this can be omitted, that will result in CREATE EXTENSION failing if no VERSION option appears, so you generally don’t want to do that.
comment (string)
A comment (any string) about the extension. The comment is applied when initially creating an extension, but not during extension updates (since that might override user-added comments). Alternatively, the extension’s comment can be set by writing a COMMENT command in the script file.
encoding (string)
The character set encoding used by the script file(s). This should be specified if the script files contain any non-ASCII characters. Otherwise the files will be assumed to be in the database encoding.
module_pathname (string)
The value of this parameter will be substituted for each occurrence of MODULE_PATHNAME in the script file(s). If it is not set, no substitution is made. Typically, this is set to $libdir/shared_library_name and then MODULE_PATHNAME is used in CREATE FUNCTION commands for C-language functions, so that the script files do not need to hard-wire the name of the shared library.
requires (string)
A list of names of extensions that this extension depends on, for example requires = ‘foo, bar’. Those extensions must be installed before this one can be installed.
superuser (boolean)
If this parameter is true (which is the default), only superusers can create the extension or update it to a new version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required.
relocatable (boolean)
An extension is relocatable if it is possible to move its contained objects into a different schema after initial creation of the extension. The default is false, i.e. the extension is not relocatable. See Section 37.17.3 for more information.
schema (string)
This parameter can only be set for non-relocatable extensions. It forces the extension to be loaded into exactly the named schema and not any other. The schema parameter is consulted only when initially creating an extension, not during extension updates. See Section 37.17.3 for more information.

五、参考资料