使用GRANT和REVOKE管理权限:

The GRANT command has two basic variants: one that grants privileges on

a database object (table,column,view,foreign table,sequence,

database,foreign-data wrapper,foreign server,function,procedural

language,schema,or tablespace),and one that grants membership in a

role. These variants are similar in many ways,but they are different

enough to be described separately.

这个是9.4.1最新的官方文档,pgsql的权限控制很精细,精确到子段.表，子段，试图，外表，序列，数据库，外键表的数据，外键服务器，函数，过程语言，模式，表空间

先创建测试数据:

create table member(uid serial primary key,username varchar(40),email varchar(100),password varchar(32));

insert into member(username,email,password) values('admin','admin@qq.com','e10adc3949ba59abbe56e057f20f883e'),('test','test@qq.com','e10adc3949ba59abbe56e057f20f883e');

testdb2=> select * from member;

uid | username | email | password

-----+----------+--------------+----------------------------------

1 | admin | admin@qq.com | e10adc3949ba59abbe56e057f20f883e

2 | test | test@qq.com | e10adc3949ba59abbe56e057f20f883e

(2 rows)

testdb2=> \d

List of relations

Schema | Name | Type | Owner

--------+----------------+----------+-------

public | member | table | sec

public | member_uid_seq | sequence | sec

数据库:testdb2

表与数据库所属用户为sec:

回收sec在member表的所有权限:

REVOKE ALL ON sec FROM member;

再执行update,query,delete会出现错误:

testdb2=> select * from member;

ERROR: permission denied for relation member

查询某个表的权限:使用\dp命令

testdb2=> \dp member;

Access privileges

Schema | Name | Type | Access privileges | Column access privileges

--------+--------+-------+-------------------+--------------------------

public | member | table | |

(1 row)

把回收的所有权限重新授权回去:

testdb2=> grant all on member to sec;

GRANT

testdb2=> \dp member;

Access privileges

Schema | Name | Type | Access privileges | Column access privileges

--------+--------+-------+-------------------+--------------------------

public | member | table | sec=arwdDxt/sec |

(1 row)

注:上面子段access privilages中arwdDxt的解释

r -- SELECT ("read")

w -- UPDATE ("write")

a -- INSERT ("append")

d -- DELETE

D -- TRUNCATE

x -- REFERENCES

t -- TRIGGER

X -- EXECUTE

U -- USAGE

C -- CREATE

c -- CONNECT

T -- TEMPORARY

回收某个指定查询权限(select,update,delete,truncate,insert):

revoke select on member from sec;

REVOKE upate,delete ON member FROM sec;

授权查询的权限:

GRANT select ON member to sec;

指定子段(password)权限的授权:(以查询为例)

1,首先你需要先回收用户sec对表member的select权限

REVOKE select ON member FROM sec;

2,授予用户sec,email与username的查询权限.

GRANT select(username,password) ON member TO sec;

如果先不做第一步,那么第二步是无效的,尽管对password这个子段作权限回收也是无效的.