Sep 23, 2021

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 to public.

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 set role;

-- 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 lower or enum_eq are not leakproof. This in turn means that indices such as some_table(lower(email)) or 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 bypassrls set).

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 explain analyze. Otherwise PostgreSQL will silently give your very, very misleading query plans.

References