PostgreSQL角色（二）

最新推荐文章于 2022-05-05 15:15:00 发布

weixin_33894992

最新推荐文章于 2022-05-05 15:15:00 发布

阅读量238

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/1171200/blog/200784

版权

2019独角兽企业重金招聘Python工程师标准>>>

role角色

1.define定义

在PostgreSQL中可以由超级用户或者拥有createrole权限的角色使用用create role命令定义一个数据库层次（所有数据库均可用）的新角色。具体语法就不另作说明了，在此仅就语法中几个参数加以说明：

•INHERIT：默认定义，使用这个参数定义的角色自动拥有其所属角色组拥有的数据库权限
•IN ROLE：将新角色定义为所列出角色的成员
•ROLE：定义新角色为角色组，而所列角色成为该角色组的成员
•ADMIN：作用与role相同，区别在于所列角色拥有新角色的with admin option权限，即可以将新角色的权限赋予给其他用户
//实验三，验证上述四个参数

lihao=# create role role1 nosuperuser nocreatedb nocreaterole noreplication noinherit password 'oracle';

CREATE ROLE

lihao=# create user user1 in role role1 password 'oracle';

CREATE ROLE

lihao=# create role role2 nosuperuser nocreatedb nocreaterole noreplication noinherit login password 'oracle';
CREATE ROLE

lihao=# create role role3 role role2 login;
CREATE ROLE
lihao=# create role role4 nosuperuser nocreatedb nocreaterole noreplication noinherit loginpassword 'oracle';
CREATE ROLE
lihao=# create role role5 admin role4 login;
CREATE ROLE
lihao=# \du+
                                    List of roles
Role name |                   Attributes                                         | Member of | Description
---------+--------------------------------------------------------+-------------+---------
lihao     | Superuser, Create role, Create DB, Replication         | {}                 |
role1     | No inheritance                                                        | {}                   |
role2     | No inheritance                                                         | {role3}           |
role3     |                                                                                  | {}                   |
role4     | No inheritance                                                          | {role5}         |
role5     |                                                                                   | {}                 |
user1     |                                                                                   | {role1}          |
lihao=# \c lihao role4
You are now connected to database "lihao" as user "role4".
lihao=# grant role5 to user1;
GRANT ROLE
lihao=# \c lihao role2
You are now connected to database "lihao" as user "role2".
lihao=# grant role3 to user1;
ERROR: must have admin option on role "role3"
lihao=# \du+
                                            List of roles
Role name |                   Attributes                                         | Member of | Description
---------+--------------------------------------------------------+-------------+---------
lihao     | Superuser, Create role, Create DB, Replication         | {}                 |
role1     | No inheritance                                                        | {}                   |
role2     | No inheritance                                                         | {role3}           |
role3     |                                                                                  | {}                   |
role4     | No inheritance                                                          | {role5}         |
role5     |                                                                                   | {}                 |
user1     |                                                                                   | {role1,role5} |

2.set role

将当前会话的当前角色更改为所指定角色，修改成功后，当前会话的命令权限使用的是新角色权限。需要注意的是：指定角色必须是超级用户或者是当前会话角色所在的角色组。

lihao=# create role role1 login password 'oracle';
CREATE ROLE
lihao=# create user user1 in role role1 noinherit password 'oracle';
CREATE ROLE
lihao=# grant select on table t1 to role1;
GRANT
lihao=# grant select on table t2 to user1;
GRANT
lihao=# \du+
                                    List of roles
Role name |                   Attributes                                          | Member of | Description
-------------+---------------------------------------------------+-------------+-------------
lihao       | Superuser, Create role, Create DB, Replication | {}                 |
role1           |                                                                           | {}                |
user1           | No inheritance                                                  | {role1}          |
lihao=# \dp
                                       Access privileges
schema |       Name        |   Type   | Access privileges | Column access privileges

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

lihao=> select * from t1;
ERROR: permission denied for relation t1
lihao=> set role role1;
SET
lihao=> select * from t1;
id
----
(0 rows)
lihao=> select * from t2;
ERROR: permission denied for relation t2

[lihao@lihao ~]$ psql -U role1 -d lihao
psql (9.1.8)
Type "help" for help.

lihao=> set role user1;
ERROR: permission denied to set role "user1"

3.set session authorization

这条命令类似于SET ROLE，区别在于：SET ROLE是由组成员角色变成组角色，而SET SESSION AUTHORIZATION却是反过来的；而且SET ROLE只能更改current_user，而后者可以更改current_user和session_user。一般来说，session_user = current_user，在类Unix系统中，session_user是“真正的用户”，而current_user是“起作用的用户”。

[lihao@lihao ~]$ psql -U role1 -d lihao
psql (9.1.8)
Type "help" for help.

lihao=# select session_user,current_user;
session_user | current_user
--------------+--------------
role1          | role1
(1 row)
lihao=# set session authorization user1;
SET
lihao=> select session_user,current_user;
session_user | current_user
--------------+--------------
user1          | user1
(1 row)
lihao=> select * from t2;
id
----
(0 rows)
lihao=> set role role1;
SET
lihao=# select session_user,current_user;
session_user | current_user
--------------+--------------
user1         | role1(1 row)
lihao=# select * from t1;
id
----
(0 rows)
lihao=# select * from t2;
id
----
(0 rows)

[lihao@lihao ~]$ psql -U user1 -d lihao
psql (9.1.8)
Type "help" for help.

lihao=> set session authorization role1;
ERROR: permission denied to set session authorization

4.结论

SET ROLE命令成功后，还可以继续使用原用户权限（即set role role1后可select * from t2;)，而SET SESSION AUTHORIZATION 就不能，可以得出结论：session_user和current_user不同时，可以使用二者的权限；相同时，只能调用current_user的权限。

个人认为这两条命令很危险，理由如下：