PostgreSQL Check if security_invoker is turned on on view

Question

Since PostgreSQL 15 it is possible to set security_invoker on views. I was wondering how to check if my views have it enabled or not, as neither pgAdmin nor DataGrip doesn't show that.

Answer 1

Here is an example to check all views in the public schema:

select 
  relname, 
  case 
    when lower(reloptions::text)::text[] && array['security_invoker=1','security_invoker=true','security_invoker=on'] 
      then true else false
  end as security_invoker
from pg_class
  join pg_catalog.pg_namespace n on n.oid = pg_class.relnamespace
where n.nspname = 'public' and relkind='v';

In the internal pg_class table there is a column called reloptions which knows if the security_invoker is turned on.

Example output for reloptions: {security_invoker=true} and {security_invoker=on} or is null by default if it isn't turned on.