Hello Harry, thank you for replying. 🙂 I am not setting any variables since I just use the pgjwt extension directly in the VALUES for the INSERT INTO. 🙂 It was just a question in regards to the SECURITY DEFINER (

SECURITY DEFINER SET search_path = public, extensions

), which I HAVE to have otherwise the Auth stuff wont work, and the extension does not either. (Inviting an user via the dashboard just gives an error without it). More or less just wanted to ask if adding extensions to the DEFINER is an okay practice.

If it's only executed by the server it's fine e.g. a trigger

If it can be called by a user then it's not good

It is just a trigger on INSERT. 🙂

CREATE TRIGGER on_user_signup
    AFTER INSERT
    ON auth.users
    FOR EACH ROW
EXECUTE PROCEDURE handle_new_user();

I just felt like no "security" would be needed if it is the server that uses the function, which it is. Guess I still am a bit confused what this SECURITY DEFINER stuff is exactly.

So its either

SECURITY DEFINER

or

SECURITY INVOKER

it means should the functions code be run as the person who made it or as the person running it.

so DEFINER is fine for server only INVOKER is wanted for function that the public can run

Hmm, guess I understand just a little bit maybe 😅 . I'm gonna read a bit up about it. I'm good for now knowing it is just fine with triggers. Thank you! 🙂

Np!

📖

I managed to get rid of the error by manually removing the

schema_migrations

table from the remote supabase database. When running

supabase start

I get now an error for a custom enum type that I created, e.g.

Error: Error running SQL: ERROR:  type "widget_type" does not exist

. The type creation is part of the

[...]_link.sql

migration but appears at the end of the file. When I manually move it at the top, the previous error disappears but I now get a relation error for some tables, e.g.

Error: Error running SQL: ERROR:  relation "public.group" does not exist

. I guess I'm doing something completely wrong here 😅 Would appreciate any hints!

I've used it as part of subzero before at work that uses sqitch though I'm keen on trying now using sqitch directly for my side projects on supabase.

In my last three supabase projects I've started using Prisma.io migrations and it works pretty well for a single schema. It brings somewhat automated migrations based on introspection and schema diff

Nice. Earlier I was thinking about migra though it seems the suggested method https://github.com/supabase/cli/tree/main/examples/tour . I'll take a look at Prisma if this is not neat.

That's something new, thanks for the resource

Hi guys, I tried to use postgrest docker image to connect the supabase database, but seems the database dont accept the connection. But I am able to use pgAdmin to connect the database. Any idea?

I saw there's a vote for postgres extensions discussion on Github that includes pg_hashids. Does anyone know if it would be possible to implement something like that using the Functions/Triggers that are currently in alpha? I'd love to set some short hash ids to use in URLs for some of my content. https://github.com/supabase/supabase/discussions/679?sort=top#discussioncomment-761401

Generating Hash Ids

Hey everyone, I get the error

ERROR: must be owner of table XX (SQLSTTE 42501)

when trying to run

supabase deploy

(the migration only adds a field to table XX). If I set the

postgres

user on app.supabase.io to be a SUPERUSER it works (

ALTER USER postgres WITH SUPERUSER;

) but this doesn't feel right. Does someone know what would be the recommended way here to apply the migrations on the remote database?

are you still having trouble with this?

hey everyone. Very pleased to find this discord. I'm trying to create a table where a logged in user can insert records for themself and they can view it and delete it, but no one else can however having trouble getting the policy to work: sql/policies

create table public.keys (
  id uuid references auth.users not null,
  inserted_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  address text,
  data text,
  primary key (id)
);

alter table public.keys enable row level security;

create policy "Users can insert their own keys."
  on public.keys for insert
  with check ( auth.uid() = id );

create policy "Users can view their own keys."
  on public.keys for select
  using ( auth.uid() = id );

CREATE POLICY "Users can delete own keys."
ON public.keys
FOR DELETE USING (
  auth.uid() = user_id
);

javascript from webbrowser

//from webbrowser
    const { response, error } = await supabase
      .from('keys')
      .insert([
        { address: address, data}
      ],
      { returning: 'minimal' })

returns me

'new row violates row-level security policy for table "keys"'

any help why this doesn't work would be awesome, thank you - I wondered about just checking if the user was authenticated to insert the record with

create policy "Users can insert their own keys."
  on public.keys for insert
  with check ( auth.role() = 'authenticated'::text );

but in that case I would have to set the auth user ID manually so seems that the above is better?

Wrong answer, sorry need coffee. Added what I think is the answer in the thread below (missing id column on insert).

thx for responding, please can you elaborate - don't quite undertstand that first part - what do i need on insert?

@User what do you mean by > You need a using on insert I'm not quite sure what this means?

I'm trying to setup a system so that - any user can do select queries via the public API - to create/update any table, they must use the functions I provide. I have a table

profiles

. RLS is enabled.

sql
create table if not exists profiles
(
    user_id uuid primary key references auth.users,
    uid uuid not null unique default uuid_generate_v4(),
    username citext not null unique check (char_length(username) >= 3),
    full_name text not null,
    avatar_url text,
    is_public boolean default true
);
alter table profiles enable row level security;
comment on table profiles is 'User Profiles';
create index if not exists on profiles(is_public);
create index if not exists on profiles(created_at);
create index if not exists on profiles(updated_at);

The only policy provided is on select

sql
create policy anyone_can_view_public_profiles_and_users_can_view_own_profile on profiles for select using (is_public or auth.uid() = user_id);

I'm creating functions for create and update

sql
create or replace function create_profile()
returns profiles as $$
    BEGIN
        insert into profiles (user_id, username, full_name)
        values (auth.uid(), auth.uid(), auth.name())
        returning *;
    end;
$$ language plpgsql;

create or replace function update_profile(username citext, full_name text, avatar_url text, is_public boolean)
returns profiles as $$
BEGIN
    update profiles
    set username=username,
        full_name=full_name,
        avatar_url=avatar_url,
        is_public=is_public
    where user_id = auth.uid()
    returning *;
end;
$$ language plpgsql;

My question is: 1. when I execute these functions directly via the database (using a client like dbeaver), then what value does

auth.uid()

have? 2. How do I get the user's name via sql?

auth.name()

doesn't seem to work 3. Is it okay to do what I'm doing here? Or am I doing this wrong? 😅

RLS

If I need to hide a column from the returned results, what are my options? usecase: I don't want to expose serial

id

and internal

user_id

to the users. They should only be able to see the corresponding uuids generated for public view. PostgREST suggests that creating views is the correct way of doing this. However, I'm not sure how the RLS will work with views. Postgresql docs here seem to say that views aren't affected by RLS 😦 https://www.postgresql.org/docs/10/sql-createpolicy.html

views still respect the RLS defined on the underlying table AS LONG AS the view is created by a role which does NOT bypass RLS (E.g. if you create the view with superuser, RLS will by bypassed). Another option is to revoke SELECT/UPDATE privileges for the specified columns for the relevant roles. There is a thread about column level security in the idea-and-suggestions channel, you can take a look there for relevant links

noob question 😅 What is the correct way to test if the policies we've implemented are safe? do we necessarily have to make a request as an authenticated user, or is there a way of testing it via sql itself? I tried

set role authenticated;

but that wasn't allowed.

Which role did you use when you tried to set the role? In general you can set another role ONLY if the your current role is a member of the role you want to set to OR if you are superuser (https://www.postgresql.org/docs/13/sql-set-role.html)?