Supabase #sql

Peanut

08/26/2021, 9:28 AM

Copy code

SELECT getIsUserEditorOrAdmin(auth.firebase_uid()) = TRUE
> ?column?
> false

jason-lynx

08/26/2021, 9:28 AM

yep so there's the issue, it's not returning true

jason-lynx

08/26/2021, 9:29 AM

SELECT role = 'admin' OR role = 'editor' FROM userAdminMeta WHERE id = auth.firebase_uid()

what do you get?

Peanut

08/26/2021, 9:29 AM

Copy code

SELECT auth.firebase_uid()

> firebase_uid
> null

Looks like an issue

Peanut

08/26/2021, 9:30 AM

Maybe I cant call my custom function with my role or something

Peanut

08/26/2021, 9:30 AM

auth.uid()

also returns

null

jason-lynx

08/26/2021, 9:30 AM

yeah then that's most likely the case

Peanut

08/26/2021, 9:32 AM

Could it be because I created my functions/policies using

postgres

not superuser?

jason-lynx

08/26/2021, 9:33 AM

ok i deleted my messages above because what i said was probably wrong

Peanut

08/26/2021, 9:35 AM

I feel like using the web console is screwing it up cause I cant do multiple requests

jason-lynx

08/26/2021, 9:40 AM

ah something might have changed recently that doesnt allow it anymore

Peanut

08/26/2021, 9:46 AM

I emailed support about it

Peanut

08/26/2021, 9:46 AM

Thanks a lot for your help

kennethcassel

08/26/2021, 12:09 PM

You can definitely use functions inside of policies, we're doing it today. One thing we found is that if you have multiple tables that reference each other in their policies, you can get a 404 error with a message that says something about infinite recursion being detected in one of your policies. Here is a good github discussion that goes into using functions in policies https://github.com/supabase/supabase/discussions/811

EmreCan

08/26/2021, 12:46 PM

Hi why i am getting this error when i am trying to add foreign key to tables Error updating foreign key: foreign key constraint "sub_categories_belongs_fkey" cannot be implemented

kyrelldixon

08/26/2021, 5:13 PM

I'm trying to create a policy where I can limit the total number of rows a user can enter into a table. any tips?

Scott P

08/26/2021, 5:59 PM

Something like this may work:

Copy code

sql
(SELECT CASE WHEN (
        SELECT COUNT(*) FROM my_table
        WHERE my_table.user_id = auth.uid()
        GROUP BY my_table.user_id
    ) <= 50
) THEN true ELSE false end)

replace

my_table

with the name of the table you're adding the policy to, and

user_id

with the name of the column that contains the user ID.

would be if you want them to be able to create up to 50 rows maximum in the table.

Scott P

08/26/2021, 6:00 PM

It could be quite slow if you've got a lot of rows in the table though, but indexes and partitions might help with that

jon.m

08/26/2021, 8:10 PM

If you need to generate a bunch of dummy data for testing, try the generate_series funciton: insert into users (city, fullname) select 'jersey city' as city, 'robbert l' as fullname from generate_series(1,10000);

Scott P

08/26/2021, 8:22 PM

Today I learned 😄

jon.m

08/26/2021, 8:22 PM

Trying to up my sql skills

Peanut

08/26/2021, 11:38 PM

Thanks but I am getting no error messages at all. Just a 404 without a body. Not very helpful at all

Peanut

08/27/2021, 12:06 AM

Why do I get this postgREST error?

Copy code

"message": "invalid input syntax for type uuid: \"jAdocMIsWmRJg5JomxDNdHjNGFJp\""

I am casting

auth.uid()

to text:

Copy code

CREATE FUNCTION public.getisusereditororadmin(id TEXT)
RETURNS BOOLEAN
AS $$
    SELECT (SELECT role = 'admin' OR role = 'editor' FROM userAdminMeta WHERE id = $1) IS NOT NULL
$$ LANGUAGE sql;

CREATE POLICY "Users can update their own profile OR staff can update any profile."
    ON public.users
    FOR UPDATE USING (
         auth.uid()::TEXT = users.id OR
        public.getisusereditororadmin(auth.uid()::TEXT)
    );

If I modify my policy to not cast to

TEXT

I get an error:

Copy code

function public.getisusereditororadmin(uuid) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

silentworks

08/27/2021, 12:14 AM

That's because your function is expecting TEXT as the id, change that to UUID instead and remove the

TEXT

cashing on the

auth.uid

Peanut

08/27/2021, 12:23 AM

I found that my TEXT is not a valid UUID so it could never be cast back/forth

Peanut

08/27/2021, 12:26 AM

Another thing: why does setting my local role to "authenticated" mean my policy fails?

Copy code

BEGIN;
SET LOCAL request.jwt.claim.sub = 'a user id';
SET LOCAL ROLE authenticated;

UPDATE PUBLIC.users SET username = 'My username' WHERE id = 'my user id';
COMMIT;

If I comment out

SET LOCAL ROLE authenticated;

the query succeeds but when it is there, it fails

Peanut

08/27/2021, 12:43 AM

Do I need to allow

authenticated

role complete write access to my tables? I have RLS policies for ALL tables

jason-lynx

08/27/2021, 1:08 AM

hmm wait do you have a SELECT policy for public.users?

jason-lynx

08/27/2021, 1:08 AM

both SELECT and UPDATE need to be allowed for an UPDATE to happen

jason-lynx

08/27/2021, 1:09 AM

you shouldnt have to - only need to allow for the tables you're referencing in the query