PostgreSQL における行レベルセキュリティの実装に関する研究

補足として PostgreSQLストアドファンクションレベルでのビジネスロジック実装に関する研究 и 主に詳細な回答のため на コメント.

理論的な部分はドキュメントで詳しく説明されています PostgreSQL - 行保護ポリシー。 以下は小さな実用的な実装です。 特定のビジネス タスク - 削除されたデータの非表示。 実装専用のスケッチ RLS を使用したロール モデリング 別途提示。

この記事には新しいことは何もなく、隠された意味や秘密の知識もありません。 理論的なアイデアの実際的な実装に関する単なるスケッチです。 興味のある人は読んでみてください。 興味がないなら、時間を無駄にしないでください。

問題の定式化

主題領域には深く立ち入りませんが、簡単に言うと、問題は次のように定式化できます。 特定のビジネスエンティティを実装するテーブルがあります。 テーブル内の行は削除できますが、行は物理的に削除できないため、非表示にする必要があります。

なぜなら、「何も削除せず、名前を変更するだけです。 インターネットにはあらゆるものが保存されます」

途中で、このエンティティで動作する既存のストアド関数を書き換えないことをお勧めします。

この概念を実装するために、テーブルには次の属性があります。 is_deleted。 そうすれば、すべてが簡単になります。クライアントには、属性が含まれる行のみが表示されるようにする必要があります。 is_deleted 間違いその仕組みは何に使われているのでしょうか？ 行レベルのセキュリティ。

具現化

別のロールとスキーマを作成する

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)

これは問題文で必要とされていたものです。

合計

このトピックが興味深い場合は、次の研究で、行レベル セキュリティを使用してデータ アクセスを分離するためのロールベースのモデルを実装する例を示すことができます。

出所： habr.com