"infinite recursion detected in policy"

[tconroy]

I have an organizations and organization_memberships table. They look like this:

organizations:

| id (PK)                              | name     |
|--------------------------------------|----------|
| a2a5e0c3-1fa8-4064-9e5b-56c18efb464c | Test Org |

organization_memberships:

| id (PK)                              | organization_id (FK)                 | user_id (FK)                         | role  |
|--------------------------------------|--------------------------------------|--------------------------------------|-------|
| c1a0c15d-8171-42cb-b224-55903e28c945 | a2a5e0c3-1fa8-4064-9e5b-56c18efb464c | b5d8ed0e-662b-484f-a8b4-479d624b3482 | owner |

I'm trying to write a policy on the organization_memberships table - a user should only be able to query memberships for the organization they belong to (think: a "view all members in your org" list, they should only see members for org they belong to).

This is the SELECT policy I attempted:

(
  organization_id IN (
    SELECT
      organization_memberships_1.organization_id
    FROM
      organization_memberships organization_memberships_1
    WHERE
      (organization_memberships_1.user_id = uid())
  )
)

the problem is this creates infinite recursion:

infinite recursion detected in policy for relation "organization_memberships"

Question: how do you enforce a SELECT policy when the criteria is based on the same table? or is there a different approach that is preferable here?

[soedirgo]

You can try using a function that bypasses RLS:

-- Parameters need to be prefixed because the name clashes with `om`'s columns
CREATE FUNCTION is_member_of(_user_id uuid, _organization_id uuid) RETURNS bool AS $$
SELECT EXISTS (
  SELECT 1
  FROM organization_memberships om
  WHERE om.organization_id = _organization_id
  AND om.user_id = _user_id
);
$$ LANGUAGE sql SECURITY DEFINER;
-- Function is owned by postgres which bypasses RLS

Then in the policy definition:

is_member_of(auth.uid(), organization_id)

[tconroy]

This worked well, thank you!

[malalecherocks]

yes it worked! thanks

[zalito12]

Remember to create your bypass rls functions in a not exposed schema: https://supabase.com/docs/guides/database/postgres/row-level-security#use-security-definer-functions

[pie6k]

We did end up with such a query:

schema: workspace with owner_id and workspace_member with workspace_id and user_id)
rule: you can access workspace if you're either owner or member of it. (owner will be also a member, but is not at the moment of creating the workspace)

CREATE OR REPLACE FUNCTION is_member_of(_user_id uuid, _workspace_id uuid) RETURNS bool AS $$
SELECT
  EXISTS (
    SELECT
      1
    FROM
      workspace_member
    WHERE
      -- workspace doesn't exist yet
      NOT EXISTS (
        SELECT
          1
        from
          workspace
        WHERE
          workspace.id = _workspace_id
      )
      -- you're owner
      OR EXISTS (
        SELECT
          1
        from
          workspace
        WHERE
          workspace.id = _workspace_id
          AND workspace.owner_id = _user_id
      )
      -- you're member
      OR EXISTS (
        SELECT
          1
        FROM
          workspace_member
          LEFT JOIN workspace ON workspace.id = workspace_member.workspace_id
        WHERE
          workspace.id = _workspace_id
          AND workspace_member.user_id = _user_id
      )
  );

$$ LANGUAGE sql SECURITY DEFINER;

It also solves some edge case: seems supabase is checking 'SELECT' permission before allowing 'INSERT' permission. This was not working, as you cannot be a member or owner of a workspace that does not exist yet (what the heck supabase?).

Thus the first condition is a bit absurd - 'yes, you have permission to workspace if this workspace doesn't exist', but it was needed in our case.

[szuliq]

This was not working, as you cannot be a member or owner of a workspace that does not exist yet (what the heck supabase?).

Are you sure it's Supabase doing this? In my understanding they're just making a query and this is checked by Postgres. The error code is definitively from inside Postgresql.

[nasheomirro]

Also ran in to this issue, the recursion I got involved two tables instead of one, each having multiple select policies, the problem I had was that one policy of each table is referencing each other. What I failed to understand was that policies aren't ran in order and aren't "short-circuited", if a policy already "lets you through" it would still go through all the other policies.

Another interaction you could get stuck on is assuming conditions in SQL can "short-circuit", for example you shouldn't expect a condition like this to solve the problem:

is_admin(auth.uid) or id in (select id from another_table)

it seems SQL is purely declarative and assumptions from procedural languages will likely cause more problems than helping.