Shulou(Shulou.com)05/31 Report--

What are the hook functions in PostgreSQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

I. demand

When you delete a database pg12db, you can only use the pg12 user to delete it, and no other user (including superuser) can delete the database.

II. Implementation steps

The command to delete the database is drop database, which belongs to the Utility command, while PG provides ProcessUtility_hook hooks for use.

Implement a hook function to determine whether the SQL statement is T_DropdbStmt. If so, determine whether the database name and user name match. If not, an error is reported. The source code is as follows:

[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] $III.

Create super user superx, use this user to log in, create pg12db database, delete database, report error.

[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 superxExpanded display is used automatically.psql 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 this is the answer to superx@testdb=# 's question about what hook functions are in PostgreSQL. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.