#sql
Title
# sql
s
sudo killall windows
10/04/2021, 10:56 PMbasically what I'm doing is checking user input (would be easier if i could eliminate this step), and making sure it matches something i have stored in a db
s
Steve
10/04/2021, 11:03 PMbasically what I'm doing is checking user input (would be easier if i could eliminate this step), and making sure it matches something i have stored in a db
> Tldr; user sends discord id, i want to check that to make sure it is what i have in my db, and if not deny it
Hm, wouldn't a foreign key be enough for that case
If you cannot create an FK, another choice might be a constraint trigger
Like the following, change it accordingly
Copy code
sql
create or replace function
basic_auth.check_role_exists() returns trigger as $$
begin
if not exists (select 1 from pg_roles as r where r.rolname = new.role) then
raise foreign_key_violation using message =
'unknown database role: ' || new.role;
return null;
end if;
return new;
end
$$ language plpgsql;
drop trigger if exists ensure_user_role_exists on basic_auth.users;
create constraint trigger ensure_user_role_exists
after insert or update on basic_auth.users
for each row
execute procedure basic_auth.check_role_exists();s
sudo killall windows
10/04/2021, 11:27 PMForeign key?
Discord idea changes based on user too
I can access auth.uid() from this right?
s
Steve
10/04/2021, 11:51 PMYes
s
sudo killall windows
10/04/2021, 11:53 PMThanks for helping me out and developing Supabase (I assume that’s what the team role means)
s
Steve
10/04/2021, 11:56 PMThanks! Let me know if that works.
Btw, the key for capturing the user input is in the
newnew.raw_user_meta_data->>'provider_id's
sudo killall windows
10/05/2021, 1:02 AMNp. So simple as new.DiscordID? (Column that has the id)
this should be so easy
yet i cannot get it to work
i just get
Bad Request|:
solved the bad request
Solved it all!
All working!
and oh frick it's not actually working
agh the stupid trigger is causing it to fail and i do not know why
i wish there was logs so i could see the errors
sorry to bug you @User But why isn't this working 😅
Copy code
begin
if ((SELECT raw_user_meta_data->>'provider_id' from auth.users WHERE auth.users.id = auth.uid())=new."DiscordID") then
raise foreign_key_violation using message =
'unknown discord id: ' || new."DiscordID";
return null;
end if;
return new;
endit returns forbidden
i think it is something with
> new.DiscordID
j
jason-lynx
10/06/2021, 1:52 AMcant tell right now what the issue is, though you're probably right that the double quotes with discord ID is causing some problems.
maybe try to debug whether
new.DiscordIDs
sudo killall windows
10/06/2021, 12:15 PMProbably should have tried that 😅 Thanks!
s
Steve
10/06/2021, 7:11 PM> i wish there was logs so i could see the errors
When you do the request through supabase-js, there should be an error field with more details.
s
sudo killall windows
10/06/2021, 9:12 PMI did check that didn’t see much
i get
Copy code
}
code: "42501"
details: null
hint: null
message: "permission denied for table users"
<prototype>: Object { … }
status: 403
statusText: "Forbidden"speed troubleshooting now
Not the issue. hardcoded it and nothing. I guess its the other query
in the sql console this is what appears
is the problem that it is a column?
i tried adding ::text to the select and still doens't work
ree
(with a hard coded id)
s
stibbs
10/07/2021, 12:59 AMError code 42501 indicates that whoever is trying to execute the command doesn't have permission to do so. I.e. your row level security is blocking them
Are you trying to use your own
usersThe supabase one is
privatepublicprivate.userss
sudo killall windows
10/07/2021, 1:37 AMRLS is off for testing
Supabase one but the function is the one accessing it not the Supabase client
Maybe I just shouldn’t validate it
And instead just edit the data
s
stibbs
10/07/2021, 1:49 AMAs in you wrote a psql function that accesses the
private.usersThat still won't work as whoever is calling the function won't have permissions to do anything with
private.userss
sudo killall windows
10/07/2021, 2:37 AMFair
Guess I have to make a new table that auto updates lol
I got it working!
https://stackoverflow.com/questions/16102188/postgresql-insert-trigger-to-set-value?answertab=active#tab-top found this post that helped a lot
Then just created a public users table that made my select queries easier