https://supabase.com/ logo
#sql
Title
# sql
s

sudo killall windows

10/04/2021, 10:56 PM
basically 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 PM
basically 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 PM
Foreign key?
Discord idea changes based on user too
I can access auth.uid() from this right?
s

Steve

10/04/2021, 11:51 PM
Yes
s

sudo killall windows

10/04/2021, 11:53 PM
Thanks for helping me out and developing Supabase (I assume that’s what the team role means)
s

Steve

10/04/2021, 11:56 PM
Thanks! Let me know if that works. Btw, the key for capturing the user input is in the
new
keyword on the trigger, for your case I think it'd be like
new.raw_user_meta_data->>'provider_id'
s

sudo killall windows

10/05/2021, 1:02 AM
Np. 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;
end
This is what I have in the dashboard
it returns forbidden
i think it is something with > new.DiscordID
j

jason-lynx

10/06/2021, 1:52 AM
cant 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.DiscordID
is indeed the issue by replacing the whole thing with a hardcoded sample discord ID you have in your data? if it really is the cause of the issue, then the error should go away and you'd know the cause
s

sudo killall windows

10/06/2021, 12:15 PM
Probably 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 PM
I 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 AM
Error 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
users
table or the one that supabase automatically creates?
The supabase one is
private
, which means it is locked down completely other than the authentication APIs. If you need to store other data or interact with a users table, I'd suggest creating your own
public
users table and it can reference the
private.users
table
s

sudo killall windows

10/07/2021, 1:37 AM
RLS 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 AM
As in you wrote a psql function that accesses the
private.users
table?
That still won't work as whoever is calling the function won't have permissions to do anything with
private.users
?
s

sudo killall windows

10/07/2021, 2:37 AM
Fair
Guess I have to make a new table that auto updates lol
I got it working!
Then just created a public users table that made my select queries easier