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

auth.uid()

doesn't apply when connecting via a DB client, as you're not authenticating as a user that's stored in the

auth.users

table. Connecting via dbclient like dbeaver or pgadmin bypasses the regular auth process for users, and is connecting directly to the database.

auth.name()

isn't a function in the database - the name of a user isn't collected by the Supabase library, and the

auth.users

schema doesn't even have a name field. The only functions available by default in the

auth

schema are

email()

,

role()

and

uid()

.

ah, thanks 🙂 About question 3, would you suggest I go forward with it or should I change my ways?

It mostly looks good, but I would ensure you have an RLS policy for

delete

too - without that, someone could easily just remove any profile from the table.

oh.. 😖 If the policy for a certain command isn't provided, isn't the default policy to deny the request?