2019独角兽企业重金招聘Python工程师标准>>> 
要想修改PG的用户权限，那么首先要对PG权限控制做一下了解：
PG的权限控制是针对到各个对象的。大家可以看一下，所有系统表（pg_catalog下）几乎都会有aclitem[]数组类型的**acl的字段，这就是对权限的标识。
这里的标识情况如下：
rolename=xxxx -- privileges granted to a role=xxxx -- privileges granted to PUBLICr -- SELECT ("read")w -- UPDATE ("write")a -- INSERT ("append")d -- DELETED -- TRUNCATEx -- REFERENCESt -- TRIGGERX -- EXECUTEU -- USAGEC -- CREATEc -- CONNECTT -- TEMPORARYarwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)* -- grant option for preceding privilege/yyyy -- role that granted this privilege这里有一个非常重要的结构体：
typedef struct AclItem
{Oid ai_grantee; /* ID that this item grants privs to */Oid ai_grantor; /* grantor of privs */AclMode ai_privs; /* privilege bits */
} AclItem;typedef uint32 AclMode;/** The upper 16 bits of the ai_privs field of an AclItem are the grant option* bits, and the lower 16 bits are the actual privileges. We use "rights"* to mean the combined grant option and privilege bits fields.*/对低16位的解释：
低16位很简单的可以明白就是在这16bit上表示权限。通过下面的宏定义可以了解一下：
#define ACL_INSERT (1<<0) /* for relations */
#define ACL_SELECT (1<<1)
#define ACL_UPDATE (1<<2)
#define ACL_DELETE (1<<3)
#define ACL_TRUNCATE (1<<4)
#define ACL_REFERENCES (1<<5)
#define ACL_TRIGGER (1<<6)
#define ACL_EXECUTE (1<<7) /* for functions */
#define ACL_USAGE (1<<8) /* for languages, namespaces, FDWs, and* servers */
#define ACL_CREATE (1<<9) /* for namespaces and databases */
#define ACL_CREATE_TEMP (1<<10) /* for databases */
#define ACL_CONNECT (1<<11) /* for databases */
#define N_ACL_RIGHTS 12 /* 1 plus the last 1<低16位上便是上面对应的权限有无了。对应权限，大家看一下名字就能明白了。
高16位存储的是各权限对应的授出或者被转授选项。其实就是上一次的受权情况，对高16位的操作我只在grant和revoke发现，还有就是对权限进行读入，对*还有处理。这两处的用法就是将上一次的受权低16位存储到高16位。
#define ACL_INSERT_CHR 'a' /* formerly known as "append" */
#define ACL_SELECT_CHR 'r' /* formerly known as "read" */
#define ACL_UPDATE_CHR 'w' /* formerly known as "write" */
#define ACL_DELETE_CHR 'd'
#define ACL_TRUNCATE_CHR 'D' /* super-delete, as it were */
#define ACL_REFERENCES_CHR 'x'
#define ACL_TRIGGER_CHR 't'
#define ACL_EXECUTE_CHR 'X'
#define ACL_USAGE_CHR 'U'
#define ACL_CREATE_CHR 'C'
#define ACL_CREATE_TEMP_CHR 'T'
#define ACL_CONNECT_CHR 'c'而以宏定义定义的是相应权限所对应的字符。这就是我们经常能看到的**acl字段所存储的信息了。
现在通过一个给数据库赋权的例子来解释一下**acl字段存储的信息：
create user ff createdb;
c test ff
create database tain;
select * from pg_database where datname = 'tain';datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | da
tfrozenxid | datminmxid | dattablespace | datacl
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+---
-----------+------------+---------------+--------------------------------------------------------tain | 16438 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12917 | 1674 | 1 | 1663 |
(5 rows)grant all on database tain to ss; select * from pg_database where datname = 'tain'; datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | da
tfrozenxid | datminmxid | dattablespace | datacl
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+---
-----------+------------+---------------+--------------------------------------------------------tain | 16438 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12917 | 1674 | 1 | 1663 | {=Tc/ff,ff=CTc/ff,ss=CTc/ff}
(5 rows)c postgres postgres
alter database tain owner to postgres;
select * from pg_database where datname = 'tain';datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | da
tfrozenxid | datminmxid | dattablespace | datacl
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+---
-----------+------------+---------------+--------------------------------------------------------tain | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12917 | 1674 | 1 | 1663 | {=Tc/postgres,postgres=CTc/postgres,ss=CTc/postgres}
(5 rows)上面有这么三种情况:
1、创建数据库后，默认为空。
2、为用户赋权后，将默认和赋权的情况都写在了datacl字段内。
=前的为受权的用户，/后的为数据库所属的用户，/前的为受权用户对其拥有的权限。
3、改变数据库属主后的权限情况。
说完权限情况，下面介绍一下用户情况，
PG的用户可以分为两类：1超级用户，2普通用户。
为什么这会分出这两类呢？
1、普通用户的权限控制可以直接用命令直接进行修改权限：
postgres=# help grant
Command: GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }[, ...] | ALL [ PRIVILEGES ] }ON { [ TABLE ] table_name [, ...]| ALL TABLES IN SCHEMA schema_name [, ...] }TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }ON [ TABLE ] table_name [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }ON { SEQUENCE sequence_name [, ...]| ALL SEQUENCES IN SCHEMA schema_name [, ...] }TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }ON DATABASE database_name [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | ALL [ PRIVILEGES ] }ON DOMAIN domain_name [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | ALL [ PRIVILEGES ] }ON FOREIGN DATA WRAPPER fdw_name [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | ALL [ PRIVILEGES ] }ON FOREIGN SERVER server_name [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { EXECUTE | ALL [ PRIVILEGES ] }ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | ALL [ PRIVILEGES ] }ON LANGUAGE lang_name [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }ON LARGE OBJECT loid [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }ON SCHEMA schema_name [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { CREATE | ALL [ PRIVILEGES ] }ON TABLESPACE tablespace_name [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | ALL [ PRIVILEGES ] }ON TYPE type_name [, ...]TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]help revoke
Command: REVOKE
Description: remove access privileges
Syntax:
REVOKE [ GRANT OPTION FOR ]{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }[, ...] | ALL [ PRIVILEGES ] }ON { [ TABLE ] table_name [, ...]| ALL TABLES IN SCHEMA schema_name [, ...] }FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }ON [ TABLE ] table_name [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }ON { SEQUENCE sequence_name [, ...]| ALL SEQUENCES IN SCHEMA schema_name [, ...] }FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }ON DATABASE database_name [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON DOMAIN domain_name [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON FOREIGN DATA WRAPPER fdw_name [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON FOREIGN SERVER server_name [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ EXECUTE | ALL [ PRIVILEGES ] }ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON LANGUAGE lang_name [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }ON LARGE OBJECT loid [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }ON SCHEMA schema_name [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ CREATE | ALL [ PRIVILEGES ] }ON TABLESPACE tablespace_name [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON TYPE type_name [, ...]FROM { [ GROUP ] role_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ ADMIN OPTION FOR ]role_name [, ...] FROM role_name [, ...][ CASCADE | RESTRICT ]这是对普通用户权限的修改。
2、对于超级用户，权限修改在这里有没有用了。
对于超级用户来说，所有的操作只有如下的判断就可以操作了：
bool
superuser(void)
{return superuser_arg(GetUserId());
}Oid
GetUserId(void)
{AssertState(OidIsValid(CurrentUserId));return CurrentUserId;
}所以你可能就需要修改超级用户的权限了，那么如何修改呢？必须通过编写代码进行对超级用户进行限制。这就用到了PostgreSQL的hook机制。
hook可以修改和中断用户的操作。
下面是常用hook列表，大家可以根据列表进行对数据库相关过程进行修改，不需要直接在PG源码下修改，仅需要加一个扩展组件即可。
| 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，可以处理很多的过程。 |
下面也是一些hook，不过使用的较少：
| Hook | 使用 | 初始版本 | 说明 |
| explain_get_index_name_hook | | 8.3 | 在寻找索引name时调用的hook |
| ExplainOneQuery_hook | IndexAdvisor | 8.3 | |
| fmgr_hook | sepgsql | 9.1 | 函数调用潜的hook |
| get_attavgwidth_hook | | 8.4 | |
| get_index_stats_hook | | 8.4 | |
| get_relation_info_hook | plantuner | 8.3 | 得到数据库对象信息的时候调用的hook |
| get_relation_stats_hook | | 8.4 | |
| join_search_hook | saio | 8.3 | |
| needs_fmgr_hook | sepgsql | 9.1 | |
| object_access_hook | sepgsql | 9.1 | |
| planner_hook | planinstr | 8.3 | 在计划开始执行前调用的hook，可以修改一些查询计划的行为 |
| shmem_startup_hook | pg_stat_statements | 8.4 | 在初始化共享内存是调用的hook |
hook工作原理：每一个hook是由一个全局性的函数指针构成的。服务端进行运行初始化其为NULL，当数据库必须调用的时候，首先会检测是否为NULL，不是则优先调用函数，否则执行标准函数。
设置函数指针：当数据库载入共享库时，首先会将其载入到内存中，然后执行一个函数调用_PG_init。这个函数存在大多数共享库中是有效的。所以我们可以通过这个函数来加载我们自己的hook。
取消函数指针设置：当数据库需要卸载其共享库时，会调用函数 _PG_fini() 。我们可以再此进行设置函数指针为NULL，这样就取消设置了。
下面来一个实战型的：
目的：超级用户sure不能对任何表进行访问。
1、在contrib下建立目录：acl_super。
[root@localhost contrib]# mkdir acl_super;
[root@localhost contrib]#/** acl_super.c* the super user sure can not have not permission to operate the * ordinary table.*/
#include "postgres.h"
#include "miscadmin.h"
#include "nodes/parsenodes.h"
#include "nodes/pg_list.h"
#include "catalog/pg_class.h"
#include "executor/executor.h"PG_MODULE_MAGIC;void _PG_init(void);
void _PG_fini(void);static ExecutorCheckPerms_hook_type prev_ExecutorCheckPerms_hook = NULL;static void myExecCheckRTPerms(List *rangeTable, bool ereport_on_violation);/** Stop the super user sure from operating the ordinary table */static
void myExecCheckRTPerms(List *rangeTable, bool ereport_on_violation)
{ListCell *l;Oid cuser = InvalidOid;char* cusername = NULL;cuser = GetSessionUserId();cusername = GetUserNameFromId(cuser);if(strcmp("sure", cusername) == 0){foreach(l, rangeTable){RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);if(rte->relkind == RELKIND_RELATION) // ereport(ERROR,(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),errmsg("permission denied to table")));}}return true;
}/** _PG_init* Install the hook.*/
void
_PG_init(void)
{prev_ExecutorCheckPerms_hook = ExecutorCheckPerms_hook;ExecutorCheckPerms_hook = myExecCheckRTPerms;
}/** _PG_fini* Uninstall the hook.*/
void
_PG_fini(void)
{ExecutorCheckPerms_hook = prev_ExecutorCheckPerms_hook;
}3、建立Makefile：
# contrib/dbrestrict/MakefileMODULES = acl_super
OBJS = acl_super.soifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/acl_super
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif[root@localhost acl_super]# ls
acl_super.c Makefile
[root@localhost acl_super]#[root@localhost acl_super]# make
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -c -o acl_super.o acl_super.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -L../../src/port -L../../src/common -Wl,-rpath,'/opt/pgdbdevel/lib',--enable-new-dtags -shared -o acl_super.so acl_super.o
[root@localhost acl_super]# make install
/bin/mkdir -p '/opt/pgdbdevel/lib/postgresql'
/usr/bin/install -c -m 755 acl_super.so '/opt/pgdbdevel/lib/postgresql/'
[root@localhost acl_super]#将data目录下的postgresql.conf中的shared_preload_libraries进行修改：
原：
#shared_preload_libraries = '' # (change requires restart)shared_preload_libraries = 'acl_super' # (change requires restart)[postgres@localhost bin]$ ./pg_ctl -D ../data restart
waiting for server to shut down....LOG: received smart shutdown request
LOG: autovacuum launcher shutting down
LOG: shutting down
LOG: database system is shut downdone
server stopped
server starting
[postgres@localhost bin]$ LOG: database system was shut down at 2014-09-12 00:21:22 PDT
LOG: autovacuum launcher started
LOG: database system is ready to accept connections[postgres@localhost bin]$ ./psql
psql (9.5devel)
Type "help" for help.postgres=#postgres=# create table sure_test(s1 int);
insert into sure_test values (1),(2),(3);
select * from sure_test;s1
----123
(3 rows)c postgres sure
You are now connected to database "postgres" as user "sure".
postgres=# select * from sure_test;
ERROR: permission denied to table
STATEMENT: select * from sure_test;
ERROR: permission denied to table
postgres=#参考：
http://www.cnblogs.com/gaojian/p/3259147.html
http://michael.otacoo.com/postgresql-2/hooks-in-postgres-super-superuser-restrictions/
http://wiki.postgresql.org/images/e/e3/Hooks_in_postgresql.pdf
以后会继续对hook进行详细介绍的，欢迎大家来使用与讨论hook机制。