noam steiner's questions

我想创建一个依赖于同一表中其他记录的策略。例如，假设有以下联结表：

 project_id | user_id
------------+----------
     1      |     1
------------+----------
     1      |     2   
------------+----------
     2      |     2   

我想阻止用户选择与其不相关的项目相关的记录，并允许选择其余的。

就像是：

CREATE POLICY project_user_read ON project_user FOR SELECT 
USING(
      project_id IN(
      SELECT project_id 
      FROM project_user 
      WHERE user_id = current_setting('app.current_user_id')::bigint ---> I use runtime variables
   )
);

这将导致这种方式：

SET app.current_user_id = 1;
SELECT * FROM project_user;

--  project_id | user_id
-- ------------+----------
--      1      |     1
-- ------------+----------
--      1      |     2   

这显然不起作用，因为它是一个循环条件。

有没有办法用 RLS 实现这样的逻辑？

尝试时CREATE POLICY出现以下错误：

ERROR: row is too big: size XXX, maximum size 8160

pg_policies表有大小限制吗？

可以扩大，还是用连接写这么多条件是不好的做法？

现在，我通过将查询更改为更少的字符来解决它，但是如何避免呢？

几乎太长的政策示例。

        CREATE POLICY check_out_insert ON check_out FOR INSERT
            WITH CHECK ( 
                (user_id = current_setting('app.current_user_id')::bigint AND
                file_version_id = ANY(ARRAY(
                    SELECT file_version.id fv_id FROM file_version 
                        INNER JOIN "file" ON (file_version.file_id = "file".id)
                        INNER JOIN "file_user" ON ("file".id = "file_user".file_id AND file_user.deleted_at IS NULL)
                        WHERE file_user.user_id = current_setting('app.current_user_id')::bigint)
                )
                OR
                true = ANY(
                    ARRAY(SELECT is_owner 
                    FROM "group"
                    WHERE id = current_setting('app.current_user_group_id')::bigint
                    LIMIT 1
                    )
                )
                OR
                file_version_id = ANY( 
                    ARRAY(SELECT DISTINCT file_version.id fv_id FROM file_version 
                        INNER JOIN "file" ON (file_version.file_id = "file".id)
                        INNER JOIN "sub_folder" ON (file.sub_folder_id = "sub_folder".id)
                        INNER JOIN "folder" ON (sub_folder.folder_id = "folder".id)
                        INNER JOIN "folder_group" ON (folder_group.folder_id = "folder".id AND folder_group.group_id = current_setting('app.current_user_group_id')::bigint)
                        INNER JOIN "project_user" ON (folder.project_id = "project_user".project_id AND "project_user".user_id = current_setting('app.current_user_id')::bigint)
                        WHERE folder.type = 'nda'
                    )
                )
            )
        );`);