@lawrencecchen what do you think? Could it be optimized?

I know this isnt a Postgres help server, but when making policies, when do you use USING and when do you use with check?

As I know, USING is for select and delete and WITH CHECK for insert and update

drop table if exists user_type;
drop type if exists userType;

-- Create an enum
create type userType as ENUM ('user', 'admin');

-- Create User Roles Table;
create table user_type (
  id uuid references auth.users not null,
  type userType not null default 'user',

  primary key (id)
);

-- enable RLS
alter table user_type enable row level security;

-- create function for getting userType
create or replace function get_user_type(id uuid, out result varchar)
RETURNS varchar as
$$
  BEGIN
  select userType INTO result from user_type where user_type.id = id LIMIT 1;

  IF NOT FOUND THEN
    result := 'null';
  END IF;

  END
$$ language plpgsql;

-- create function for adding user to types at signup
CREATE OR REPLACE FUNCTION give_user_default_role() RETURNS TRIGGER as
$$
  BEGIN
    insert into public.user_type(id) VALUES (new.id);
    return new;
  END
$$ language plpgsql security definer;

--Use the function when auth.users gets an insert
drop trigger if exists default_user_type on auth.users;
create trigger default_user_type after insert on auth.users execute procedure give_user_default_role();

I am getting errors when i try to test this with new users, how do I see the new users?

How do i see the errors for the new users that is

This is the error I am getting, but how do I get to the error logs?

Ok i figured it out, I was not defining a value for type in the insert. I thought the default would handle it. I was wrong

id still love a way to get the error logs from the database

Error Log Access

Hi I am trying to add policies to a table to allow reading all records by all users but restricting UPDATE to only if my JWT user ID (TEXT) matches an ID column (TEXT). The inbuilt JWT stuff deals with UUID so I made a new function to support TEXT but it isnt working. What am I doing wrong? Is there a way to debug this easily?

ALTER TABLE users 
    ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Everyone can read all users."
    ON users FOR SELECT USING (
        true
    );

CREATE POLICY "Users can update their own profile."
    ON users FOR UPDATE USING (
        auth.firebase_uid()::TEXT = id
    );

CREATE FUNCTION auth.firebase_uid() RETURNS TEXT LANGUAGE sql AS $$
BEGIN
    SELECT nullif(current_setting('request.jwt.claim.sub', true), '')::TEXT;
END $$;

I can read all documents but get I get a generic postgrest permission error in my frontend app on UPDATE.

Does

auth.uid()::TEXT = id

or

auth.uid() = id::UUID

work? Do you have a reason for not having your

id

column set as

uuid

type?

What's the difference between grants and policies? Would something like

create policy write on table for insert with check (true);

be equivalent to

grant insert on table

? I noticed the examples used the former so I was wondering why not the latter.

My IDs are Firestore document IDs which I'm unsure if they are UUIDs I tried casting to TEXT but that didn't work. Just tried

auth.uid() = id::UUID

and it didnt work

How can I debug this? I am just guessing at this point because the REST API just gives me a generic no permission error

I created a profile table in the sql dashbaord as so

CREATE TABLE profiles (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users on delete cascade,

but when I try and delete a user with a profile, I'm getting this error

Deleting user failed: update or delete on table "users" violates foreign key constraint "profiles_user_id_fkey" on table "profiles"

wondering why my on delete cascade isn't registering

Hey all! I'm trying to get my head around some concepts related to row-level security and having a tricky time figuring out this one use case. I've got a resource in my system I want users to be able to access on an invite-only basis; one user creates the resource, and can then invite other users to edit it. I've got a

public.collaborators

table that models the many-to-many relationship between users in

auth.users

and the equivalent of a

public.resources

table for the resource in question. How do I write a policy that lets collaborators select and update rows from that

public.resources

table?

The schema is roughly:

sql
CREATE TABLE public.resources (
  id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  owner_id uuid REFERENCES auth.users(id) NOT NULL,
  name TEXT NOT NULL,
  invite_code CHARACTER(32) NOT NULL UNIQUE
);

CREATE TABLE public.collaborators (
  id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  resource_id uuid REFERENCES public.resources(id) NOT NULL,
  UNIQUE (user_id, resource_id)
);

Is this the way to do that?

sql
CREATE POLICY "Collaborators can view their resources."
  ON resources FOR SELECT
  USING ( EXISTS (SELECT * FROM collaborators WHERE resource_id = id AND user_id = auth.uid()) );

grants vs policies

Anyone know how to replace the

net

functions like

net.http_post

? I want to fix a function hook now while waiting for a release: https://github.com/supabase/supabase/issues/2871 but I am getting error

ERROR:  must be owner of function http_post

(I am user

postgres

)

Has anyone tried to put an image into storage within sql? Like using pg_net to get an image and then insert the data?

hi folks — a general question about schema design or structure — let's say I wanted files and folders — like a tree with nested folders (however many deep) — where would I start? a file would be an arbitrary table with its columns. also, a folder would need to be a column too since it may have its own metadata.

is there a way(perhaps via trigger) to catch an insert and insert a value for a certain column?

you can do that with a trigger with

before insert

I tried to create 2 insert trigger to call 2 different function the first trigger happens before insert and the other after but the before insert didn't seem to work this is the code of the before insert trigger function BEGIN new.admin_id = auth.uid(); RETURN NEW; END;

You're not actually telling it to insert any data with that trigger. Try this instead (replace

my_table

with the name of your table):

BEGIN
  INSERT INTO public.my_table (admin_id)
  VALUES (auth.uid())
  return new;
END;

Can I login with this role? I need full control over my database

Login to database