RLS policy help

Hi everyone!
I'm trying to write RLS policies to only allow access to rows of a "vendors" table that are identified in a "vendor_communities" table as belonging to the same community as the active user as determined by the "user_communities" table. Here's what I have:

-- Create a policy for SELECT access on the "vendors" table
CREATE POLICY "Enable select for community members only"
ON "public"."vendors"
AS PERMISSIVE FOR SELECT
    USING (
        EXISTS (
            SELECT 1
            FROM vendor_communities vc
            JOIN user_communities uc ON vc.community = uc.community
            WHERE vc.vendor = vendors.id
            AND uc."user" = auth.uid()
        )
    );

-- Create a policy for INSERT access on the "vendors" table
CREATE POLICY "Enable insert for community members only"
 ON "public"."vendors"
AS PERMISSIVE FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1
            FROM vendor_communities vc
            JOIN user_communities uc ON vc.community = uc.community
            WHERE vc.vendor = vendors.id
            AND uc."user" = auth.uid()
        )
    );

-- Create a policy for SELECT access on the "vendors" table
CREATE POLICY "Enable select for community members only"
ON "public"."vendors"
AS PERMISSIVE FOR SELECT
    USING (
        EXISTS (
            SELECT 1
            FROM vendor_communities vc
            JOIN user_communities uc ON vc.community = uc.community
            WHERE vc.vendor = vendors.id
            AND uc."user" = auth.uid()
        )
    );

-- Create a policy for INSERT access on the "vendors" table
CREATE POLICY "Enable insert for community members only"
 ON "public"."vendors"
AS PERMISSIVE FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1
            FROM vendor_communities vc
            JOIN user_communities uc ON vc.community = uc.community
            WHERE vc.vendor = vendors.id
            AND uc."user" = auth.uid()
        )
    );

These are not working as expected in that they don't grant access when the desired conditions are met. Thanks to Supabase's new user impersonator feature I can confirm that this is a problem with the policies and not something on the front end.

I have another table with similar requirements but an added user role check that's also not working as expected, I suspect for the same reason. Any help would be appreciated!

Thanks,

GD

RLS policy help

-- Create a policy for SELECT access on the "vendors" table
CREATE POLICY "Enable select for community members only"
ON "public"."vendors"
AS PERMISSIVE FOR SELECT
    USING (
        EXISTS (
            SELECT 1
            FROM vendor_communities vc
            JOIN user_communities uc ON vc.community = uc.community
            WHERE vc.vendor = vendors.id
            AND uc."user" = auth.uid()
        )
    );

-- Create a policy for INSERT access on the "vendors" table
CREATE POLICY "Enable insert for community members only"
 ON "public"."vendors"
AS PERMISSIVE FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1
            FROM vendor_communities vc
            JOIN user_communities uc ON vc.community = uc.community
            WHERE vc.vendor = vendors.id
            AND uc."user" = auth.uid()
        )
    );

-- Create a policy for SELECT access on the "vendors" table
CREATE POLICY "Enable select for community members only"
ON "public"."vendors"
AS PERMISSIVE FOR SELECT
    USING (
        EXISTS (
            SELECT 1
            FROM vendor_communities vc
            JOIN user_communities uc ON vc.community = uc.community
            WHERE vc.vendor = vendors.id
            AND uc."user" = auth.uid()
        )
    );

-- Create a policy for INSERT access on the "vendors" table
CREATE POLICY "Enable insert for community members only"
 ON "public"."vendors"
AS PERMISSIVE FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1
            FROM vendor_communities vc
            JOIN user_communities uc ON vc.community = uc.community
            WHERE vc.vendor = vendors.id
            AND uc."user" = auth.uid()
        )
    );

RLS policy help

RLS policy help

Similar Threads

Similar Threads

Similar Threads