使用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这个子段作权限回收也是无效的.