Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Auth

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:


_10
create 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
_10
alter table public.profiles enable row level security;

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.

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:


_10
select * from auth.users;

You can also opt to export the results via CSV through the dashboard if you wish: export_users.png

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:


_11
create policy "Public profiles are viewable by everyone."
_11
on profiles for select
_11
using ( true );
_11
_11
create policy "Users can insert their own profile."
_11
on profiles for insert
_11
with check ( auth.uid() = id );
_11
_11
create policy "Users can update own profile."
_11
on profiles for update
_11
using ( 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.


_10
create policy "Profiles are viewable by users who created them."
_10
on profiles for select
_10
using ( 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:


_10
// This will return nothing while the user is logged out
_10
const { data } = await supabase.from('profiles').select('id, username, avatar_url, website')
_10
_10
// After the user is logged in, this will only return
_10
// the logged-in user's data - in this case a single row
_10
const { error } = await supabase.auth.signIn({ email })
_10
const { data: profile } = await supabase
_10
.from('profiles')
_10
.select('id, username, avatar_url, website')

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:


_10
const { data, error } = await supabase.auth.signUp({
_10
email: 'example@email.com',
_10
password: 'example-password',
_10
options: {
_10
data: {
_10
first_name: 'John',
_10
age: 27,
_10
},
_10
},
_10
})

User metadata is stored on the raw_user_meta_data column of the auth.users table. To view the metadata:


_10
const {
_10
data: { user },
_10
} = await supabase.auth.getUser()
_10
let metadata = user.user_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
_17
create function public.handle_new_user()
_17
returns trigger
_17
language plpgsql
_17
security definer set search_path = public
_17
as $$
_17
begin
_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;
_17
end;
_17
$$;
_17
_17
-- trigger the function every time a user is created
_17
create trigger on_auth_user_created
_17
after insert on auth.users
_17
for each row execute procedure public.handle_new_user();