How to get a list of Row Policies in PostgreSQL?
There is a catalog view that holds what you want: pg_policy
. It's a little obscure, but it holds what you need.
# \d pg_policy
Table "pg_catalog.pg_policy"
Column | Type | Modifiers
--------------+--------------+-----------
polname | name | not null
polrelid | oid | not null
polcmd | "char" | not null
polroles | oid[] |
polqual | pg_node_tree |
polwithcheck | pg_node_tree |
Indexes:
"pg_policy_oid_index" UNIQUE, btree (oid)
"pg_policy_polrelid_polname_index" UNIQUE, btree (polrelid, polname)
More helpful than catalog view pg_policy is pg_policies. You see what table it applies to and even what the policy does (though that seems to get truncated).
So just run: select * from pg_policies
which actually boils down to this (thanks to @jmelesky):
SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,
CASE
WHEN pol.polpermissive THEN 'PERMISSIVE'::text
ELSE 'RESTRICTIVE'::text
END AS permissive,
CASE
WHEN pol.polroles = '{0}'::oid[] THEN string_to_array('public'::text, ''::text)::name[]
ELSE ARRAY( SELECT pg_authid.rolname
FROM pg_authid
WHERE pg_authid.oid = ANY (pol.polroles)
ORDER BY pg_authid.rolname)
END AS roles,
CASE pol.polcmd
WHEN 'r'::"char" THEN 'SELECT'::text
WHEN 'a'::"char" THEN 'INSERT'::text
WHEN 'w'::"char" THEN 'UPDATE'::text
WHEN 'd'::"char" THEN 'DELETE'::text
WHEN '*'::"char" THEN 'ALL'::text
ELSE NULL::text
END AS cmd,
pg_get_expr(pol.polqual, pol.polrelid) AS qual,
pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check
FROM pg_policy pol
JOIN pg_class c ON c.oid = pol.polrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace;