postgresql - 可以从父母那里继承的 Postgres 父子行级安全性？

Question

看起来像这样的子表

CREATE TABLE folder_item (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid()
    ,parent_id uuid REFERENCES folder_item (id) ON DELETE CASCADE
    ,role text NOT NULL DEFAULT 'inherit'
);

使用权限模型

CREATE POLICY folder_item_rolecheck ON folder_item FOR SELECT USING ( role = assigned_role );

但是，如果它找到带有“继承”的行，我希望它改为查看父角色（递归）

那可能吗？

标签： postgresqlrow-level-security

Answer 1

-- Set NO FORCE ROW LEVEL SECURITY on table "folder_item" to off RLS for OWNER
ALTER TABLE folder_item NO FORCE ROW LEVEL SECURITY

-- Create function with RECURSIVE qwery and SECURITY DEFINER with OWNER as for table "folder_item"
CREATE OR REPLACE FUNCTION folder_item_check_child(
    in_parent_id uuid
    , in_role text)
    RETURNS boolean
    LANGUAGE 'plpgsql'

    COST 100
    STABLE SECURITY DEFINER 

AS $BODY$BEGIN
    RETURN EXISTS(
        WITH RECURSIVE 
        childs AS (
            SELECT tt.id, tt.role FROM folder_item AS tt 
            WHERE tt.parent_id=in_parent_id
            UNION 
            SELECT child.id, child.role 
            FROM childs AS parent 
            INNER JOIN folder_item AS child ON child.parent_id=parent.id  
        )
        SELECT * FROM childs AS tt WHERE tt.role=in_role
    );
END$BODY$;


-- CREATE POLICY
CREATE POLICY folder_item_rolecheck ON folder_item FOR SELECT USING ( role = assigned_role 

OR folder_item_check_child(id, assigned_role)  

);