ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(102) - pgAdmin(Row Level Security)

PostgreSQL DBA(102) - pgAdmin(Row Level Security)

原创 PostgreSQL 作者:husthxd 时间:2019-10-09 14:35:21 0 删除 编辑

PostgreSQL提供了Row Level Security(RLS)来控制哪些行可以访问或修改。

一、简介
其标准的语法是:

[pg12@localhost ~]$ psql -d testdb
Timing is on.
Expanded display is used automatically.
psql (12.0)
Type "help" for help.
[local]:5432 pg12@testdb=# \help create policy
Command:     CREATE POLICY
Description: define a new row level security policy for a table
Syntax:
CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
URL: https://www.postgresql.org/docs/12/sql-createpolicy.html
[local]:5432 pg12@testdb=#

其中,USING表示满足using_expression表达式为T的行才能看到或操作,否则这些行将被隐藏(hidden);
WITH CHECK表示在执行INSERT/UPDATE操作时,如新数据不能通过check_expression表达式的校验则被视为非法数据。

二、注意事项
1.RLS必须通过ALTER TABLE … ENABLE ROW LEVEL SECURITY显式启用;
2.如启用RLS,那么至少存在一个policy
创建数据表,插入数据,启用RLS

[local]:5432 pg12@testdb=# drop table if exists t_rls1;
NOTICE:  table "t_rls1" does not exist, skipping
DROP TABLE
Time: 37.363 ms
[local]:5432 pg12@testdb=# create table t_rls1(id int,c1 int);
CREATE TABLE
Time: 137.931 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_rls1 values(1,1);
INSERT 0 1
Time: 1.820 ms
[local]:5432 pg12@testdb=# insert into t_rls1 values(2,2);
INSERT 0 1
Time: 0.582 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# ALTER TABLE t_rls1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE
Time: 1.714 ms
[local]:5432 pg12@testdb=# select * from t_rls1;
 id | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

创建新的user,查询数据表,因为不存在policy,因此没有数据返回(但为什么pg12这个用户可以?后续有解释)

Time: 23.349 ms
[local]:5432 pg12@testdb=# create user testuser with passwod 'test';
ERROR:  unrecognized role option "passwod"
LINE 1: create user testuser with passwod 'test';
                                  ^
Time: 1.165 ms
[local]:5432 pg12@testdb=# create user testuser with password 'test';
CREATE ROLE
Time: 15.721 ms
[local]:5432 pg12@testdb=# grant all on t_rls1 to testuser;
GRANT
Time: 8.125 ms
[local]:5432 pg12@testdb=#

3.同一个命令,同一种类型,多个policys之间是OR的关系,即某行满足其中一个policy就可以返回(如select命令);同一个命令不同的命令类型,则多个policys之间的关系是AND,必须所有policys都满足才能返回(如update中存在where子句时)。
4.只有表的owner可以创建policys
5.超级用户或者具有BYPASSRLS权限的用户会跳过RLS检查。这解释先前为什么policy没有,但pg12查询有数据返回但testuser用户没有。

[local]:5432 pg12@testdb=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 pg12      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  |

6.数据表的owner用户默认会跳过RLS检查

[local]:5432 testuser@testdb=> create table t_rls2(id int,c1 int);
CREATE TABLE
Time: 10.256 ms
[local]:5432 testuser@testdb=> 
[local]:5432 testuser@testdb=> insert into t_rls2 values(1,1);
INSERT 0 1
Time: 3.422 ms
[local]:5432 testuser@testdb=> insert into t_rls2 values(2,2);
INSERT 0 1
Time: 2.580 ms
[local]:5432 testuser@testdb=> 
[local]:5432 testuser@testdb=> ALTER TABLE t_rls2 ENABLE ROW LEVEL SECURITY;
ALTER TABLE
Time: 2.124 ms
[local]:5432 testuser@testdb=> select * from t_rls2;
 id | c1 
----+----
  1 |  1
  2 |  2
(2 rows)
Time: 1.127 ms
[local]:5432 testuser@testdb=> \d t_rls2
               Table "public.t_rls2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 c1     | integer |           |          | 
Policies (row security enabled): (none)

testuser1&testuser2均为普通用户,但testuser1查询数据有返回,但testuser2没有

[pg12@localhost ~]$ psql -d testdb -U testuser2
Timing is on.
Expanded display is used automatically.
psql (12.0)
Type "help" for help.
[local]:5432 testuser2@testdb=> select * from t_rls2;
 id | c1 
----+----
(0 rows)
Time: 3.808 ms
[local]:5432 testuser2@testdb=>

可通过ALTER TABLE … FORCE ROW LEVEL SECURITY命令强制启用

[local]:5432 testuser@testdb=> ALTER TABLE t_rls2 FORCE ROW LEVEL SECURITY;
ALTER TABLE
Time: 1.967 ms
[local]:5432 testuser@testdb=> select * from t_rls2;
 id | c1 
----+----
(0 rows)
Time: 2.369 ms

具体的应用案例可详见参考资料中的链接。

三、参考资料
PG Conf of US 2019 - Row Level Security
Using “Row Level Security” to make large companies more secure

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2659195/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1393
  • 访问量
    3839906