Home PostgreSQL query not using INDEX when RLS (Row Level Security) is enabled
Reply: 0

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

user1883
1#
user1883 Published in April 23, 2018, 8:51 pm

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!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310977 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO