User is undefined (FK) with inconsistent behaviour

[ivanasetiawan]

Bug report

I have a table row inventory_product and one of the columns is user_id (FK to user table).
As you can see from the images below, it's pretty simple and straightforward:

This is the data from user table:

This is the inventory_product with FK to that user:

However, when I try to print the name, email, and id of the user who created the row, they all return to undefined:

This is weird because you can see it's not undefined on the DB.

Describe the bug

I have a table with rows; one of them is user_id, which is an FK to user table.
Some rows work as expected, I can see the name, email, id as expected. However, some rows just show undefined even when there's a user attached to it.

To Reproduce

I cannot reproduce this. I have debugged the problem locally, and all user data returned properly. Therefore, I am very confused why on production, the user name, email, and id are undefined when there is clearly a user attached to the row.

Expected behavior

I should be able to see the user name, email, id as expected.

System information

• OS: macOS
• Version of supabase-js: 2.39.7
• Version of Node.js: 18.17.1

[encima]

Hey there,
Thanks for reporting this, let's try and figure out what is going on. Is the database schema locally identical to the one that is hosted?
Can you provide:

• The call you are making
• Any relevant RLS policies for the table(s)

Thanks!

[ivanasetiawan]

Hi @encima,

Thank you for the reply! The schema is identical, but not the rows inside the tables.
The call I am making:

const productInventoryId = event.params.productInventoryId;

const {
    locals: { supabase }
} = event;

const { data: productInventory, error: errProductInventory } =
    await supabase
        .from('inventory_product')
        .select(`user: user_id (name, email, id)`)
        .eq('hash_id', productInventoryId);

It's straightforward and mostly works for other users. It seems like only 1 particular user got undefined. (as far as I could find).

Any relevant RLS policies for the table(s):
I assume the SELECT one is relevant:

on "public"."inventory_product"
to anon, authenticated
using (
  true
);

and I also use this pattern for enabling UPDATE and INSERT for authenticated users based on company_id:

  ((((auth.jwt() -> 'user_metadata'::text) ->> 'company_id'::text) IS NOT NULL) AND (((auth.jwt() -> 'user_metadata'::text) ->> 'company_id'::text) <> ''::text) AND (((auth.jwt() -> 'user_metadata'::text) ->> 'company_id'::text) = (company_id)::text))

On a side note, I got some errors notification on this, one example:
Table public.inventory_product has a row level security policy Enable insert for authenticated users based on company_id that references Supabase Auth user_metadata. user_metadata is editable by end users and should never be used in a security context.

Detects when Supabase Auth user_metadata is referenced insecurely in a row level security (RLS) policy.

Could this be something? if so, how do I set it up so that only authenticated users can insert, update, or delete rows in inventory_product based on the company_id properly? The goal is to ensure that only users from the correct company can modify specific rows in inventory_product. Each row in inventory_product has a company_id, and each logged-in user is associated with a company. Users should be able to insert, update, or delete rows only if their company_id matches the company_id in the row.

Thank you so much @encima