How to check if row level security is enabled for a table in postgres

Question

Enabling row level security on a table in postgres is pretty straightforward:

alter table some_table enable row level security;

How would you check to see which tables in a given schema have row level security enabled (for testing)?

Qualifier

Answer 1

This is stored in pg_class

• relrowsecurity bool True if table has row level security enabled; see pg_policy catalog
• relforcerowsecurity bool True if row level security (when enabled) will also apply to table owner; see pg_policy catalog

So you can use:

select relname, relrowsecurity, relforcerowsecurity
from pg_class
where oid = 'your_table_name'::regclass;

Alternatively use pg_tables

Jeana

select relname, relrowsecurity, relforcerowsecurity from pg_class where relrowsecurity=true or relforcerowsecurity=true; – Tyburn

Answer 2

If you want to check if row level security is enabled for lots of tables for a particular schema (in this case public) you can use:

select relname, relrowsecurity, relforcerowsecurity
  from pg_class
  join pg_catalog.pg_namespace n on n.oid = pg_class.relnamespace
  where n.nspname = 'public' and relkind = 'r';

Dartmouth

What does the relforcerowsecurity col represent? – Alissaalistair

The documentation for pgclass availed at postgresql.org/docs/current/catalog-pg-class.html reads:- a) relrowsecurity True if table has row-level security enabled b) relforcerowsecurity True if row-level security (when enabled) will also apply to table owner – Seismoscope