sql - 启用 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 (Row Level Security)介绍一些基本的访问控制，首先我启用它:

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 Scan 而不是 Index扫描导致查询速度减慢 300 倍!

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

提前致谢!

Answer 1

我从发帖的时候就解决了这个问题...任何遇到这个问题的人，我都是这样做的:

我的解决方案是使用一个private SECURITY DEFINER“包装”函数，其中包含 propper 查询和另一个调用 的 public 函数>private 一个和 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 (这是 super 棒 顺便说一句!)，我能够省略对 private 模式的自省(introspection)，使“危险”功能无法访问!通过适当的安全实现，最终用户将只能看到最终的 GraphQL 模式，完全从外界移除 Postgres。

这很漂亮! 直到最近 Postgres 10.3 发布并修复了它，才不再需要这种 hack。

另一方面，我的 RLS 策略非常复杂、嵌套且非常深入。他们再次运行的表也非常大(总共大约有 50,000 多个条目运行 RLS)。即使使用 super 复杂和嵌套的策略，我也设法将性能保持在合理的范围内。

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

1. 创建适当的INDEXES
2. 到处都喜欢内联查询! (即使这意味着将同一个查询重写 N 次)
3. 一定要避免策略中的函数!如果您绝对必须将它们放在里面，请确保它们是 STABLE 并且具有较高的 COST(如@mkurtz 指出的那样)；或者是 IMMUTABLE
4. 从策略中提取查询，直接使用 EXPLAIN ANALYZE 运行它并尝试尽可能优化它

希望你们能像我一样发现这些信息对您有帮助!