关于Public角色

先思考一个问题，当我们创建一个用户的时候这个用户到底具有什么样的默认权限。

postgres=# create user user5 password 'redhat';
CREATE ROLE
postgres=# \c test   user5
You are now connected to database "test" as user "user5".  <---具有连接数据库的权限
test=> \d public.*     <---对public schema有USAGE的权限              
               Table "public.pbc_1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 x      | integer |           |          | 
 
test=> create table public.user5_t (x int);  <---也具有public模式下建表的权限
CREATE TABLE

上述列出的只是默认权限的一部分，那么这些默认权限是怎么来的？是否可以通过一些手段去控制默认权限？

在这里就需要提到PostgreSQL的一个Public角色，在PostgreSQL中有一个public角色，任何新建的用户都会被首先赋予这个角色，需要注意的是，这个角色在pg_roles里面是看不到public角色的。Public的默认权限有:

  • 对数据库。connect,temp/temprary权限
  • 对public模式。具有USAGE、CREATE的权限，还具有执行程序的权限

当在PostgreSQL创建一个具有login属性用户的时候，就会自动具有上述的权限，所以从安全性来说，需要对public角色做一些权限的回收。

关于Set role

set role的功能就是在不改变session role的情况下设置当前用户的用户标识为指定的role，这样就需要了指定role的权限。比如存在如下role关系

聊聊PostgreSQL中的public角色以及Set role的作用_用户标识

有一个role1，包含了role1_1,role1_2两个角色，其中：set role role1_1 具有select vic1_s.t1的权限；通过set role role1_2 具有select vic1_s.t2的权限；

当我们把role1这个角色授权给一个用户的时候，这个用户就可以通过set role role1_1具有查看vic1_s.t1的权限，通过set role role1_2就具有查看vic1_s.t2的权限，当然可以可以通过继承属性具有全部权限。

  • 创建需要的角色
#创建角色
test=# create role role1_1; 
CREATE ROLE
test=# create role role1_2;   
CREATE ROLE
# 分配角色权限
test=# grant usage on schema vic1_s to role1_1;
GRANT
test=# grant usage on schema vic1_s to role1_2;
GRANT
test=> grant select on table vic1_s.t1 to role1_1;
GRANT
test=> grant select on table vic1_s.t2 to role1_2; 
GRANT

test=# grant role1_1 to role1;
GRANT ROLE
test=# grant role1_2 to role1; 
GRANT ROLE
test=# 
test=# \du
                                       List of roles
 Role name |                         Attributes                         |     Member of     
-----------+------------------------------------------------------------+-------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {role1_1,role1_2}
 
 # 确认权限
 test=> select * from information_schema.table_privileges where grantee='role1_1';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 vic1    | role1_1 | test          | vic1_s       | t1         | SELECT         | NO           | YES
(1 row)

test=> select * from information_schema.table_privileges where grantee='role1_2';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 vic1    | role1_2 | test          | vic1_s       | t2         | SELECT         | NO           | YES
(1 row)
  • 创建用户测试
test=# create user user1 NOINHERIT password 'redhat';
CREATE ROLE
test=# grant role1 to user1;
GRANT ROLE

# 切换role1_1
test=> set role role1_1;
SET
test=> select current_user,session_user;
 current_user | session_user 
--------------+--------------
 role1_1      | user1
test=> select * from vic1_s.t1;
 x 
---
 1
(1 row)

test=> select * from vic1_s.t2;
ERROR:  permission denied for table t2
test=> 

# 切换role1_2
test=>  set role role1_2;
SET
test=> select current_user,session_user;
 current_user | session_user 
--------------+--------------
 role1_2      | user1
 
test=> select * from vic1_s.t2;
 x 
---
 2
(1 row)

test=> select * from vic1_s.t1;
ERROR:  permission denied for table t1

从上诉试验中可以发现，在使用set role做切换用户的时候不会导致session_user的变化，但是会改变current_user这个用户标识，这样在执行sql做权限校验的时候就会使用current_user的这个用户，继而具有不同的权限。