Drizzle Team•9mo ago

How to set up auth and user management with supabase

I'm using supabase auth, and I want to have a dedicated table for user data that's not auth related. auth.users is being created and managed by Supabase. In their docs (https://supabase.com/docs/guides/auth/managing-user-data#creating-user-tables), they show -

create table public.profiles (
  id uuid not null references auth.users on delete cascade,
  first_name text,
  last_name text,

  primary key (id)
);

alter table public.profiles enable row level security;

create table public.profiles (
  id uuid not null references auth.users on delete cascade,
  first_name text,
  last_name text,

  primary key (id)
);

alter table public.profiles enable row level security;

And I've been trying to setup my schema to imitate that -

export const roleEnum = pgEnum('role', ['admin', 'viewer', 'editor']);

export const user = pgTable('user', {
  id: uuid('id')
    .notNull()
    .references(() => auth.users.id, { onDelete: 'cascade' })
    .primaryKey(),
  firstName: varchar('first_name', { length: 256 }).notNull(),
  lastName: varchar('last_name', { length: 256 }).notNull(),
  email: varchar('email', { length: 256 }).notNull().unique(),
  phone: varchar('phone', { length: 256 }),
  createdAt: timestamp('created_at', { withTimezone: true })
    .notNull()
    .default(sql`now()`),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .notNull()
    .default(sql`now()`),
  role: roleEnum('role').notNull().default('viewer'),
});

export const roleEnum = pgEnum('role', ['admin', 'viewer', 'editor']);

export const user = pgTable('user', {
  id: uuid('id')
    .notNull()
    .references(() => auth.users.id, { onDelete: 'cascade' })
    .primaryKey(),
  firstName: varchar('first_name', { length: 256 }).notNull(),
  lastName: varchar('last_name', { length: 256 }).notNull(),
  email: varchar('email', { length: 256 }).notNull().unique(),
  phone: varchar('phone', { length: 256 }),
  createdAt: timestamp('created_at', { withTimezone: true })
    .notNull()
    .default(sql`now()`),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .notNull()
    .default(sql`now()`),
  role: roleEnum('role').notNull().default('viewer'),
});

Thing is, that under references I have no way to actually access auth schema cause it's done by supabase. I can't seem to find a solution.

Managing User Data | Supabase Docs

Securing your user data with Row Level Security.

2 Replies

ar7casperOP•9mo ago

In case anyone will ever come back here, - The way I solved the issue is by creating my own user table as in the snippet above (minues some attributes) and adding references auth.users.... to the migration file manually. It's not like I wanted - having everything in the schema file, but it's a solution. If you come accross something nicer, would love to hear about it.

Mykhailo•9mo ago

Hello @ar7casper you can check solutions here: https://twitter.com/rphlmr/status/1740692744992092438 or https://github.com/drizzle-team/drizzle-orm/issues/594

Raphaël Moreau ⚡️ (@rphlmr) on X

@DrizzleORM & @supabase: How to link https://t.co/mLtHnWelDr with your https://t.co/rnHxTxD3nX. https://t.co/7wUz4aldFu

Twitter

GitHub

[FEATURE]: Support PostgreSQL's Row Level Security (RLS) · Issue #5...

Describe want to want Supabase is really nicely using Row Level Secruity for granular authorization rules. 🔗 Here's the link to their docs: https://supabase.com/docs/guides/auth/row-level-secur...

Gaming

Programming

How to set up auth and user management with supabase