Yes. https://postgrest.org/en/v8.0/api.html#http-logic

Ah perfect, I didn’t realize the postgrest bindings were available in function calls. Makes perfect sense

DROP TABLE IF EXISTS public.user_level;

-- A user level can be between 1 and 3.
-- 1 is a basic user
-- 2 is an admin
-- 3 is a super user
CREATE TABLE IF NOT EXISTS public.user_level (
  id uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
  user_id uuid NOT NULL REFERENCES auth.users(id),
  level smallint DEFAULT 1 CHECK (level >= 1 AND level <= 3)
);

DROP TRIGGER IF EXISTS on_user_created_give_level ON auth.users;

DROP FUNCTION IF EXISTS handle_new_user();
CREATE FUNCTION handle_new_user()
LANGUAGE plpgsql
RETURNS trigger
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO public.user_level (user_id, level) VALUES (new.id, 1);
  RETURN new;
END;
$$;

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

DROP FUNCTION IF EXISTS get_user_level(uuid);
CREATE FUNCTION get_user_level(id uuid)
LANGUAGE plpgsql
AS $$
DECLARE 
  input_id ALIAS FOR $1;
  user_level INTEGER;
BEGIN
  SELECT level INTO user_level FROM public.user_level WHERE public.user_level.user_id = input_id;
  RETURN user_level;
END;
$$ RETURNS INTEGER;

Getting the error "syntax error at or near "trigger"" But i cant figure out for the life of me what it is

And once I post this I figure it out.

I moved the RETURNS to immediately after the CREATE and its good

is that in the postgres docs?

Fuck me it is

is there a way to list all public tables via the supabase client?

[Not tested] Something like this perhaps:

js
const SBClient = createClient(
  'https://my_id.supabase.co',
  'anon-key',
  {
    schema: 'information_schema'
  }
);

SBClient
  .from('tables')
  .eq('table_schema', 'public');

The equivalent SQL would be:

sql
SELECT * FROM information_schema.tables 
WHERE table_schema = 'public'

thanks Scott!

Is this the right way to create a table where I can save 1 row linked to a user?

create table integrations (
  id uuid references auth.users UNIQUE NOT NULL,
  updated_at timestamp with time zone,
  airtable_key text unique,
  upi_handle text unique,
  inserted_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  primary key (id)
);

And the function to create a new row whenever a new user is created.

create function handle_new_user()
returns trigger as $$
begin
  insert into integrations (id)
  values (new.id);
  return new;
end;
$$ language plpgsql security definer;

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

New to databased and sql, hoping this is correct

http context

Can someone help me with this, I added the above trigger and function, now everytime I sign up I get an error

Database error saving new user

.

in which schema did you define the integrations table? Is it in "public"?

Hi everyone! hey, quick q, I need to give read-only access to my database to some team members. I tried adding. CREATE USER "[user]" WITH PASSWORD '[password]'; GRANT CONNECT ON DATABASE postgres TO "[user]"; GRANT USAGE ON SCHEMA public TO "[user]"; GRANT SELECT ON ALL TABLES IN SCHEMA public TO "[user]"; But the last step fails I think it is related to my user not having full access to the DB supabase provides, does anyone knows how to fix this?

solution found: just run the query from supabase admin instead of doing it from your Postgres client

Hi everyone - I am super new to interacting with databases and using SQL/postgres. Could someone help me put together a query for this and help me understand some of what its doing? I have a table and the table has the columns [id, timestamp, name, price, volume]. Every hour, new data populates into the table with updated price/volume for a given name. This is how I want the table to display on the front end in a table:

html
<tr>
    <th>Collection Name</th>
    <th>Current Floor Price</th>
    <th>Previous Floor Price</th>
    <th>Volume</th>
</tr>

So, I need to group by collection name and then order by timestamp and get the two most recent prices to put into the table (current and previous). What would the SQL for something like this look like? Hopefully I worded this properly enough to portray what I am trying to accomplish!

Thankfully > PostgreSQL 14 makes it convenient to assign read-only and write-only privileges to users on tables, views, and schemas using the pg_read_all_data and pg_write_all_data predefined roles. So once supabase moves to pg14 read only users will be straight forward.

Its pg14 now isnt it?

I’m unsure though can run

select version();

to find out next time at a computer. Edit. My instance is

PostgreSQL 13.3

I am pretty sure supabase has the option to push 14 on postgres

I may be smoking weed

that being said, I dont think the users postgres is refering to and the ones admin.users are one in the same

It's pg14 for new projects, not existing projects.

Does anyone know for self-hosted postgresql database, how do you get Supabase patches to fix Supabase bugs or to work with new versions of Supabase middleware? https://github.com/supabase/supabase/discussions/4394

Hello i'm quiete new to SQL and RLS, i'm playing a bit with the editor and I can't seem to make work 2 RLS of the same type work, is it just not possible and I should use a ``and`` statement rather than this ?

sql
create policy "Users can select personas is they have a relation (uuid)."
on persona
for select using (
  auth.uid() = profile_id
);

create policy "Users can select personas if their profile status > 0"
on persona
for select using (
  auth.uid() in (
    select id from profile
    where status > 0
  )
);

Easier to combine into one policy. Or experiment with the 2nd policy being restrictive https://www.postgresql.org/docs/13/sql-createpolicy.html

Thank you for the link, everything is clearer :)