Row level security policies do not apply to the table owner (by default)
By default RLS policies are not applied to the table owner - one needs to explicitly specify this using
create policy some_policy on some_table for all to public using (...);
Row level security policies do not apply to superusers
Any user that is a superuser, or has the
bypassrls bit set will always bypass the RLS system.
There is no way to disable this except for dropping permissions using
-- Will bypass RLS on some_table set role 'some-bypassrls-user'; select * from some_table; -- Will enforce RLS on some_table set role 'some-unprivileged-user'; select * from some_table;
Functional indices can only use leakproof functions
Due to security concerns, functional indices using leakproof functions can not be used for RLS-secured queries.
Unfortunately, functions such as
enum_eq are not leakproof.
This in turn means that indices such as
some_table(some_enum_column) will not be used when RLS is active.
If you prefer not to wait until your production database blows up because your functional index is not being used, you can check the
pg_proc table to find out whether a specific function is leakproof.
select proname, proleakproof from pg_proc order by proname;
As a possible solution one can work around this issue by wrapping all queries that require these indices in
security definer functions that are owned by a superuser (or a user with
set role to 'a-superuser'; create or replace function page_by_status(query_status page_status) returns table ( id bigint ) as $$ begin return query select page.id from page -- Optionally also check RLS policy here, depending on your security requirements where page.status = query_status; end; $$ language plpgsql stable security definer; -- Query pages by status, will check RLS in the outer query set role to 'not-a-superuser'; select p.* from page p where p.id in (select id from page_by_status('draft'::page_status));
If you prefer a more generic solution, one could also create a function to execute arbitrary queries outside the RLS system.
create or replace function UNSAFE_execute_bypassrls(query text) returns setof record as $$ begin return query execute query; end; $$ language plpgsql stable security definer;
If you have superuser permissions on the database and are okay with your functions leaking some data you can wrap them in leakproof helper functions. This may, for example, be the case if you only have trusted clients and are using RLS to create a multi-tenant schema. Note that many managed database providers (Heroku, AWS RDS, …) to not actually provide superuser access.
create or replace function leakproof_lower(str text) returns text as $$ begin return lower(text); end; $$ language plpgsql immutable leakproof;
The PostgreSQL optimizer dislikes functions in row level security policies
In my experiments I tried to first implement a generic
check_permissions(tenant_id) function and use it for all RLS constraints.
Unfortunately, the PostgreSQL optimizer really struggled with inlining this function into the query and often declined to use the composite indices.
Explicitly inlining the function as
tenant_id = get_current_tenant_id() solved this issue, for the most part.
Notes on debugging
Always make sure you set your role to a non-superuser before running
Otherwise PostgreSQL will silently give your very, very misleading query plans.