要想修改PG的用户权限，那么首先要对PG权限控制做一下了解：
PG的权限控制是针对到各个对象的。大家可以看一下，所有系统表（pg_catalog下）几乎都会有aclitem[]数组类型的**acl的字段，这就是对权限的标识。
这里的标识情况如下：
1rolename=xxxx -- privileges granted to a role 2 =xxxx -- privileges granted to PUBLIC 3 4 r -- SELECT ("read") 5 w -- UPDATE ("write") 6 a -- INSERT ("append") 7 d -- DELETE 8 D -- TRUNCATE 9 x -- REFERENCES 10 t -- TRIGGER 11 X -- EXECUTE 12 U -- USAGE 13 C -- CREATE 14 c -- CONNECT 15 T -- TEMPORARY 16 arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects) 17 * -- grant option for preceding privilege 18 19 /yyyy -- role that granted this privilege
这里有一个非常重要的结构体：
1typedef struct AclItem 2{ 3 Oid ai_grantee; /* ID that this item grants privs to */ 4 Oid ai_grantor; /* grantor of privs */ 5 AclMode ai_privs; /* privilege bits */ 6} AclItem; 7 8typedef uint32 AclMode;
然后注释对AclMode的解释是这样的：
1/* 2 * The upper 16 bits of the ai_privs field of an AclItem are the grant option 3 * bits, and the lower 16 bits are the actual privileges. We use "rights" 4 * to mean the combined grant option and privilege bits fields. 5 */
高16位存储的是grant option，而低16位存储的是各个权限位的授予情况，有没有对应的权限。
对低16位的解释：
低16位很简单的可以明白就是在这16bit上表示权限。通过下面的宏定义可以了解一下：
1#define ACL_INSERT (1<<0) /* for relations */ 2#define ACL_SELECT (1<<1) 3#define ACL_UPDATE (1<<2) 4#define ACL_DELETE (1<<3) 5#define ACL_TRUNCATE (1<<4) 6#define ACL_REFERENCES (1<<5) 7#define ACL_TRIGGER (1<<6) 8#define ACL_EXECUTE (1<<7) /* for functions */ 9#define ACL_USAGE (1<<8) /* for languages, namespaces, FDWs, and 10 * servers */ 11#define ACL_CREATE (1<<9) /* for namespaces and databases */ 12#define ACL_CREATE_TEMP (1<<10) /* for databases */ 13#define ACL_CONNECT (1<<11) /* for databases */ 14#define N_ACL_RIGHTS 12 /* 1 plus the last 1<<x */ 15#define ACL_NO_RIGHTS 0 16/* Currently, SELECT ... FOR [KEY] UPDATE/SHARE requires UPDATE privileges */ 17#define ACL_SELECT_FOR_UPDATE ACL_UPDATE
低16位上便是上面对应的权限有无了。对应权限，大家看一下名字就能明白了。
高16位存储的是各权限对应的授出或者被转授选项。其实就是上一次的受权情况，对高16位的操作我只在grant和revoke发现，还有就是对权限进行读入，对*还有处理。这两处的用法就是将上一次的受权低16位存储到高16位。
1#define ACL_INSERT_CHR 'a' /* formerly known as "append" */ 2#define ACL_SELECT_CHR 'r' /* formerly known as "read" */ 3#define ACL_UPDATE_CHR 'w' /* formerly known as "write" */ 4#define ACL_DELETE_CHR 'd' 5#define ACL_TRUNCATE_CHR 'D' /* super-delete, as it were */ 6#define ACL_REFERENCES_CHR 'x' 7#define ACL_TRIGGER_CHR 't' 8#define ACL_EXECUTE_CHR 'X' 9#define ACL_USAGE_CHR 'U' 10#define ACL_CREATE_CHR 'C' 11#define ACL_CREATE_TEMP_CHR 'T' 12#define ACL_CONNECT_CHR 'c'
而以宏定义定义的是相应权限所对应的字符。这就是我们经常能看到的**acl字段所存储的信息了。
现在通过一个给数据库赋权的例子来解释一下**acl字段存储的信息：
1create user ff createdb; 2\c test ff 3create database tain; 4select * from pg_database where datname = 'tain'; 5 datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | da 6tfrozenxid | datminmxid | dattablespace | datacl 7-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--- 8-----------+------------+---------------+-------------------------------------------------------- 9 tain | 16438 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12917 | 10 1674 | 1 | 1663 | 11(5 rows) 12 13grant all on database tain to ss; select * from pg_database where datname = 'tain'; datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | da 14tfrozenxid | datminmxid | dattablespace | datacl 15-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--- 16-----------+------------+---------------+-------------------------------------------------------- 17 tain | 16438 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12917 | 18 1674 | 1 | 1663 | {=Tc/ff,ff=CTc/ff,ss=CTc/ff} 19(5 rows) 20 21\c postgres postgres 22alter database tain owner to postgres; 23select * from pg_database where datname = 'tain'; 24 datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | da 25tfrozenxid | datminmxid | dattablespace | datacl 26-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--- 27-----------+------------+---------------+-------------------------------------------------------- 28 tain | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12917 | 29 1674 | 1 | 1663 | {=Tc/postgres,postgres=CTc/postgres,ss=CTc/postgres} 30(5 rows)
上面有这么三种情况:
1、创建数据库后，默认为空。
2、为用户赋权后，将默认和赋权的情况都写在了datacl字段内。
=前的为受权的用户，/后的为数据库所属的用户，/前的为受权用户对其拥有的权限。
3、改变数据库属主后的权限情况。
说完权限情况，下面介绍一下用户情况，
PG的用户可以分为两类：1超级用户，2普通用户。
为什么这会分出这两类呢？
1、普通用户的权限控制可以直接用命令直接进行修改权限：
1postgres=# \help grant 2Command: GRANT 3Description: define access privileges 4Syntax: 5GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } 6 [, ...] | ALL [ PRIVILEGES ] } 7 ON { [ TABLE ] table_name [, ...] 8 | ALL TABLES IN SCHEMA schema_name [, ...] } 9 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 10 11GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) 12 [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) } 13 ON [ TABLE ] table_name [, ...] 14 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 15 16GRANT { { USAGE | SELECT | UPDATE } 17 [, ...] | ALL [ PRIVILEGES ] } 18 ON { SEQUENCE sequence_name [, ...] 19 | ALL SEQUENCES IN SCHEMA schema_name [, ...] } 20 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 21 22GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } 23 ON DATABASE database_name [, ...] 24 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 25 26GRANT { USAGE | ALL [ PRIVILEGES ] } 27 ON DOMAIN domain_name [, ...] 28 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 29 30GRANT { USAGE | ALL [ PRIVILEGES ] } 31 ON FOREIGN DATA WRAPPER fdw_name [, ...] 32 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 33 34GRANT { USAGE | ALL [ PRIVILEGES ] } 35 ON FOREIGN SERVER server_name [, ...] 36 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 37 38GRANT { EXECUTE | ALL [ PRIVILEGES ] } 39 ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] 40 | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } 41 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 42 43GRANT { USAGE | ALL [ PRIVILEGES ] } 44 ON LANGUAGE lang_name [, ...] 45 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 46 47GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } 48 ON LARGE OBJECT loid [, ...] 49 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 50 51GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } 52 ON SCHEMA schema_name [, ...] 53 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 54 55GRANT { CREATE | ALL [ PRIVILEGES ] } 56 ON TABLESPACE tablespace_name [, ...] 57 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 58 59GRANT { USAGE | ALL [ PRIVILEGES ] } 60 ON TYPE type_name [, ...] 61 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] 62 63GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ] 64 65\help revoke 66Command: REVOKE 67Description: remove access privileges 68Syntax: 69REVOKE [ GRANT OPTION FOR ] 70 { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } 71 [, ...] | ALL [ PRIVILEGES ] } 72 ON { [ TABLE ] table_name [, ...] 73 | ALL TABLES IN SCHEMA schema_name [, ...] } 74 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 75 [ CASCADE | RESTRICT ] 76 77REVOKE [ GRANT OPTION FOR ] 78 { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) 79 [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) } 80 ON [ TABLE ] table_name [, ...] 81 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 82 [ CASCADE | RESTRICT ] 83 84REVOKE [ GRANT OPTION FOR ] 85 { { USAGE | SELECT | UPDATE } 86 [, ...] | ALL [ PRIVILEGES ] } 87 ON { SEQUENCE sequence_name [, ...] 88 | ALL SEQUENCES IN SCHEMA schema_name [, ...] } 89 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 90 [ CASCADE | RESTRICT ] 91 92REVOKE [ GRANT OPTION FOR ] 93 { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } 94 ON DATABASE database_name [, ...] 95 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 96 [ CASCADE | RESTRICT ] 97 98REVOKE [ GRANT OPTION FOR ] 99 { USAGE | ALL [ PRIVILEGES ] } 100 ON DOMAIN domain_name [, ...] 101 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 102 [ CASCADE | RESTRICT ] 103 104REVOKE [ GRANT OPTION FOR ] 105 { USAGE | ALL [ PRIVILEGES ] } 106 ON FOREIGN DATA WRAPPER fdw_name [, ...] 107 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 108 [ CASCADE | RESTRICT ] 109 110REVOKE [ GRANT OPTION FOR ] 111 { USAGE | ALL [ PRIVILEGES ] } 112 ON FOREIGN SERVER server_name [, ...] 113 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 114 [ CASCADE | RESTRICT ] 115 116REVOKE [ GRANT OPTION FOR ] 117 { EXECUTE | ALL [ PRIVILEGES ] } 118 ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] 119 | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } 120 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 121 [ CASCADE | RESTRICT ] 122 123REVOKE [ GRANT OPTION FOR ] 124 { USAGE | ALL [ PRIVILEGES ] } 125 ON LANGUAGE lang_name [, ...] 126 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 127 [ CASCADE | RESTRICT ] 128 129REVOKE [ GRANT OPTION FOR ] 130 { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } 131 ON LARGE OBJECT loid [, ...] 132 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 133 [ CASCADE | RESTRICT ] 134 135REVOKE [ GRANT OPTION FOR ] 136 { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } 137 ON SCHEMA schema_name [, ...] 138 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 139 [ CASCADE | RESTRICT ] 140 141REVOKE [ GRANT OPTION FOR ] 142 { CREATE | ALL [ PRIVILEGES ] } 143 ON TABLESPACE tablespace_name [, ...] 144 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 145 [ CASCADE | RESTRICT ] 146 147REVOKE [ GRANT OPTION FOR ] 148 { USAGE | ALL [ PRIVILEGES ] } 149 ON TYPE type_name [, ...] 150 FROM { [ GROUP ] role_name | PUBLIC } [, ...] 151 [ CASCADE | RESTRICT ] 152 153REVOKE [ ADMIN OPTION FOR ] 154 role_name [, ...] FROM role_name [, ...] 155 [ CASCADE | RESTRICT ]
这是对普通用户权限的修改。
2、对于超级用户，权限修改在这里有没有用了。
对于超级用户来说，所有的操作只有如下的判断就可以操作了：
1bool 2superuser(void) 3{ 4 return superuser_arg(GetUserId()); 5} 6 7Oid 8GetUserId(void) 9{ 10 AssertState(OidIsValid(CurrentUserId)); 11 return CurrentUserId; 12}
只要判断是超级用户即可进行操作。超级用户的权限很大，在这里提醒各位超级用户慎用。而且在PG里默认用户postgres是就是超级用户，而且不能删除。
所以你可能就需要修改超级用户的权限了，那么如何修改呢？必须通过编写代码进行对超级用户进行限制。这就用到了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。
1[root@localhost contrib]# mkdir acl_super; 2[root@localhost contrib]#
2、建立C文件：
1/* 2 * acl_super.c 3 * the super user sure can not have not permission to operate the 4 * ordinary table. 5 */ 6#include "postgres.h" 7#include "miscadmin.h" 8#include "nodes/parsenodes.h" 9#include "nodes/pg_list.h" 10#include "catalog/pg_class.h" 11#include "executor/executor.h" 12 13PG_MODULE_MAGIC; 14 15void _PG_init(void); 16void _PG_fini(void); 17 18static ExecutorCheckPerms_hook_type prev_ExecutorCheckPerms_hook = NULL; 19 20static void myExecCheckRTPerms(List *rangeTable, bool ereport_on_violation); 21 22/* 23 * Stop the super user sure from operating the ordinary table */ 24 25static 26void myExecCheckRTPerms(List *rangeTable, bool ereport_on_violation) 27{ 28 ListCell *l; 29 Oid cuser = InvalidOid; 30 char* cusername = NULL; 31 32 cuser = GetSessionUserId(); 33 cusername = GetUserNameFromId(cuser); 34 35 if(strcmp("sure", cusername) == 0) 36 { 37 foreach(l, rangeTable) 38 { 39 RangeTblEntry *rte = (RangeTblEntry *) lfirst(l); 40 if(rte->relkind == RELKIND_RELATION) // ereport(ERROR, 41 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), 42 errmsg("permission denied to table"))); 43 } 44 } 45 46 return true; 47} 48 49/* 50 * _PG_init 51 * Install the hook. 52 */ 53void 54_PG_init(void) 55{ 56 prev_ExecutorCheckPerms_hook = ExecutorCheckPerms_hook; 57 ExecutorCheckPerms_hook = myExecCheckRTPerms; 58} 59 60/* 61 * _PG_fini 62 * Uninstall the hook. 63 */ 64void 65_PG_fini(void) 66{ 67 ExecutorCheckPerms_hook = prev_ExecutorCheckPerms_hook; 68}
3、建立Makefile：
1# contrib/dbrestrict/Makefile 2 3MODULES = acl_super 4OBJS = acl_super.so 5 6ifdef USE_PGXS 7PG_CONFIG = pg_config 8PGXS := $(shell $(PG_CONFIG) --pgxs) 9include $(PGXS) 10else 11subdir = contrib/acl_super 12top_builddir = ../.. 13include $(top_builddir)/src/Makefile.global 14include $(top_srcdir)/contrib/contrib-global.mk 15endif
现在目录下的文件为：
1[root@localhost acl_super]# ls 2acl_super.c Makefile 3[root@localhost acl_super]#
4、编译与安装
1[root@localhost acl_super]# make 2gcc -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 3gcc -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 4[root@localhost acl_super]# make install 5/bin/mkdir -p '/opt/pgdbdevel/lib/postgresql' 6/usr/bin/install -c -m 755 acl_super.so '/opt/pgdbdevel/lib/postgresql/' 7[root@localhost acl_super]#
5、配置文件：
将data目录下的postgresql.conf中的shared_preload_libraries进行修改：
原：
#shared_preload_libraries = '' # (change requires restart)
修改为：
shared_preload_libraries = 'acl_super' # (change requires restart)
6、重启数据库服务
1[postgres@localhost bin]$ ./pg_ctl -D ../data restart 2waiting for server to shut down....LOG: received smart shutdown request 3LOG: autovacuum launcher shutting down 4LOG: shutting down 5LOG: database system is shut down 6 done 7server stopped 8server starting 9[postgres@localhost bin]$ LOG: database system was shut down at 2014-09-12 00:21:22 PDT 10LOG: autovacuum launcher started 11LOG: database system is ready to accept connections 12 13[postgres@localhost bin]$ ./psql 14psql (9.5devel) 15Type "help" for help. 16 17postgres=#
7、实验结果：
1postgres=# create table sure_test(s1 int); 2insert into sure_test values (1),(2),(3); 3select * from sure_test; 4 s1 5---- 6 1 7 2 8 3 9(3 rows) 10 11\c postgres sure 12You are now connected to database "postgres" as user "sure". 13postgres=# select * from sure_test; 14ERROR: permission denied to table 15STATEMENT: select * from sure_test; 16ERROR: permission denied to table 17postgres=#
以上就是对超级用户进行权限限制，当然这里的处理是简单暴力的，而更深层次的权限修改，那就需要花费比较大的时间进行改动，甚至会修改源代码，这里就暂不涉及。
参考：
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机制。