Fix Circular Dependencies in PostgreSQL Row-Level Security With SECURITY DEFINER Functions

Row-level security in PostgreSQL is one of the more useful features for multi-tenant applications. The idea is straightforward: define a policy on a table that tells PostgreSQL which rows a given user is allowed to see or modify, and the database engine enforces it on every query, regardless of which application code issued the request.

The trouble comes when your policies form a cycle. This is more common than it sounds, and it produces one of the more confusing failure modes in PostgreSQL: a query that should return data returns nothing, with no error.

This article walks through how circular RLS dependencies arise, why they silently eat your data, and how to break the cycle using SECURITY DEFINER functions.

How the Circular Dependency Happens

Consider a simple multi-tenant schema. You have a properties table and a property_members table that tracks which users have access to which properties:

CREATE TABLE public.properties (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text UNIQUE NOT NULL
);

ALTER TABLE public.properties ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.property_members (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id uuid NOT NULL REFERENCES public.properties(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role text NOT NULL DEFAULT 'member',
  accepted_at timestamptz,
  UNIQUE(property_id, user_id)
);

ALTER TABLE public.property_members ENABLE ROW LEVEL SECURITY;

Now you write your policies. A user should be able to see a property if they are an accepted member of it:

CREATE POLICY "properties_select_members" ON public.properties
  FOR SELECT TO authenticated USING (
    EXISTS (
      SELECT 1 FROM property_members
      WHERE property_id = properties.id
        AND user_id = auth.uid()
        AND accepted_at IS NOT NULL
    )
  );

And a user should be able to see other members of a property if they are also a member:

CREATE POLICY "property_members_select_comembers" ON public.property_members
  FOR SELECT TO authenticated USING (
    EXISTS (
      SELECT 1 FROM property_members pm2
      WHERE pm2.property_id = property_members.property_id
        AND pm2.user_id = auth.uid()
        AND pm2.accepted_at IS NOT NULL
    )
  );

This looks reasonable. In fact, it compiles without error. Then you run a query, and it returns zero rows.

Why This Silently Returns Nothing

Here is the execution path PostgreSQL follows when an authenticated user queries properties:

1. Apply properties_select_members. This requires checking property_members. 
2. To read property_members, apply property_members_select_comembers. This requires checking property_members again. 
3. To check property_members in step 3, apply property_members_select_comembers. This requires checking property_members again.

PostgreSQL does not raise an error here. Instead, when it detects the recursive RLS evaluation, it short-circuits and evaluates the recursive reference as returning no rows. The result is that the policy conditions that depend on property_members always see an empty set, every EXISTS(...) check returns false, and no rows are visible.

This is consistent with how PostgreSQL handles RLS recursion to prevent infinite loops, but the silent behavior makes it genuinely difficult to diagnose. You add your membership record, you enable RLS, you query your table, and you get nothing. No error message. No warning. Just an empty result.

The Fix: SECURITY DEFINER Functions

The solution is to introduce a layer of indirection. Instead of having your policies query property_members directly (which triggers RLS on that table), you wrap the membership check in a function that runs with elevated privileges and bypasses RLS entirely.

CREATE OR REPLACE FUNCTION public.is_property_member(p_property_id uuid, p_user_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM property_members
    WHERE property_id = p_property_id
      AND user_id = p_user_id
      AND accepted_at IS NOT NULL
  );
$$;

The SECURITY DEFINER attribute tells PostgreSQL to run the function as the user who defined it (typically a superuser or the role that owns the schema), not as the calling user. Inside the function body, RLS on property_members is not applied, because the function owner has full access.

You can add role-specific variants for the same pattern:

CREATE OR REPLACE FUNCTION public.is_property_admin(p_property_id uuid, p_user_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM property_members
    WHERE property_id = p_property_id
      AND user_id = p_user_id
      AND role IN ('owner', 'admin')
      AND accepted_at IS NOT NULL
  );
$$;

Now rewrite your policies to call the function instead of querying the table directly:

DROP POLICY IF EXISTS "properties_select_members" ON public.properties;
DROP POLICY IF EXISTS "property_members_select_comembers" ON public.property_members;

CREATE POLICY "properties_select_members" ON public.properties
  FOR SELECT TO authenticated USING (
    public.is_property_member(id, auth.uid())
  );

CREATE POLICY "property_members_select_comembers" ON public.property_members
  FOR SELECT TO authenticated USING (
    public.is_property_member(property_id, auth.uid())
  );

The cycle is broken. properties policies call is_property_member. property_members policies also call is_property_member. But is_property_member is a function that executes with SECURITY DEFINER privileges, so when PostgreSQL evaluates it, it does not apply RLS to the property_members table inside the function body. There is no loop.

Applying the Pattern Consistently

Once you have your helper functions in place, the pattern composes cleanly across your entire schema. Every table in your multi-tenant application can reference the same small set of helper functions in its policies:

-- Bookings: members can read, admins can write
CREATE POLICY "bookings_select" ON public.bookings
  FOR SELECT TO authenticated USING (
    public.is_property_member(property_id, auth.uid())
  );

CREATE POLICY "bookings_update" ON public.bookings
  FOR UPDATE TO authenticated USING (
    public.is_property_admin(property_id, auth.uid())
  );

-- Board posts: members can read and post, owner or author can delete
CREATE POLICY "board_posts_delete" ON public.board_posts
  FOR DELETE TO authenticated USING (
    user_id = auth.uid()
    OR public.is_property_admin(property_id, auth.uid())
  );

The policies stay readable and short. The access logic lives in one place. When your membership rules change (say, you add a new role), you update the functions rather than hunting through every policy across every table.

A Few Things to Keep in Mind

SET search_path = public in the function definition is not optional. Without it, a malicious user could create objects in a schema earlier in the search path and potentially redirect function calls. PostgreSQL's own documentation recommends this for any SECURITY DEFINER function.

Marking functions as STABLE (rather than VOLATILE, the default) lets PostgreSQL cache the result within a single query. A single SELECT that reads many rows from properties will call is_property_member once per row, and the STABLE declaration allows the planner to optimize those calls. If your membership table changes mid-transaction, this is worth thinking about, but for most access-control use cases, STABLE is the right choice.

Finally, grant EXECUTE on these functions only to the roles that need them. For a Supabase project, that typically means the authenticated role. The function runs as the owner, but you still control who can call it.

GRANT EXECUTE ON FUNCTION public.is_property_member(uuid, uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION public.is_property_admin(uuid, uuid) TO authenticated;

The circular dependency problem is a good example of why it pays to understand what your framework is doing underneath. Supabase makes RLS easy to enable. It does not protect you from cycles in the policies you write. But once you understand the pattern, the fix is clean, and it scales to a large schema without adding complexity.