Add ability to (read-only) query the auth.users table directly with a service key using supabase.js

[miketromba]

Feature request

I suggest adding the ability to query (read-only) the auth.users table directly with a service key. Many production apps need this ability...

Describe the solution you'd like

// In my case, I need to add additional users as 'owners' of a record using their email address

// Find user by email
const { data, error } = await supabase
  .from('auth.users') // Not possible now
  .select()
  .eq('email', '[email protected]')

// Then use the user's data for some business logic

Describe alternatives you've considered

Some developers have used workarounds like creating a public.users table which replicates relevant data from the auth.users table using triggers. This feels like a hacky workaround to me... I would much rather avoid this unnecessary redundancy / potential for data inconsistency down the line.

In my case, since I just need to be able to query for users by email, I've considered replicating a .email field to my public.profiles table. The problem is the same... the email field becomes redundant and opens up the possibility of data inconsistencies down the line. (E.g. user updates email with 3rd party provider which updates the auth.users table, but not my public.profiles.email field)

Additional context

See: https://nikofischer.com/supabase-how-to-query-users-table

[kiwicopple]

Hey @miketromba - there is a newly (undocumented) function for this:

https://github.com/supabase/gotrue-js/blob/d7b334a4283027c65814aa81715ffead262f0bfa/src/GoTrueApi.ts#L468

const { data, error } = await supabase.auth.api.listUsers()

I'll keep this open because we definitely need to add this to the docs

[soedirgo]

For now it's in the gotrue-js docs: https://supabase.github.io/gotrue-js/classes/GoTrueApi.html#listUsers

[miketromba]

It appears that that function does not accept filters similar to what a .match() would let you do... That being said, I think it's fair to assume that the primary .match() criteria that would be used would be id / email / user metadata. Since getUserById is already implemented, I suggest also adding:

// Similar to supabase.auth.api.getUserById('id')
const { data, error } = await supabase.auth.api.getUserByEmail('email')

[ydennisy]

@soedirgo @kiwicopple I think it would be good to allow filter by user meta too.

We are storing account IDs in this meta, and would be great to be able to list all users by account.

Is direct access to this table not possible?

Also sorry for the many questions, but would you suggest maintaining a users table in the main DB and relying on auth tables?

[soedirgo]

Seems like this is possible by using a view:

create view public.users as select * from auth.users;
revoke all on public.users from anon, authenticated;

Then you can .select() as usual with your service_role key.

[J0]

Let's document this somewhere and we should be able to close thereafter

Edit: Ah I see the original issue was asking for a way to do this with the client lib. Transferring to relevant repo.

[JhaAman]

Seems like this is possible by using a view:

create view public.users as select * from auth.users;
revoke all on public.users from anon, authenticated;

Then you can .select() as usual with your service_role key.

This worked but seems like it's only limited to 1000 users - not sure if that's a limitation of views or something else.

[hf]

Seems like this is possible by using a view:

create view public.users as select * from auth.users;
revoke all on public.users from anon, authenticated;

Then you can .select() as usual with your service_role key.

This worked but seems like it's only limited to 1000 users - not sure if that's a limitation of views or something else.

Probably a limitation of PostgREST. Please make sure you're setting correct limit and offset params.

[hf]

Closing this issue as we're not going to be exposing auth.users to the public at all -- there's some very sensitive data there like the password hash and other things.

You can use some of these workarounds at your own risk.

[mewc]

Use the view but only expose data you need. Heres a few, expand as needed

create view public.users as select id, email, role, email_confirmed_at, last_sign_in_at, created_at, updated_at, phone, is_sso_user, deleted_at from auth.users;
revoke all on public.users from anon, authenticated;

[Teiem]

If you start getting the error: { "message": "An invalid response was received from the upstream server" }

delete your view and add it again.

(Email from Supabase support: Thank you for contacting Supabase Support. Recently we had to modify the type of the phone and phone_change columns in [auth.users](https://github.com/supabase/gotrue-js/issues/auth.users) which requires any triggers or views dependent on those columns to be recreated. You can fix this problem by recreating (dropping then adding again) the triggers and views that were using these columns.)

[FabianMontoya]

Seems like this is possible by using a view:

create view public.users as select * from auth.users;
revoke all on public.users from anon, authenticated;

Then you can .select() as usual with your service_role key.

It's works for me, but in my front I'm getting the error: {"code":"42501","details":null,"hint":null,"message":"permission denied for view users"}. What could be my error?

[FabianMontoya]

Haha, my mistake, I ran revoke all on public.users from anon, authenticated; and it revoke all the permissions, only run revoke all on public.users from anon; for allow the access only to auth users

[eRuaro]

Hi! I want to send a bulk email but I'm only receiving 50 from calling the function. Is there a programmatic way to retrieve all the emails using the supabase SDK?

[kaelruland]

Hi! I want to send a bulk email but I'm only receiving 50 from calling the function. Is there a programmatic way to retrieve all the emails using the supabase SDK?

You can paginate: await supabase.auth.api.listUsers({page: 1, perPage: 100). The default perPage is 50.

[sebringj]

FYI You can also individually call from a list of user IDs but its not ideal:

const userResp = await client.auth.admin.getUserById('some uuid')
const email = userResp.data.email