Has anyone used the ADDRESS_STANDARDIZER extension? Im completely lost on how this works! 😤

I beat my head against a wall trying to figure this out, but the source column on my users table accepts an enum `('WEB', 'IOS', 'ANDROID')`; For some reason grabbing a value from a the raw_user_meta_data that returns one of the accepted enum values wasnt working. If I changed the source column to just text, it would save. Seems like a nuance with taking a jsonb value and having it recognized as an enum?

Hi, I want to create a row level security that allows only the creator to select/insert/... his records. I created the rls's for the first two tables. I could apply the following example on my second table as they are connected through a foreign key:

create 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?

Hey, I have a table with column of id and another one with info, how can I delete All the data in the info column and to keep the id column - so now i have id :1 info: null id:2 info : null

etc

I tried the docs but couldn't find a good solution

you will need to learn SQL sooner or later if you are going to work with supabase

Hey I have a table with column of id and

Hi everyone, I'm new to supabase and don't have a deep backend/sql experience. I'd like to pass a query param (like id) in my request and use it in my RLS Policy. how can I get it and then use it ? I couldn't find an answer on the web. Moreover, I'd like to go a bit further. This need occurs because I want ppl to be able to unsubscribe from an email list (from an email link) and I want to restrict the DELETE (thus SELECT in RLS) to the specific user (avoiding bad ppl abuses) I don't know how it usually works but I have first thought about passing an access_token-like param to verify the user, which seem the more secure way to do so. But could do that/generate an jwt_token without authenticating the user first? That is why I thought about checking req.email or .id === email/id's row. What are the best practices for this purpose plz? Thanks a lot for your help 🙏 (let me know if sth is unclear)

Hi! In the SQL Editor, I'm trying to remove rows with the following statement:

delete 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 online

Hello, it's my first project with supabase so I'm pretty new to this, I have a Twitch authentication and I'm trying to setup a trigger + function to update my

public.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 RLS

How does RLS work for non-public schemas? I wanted to expose some additional details for a few tables, so I placed those tables in a non-public schema and created corresponding views in the public schema. RLS is not enabled on any of my tables (for now). When I try to insert into a simple view, it works just fine. For complex views, I've created a trigger function to handle the insert/updates. However, on inserting I get the error

permission denied for schema "internal"

Hi In the SQL Editor I m trying to

Hi everyone!

I want to make new table for each new admin account in my app. App is planned as a multi tenant app so there could be really big amounts of data per each admin account. So I want to optimize db performance this way). Like to have reference table admin-data-example with it's data structure and to have admin1-data, admin2-data etc. tables for each admin that will mirror data structure from * admin-data-example* table and will catch up with admin-data-example* data structure changes

Does anyone have any thoughts how it can be done with Supabase?

I m building a multi tenant app so there

Hii i have a friends table, it looks like this

{
  "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(`*`);

You are asking in sql but showing js call, so js answer is here: https://supabase.com/docs/reference/javascript/select#query-the-same-foreign-table-multiple-times This assumes your profile is in the public schema and you have sender and receiver id columns as foreign keys pointing to that.

Hii i have a friends table, it looks like this

{
  "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) )

I have a question regarding Row-Level Security. Let's say I have a table named

users

. 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?

I just put a before update trigger function and set new.col = old.col (you many need insert also if you allow user inserts). You can check in the trigger function for auth.uid() != null to only block if coming from a user versus a service role role call to update it.

wow. that's an interesting approach. ummm, I like it! I'll give it a try. thanks @garyaustin 🙂

I'm displaying posts. I have a join table

likes (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.

Thread automatically created by @silentworks in #869405720934744086.

I have a policy that uses a function for the

USING

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"}

One more question while Im at it! Is it possible to add a database view to the graphql schema? Seems to only look at the tables in the public schema. Appreciate anyones help!

I have had an app running for over a year without problems, no changes in the app at all. About 2-3 months ago I started to get this error every 3-10 days which causes the app to just stop updating due to open transaction. It never happened until recently but nothing has changed in the code base and it keeps happening.

info: 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).

============================= Channel locked. Please ask in #1006358244786196510. =============================