Row level security is an awesome feature that let you control how your database (PostgreSQL in my case) manage access to each row of a table based on some policies declared upfront. It's also really useful when you expose your database through a REST API with a gateway like PostgREST. I already talked enough about that :).

I often forgot to add the ALTER TABLE schema.table ENABLE ROW LEVEL SECURITY; statement when I declare row level security policies. Do you?

Let's use our database awesome introspection feature to list tables for which we attached access policies and then automatically activate row level security. The SQL request below list tables and display whether or not row level security is activated.

select pg_class.oid,
       pg_namespace.nspname || '.' || pg_class.relname as schema_table,
       pg_policy.polname as policy_name,
       pg_class.relrowsecurity as has_row_level_security_enabled
from pg_catalog.pg_policy
       inner join pg_catalog.pg_class on pg_class.oid = pg_policy.polrelid
       inner join pg_catalog.pg_namespace on pg_class.relnamespace = pg_namespace.oid;

From the query output we observe that contract_manager.contract table does have an associated access policy called contract_access_policy but without row level security enabled on the table.

Let's now enable row level security for each table where at least one policy was defined:

update pg_catalog.pg_class
set relrowsecurity = true
where pg_class.oid in (select pg_class.oid
      from pg_catalog.pg_policy
      inner join pg_catalog.pg_class on pg_class.oid = pg_policy.polrelid
      where pg_class.relrowsecurity = false);

This is it! We've activated row level security (not in FORCE mode) to every table with attached policies. No more mistakes. No more boilerplate \o/