Failed to run SQL Query, what is wrong with my function?
I'm trying to create a small function to return just the column user_metadata from auth.users. The select seems to work on it's own, but when I try to fun the code in the picture, I get a 'Failed to run sql query: syntax error at or near "END"' and I can't find what I did wrong... Can someone help me?
7 Replies
You should always post code with triple back ticks versus an image so the code can be run thru a syntax checker....
In this case it looks like you are missing
; after FROM auth.usersThank you, it was just that! I know about the back tick, but even if I use them, I often get bad formating in Discord... But I'll try again! Thank you!
Yeah it helps because I could have just grabbed your code and run it thru my IDE with Datagrip to get more detail than the UI error.
Alright! Sorry for that! Thanks again!
Another quick question, When I try:
I receive "permission denied for table users"... How would I use my RPC?
Yeah, I did not read your function for what it is doing... You can not access auth.users as a normal rpc call with user privilege. The function needs to be declared "security definer", which is an option in the UI, but I don't think you can change it once you have the function with the UI. "security definer" means your function runs as the creator/owner of the function versus the caller of the function.
A word of caution you would be allowing anyone to access all your uses metadata if you do that and don't put code in to restrict it.
In my case, isn't the request done with enough permissions since I'm using the service key?
So I should make the function Security Definer but check the permission of the user to alow or not the function?
The service key should be good
But it is late, and I always hate accessing auth.users and what works and does not. I'd try adding "security definer" to your function. If it works, the service role is not enough. You are not just bypassing RLS but needing access to auth schema thru the API.