why i have this error

sql
select * from storage.buckets.id = "hiking-picture"::text

``Failed to validate sql query: syntax error at or near "="``

I made person table that gets row on trigger of new auth user where i make field user_id for auth id and a auto generated uuid for person_id. (I guess thought maybe dont tie person id to auth id in case move account to diff auth user). Anyway for storage I am trying to create a crud policy but I dont believe you can do declare, begin, end I also have a person_roles table with person_id, role_id like 'admin' I have auth.uid in policy do i need to use that to get the person id and then check if there is a person_id, 'admin' row in person_roles How might I accomplish this?

declare
  _person_id uuid;
begin
  select id from public.persons where user_id = auth.uid()::text into _person_id;
  select 1 from public.person_roles where person_id = _person_id and role_id = 'admin';
end;

This is my not correct but gives idea of aim

error in select

I have a purchases table which connects users to digital products they've purchased. How do I get RLS set up so that only users who have purchased a product can view the product info? Somewhat programmatically,

view product_info if purchases has row with product.id and user.id

. Hope that makes sense.

I have a purchases table which connects

I have 2 tables

commands

and

user_access

how can I make a policy so that when selecting from the

commands

column, users will only be able to retrieve the rows that have the same

server_id

value to what has been specified in the

user_access

table linked with their

user_id

. The

commands

table has the columns

id

and

server_id

The

user_access

table has the columns

id

,

user_id

and

server_id

https://supabase.com/docs/guides/auth/row-level-security#policies-with-joins is probably close to what you are looking for.

Hello, I am looking to use the supabase js fetch to get this result, but I cannot wrap my head about how I could do something like this:

sql
SELECT season
FROM battle
INNER JOIN team ON battle.id = team.battle_id
WHERE wizard_id = 3
GROUP BY season

Any ideas? I would like to avoid creating a view or rpc if possible

The group by can not be done with standard API/PostgREST call. So either have to do view/rpc or handle that part after the fact in js code.

@garyaustin I see, too bad. Any chances I could do a select distinct ?

If you ever have detail questions on what can be done in the API googling PostgREST and your need or going to it's github or postgrest.org can usually get you an answer: Googling postgrest and distinct got these hits: https://github.com/supabase/postgrest-js/issues/206 https://github.com/PostgREST/postgrest/issues/915#issuecomment-1002232582 PostgREST has been hesitant to add a bunch of detail SQL because of performance and security concerns, including denial attacks.

I kept reading a bit and apparently distinct now is syntaxic sugar over group by, so it makes sense not to see it either

For anyone passing by, this one was a good read too https://github.com/PostgREST/postgrest/issues/915

@garyaustin thanks for the help!!

Hello! I am working on converting a project from firebase to Supabase and I'm trying to set up some rules to allow for content sharing between accounts. My strategy is to have table that includes a column that is an array of JSON objects with usernames and ids that I can check against the currently authed user. I'm having trouble figuring out how to write an RLS statement that will allow me to check against the content of an array of JSON objs. Any help would be greatly appreciated

Not sure having both an array column and then json objects is going to be the best way to handle RLS lookups and updates performance and sanity wise. If you need to though, you should focus on generating a straight SQL query and testing that before you worry about putting it in an RLS statement. RLS policy is just a where with SQL. Postgres has array and json/b operators and functions that can do almost anything you could want. Just google those to get started.

Ok thanks for the tip!

So I'm a front end guy (if that wasn't clear lol) and trying to get more experience on the backend. Would you set up a separate table to handle sharing instead of trying to get all that info into one column?

Yes tables with indexes are much faster than processing data in the rows. But alot really depends on how much data, and the structure you need, and how you need to search and update it.

Yeah fair enough. Still trying to get a sense of where that line is. My sense is that the number of shares would be low, hence my initial plan to stick that data in a column rather than a separate table. Maybe the better move would be to simplify to just an array of IDs and just read that. Then it wouldn't be reading JSON. Anyway thanks for the perspective

Hi all, I have problems acquiring a connection when I have LOTS of tables and connections.

Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

increasing acquireTimeoutMillis on the pool doesn't help, the connection attempt times out after 60000ms anyways

I'm using Strapi - but they claim it is unrelated to the framework

Pool issue

hello, I'm new to PostgreSQL, is there a way to trigger a delete on

public.users

when a user is deleted manually in

auth.users

? I created a trigger on auth.users after a user is deleted and called this function, but it's not working

begin
  delete from public.users where id = auth.uid();
  return auth.uid();
end;

Also on the same note, is it me or this process of syncing auth.users with public.users is pretty cumbersome? You have to create a public.users table to if you want to check if an email is already registered, because you cannot query auth.users directly, so you have to create a trigger for that. For every sync between these table a trigger is required. Am I missing something? It seems that instead of helping with the streamlining the users auth handling, you have to start spending a lot of time looking for these kind of solutions for these type of issues.

Auth.uid() is only used with PostgREST (used for SB database API) calls. In a delete trigger function you need to use old.id. This assume you are using the UI or auth function to delete.

Auth uid is only used with PostgREST

How would I run this query in supabase js?

select user_access.id, profiles.avatar from user_access inner join profiles on user_access.user_id = profiles.id

Thanks, that did not seem to work but not to worry ive found another solution. I have another issue where I have a table that has a column with an array of user ids, how would I make a policy to allow users who's user id is in that array to access that row?