Managing User Data
For security purposes, the auth schema is not exposed on the auto-generated API.
Even though Supabase provides an auth.users table, it can be helpful to create tables in the public schema for storing user data that you want to access via the API.
Creating user tables
When you create tables to store user data, it's helpful to reference the auth.users table in the primary key to ensure data integrity. Also specify the on delete cascade clause when referencing auth.users. Omitting it may cause problems when deleting users. For example, a public.profiles table might look like this:
_10create table public.profiles (_10 id uuid not null references auth.users on delete cascade,_10 first_name text,_10 last_name text,_10_10 primary key (id)_10);_10_10alter table public.profiles enable row level security;
Only use primary keys as foreign key references for schemas and tables like auth.users which are managed by Supabase. PostgreSQL lets you specify a foreign key reference for columns backed by a unique index (not necessarily primary keys).
Primary keys are guaranteed not to change. Columns, indices, constraints or other database objects managed by Supabase may change at any time and you should be careful when referencing them directly.
Deleting users
You may delete users directly or via the management console at Authentication > Users. Note that deleting a user from the auth.users table does not automatically sign out a user. As Supabase makes use of JSON Web Tokens (JWT), a user's JWT will remain "valid" until it has expired. Should you wish to immediately revoke access for a user, do consider making use of a Row Level Security policy as described below.
You cannot delete a user if they are the owner of any objects in Supabase Storage.
You will encounter an error when you try to delete an Auth user that owns any Storage objects. If this happens, try deleting all the objects for that user, or reassign ownership to another user.
Exporting users
As Supabase is built on top of PostgreSQL, you can query the auth.users and auth.identities table via the SQL Editor tab to extract all users:
_10select * from auth.users;
You can also opt to export the results via CSV through the dashboard if you wish:
Public access
Since Row Level Security is enabled, this table is accessible via the API but no data will be returned unless we set up some Policies. If we wanted the data to be readable by everyone but only allow logged-in users to update their own data, the Policies would look like this:
_11create policy "Public profiles are viewable by everyone."_11 on profiles for select_11 using ( true );_11_11create policy "Users can insert their own profile."_11 on profiles for insert_11 with check ( (select auth.uid()) = id );_11_11create policy "Users can update own profile."_11 on profiles for update_11 using ( (select auth.uid()) = id );
Private access
If the data should only be readable by the user who owns the data, we just need to change the for select query above.
_10create policy "Profiles are viewable by users who created them."_10 on profiles for select_10 using ( (select auth.uid()) = id );
The nice thing about this pattern? We can now query this table via the API and we don't need to include data filters in our API queries - the Policies will handle that for us:
Security is handled at the database level by the RLS policy. The policy restricts the returned rows, so you don't need a filter on id. Depending on your table size and query, you might still want to add a filter for performance. Postgres can use the filter to construct a more efficient query.
Bypassing Row Level Security
If you need to fetch a full list of user profiles, we supply a service_key which you can use with your API and Client Libraries to bypass Row Level Security.
Make sure you NEVER expose this publicly. But it can be used on the server-side to fetch all of the profiles.
Accessing user metadata
You can assign metadata to users on sign up:
User metadata is stored on the raw_user_meta_data column of the auth.users table. To view the metadata:
Advanced techniques
Using triggers
If you want to add a row to your public.profiles table every time a user signs up, you can use triggers.
If the trigger fails however, it could block the user sign ups - so make sure that the code is well-tested.
Example:
_17-- inserts a row into public.profiles_17create function public.handle_new_user()_17returns trigger_17language plpgsql_17security definer set search_path = public_17as $$_17begin_17 insert into public.profiles (id, first_name, age)_17 values (new.id, new.raw_user_meta_data ->> 'first_name', new.raw_user_meta_data['age']::integer);_17 return new;_17end;_17$$;_17_17-- trigger the function every time a user is created_17create trigger on_auth_user_created_17 after insert on auth.users_17 for each row execute procedure public.handle_new_user();