PostgreSQL query not using INDEX when RLS (Row Level Security) is enabled

Question

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

Lets say I have a TABLE:

CREATE TABLE public.document (
    id uuid PRIMARY KEY,

    title   text,
    content text NOT NULL
);

Together with a GIN INDEX on it:

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)
)

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.

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

ALTER TABLE public.document ENABLE ROW LEVEL SECURITY;

and then I add the policy:

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

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

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?

Thanks in advance!

Answer 1

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

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;

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.

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

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.

When working with RLS, keep in mind the following:

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!