PostgreSQL访问控制

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

转载于:https://my.oschina.net/websec/blog/386146