I don't fully understand the performance implications here.
Say I was using this for a blog engine, and I wanted to run this SQL query:
select * from entries;
But I actually only want to get back entries that my current user is allowed to view - where author_id = 57 for example.
Would PostgreSQL automatically turn the above query into the equivalent of this:
select * from entries where author_id = 57;
And hence run quickly (assuming there's an index on that author_id column)?
Or would it need to run an additional SQL query check for every single row returned by my query to check row permissions, adding up to a lot of extra overhead?
yes, postgres will add such a condition to the query and in simple cases like this is able to use a corresponding index
unfortunately this can break down in more complex cases. roughly postgres trusts a limited set of functions and operators not to leak information about rows (e.g. via error messages) that the RLS policy says a query should not be able to see. that set includes basic comparisons but not more esoteric operations like JSON lookups. at some point postgres will insist on checking the RLS policy result for a row before doing any further work, which can preclude the use of indexes
Say I was using this for a blog engine, and I wanted to run this SQL query:
But I actually only want to get back entries that my current user is allowed to view - where author_id = 57 for example.Would PostgreSQL automatically turn the above query into the equivalent of this:
And hence run quickly (assuming there's an index on that author_id column)?Or would it need to run an additional SQL query check for every single row returned by my query to check row permissions, adding up to a lot of extra overhead?