Thanks, works!

I added a trigger to the auth.users table on insert. But author_id is still empty whilst it should contain the

id

from the auth.users table?

-- create function for adding user to default watchlist
CREATE OR REPLACE FUNCTION give_user_default_watchlist() RETURNS TRIGGER as
$$
  BEGIN
    insert into public.watchlists(author_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_watchlist on auth.users;
create trigger default_user_watchlist after insert on auth.users execute procedure give_user_default_watchlist();

looks right, but are you trying to insert a new row or update the existing row that you have in your screenshot?

Insert a new row!

insert trigger

Hi, I am connecting directly to the postgres database and after a few minutes of inactivity it seems like the connection is broken. Is this expected behaviour?

Can someone spot if I'm doing something wrong here? I'm trying to go through all the rows in my table and insert data to a different table. Inserting outside the loop works fine, however nothing happens here even though the functions results in success.

create or replace function public.add_region_ranks() 
returns void
as $$
declare
  player record;
begin
  for player in select * from profiles
  loop
    insert into profiles_ratings (profile_id, region_id, game_mode_id) values (player.id, 1, 1);
  end loop;
end;
$$ language plpgsql security definer;

How do I make this sql sort using supabase-js?

sql
order by min != 0 desc, inserted_at desc

I thought it would be something like

js
...
.order('min', { "!= 0", ascending: false })
.order('inserted_at', { ascending: false });

Alternatively, if I make all my

min

0 values null, is there a way I can do order by nulls last, but NOT also sort min values asc/desc?

Is there a way to do two queries and

union

the results with the API?

From what i know, you can write that query in a rpc

And call that from the Api

That was the answer, thanks!

Hello! I am wondering if anyone has any suggestions on how to get a sort of "column-level" security for my table? I have a users-table (in the

public

schema), and want to allow users to change their own name, but not the id (which references

auth.id

). I have the following policy:

sql
CREATE POLICY "Users may only view their own data"
        ON pub_users FOR SELECT using (
            auth.uid() = id
        )

And need something similar for an update policy

sql
-- How do I restrict the UPDATE to only be allowed for the `name`-column?
CREATE POLICY "Users may only update their own name"
ON pub_users FOR UPDATE using (
   auth.uid() = id
)

In addition to RLS, Postgres also has a Privileges system which allows to control the access to columns. For example, you can grant only read access to a column but revoke updates. You can read more about it here: https://www.postgresql.org/docs/13/ddl-priv.html

Another option is to move the pub_users table to a private schema (so it's NOT accessible via REST) and then allow reading it using a read-only view and allow updating it it through a stored procedure so you have control over what can be updated and what not

Thanks for your feedback @User! I am looking mostly into the second option, of creating a view, but a writable view. Something like

sql
CREATE OR REPLACE VIEW editable_user AS
            SELECT u.name
        FROM pub_users u 
            WHERE auth.uid() = u.id

This seems to do the trick and I am successfully writing to the view, which is then updating the underlying pub_users (which has RLS enabled). I am still experimenting around this to understand the implications this has, or if I am opening up some unwanted access

This will work, as long as all you need to expose to the user for reading is the username. However, there are a couple of caveats to be aware of: 1) If your pub_users table is in the public schema (like the view) users will still have access to it via REST so the column will still be updatable through the table 2) You can update the view because it is simple enough to be considered by Postgres auto-updatable (see "Updatable Views" section here: https://www.postgresql.org/docs/13/sql-createview.html) - if you complicate the view, it might be updatable anymore 3) View are executed with the role of the user which DEFINED the view, NOT the role who is calling the view. This means, that you need to ensure to define the view with a role which does NOT bypass RLS (for example, the postgres and supabase_admin roles which come with the Supabase PostgreSQL bypass RLS). You can read a little more here: https://github.com/supabase/supabase/discussions/1501#discussioncomment-1439606 (or in Postgres docs) 4) Pros and cons of updatable views according to the folks at Hasura: https://hasura.io/blog/the-pros-and-cons-of-updatable-views/

Thanks, these are some very good points! I am indeed keeping my

pub_users

in public, and it has RLS enabled with the following policies:

sql
CREATE POLICY "Users may only view their own data"
        ON pub_users FOR SELECT using (
            auth.uid() = id
        )

And my admin-roles (also defined in the pub_users table)

sql
CREATE POLICY "Admin users have full access to user data"
            ON pub_users FOR ALL using (is_admin(auth.uid()))

All my DB code is handled (migrated, rolled back, seeded) by knex, connecting with service key. I am now wondering if this means the view will be owned by the service/superadmin. I will certainly have to dig a bit deeper to see if this works in practice!

Did you create a service key? Or how did you manage your migration. I am currently using db-migrate and am not sure how to include for example buckets. The normal postgres user has no access to the table. I think that is not a good idea to give the normal postgres user access to this table. Did you create a new user for managing the migrations?

@User I am using the service key to run migrations using Knex. I run migrations from local dev (against my local dev supabase) and from github workflow actions against my test and prod environments. In local dev I have the service key in a

.env.local

file, and in my GitHub workflows it is stored as a secret. I do not know if this will work for buckets, as my project currently does not use any buckets

Can you show the connection object you are setting up for knex?

Sure, here is my knexfile:

for my local dev env the connection string would be

SUPABASE_CONNECTION_STRING=postgres://postgres:postgres@localhost:5432/postgres

and then for the test/prod environments replace the pw and host with your app password/host

But then your are connecting to the database with the normal postgres user. Where does the service key comes into play?

Yeah you're right, no service key involved here! Apologies, it's been a few months since I set this up, so I forgot 😬 Sorry, complete brain fart there. Of course, knex needs a db-connection, not a REST API

no problem 😉

Anyone know how I can manually set a jwt for auth in sql? I want to run an explain command against a query but as an auth’d user so I can test RLS performance