need help with RLS policy

problem :

I was trying to sign up a user. After calling supabase.auth.signUp, I create a row in the business table, then in the profiles table, using auth.user.id, the business_id, and some other details. However, I encounter an issue with RLS during the business creation process.

I was trying to sign up a user. After calling supabase.auth.signUp, I create a row in the business table, then in the profiles table, using auth.user.id, the business_id, and some other details. However, I encounter an issue with RLS during the business creation process.

error :

new row violates row-level security policy for table "businesses"

new row violates row-level security policy for table "businesses"

RLS policy:

[
  {
    "schemaname": "public",
    "tablename": "businesses",
    "policyname": "allow authenticated inserts with owner",
    "permissive": "PERMISSIVE",
    "roles": "{authenticated}",
    "cmd": "INSERT",
    "qual": null,
    "with_check": "(owner_id = ( SELECT auth.uid() AS uid))"
  },
  {
    "schemaname": "public",
    "tablename": "businesses",
    "policyname": "businesses_select",
    "permissive": "PERMISSIVE",
    "roles": "{public}",
    "cmd": "SELECT",
    "qual": "(id = ( SELECT profiles.business_id\n   FROM profiles\n  WHERE (profiles.id = auth.uid())))",
    "with_check": null
  },
  {
    "schemaname": "public",
    "tablename": "businesses",
    "policyname": "businesses_update",
    "permissive": "PERMISSIVE",
    "roles": "{public}",
    "cmd": "UPDATE",
    "qual": "(id = ( SELECT profiles.business_id\n   FROM profiles\n  WHERE ((profiles.id = auth.uid()) AND (profiles.role = 'owner'::text))))",
    "with_check": "(id = ( SELECT profiles.business_id\n   FROM profiles\n  WHERE ((profiles.id = auth.uid()) AND (profiles.role = 'owner'::text))))"
  }
]

[
  {
    "schemaname": "public",
    "tablename": "businesses",
    "policyname": "allow authenticated inserts with owner",
    "permissive": "PERMISSIVE",
    "roles": "{authenticated}",
    "cmd": "INSERT",
    "qual": null,
    "with_check": "(owner_id = ( SELECT auth.uid() AS uid))"
  },
  {
    "schemaname": "public",
    "tablename": "businesses",
    "policyname": "businesses_select",
    "permissive": "PERMISSIVE",
    "roles": "{public}",
    "cmd": "SELECT",
    "qual": "(id = ( SELECT profiles.business_id\n   FROM profiles\n  WHERE (profiles.id = auth.uid())))",
    "with_check": null
  },
  {
    "schemaname": "public",
    "tablename": "businesses",
    "policyname": "businesses_update",
    "permissive": "PERMISSIVE",
    "roles": "{public}",
    "cmd": "UPDATE",
    "qual": "(id = ( SELECT profiles.business_id\n   FROM profiles\n  WHERE ((profiles.id = auth.uid()) AND (profiles.role = 'owner'::text))))",
    "with_check": "(id = ( SELECT profiles.business_id\n   FROM profiles\n  WHERE ((profiles.id = auth.uid()) AND (profiles.role = 'owner'::text))))"
  }
]

table definion:

create table public.businesses (
  id uuid not null default gen_random_uuid (),
  name text not null,
  address text not null,
  phone text not null,
  currency text null,
  created_at timestamp with time zone null default now(),
  updated_at timestamp with time zone null default now(),
  owner_id uuid null,
  constraint businesses_pkey primary key (id),
  constraint businesses_owner_id_fkey foreign KEY (owner_id) references auth.users (id)
) TABLESPACE pg_default;

create table public.businesses (
  id uuid not null default gen_random_uuid (),
  name text not null,
  address text not null,
  phone text not null,
  currency text null,
  created_at timestamp with time zone null default now(),
  updated_at timestamp with time zone null default now(),
  owner_id uuid null,
  constraint businesses_pkey primary key (id),
  constraint businesses_owner_id_fkey foreign KEY (owner_id) references auth.users (id)
) TABLESPACE pg_default;

9 Replies

NinjaOP•3w ago

supbase client:

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
const serviceRoleKey = process.env.SUPABASE_KEY_SECRET!;

if (!serviceRoleKey) {
  throw new Error("Missing SUPABASE_SERVICE_ROLE_KEY environment variable.");
}



const supabaseAdmin: SupabaseClient<Database> = createClient(
  supabaseUrl,
  serviceRoleKey,
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  }
);

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
const serviceRoleKey = process.env.SUPABASE_KEY_SECRET!;

if (!serviceRoleKey) {
  throw new Error("Missing SUPABASE_SERVICE_ROLE_KEY environment variable.");
}



const supabaseAdmin: SupabaseClient<Database> = createClient(
  supabaseUrl,
  serviceRoleKey,
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  }
);

calling the query from next.js router handler:

 const { data: businessData, error: businessError } = await supabaseAdmin
      .from("businesses")
      .insert({
        owner_id: user.id,
        name: businessName,
        phone: phoneNumber,
        address: "To be updated",
      })
      .select("id")
      .single();

 const { data: businessData, error: businessError } = await supabaseAdmin
      .from("businesses")
      .insert({
        owner_id: user.id,
        name: businessName,
        phone: phoneNumber,
        address: "To be updated",
      })
      .select("id")
      .single();

i tried multiple methods from online and llms nothing helped so dropped here if anyone can please

garyaustin•3w ago

Are you sure you have a user session there? Usually right after a signup call there is no user session as they have not confirmed yet. (There is a user object but not a session) Also you have to meet select RLS on profiles for those policies you show to work.

NinjaOP•3w ago

1. i have turned off the confirmation mail thing 2. i have posted the complete rls policy above could you please check

garyaustin•3w ago

I looked at it and pointed out one thing. You have to meet select on profiles too.

NinjaOP•3w ago

oops sorry lemme check Now it makes sense, I guess. My signup flow is: → Supabase creates the user → Then I create a new row in the business table using auth.id → Then i create a new row in the profiles table using auth.id and business.id The issue is that the business table needs profiles.business_id, which hasn’t been created yet. So the SELECT query isn’t able to execute properly and returns null. But my question is: since I’m using the service role key, shouldn’t the RLS policies be bypassed?

garyaustin•3w ago

Not if you use the same client to signup a user. The user session takes over. Also you should never use a service_role key on a browser, or really bother with a client that will have a signed in user. Why don't you create your profile table with an auth.users trigger. That is what most do.

NinjaOP•3w ago

Yeah, but the trigger only knows the new user's ID. It has no way of knowing the businessName, ownerName, or phoneNumber that the user just entered in the signup form. I need that data to correctly create the business and profile records, so I skipped that option and went with this method instead. idk i was right but correct me if im wrong please.

garyaustin•3w ago

About what. I have no idea how or when you get your business id from the user. If you are only using signup and not OAuth then you can always pass that data in with the data option on signUp and the auth.users trigger function can get the data and set up any table rows needed. Otherwise you would need to do operations serverside, edge functions, or rpc call to security definer function if you have to bypass RLS.

NinjaOP•3w ago

changed the logic of RLS policy now working as expected! thank you so much for your insights really helped to solve the problem also added triggers 🙂

Gaming

Programming

need help with RLS policy

Did you find this page helpful?