启用RLS（行级安全性）时，PostgreSQL查询未使用INDEX

Question

我正在使用 PostgreSQL 10.1 ，直达关键点…

可以说我有一张table ：

CREATE TABLE public.document (
    id uuid PRIMARY KEY,

    title   text,
    content text NOT NULL
);

连同上面的 GIN INDEX ：

CREATE INDEX document_idx ON public.document USING GIN(
    to_tsvector(
        'english',
        content || ' ' || COALESCE(title, '')
    )
);

和一个基本的全文搜索查询：

SELECT * FROM public.document WHERE (
    to_tsvector(
        'english',
        content || ' ' || COALESCE(title, '')
    ) @@ plainto_tsquery('english', fulltext_search_documents.search_text)
)

无论 public.document 表的大小如何，查询都非常快（您已经知道了）！计划人员使用INDEX，一切都很好。

现在，我通过 RLS（行级安全性） 介绍一些基本的访问控制，首先启用它：

ALTER TABLE public.document ENABLE ROW LEVEL SECURITY;

然后添加策略：

CREATE POLICY document_policy ON public.document FOR SELECT
    USING (EXISTS (
        SELECT 1 FROM public.user WHERE (is_current_user) AND ('r' = ANY(privileges))
    ));

为了简单 起见，is_current_user 是另一个查询，它精确地检查了这一点。

现在， 全文搜索查询 将通过 document_policy查询进行 平整，这样计划人员将执行 Seq扫描 而不是
索引扫描， 从而使查询速度降低300倍！

我认为问题很明显，如何解决这个问题，以便全文搜索查询保持快速？

提前致谢！

Answer 1

我已经从发布之日起解决了这个问题…任何面临此问题的人，这就是我的做法：

我的解决方案是拥有一个包含“ propper”查询的 私有* SECURITY DEFINER“包装器”函数，以及另一个调用该 私有
函数和需要访问控制的表的 公共 函数。
*INNER JOINS

因此，在上面的特定情况下，将是这样的：

CREATE FUNCTION private.filter_document() RETURNS SETOF public.document AS
$$
    SELECT * FROM public.document WHERE (
        to_tsvector(
            'english',
            content || ' ' || COALESCE(title, '')
        ) @@ plainto_tsquery('english', fulltext_search_documents.search_text)
    )
$$
LANGUAGE SQL STABLE SECURITY DEFINER;
----
CREATE FUNCTION public.filter_document() RETURNS SETOF public.document AS
$$
    SELECT filtered_d.* FROM private.filter_documents() AS filtered_d
        INNER JOIN public.document AS d ON (d.id = filtered_d.id)
$$
LANGUAGE SQL STABLE;

由于我使用的是Postgraphile（
超级棒的 BTW！），因此我可以省去 私有
模式的自省，使“危险”功能无法访问！通过适当的安全性实现，最终用户将只能看到最终的GraphQL模式，从而将 Postgres 从外界中删除。

这工作得很漂亮！ 直到最近 发布并修复 Postgres 10.3 时，才不再需要这种hack。

另一方面，我的RLS策略非常复杂，嵌套并且非常深入。它们再次运行的表也很大（总共要运行50,000多个条目才能针对RLS运行）。即使采用了超级复杂和嵌套的策略，我也设法将性能保持在合理的范围内。

使用RLS时，请记住以下几点：

1. 建立适当的 INDEXES
2. 在任何地方都首选内联查询 ！（即使这意味着重写N次相同的查询）
3. 务必避免使用策略中的功能！如果您绝对必须在内部安装它们，请确保它们STABLE高且高COST（例如@mkurtz指出）；或者是IMMUTABLE
4. 从策略中提取查询，直接与查询一起运行，EXPLAIN ANALYZE并尝试尽可能对其进行优化

希望你们能像我一样找到有用的信息！