PostgreSQL中的行级安全性实现研究

作为补充，爱丽的业务逻辑在PostgreSQL的存储功能的一级执行和主要的详细解答的评论。



该理论在PostgreSQL文档-行安全策略中得到了很好的描述。以下是小型特定业务任务的实际实现-隐藏已删除的数据。单独介绍了使用RLS实现角色模型的研究。

这篇文章没有什么新意，没有隐藏的意义和秘密知识。只是有关理论构想的实际实施的草图。如果有人感兴趣，请继续阅读。谁不感兴趣-不要浪费您的时间。

问题的提法

无需深入探讨主题领域，可以将任务表述如下：有一个实现特定业务实体的表。可以删除表中的行，但是您不能实际删除行，需要隐藏它们。



据说-“请勿删除任何内容，只需重命名即可。Internet上将存储所有内容。“



在此过程中，建议不要重写可用于此实体的现有存储功能。



为了实现此概念，该表具有is_deleted属性。然后，一切都变得简单了-您需要进行设置，以便客户端只能看到is_deleted属性为false的行。对于什么使用行级安全性机制。

实作

创建一个单独的角色和架构

CREATE ROLE repos;
CREATE SCHEMA repos;

创建目标表

CREATE TABLE repos.file
(
...
is_del BOOLEAN DEFAULT FALSE
);
CREATE SCHEMA repos

开启行级安全性

ALTER TABLE repos.file  ENABLE ROW LEVEL SECURITY ;
CREATE POLICY file_invisible_deleted  ON repos.file FOR ALL TO dba_role USING ( NOT is_deleted );
GRANT ALL ON TABLE repos.file to dba_role ;
GRANT USAGE ON SCHEMA repos TO dba_role ;

服务功能-删除表中的一行

CREATE OR REPLACE repos.delete( curr_id repos.file.id%TYPE)
RETURNS integer AS $$
BEGIN
...
UPDATE repos.file
SET is_del = TRUE 
WHERE id = curr_id ; 
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

业务功能-删除文件

CREATE OR REPLACE business_functions.deleteDoc( doc_for_delete JSON )
RETURNS JSON AS $$
BEGIN
...
PERFORM  repos.delete( doc_id ) ;
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

结果

客户删除文件

SELECT business_functions.delCFile( (SELECT json_build_object( 'CId', 3 )) );

删除后，客户端看不到文档

SELECT business_functions.getCFile"( (SELECT json_build_object( 'CId', 3 )) ) ;
-----------------
(0 rows)

但是文档尚未在数据库中删除，只有is_del属性已更改

psql -d my_db
SELECT  id, name , is_del FROM repos.file ;
id |  name  | is_del
--+---------+------------
 1 |  test_1 | t
(1 row)

这是解决问题所需要的。

结果

如果该主题很有趣，那么在下一个练习中，您可以显示一个示例，该示例实现了使用行级安全性实现基于角色的模型以共享数据访问的功能。