S
Supabase•2y ago
Trezy

Is there a way to get auth users based on their third-party login ID?

The Goal When a role is added to a user in Discord, I'm trying to see if they also have a user in my Supabase Auth instance. If so, I'll end up creating some new roles in the database. The Problem I haven't found a way to query SupabaseAuth users based on their third party connections. Ideally, I could use supabase.auth.admin.getUserById(discordUserID) (or maybe .getUserByDiscordId(discordUserID) or .getUserByThirdPartyId(discordUserID)) to get the user based on their Discord ID, but from digging through the docs and the types that doesn't seem to exist. Am I missing something? Potential Alternatives If there's not a built-in solution for this, it seems like my best bet is to create a join table that gets updated whenever a new user is created for matching users to their connection IDs. Some thing like this... 
CREATE TABLE [IF NOT EXISTS] userConnectionIDs (
  userID uuid generated always as identity primary key,
  discordID uuid unique,
  itchID uuid unique,
  patreonID uuid unique,
  githubID uuid unique,
);
CREATE TABLE [IF NOT EXISTS] userConnectionIDs (
  userID uuid generated always as identity primary key,
  discordID uuid unique,
  itchID uuid unique,
  patreonID uuid unique,
  githubID uuid unique,
);
That would allow me to use this table to get the correct user... 
const result = await supabase
  .from('userConnectionIDs')
  .eq('discordID', discordID)
  .select()

const userConnectionIDs = result.data?[0]

if (userConnectionIDs) {
  const { userID } = result.data?[0]
  const user = await supabase.auth.admin.getUserById(userID)

  // TODO: Do things with the user record.
}
const result = await supabase
  .from('userConnectionIDs')
  .eq('discordID', discordID)
  .select()

const userConnectionIDs = result.data?[0]

if (userConnectionIDs) {
  const { userID } = result.data?[0]
  const user = await supabase.auth.admin.getUserById(userID)

  // TODO: Do things with the user record.
}
10 Replies
Trezy
TrezyOP•2y ago
It would also work if there was a way to directly query the identities table, like this: 
supabase
  .schema('auth')
  .from('identities')
  .eq('identity_data->provider_id', discordID)
  .select()
supabase
  .schema('auth')
  .from('identities')
  .eq('identity_data->provider_id', discordID)
  .select()
But I get an error when I try to query it, even if I'm using a service role key.
garyaustin
garyaustin•2y ago
You can't access the auth schema from the API
Trezy
TrezyOP•2y ago
Also confirmed that accessing identities is — at least theoretically — locked, preventing me from creating policies on it. According to the docs service roles shouldn't be affected by policies anyway, but... 🤷🏻‍♂️
No description
garyaustin
garyaustin•2y ago
You would need an rpc call to a security definer function. You are not supposed to write anything to auth schema unless documented somewhere (like app_meta_data or user_meta_data).
Trezy
TrezyOP•2y ago
For sure. I don't need to write anything to it, but I was investigating policies to see if there was a way I could enable SELECT on the table. Clearly not, tho.
garyaustin
garyaustin•2y ago
garyaustin
garyaustin•2y ago
No the API can not access auth at all.
Trezy
TrezyOP•2y ago
Yeah, that requires the user to be authenticated, tho. I'm making the request from a secure backend that is using a service role key, not an authenticated user token. Got it. By "security definer function," do you mean a stored procedure that would handle the query for me? Or something else?
garyaustin
garyaustin•2y ago
Yes. Security definer means it runs as postgres user (usually) so it can access everything.
Trezy
TrezyOP•2y ago
Took a few tries to get it figured out, but I just got it all to work! Here's my heavily commented solution in case anybody runs across this issue in the future. I tried to cover all of the things that tripped me up. 😉 
-- The function will be created on the default `public` schema, though we can
-- access other schemas from inside the function. For example,
-- `auth.get_user_from_external_id` won't work because the `auth` schema isn't
-- available via the API.
create or replace function get_user_from_external_id(external_id text)
-- This must match the type of the column that will be returned. In this case,
-- I'm returning a string field from inside of a jsonb field, so we're using
-- the `text` type.
returns text
language plpgsql
-- This ensures the security level will match the user that created this
-- function. If you're creating the function via the Supabase web UI, `postgres`
-- is the default user that will be selected and they have global admin
-- permissions.
security definer
as $$
begin
  return (
    -- Only 1 column can be returned, so using `select *` won't work here.
    select user_id
    -- We can access any schema that the definer has permission to access. Since
    -- I'm using the `postgres` user with global admin access, I can read from
    -- the `auth` schema that otherwise isn't available.
    from auth.identities
    -- `identity_data` is a jsonb field, and I needed to access a field inside
    -- of the jsonb. Using `->>` allows accessing sub keys of the jsonb object,
    -- and wrapping the property in quotes coerces it to a string type.
    where identity_data ->> 'provider_id' = external_id
  );
end;
$$;
-- The function will be created on the default `public` schema, though we can
-- access other schemas from inside the function. For example,
-- `auth.get_user_from_external_id` won't work because the `auth` schema isn't
-- available via the API.
create or replace function get_user_from_external_id(external_id text)
-- This must match the type of the column that will be returned. In this case,
-- I'm returning a string field from inside of a jsonb field, so we're using
-- the `text` type.
returns text
language plpgsql
-- This ensures the security level will match the user that created this
-- function. If you're creating the function via the Supabase web UI, `postgres`
-- is the default user that will be selected and they have global admin
-- permissions.
security definer
as $$
begin
  return (
    -- Only 1 column can be returned, so using `select *` won't work here.
    select user_id
    -- We can access any schema that the definer has permission to access. Since
    -- I'm using the `postgres` user with global admin access, I can read from
    -- the `auth` schema that otherwise isn't available.
    from auth.identities
    -- `identity_data` is a jsonb field, and I needed to access a field inside
    -- of the jsonb. Using `->>` allows accessing sub keys of the jsonb object,
    -- and wrapping the property in quotes coerces it to a string type.
    where identity_data ->> 'provider_id' = external_id
  );
end;
$$;
And here's the JS I used to call the function via RPC any time a Discord user has a role added or removed. 
// Local imports
import { supabase } from './supabase.js'

/**
 * Starts the Discord bot.
 *
 * @param {import('discord.js').GuildMember} oldMember The old guild member data.
 * @param {import('discord.js').GuildMember} newMember The new guild member data.
 */
export async function handleGuildMemberUpdate(oldMember, newMember) {
  const addedRoles = newMember
    .roles
    .cache
    .filter(role => !oldMember.roles.cache.has(role.id))

  const {
    data: userID,
    error,
  } = await supabase.rpc('get_user_from_external_id', { external_id: newMember.user.id })

  // TODO: Do something with the user ID.
}
// Local imports
import { supabase } from './supabase.js'

/**
 * Starts the Discord bot.
 *
 * @param {import('discord.js').GuildMember} oldMember The old guild member data.
 * @param {import('discord.js').GuildMember} newMember The new guild member data.
 */
export async function handleGuildMemberUpdate(oldMember, newMember) {
  const addedRoles = newMember
    .roles
    .cache
    .filter(role => !oldMember.roles.cache.has(role.id))

  const {
    data: userID,
    error,
  } = await supabase.rpc('get_user_from_external_id', { external_id: newMember.user.id })

  // TODO: Do something with the user ID.
}

Did you find this page helpful?