Postgresql 数据库权限功能的使用总结

postgresql数据库支持灵活的权限管理，可以控制一个角色（组、用户）对某张表的读、写、更新、删除等操作权限、执行某个函数的权限以及操作（使用、更新等）视图、序列的权限。

pg的权限管理功能比较强大，可以细化到对一张表的各个字段，比如禁止用户访问一张表里的密码字段等，在稍后的内容中给出详细的解释。

虽然在pg数据库中把用户、角色统一叫做角色，甚至创建语句都为create role xxx,但用户和角色之间仍有一定的区别。在这里我们统一把拥有登录权限的叫做用户，没有登录权限的叫做角色，用此方式加以区分。

实际上，在pgadmin管理工具中，可以看到用户和角色的区别，没有登录权限的被放在组角色下，有登录权限的被放在登录角色下。

基本权限

用户和角色都可以被赋予基本权限，比如创建数据库权限、超级用户权限、创建角色权限等。

比如创建用户的语句为：

create role guest login
 nosuperuser inherit nocreatedb nocreaterole noreplication;

注意上述role guest拥有login的权限，所以叫它用户。

创建角色的语句为：

create role "group"
 nosuperuser inherit nocreatedb nocreaterole noreplication;

注意这里没有login权限，所以是角色。

上述角色和用户的创建语句中，都没有赋予超级用户、创建数据库等权限。

操作数据库对象权限

只能把数据库对象的操作权限赋予没有登录权限的角色，而不能直接赋予拥有登录权限的用户。

那么这样就带来一个问题，怎么样控制登录用户操作数据库对象的权限呢？

答案是让用户成为角色的成员，此时用户即可拥有角色的权限，进一步限制了登录用户操作数据库对象的权限。

如把上述角色group赋予guest用户：

grant "group" to guest;

之后，guest用户就拥有了group角色所拥有的数据库对象权限。

比如控制group角色只能对class表执行insert操作：

grant insert on table class to "group";

此时使用guest用户登录数据后，就只能对表class执行insert操作，无法执行delete、update等操作。

示例代码如下，使用guest用户登录，访问test数据库下的class表。

server [localhost]:
database [postgres]:
port [5433]:
username [postgres]: guest
用户 guest 的口令：
psql (9.4.5)
输入 "help" 来获取帮助信息.
postgres=> \c test
您现在已经连线到数据库 "test",用户 "guest".
test=> select * from class;
error: permission denied for relation class
test=> insert into class values(2,'class1');
insert 0 1

从上述结果中可以看到，guest用户没有权限查询class表，但是可以插入数据库。原因就是只对group角色赋予了class表的insert权限，然后guest用户也就只有class表的insert权限。

前面说到pg的权限管理可以细化到表的某个字段，现在继续用class表和guest用户做实验。

test=> \c postgres postgres;
您现在已经连线到数据库 "postgres",用户 "postgres".
postgres=# \c test;
您现在已经连线到数据库 "test",用户 "postgres".
test=# grant select(num) on class to "group";
grant
test=# \echo 切换到postgres用户连接test数据库，对class表的num字段的select权限赋予group角色
切换到postgres用户连接test数据库，对class表的num字段的select权限赋予group角色
test=# \c test guest
用户 guest 的口令：
您现在已经连线到数据库 "test",用户 "guest".
test=> \echo 切换回guest用户登录test数据库
切换回guest用户登录test数据库
test=> select * from class;
error: permission denied for relation class
test=> select num from class;
 num
-----
  1
  2
(2 行记录)

从上述结果中可以看到，guest用户依然没有查询class表的权限，但是却有了查询class表里的num字段的权限。

在pg数据库中不单单可以控制操作表的权限，其他数据库对象，比如序列、函数、视图等都可以控制。

所以pg的权限控制功能非常强大。

补充：postgres用户对数据库的权限

用户对数据库的权限（登录、超级用户权限）

（1）查看当前数据库中有用户highgo和用户a

highgo=#\du
               list of roles
 role name |          attributes          | member of
-----------+------------------------------------------------+-----------
 a     |                        | {}
 highgo  | superuser, create role, create db, replication | {}

（2）查看确认当前连接的用户为超级用户highgo，且该用户后创建角色和数据库的权限等

highgo=#select current_user;
 current_user
--------------
 highgo
(1row)

（3）查看当前集群中的数据库

highgo=#\l
               list of databases
  name  | owner | encoding | collate |  ctype  | access privileges
-----------+--------+----------+------------+------------+-------------------
 highgo  | highgo | utf8   | zh_cn.utf8 |zh_cn.utf8 |
 template0 | highgo | utf8   | zh_cn.utf8 | zh_cn.utf8 | =c/highgo    +
      |    |     |      |      | highgo=ctc/highgo
 template1 | highgo | utf8   | zh_cn.utf8 | zh_cn.utf8 | =c/highgo    +
      |    |     |      |      | highgo=ctc/highgo
(3rows)

（4）使用普通用户a连接数据库正常

highgo=#\c highgo a
youare now connected to database "highgo" as user "a".
highgo=>select current_user;
 current_user
--------------
 a
(1row)
（5）使用超级用户highgo连接数据库正常
highgo=>\c highgo highgo
youare now connected to database "highgo" as user "highgo".
highgo=#select current_user;
 current_user
--------------
 highgo
(1row)

（6）在超级用户连接highgo后，设置不允许普通用户a连接数据库

highgo=#alter role a nologin;
alter role
highgo=#\c highgo a
致命错误: 不允许角色"a" 进行登录
previousconnection kept
highgo=#

（7）在超级用户连接highgo后，设置不允许普通用户a连接数据库后，赋予用户a超级用户权限后仍然无法连接数据库

highgo=#alter role a superuser;
alterrole
highgo=#\du
               list of roles
 role name |          attributes          | member of
-----------+------------------------------------------------+-----------
 a    | superuser, cannot login            | {}
 highgo  | superuser, create role, create db, replication | {}
 
highgo=#\c highgo a
致命错误: 不允许角色"a" 进行登录
previousconnection kept

（8）将登录数据库的权限赋予用户a后，用户a可登录数据库

highgo=#alter role a login;
alterrole
highgo=#\c highgo a
youare now connected to database "highgo" as user "a".
highgo=#select current_user;
 current_user
--------------
 a
(1row)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。