dont know if this still works since it's been a while, but you can also use something like this:

SET request.jwt.claim.sub = 'MY USER ID';
SET ROLE authenticated;

Anyone managed to use a COPY command in the seed.sql to seed the db from the csv file? I keep getting this error:

Error: Error resetting database: ERROR:  could not open file "./my-file.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

I had troubles before using

COPY

with

stdin

in the seed file. I created a separate

manual_seed.sql

that I feed the database after each

supabase start

or

supabase db reset

using

psql

sql
psql postgresql://postgres:postgres@localhost:54322/postgres < manual_seed.sql

^ something like that may help

thanks a lot, I will check that out 🙂

hi guys, how can i set null references when deleting a foreign key?

@zecik11 Your CSV file is probably not copied into the docker container, hence why it can't find it. Running it from outside after the db is initialized seems like a good alternative 👍

I am trying to write a policy that allows a user with an admin role set to SELECT from a table. The role data is stored in a separate table compared to the table I am writing the policy for.

(( SELECT user_roles.admin
  FROM user_roles
  WHERE (uid() = user_roles.id)) = true)

I am trying to subquery the role (which is currently a boolean value in column "admin"). The above I tried does not work.

Ah found the problem. My user_roles table policy did not allow the user select access so the policy above failed.

If you don't want to change the policy you can use a function to return true/false and use "security definer" in the definition. Then it will skip the policy to do the test and user will not have direct access. But if you only allow read by user id policy, not much useful info they could see anyway.

hey, might be a stupid question - but if I have a field in my profiles table that has a foreign key relation with the email in the auth table, how can I make sure that the email here is updated every time a new user is created?

should I also add an email in my function that handles creating a new id in the 'profiles' table each time a new user appears in the auth table?

Update email in profile table

Is it possible to have a PLPGSQL function take an entire row as a single param to insert it? and would it even be a good idea? e.g. something like this:

sql
create or replace function create_planet(new_planet "planet")
returns varchar
language plpgsql
as $$
declare
  new_row bigint;
begin
  insert into public."planets"(new_planet)
  values(new_planet)
  returning "id" into new_row;
  return new_row;
end;
$$

#869405720934744086 is it possible to add security rules for a specific column in a table? As an example, is it possible to block changing the "isVerified" column in the profile table? ( And also we need to let users change their other details like 'Bio'. ) Is it posssible to the both?

guys if you are using realtime and u have postgresql logs on and you have issues like me on disk usage I did a pull request where I allow the user to apply his own PostgreSQL configuration: https://github.com/supabase/supabase/pull/5537

Security rules for column

Hi all! Exploring the Supabase and it looks really nice, but have a question regarding the SQL part. I'm implementing the custom auth flow. I also want to support Supabase native authentication methods at the same time. So the idea is to plug users from my flow into

auth.users

table so that I can reference both native auth and my auth users from the same table. I dont get how to add documents into

auth.users

. Should it be some custom trigger?

Hi everyone! I tried out the snaplet "Supabase Clone Environments" tutorial from the last newsletter. But I get now when I try to create a user a 502 error. Is there something that I have to execute after the setup to make the authentication work?

Hi! I'm learning my way through setting up RLS and wondering: what's the best way to handle deep object hierarchies? We have a "document" that is a 4-levels deep object hierarchy and we want to restrict updates on all entities of a document to the contributors of that document. We have a

contributors

table that maps

user_id

to

document_id

Two solutions I have so far: 1. Define policies that perform joins to find the

document_id

and check that the user is a contributor of the document. At the deepest level, this is a join across 5 tables (4 levels + contributors table) within RLS policies. 2. Keep a

document_id

column for entities at each level of the hierarchy, since that value will never change after creation anyways. Breaks normalization, but RLS policies become very simple & efficient. So far, I'm leaning towards 2. but would love input from people with more experience!

Whats the best way to handle deep object hierarchies RLS?

Hey guys, a policy so that users can view only the email?

Like, all users can view only the auth.users.email and not ALL columns

view on ly emails

admin user access

Would it make sense to add

WITH CHECK

to this policy as well?

I'm having a strange issue when extracting a value from the JSON

auth.users.raw_user_meta_data

in a trigger. I save a user name in there during sign up and use it to populate a profile. Here's the trigger:

pgsql
create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
    insert into public.users(id, user_name, email)
    -- this works, but populates the `user_name` column with quoted text (as expected)
    values (new.id, new.raw_user_meta_data->'user_name', new.email);
    -- this does not work:
    -- values (new.id, new.raw_user_meta_data->>'user_name', new.email);
    return null;
end;
$$;

create trigger on_new_user after insert on auth.users
    for each row execute function public.handle_new_user();

For some reason, using the

->>

operator to extract the value as unquoted text seems to trigger a database error. Anyone has an idea why?

Update: adding

extensions

to

search_path

did the trick. If someone knows why, I'm interested in the explanation!

hey everyone, I have two tables,

requests

and

tasks

. The requests table contains requests to run tasks. A request will have a status of

pending

or

approved

or

rejected

. How do I write a policy that prevents anyone from inserting a request for task when a similar request with a same task id have a status

pending

? Thenks

Upsert?

Something like this? (haven't tried against a test case, so there may be syntax errors but I think the logic is sound)

pgsql
create policy "Do not schedule tasks twice"
  on requests for insert
  with check (
    not exists (
      select 1
      from requests as reqs
      where reqs.status = 'pending' and reqs.task_id = task_id
    )
  );