mrwinbush
08/02/2022, 8:08 PMdrewbie
08/03/2022, 4:07 PMJomatom
08/03/2022, 4:17 PMcreate policy "Team members can update team details if they belong to the team."
on teams
for update using (
auth.uid() in (
select user_id from members
where team_id = id
)
);
Now I want to create a rls for the third table which is also connected via a foreign key to the second table.
table1 table2 table3
How can I apply the example above to join the second table first and then the first where I can access the user_id?Smirnovious
08/04/2022, 8:33 AMSmirnovious
08/04/2022, 8:33 AMSmirnovious
08/04/2022, 8:33 AMAlbert [tox/cis]
08/04/2022, 8:56 AMAlbert [tox/cis]
08/04/2022, 8:59 AMpixtron
08/04/2022, 2:53 PMMath
08/05/2022, 2:32 PMRikard
08/06/2022, 7:54 AMdelete from t1
inner join t2 on t1.id = t2.t1_id
where t1.id = 2;
Which returns Failed to validate sql query: syntax error at or near "inner"
Can someone see what I'm doing wrong? I can't seem to find the answer looking at resources onlineEquinox
08/06/2022, 7:54 AMpublic.users
table avatar_url
field when the auth.users
table is updated (when the user refreshes its login if I understood correctly)
However if I do this
begin
update public.users set avatar_url = new.raw_user_meta_data->>'avatar_url' where id = new.id;
return new;
end;
or even this
begin
update public.users set avatar_url = 'test' where id = new.id;
return new;
end;
I keep on getting the following error : ERROR A permission denied for table users SQLSTATE 42501
I have RLS enabled on my public.users
table with the following rules:
* UPDATE Can update own user data. (uid() = id)
* SELECT Can view own user data. (uid() = id)
Note that it also doesn't work if I disable RLSPragy
08/06/2022, 12:03 PMpermission denied for schema "internal"
garyaustin
08/06/2022, 2:37 PMYevhen
08/06/2022, 3:37 PMYevhen
08/06/2022, 3:44 PMYevhen
08/06/2022, 3:50 PMgaryaustin
08/06/2022, 4:25 PMdipankarmaikap
08/06/2022, 8:03 PM{
"id": 7,
"created_at": "2022-08-06T19:27:28.8552+00:00",
"sender_id": "e38ce1ad-1e45-4e34-b6f0-4b520467d1fb",
"reciver_id": "f00707ec-a837-49ca-a7c6-25218b67370d",
"status": 1,
"last_action_by": "e38ce1ad-1e45-4e34-b6f0-4b520467d1fb"
}
sender_id
, reciver_id
and last_action_by
all are id
of profile table. How can i retrive the name and other profile info instade of just the id for each of these.?
currently doing this
const { data, error } = await supabase.from("friends").select(`*`);
garyaustin
08/06/2022, 8:09 PMdipankarmaikap
08/07/2022, 7:47 PM{
"id": 7,
"created_at": "2022-08-06T19:27:28.8552+00:00",
"sender_id": "e38ce1ad-1e45-4e34-b6f0-4b520467d1fb",
"reciver_id": "f00707ec-a837-49ca-a7c6-25218b67370d",
"status": 1,
"last_action_by": "e38ce1ad-1e45-4e34-b6f0-4b520467d1fb"
}
In a function send_friend_request
I'm trying to check if the loggedin user and another uid
already have a table,.
currently doing this
if EXISTS ( SELECT 1 FROM friends WHERE reciver_id = auth.uid() or sender_id = auth.uid()
and (sender_id::text = send_friend_request.account_id or reciver_id::text = send_friend_request.account_id) )
eunjae
08/07/2022, 8:02 PMusers
. I want to give full access to user for their own row. However, there is one column that is concerning, which is plan
. Its value can be either free
or pro
. Of course, I don't want my users to update this column by mimicking an API call. How can I prevent it?
Possible solution 1. Column-level security? I guess it's a thing in PostgreSQL? I haven't looked into it in depth, but I found this article: https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql
Possible solution 2. Have a separate table. Instead of including plan
in the users
table, I can have a separate table like user_plans
and user gets only read access to it, and the admin can, of course, have full access. Then the client calls an API to the server, and the server authenticates the call, and then it calls the Supabase API with the admin key. It should work, but introduces a bit of overhead of a new table.
What is your approach? What's the known best practice in Supabase's land?garyaustin
08/07/2022, 8:11 PMeunjae
08/07/2022, 8:14 PMSpaceface16518
08/07/2022, 10:35 PMlikes (post, user)
. I want to display the total count of likes on the post as well as whether the current user has liked it. I can accomplish the first using a GROUP BY
.
postgresql
select posts.*, count(likes.user) from posts
left join likes on post.id = likes.post
group by posts.id
I'm having more trouble including the second one in this query. I basically want to check whether likes.user = auth.uid()
for any of the joined likes. Is there a way to do this? I investigated DISTINCT CASE WHEN
and bool_or
but I'm not sure how to implement this.Needle
08/08/2022, 12:24 PMdrewbie
08/09/2022, 2:13 PMUSING
and its working as intended, however the response from the policy is a 404 since technically it doesnt find a matching record in the function. How do I get the RLS error to bubble up to the database query instead of just returning a 404?
create policy "Products can only be updated by a shop admin or moderator"
ON public.products for UPDATE
USING (
user_belongs_to_shop(auth.uid(), shop_id)
AND
(
shop_user_has_role(auth.uid(), 'ADMIN'::user_shops_roles, id)
OR
shop_user_has_role(auth.uid(), 'MODERATOR'::user_shops_roles, id)
)
);
When I try to make an update that'll return false for the USING
I get the following --> {"body": null, "count": null, "data": null, "error": [], "status": 404, "statusText": "Not Found"}
drewbie
08/09/2022, 4:14 PMMarky
08/10/2022, 12:51 PMinfo: current transaction is aborted, commands ignored until end of transaction block {"length":144,"name":"error","severity":"ERROR","code":"25P02","file":"postgres.c","line":"1101","routine":"exec_simple_query"}
I am primarily using postgres client to work directly with db rather than going through API as I need atomic transactions and python Library wasn't stable (and has bugs).Olyno
08/11/2022, 1:14 PM