作者
digoal
日期
2022-03-23
标签
PostgreSQL , view , base relation , 权限检测 , invoker , owner , user , definer
数据库 function 有定义者、调用者.
定义者: 即function owner
调用者: 指谁在执行这个function. (当然定义者也可能去执行这个function.)
函数有个安全策略的定义, 即执行函数时, 有没有权限执行函数体内的code? 那是以定义者还是调用者去判断呢? 就在这设置:
https://www.postgresql.org/docs/current/sql-createfunction.html
create function ....
....
SECURITY INVOKER | SECURITY DEFINER
- invoker 调用者
- definer 定义者
那么接下来看一下视图, 实际上视图也有权限穿透的情况, 例如给角色赋予查询视图的权限, 这个角色就能查询视图, 而不管这个视图下面的表它有没有权限.
r1: select * from tbl; -- 无权
create view v as select * from tbl;
grant select on v to r1;
r1: select * from v; -- 有权
PostgreSQL 15 支持 security invoker views , 啥意思呢? 原来View的base relation权限都是看owner的, 不管调用view的角色是谁, 现在呢, security invoker views 看的是调用者有没有base relation的权限.
不仅仅是权限, 连RLS也看调用者了.
- view owner
- view user(invoker)
- table owner
- table user(invoker, 即view的invoker)
table RLS 策略针对谁?
- security invoker views: 针对invoker
- 普通view, 针对table owner
base relations(包括table,view,mv等等) privilege 的判定 针对谁?
- security invoker views: 针对invoker
- 普通view, 针对table owner
lock和rule 的权限判断类似如上.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7faa5fc84bf46ea6c543993cffb8be64dff60d25
Add support for security invoker views.
author Dean Rasheed <dean.a.rasheed@gmail.com>
Tue, 22 Mar 2022 10:28:10 +0000 (10:28 +0000)
committer Dean Rasheed <dean.a.rasheed@gmail.com>
Tue, 22 Mar 2022 10:28:10 +0000 (10:28 +0000)
commit 7faa5fc84bf46ea6c543993cffb8be64dff60d25
tree 5e2f4c3b96cd77946916cd64369f3d71d8e86dc8 tree
parent f5576a21b0778f275d7418f6f7a44d9400ee90aa commit | diff
Add support for security invoker views.
A security invoker view checks permissions for accessing its
underlying base relations using the privileges of the user of the
view, rather than the privileges of the view owner. Additionally, if
any of the base relations are tables with RLS enabled, the policies of
the user of the view are applied, rather than those of the view owner.
This allows views to be defined without giving away additional
privileges on the underlying base relations, and matches a similar
feature available in other database systems.
It also allows views to operate more naturally with RLS, without
affecting the assignments of policies to users.
Christoph Heiss, with some additional hacking by me. Reviewed by
Laurenz Albe and Wolfgang Walther.
Discussion: https://postgr.es/m/b66dd6d6-ad3e-c6f2-8b90-47be773da240%40cybertec.at
+ <varlistentry>
+ <term><literal>security_invoker</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ This option causes the underlying base relations to be checked
+ against the privileges of the user of the view rather than the view
+ owner. See the notes below for full details.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ All of the above options can be changed on existing views using <link
+ linkend="sql-alterview"><command>ALTER VIEW</command></link>.
例子
+CREATE VIEW mysecview7 WITH (security_invoker=true)
+ AS SELECT * FROM tbl1 WHERE a = 100;
+CREATE VIEW mysecview8 WITH (security_invoker=false, security_barrier=true)
+ AS SELECT * FROM tbl1 WHERE a > 100;
+CREATE VIEW mysecview9 WITH (security_invoker)
+ AS SELECT * FROM tbl1 WHERE a < 100;
+-- security invoker view permissions
+SET SESSION AUTHORIZATION regress_view_user1;
+CREATE TABLE base_tbl(a int, b text, c float);
+INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
+CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
+ALTER VIEW rw_view1 SET (security_invoker = true);
+INSERT INTO rw_view1 VALUES ('Row 2', 2.0, 2);
+GRANT SELECT ON rw_view1 TO regress_view_user2;
+GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2;
+SET SESSION AUTHORIZATION regress_view_user2;
+SELECT * FROM base_tbl; -- not allowed
+ERROR: permission denied for table base_tbl
+SELECT * FROM rw_view1; -- not allowed
+ERROR: permission denied for table base_tbl
+INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- not allowed
+ERROR: permission denied for table base_tbl
+INSERT INTO rw_view1 VALUES ('Row 3', 3.0, 3); -- not allowed
+ERROR: permission denied for view rw_view1
+UPDATE base_tbl SET a=a; -- not allowed
+ERROR: permission denied for table base_tbl
+UPDATE rw_view1 SET bb=bb, cc=cc; -- not allowed
+ERROR: permission denied for table base_tbl
+DELETE FROM base_tbl; -- not allowed
+ERROR: permission denied for table base_tbl
+DELETE FROM rw_view1; -- not allowed
+ERROR: permission denied for view rw_view1
+SET SESSION AUTHORIZATION regress_view_user1;
+GRANT SELECT ON base_tbl TO regress_view_user2;
+GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2;
+SET SESSION AUTHORIZATION regress_view_user2;
+SELECT * FROM base_tbl; -- ok
+ a | b | c
+---+-------+---
+ 1 | Row 1 | 1
+ 2 | Row 2 | 2
+(2 rows)
+
+SELECT * FROM rw_view1; -- ok
+ bb | cc | aa
+-------+----+----
+ Row 1 | 1 | 1
+ Row 2 | 2 | 2
+(2 rows)
+
参考:
- 《PostgreSQL rls 行安全策略表在pg_dump导出时必须加--enable-row-security开关导出, 否则报错》
- 《PostgreSQL 敏感信息遮掩插件 anon - security label provider - anon》
- 《KEY 管理 - kms (Key management services) , hsm (hardware security modules) , hsm aas》
- 《PostgreSQL function 会遗传security 属性吗？》
- 《PostgreSQL 安全陷阱 - 利用触发器或规则，结合security invoker函数制造反噬陷阱》
- 《PostgreSQL 行安全策略 - PostgreSQL 9.5 new feature - can define row security policy for table》
- 《CentOS 6.x 优先级 limit change to /etc/security/limits.d/xxx》
- 《PostgreSQL leakproof function in rule rewrite("attack" security_barrier views)》
- 《PostgreSQL views privilege attack and security with security_barrier(视图攻击)》
期望 PostgreSQL 增加什么功能?
PolarDB for PostgreSQL云原生分布式开源数据库
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
