https://supabase.com/ logo
Join Discord
Powered by
# sql
  • p
    I tried 
    set role authenticated;
    when logging in via the db client, 
    select current_user, session_user;
    gives 
    postgres, postgres
  • c
    Ok, I think the reason why this doesn't work is because the postgres role (which you use to login) is NOT a member of the authenticated role. You can workaround that by making it part of the authenticated role OR if you grant superuser priviliges to the postgres role (for those 2 things, you need to do it from a superuser login, I think the Supabase dashboard runs with such a user). This post has instructions about how to do that: https://www.slip.so/tutorials/row-level-security-performance-in-supabase (credit goes to @User )
  • p
    Edit: Resolved I tried the solution mentioned in the article. Got the authenticated role and auth.uid() successfully, but I'm unable to access the tables for some reason Create the table as postgres
    Copy code
    sql
set role postgres;
select current_user, session_user;
drop table if exists public.profiles;
create table public.profiles
(
    user_id   uuid primary key references auth.users,
    full_name text,
    is_public boolean default true
);
insert into public.profiles values ('717cd048-5cf6-4d51-b43e-e71ae1ade721', 'Pragy Agarwal', true);
select * from public.profiles;
    Output
    Copy code
    postgres,postgres
717cd048-5cf6-4d51-b43e-e71ae1ade721,Pragy Agarwal,true
    But when I switch to the authenticated role
    Copy code
    sql
begin;
    set local role authenticated;
    set local request.jwt.claim.sub = '717cd048-5cf6-4d51-b43e-e71ae1ade721';
    select current_user, session_user;
    select auth.uid();
    select * from profiles;
end;
    output
    Copy code
    authenticated,postgres
717cd048-5cf6-4d51-b43e-e71ae1ade721
ERROR: relation "profiles" does not exist
    The role switch works, but I can't access the table. Edit: Resolved Turns out, that deleting and re-creating the public schema is a bad idea as it removes all the permissions and roles setup that PostgREST needs to work ๐Ÿ˜…
  • s
    Rule of thumb, if you didn't create that schema, don't delete it. Also never modify any schema created by the Supabase team or add tables to them (besides 
    public
    )
  • p
    Any way to debug Database Errors? I'm trying to use the trigger for creating a new profile as mentioned here https://database.dev/handle-new-user but the moment I set a trigger on auth.users, the user registration stops working. "Database error saving new user"
  • p
    It seems that none of the following work inside the trigger ๐Ÿ˜ฆ - 
    new.raw_user_meta_data->>'avatar_url'
    - 
    new.raw_user_meta_data->>'full_name'
    - 
    uuid_generate_v4()
    - 
    uuid_generate_v4()::text
    weirdly, 
    random()::text
    works though any way to generate the same random value for 2 columns when inserting a value via sql?
    s
    • 2
    • 19
  • s
    Using json data inside of a trigger
  • j
    Hey guys! I have a table called companies - which holds some metadata about a specific company. A user from the users table can have a relation to one or many companies. I also have a table called connections, which holds a reference to a user_id and a company_id (from the companies table). Is it possible in some way, to make a RLS policy on the companies table - which only lets users read/write/update on companies that exists in the connections table, where user_id = uid()? If so, can someone guide me in the right direction of writing that policy?
  • p
  • p
    a nice video explanation:

    https://www.youtube.com/watch?v=Ow_Uzedfohkโ–พ

  • j
    Many thanks dude! Do you have any experience with it? Good to have someone to ask if I run in to any troubles implementing it ๐Ÿ™‚
  • p
    haven't used it myself, but I should be able to help you with it ๐Ÿ˜… Do let me know if you get stuck
    j
    • 2
    • 7
  • j
    RLS joins
  • s
    Can someone point me to a good method for creating dummy data with postgresql? I have a table like so
    Copy code
    sql
CREATE TABLE jobs (
  id uuid DEFAULT uuid_generate_v4 () primary key,
  invoice_id uuid references public.invoices not null,
  user_id uuid references auth.users not null,
  application_url text,
  business_name text not null,
  content_description text not null,
  content_how_to_apply text not null,
  content_who text not null,
  job_location text,
  job_type text not null,
  title text not null,
  timezone text,
  work_style text not null,
  valid_until timestamp with time zone,
);
    The confusing bit to me is that in this table you either populate job 
    job_location
    OR 
    timezone
    depending on the value in 
    work_style
    I have no idea if I'm on the right path, but so far I have:
    Copy code
    sql
insert into public.jobs (
  invoice_id,
  user_id,
  application_url,
  business_name,
  content_description,
  content_how_to_apply,
  content_who,
  job_location,
  job_type,
  title,
  timezone,
  work_style,
  valid_until
)
select 
  'manually created invoice_id',
  'manually created user_id',
  'https://www.google.com',
  MD5(random()::text), -- business_name
  MD5(random()::text), -- content_description
  MD5(random()::text), -- content_how_to_apply
  MD5(random()::text), -- content_who
  -- how do i make some populate job_location?
  'ft', -- job_type
  MD5(random()::text), -- title
  -- how do i make some populate timezone?
  -- work_style needs to be 'remote' or 'flex'
  now() + interval '5 day'
from generate_series(1, 100);
    g
    • 2
    • 18
  • s
    generate testing data in psql
  • s
    It would seem like raising exceptions don't get passed back down through the Supabase library, I'm trying to raise an exception on a trigger function on the 
    auth.users
    table and the only error I'm getting is "Database error saving new user" while I was expecting the message from the exception to show instead.
    c
    • 2
    • 9
  • s
    Does anyone know if you can use 
    case
    or 
    if then
    logic in a postgres generated column?
  • s
    Copy code
    sql
-- currently like this
priority smallint default 0

-- I want to change it to something like this
priority smallint generated always as (if x is not null and y is not null then 1 else 0 end) stored;
    s
    • 2
    • 3
  • s
    Raising exception not working with signUp and signIn
  • s
    Generated column logic
  • p
    Signed URLs
    s
    g
    • 3
    • 5
  • m
    alright, So i'm kind of an SQL noob here, wondering how i format a response from a query so that each selected field is it's own column, anybody have any ideas or know where to point me?
    s
    • 2
    • 17
  • s
    Format field
  • a
    So, I'm trying this:
  • a
    Nooby RLS question
    s
    • 2
    • 8
  • t
    Hello all, I'm having trouble running knex migrations against the supabase DB. While my script runs great in the supabase SQL editor, it fails to run in knex when removing a trigger from the auth.users table. The same script creates that trigger fine, but cannot delete it. Is this a known limitation?
    s
    • 2
    • 1
  • v
    If you created the trigger from the dashboard editor, it belongs to the super user, and probably the postgres user doesn't have permission to modify it
  • s
    Issue with removing a trigger on auth.users table with migration
  • t
    I've created it from pgadmin using the postgres user. Then I try to drop it using the postgres user and it fails.
  • s
    Check my message in the thread above
1...232425...52Latest