启用RLS(行级安全性)时，PostgreSQL查询未使用INDEX [英] PostgreSQL query not using INDEX when RLS (Row Level Security) is enabled

问题描述

我正在使用 PostgreSQL 10.1 ，一直到重点...

I am using PostgreSQL 10.1, going right to the point...

让我们说我有一个表:

CREATE TABLE public.document (
    id uuid PRIMARY KEY,

    title   text,
    content text NOT NULL
);

上面加上 GIN索引:

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

一个基本的全文搜索查询:

And a basic fulltext search query:

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

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

Regardless of the public.document table size, the query is (you already know it) hella fast! The planner uses the INDEX and everything works out great.

现在，我通过 RLS(行级安全性)引入一些基本的访问控制，首先启用它:

Now I introduce some basic access control through RLS (Row Level Security), firstly I enable it:

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 是另一个查询，可以对其进行精确检查.

To keep things simple the is_current_user is another query which checks exactly that.

现在将全文搜索查询与 document_policy查询进行拼合，然后计划者执行 Seq Scan 而不是 Index扫描，查询速度降低300倍！

Now the fulltext search query is flattened with document_policy query and by doing so the planner executes a Seq Scan instead of Index Scan resulting in a 300x slower query!

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

I think the question is pretty obvious, how can I fix this so that the fulltext search query stays fast?

提前谢谢！

推荐答案

从发布之日起，我已经解决了这个问题...面对此问题的任何人，这就是我的解决方法:

I have solved this from the time of posting... Anyone facing this issue, this is how I did it:

我的解决方案是拥有一个包含私有查询的私有 SECURITY DEFINER 包装器"函数和另一个称为的 public 函数>私有一个和 INNER JOINS 需要访问控制的表.

My solution was to have a private SECURITY DEFINER "wrapper" function containing the propper query and another public function which calls the private one and INNER JOINS the table which requires access control.

因此，在上述特定情况下，将是这样的:

So in the specific case above, it would be something like this:

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 (这是超级棒顺便说一句！)，我省去了对 private 模式的自省，使危险"功能无法访问！通过适当的安全性实施，最终用户将只能看到最终的GraphQL模式，并从外部删除 Postgres .

Since I was using Postgraphile (which is super awesome BTW!), I was able to omit introspection of the private schema, making the "dangerous" function inaccessible! With proper security implementations, the end-user will only see the final GraphQL schema, complately removing Postgres from the outside world.

这很漂亮！直到最近 Postgres 10.3 发布并修复它，才不再需要此hack.

This worked beautifly! Until recently when Postgres 10.3 was released and fixed it, dropping the need for this hack.

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

On the other hand, my RLS policies are very complex, nested and go really deep. The tables which they are run agains are also quite large (roughly 50,000+ entries to run RLS against in total). Even with super complex and nested policies, I managed to maintain the performance within reasonable boundries.

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

When working with RLS, keep in mind the following:

1. 创建正确的 INDEXES
2. 在任何地方都首选内联查询！(即使那意味着要重写N次相同的查询)
3. 一定要避免策略中的功能！如果绝对必须将它们放入其中，请确保它们 STABLE 且具有较高的 COST (例如@mkurtz指出)；还是 IMMUTABLE
4. 从策略中提取查询，直接使用 EXPLAIN ANALYZE 运行查询，并尝试尽可能地对其进行优化

1. Create proper INDEXES
2. Prefer inline queries everywhere! (Even if that means rewriting the same query N times)
3. Avoid functions in policies by all means! If you absolutely must have them inside, make sure that they are STABLE and have a high COST (like @mkurtz pointed out); or are IMMUTABLE
4. Extract the query from the policy, run it directly with EXPLAIN ANALYZE and try optimizing it as much as possible

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

Hope you guys find the information helpful as much as I did!

这篇关于启用RLS(行级安全性)时，PostgreSQL查询未使用INDEX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！