How to get a list of Row Policies in PostgreSQL?

Question

the PostgreSQL-Documentation describes how to create or drop a row policy, but not how to get a list of the existing ones. My aim is to be able to know, by using pgAdmin, if there are row policies affecting a table and how they work.

Thanks!

Passifloraceous

I found the answer : everything is in the pg_catalog.pg_policies view. – Passifloraceous

Answer 1

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;

Hyaena

Answer 2

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)

Sukiyaki

Thanks. Sadly, you can't see what the policy really does – Passifloraceous

Ho, my bad. I looked in the pg_policy table instead of the pg_policies view. It's got all I needed ! – Passifloraceous

Obscure is correct. Does anyone now how to get table names, probably via polrelid? – Hyaena

@Alex, the pg_policies view has schemaname and tablename, which you can get by querying pg_class with the polrelid, and then pg_namespace from the class.relnamespace. That sounds tedious, but you can also do a \d+ pg_policies to get the view query, which you can then edit to get the info however you need. – Sukiyaki

Answer 3

Since version 4.23 - pgAdmin supports RLS Policies.

• https://www.pgadmin.org/docs/pgadmin4/development/release_notes_4_23.html

Choking