missing FROM-clause entry for table "new"

I wrote RLS like so:

DROP POLICY IF EXISTS crm_rbac_insert ON public.crm;
CREATE POLICY crm_rbac_insert ON public.crm
  FOR INSERT
  TO authenticated
  WITH CHECK (
    public.rbac_organization_has_permission(
      (SELECT auth.uid()),
      NEW.organisation_id,
      'crm_create'
    )
  );

DROP POLICY IF EXISTS crm_rbac_insert ON public.crm;
CREATE POLICY crm_rbac_insert ON public.crm
  FOR INSERT
  TO authenticated
  WITH CHECK (
    public.rbac_organization_has_permission(
      (SELECT auth.uid()),
      NEW.organisation_id,
      'crm_create'
    )
  );

But I'm getting above error. I need that NEW because I have to check if user has role with permission allowing them to create new CRM

12 Replies

garyaustin•2d ago

There is no NEW or OLD in RLS policies. If that is a column in the crm table then just plain organization_id would work. It some cases if you are doing a table select in the RLS policy then you and the table has the same column as the table the RLS is on then you need to do crm.organization_id. WITH CHECK is on the new data and USING is on the existing data in the row.

Floppy DiskOP•2d ago

So now I have problem with this RLS

DROP POLICY IF EXISTS crm_project_or_org_member_select ON public.crm;
CREATE POLICY crm_project_or_org_member_select ON public.crm
  FOR SELECT
  TO authenticated
  USING (
    (
      EXISTS (
        SELECT 1
        FROM public.organisation_members om
        WHERE om.organisation_id = public.get_crm_organisation_id(public.crm.id)
          AND om.user_id = (SELECT auth.uid())
      )
    )
  );

DROP POLICY IF EXISTS crm_project_or_org_member_select ON public.crm;
CREATE POLICY crm_project_or_org_member_select ON public.crm
  FOR SELECT
  TO authenticated
  USING (
    (
      EXISTS (
        SELECT 1
        FROM public.organisation_members om
        WHERE om.organisation_id = public.get_crm_organisation_id(public.crm.id)
          AND om.user_id = (SELECT auth.uid())
      )
    )
  );

Because I'm trying to run:

 const createRes = await supabase
        .from('crm')
        .insert(crm)
        .select()
        .single()
    if (createRes.error) throw new Error(createRes.error.message)
    const associateRes = await supabase.from('organisation_crm').insert({ organisation_id: crm.organisation_id!, crm_id: createRes.data.id })
    if (associateRes.error) {
        await supabase.from('crm').delete().eq('id', createRes.data.id)
        throw new Error(associateRes.error.message)
    }

 const createRes = await supabase
        .from('crm')
        .insert(crm)
        .select()
        .single()
    if (createRes.error) throw new Error(createRes.error.message)
    const associateRes = await supabase.from('organisation_crm').insert({ organisation_id: crm.organisation_id!, crm_id: createRes.data.id })
    if (associateRes.error) {
        await supabase.from('crm').delete().eq('id', createRes.data.id)
        throw new Error(associateRes.error.message)
    }

Select causes RLS errors and Idk really how to get around this

garyaustin•2d ago

You are returning data on the insert so you must meet the Select policy. If your select from other tables in RLS policies you also have to meet select on all of those tables unless you embed them in a security definer function and call that instead.

Floppy DiskOP•2d ago

So ig there is no pretty js way around this and I have to resort to triggers? To create this organisation_crm record?

garyaustin•2d ago

Not sure I understand. You have to meet RLS for the insert to work. Your first insert has to meet both INSERT and SELECT RLS for crm table. You also have to meet SELECT for organisation_members as you use that in the policy. Your 2nd insert needs to meet INSERT RLS for organization_crm.

Floppy DiskOP•2d ago

Actually doing this with trigger was far simplier than I expected. Your AI sometimes does wonders and sometimes acts like it had a stroke

garyaustin•2d ago

"Your AI"... This is a user helping user forum and not regularly monitored by Supabase staff.

Floppy DiskOP•2d ago

SELECT RLS for crm table

It met insert, it didnt meet select for some reason even tho I can see this record seconds after insert like almost immediately when I refetch

"Your AI"... This is a user helping user forum and not regularly monitored by Supabase staff.

Was sure Ive seen u had some moderator rank previously so I assumed u r part of the supabase team, sorry for that

garyaustin•2d ago

Do you meet Select RLS on that table. If not then crm RLS will fail.

garyaustin•2d ago

Also if your first insert RLS (select or insert policy including any tables involved in that policy) depends on your 2nd insert of an organization_id then that will fail.

Floppy DiskOP•2d ago

Yeah, so my Select checks if organisation given member is part of has access to this given crm via organisation_crm table so there was no way it would work

garyaustin•2d ago

A typical solution for this is to use an rpc call to a postgres function and have it handle the inserts and any fixed id generation. A trigger can also work in some cases. IF I understand what you are doing. These also run as a transaction so if either insert fails then both will be backed out.

Gaming

Programming

missing FROM-clause entry for table "new"

Did you find this page helpful?