Creating User Profiles on Sign-Up in Supabase
Tags: supabase, postgres, webdev
Published: 2021-08-11 18:16:38

Supabase stores authentication-related information (unique ID, email, password, etc.) in the `auth.users` table when users sign up. The `auth` schema isn't publicly accessible for security reasons, so you can't store additional information on your users in this table.

Depending on what type of application you are building, you might need a `profile` table containing profile information your users can interact with. In this post, we will:

- Create a simple `profile` table in the `public` schema.
- Enable Row-Level Security on it and restrict access for users to their own profile data.
- Write a database trigger that automatically creates a row for every user on sign-up.

## Creating & Securing a Profile Table

We start by creating a `profile` table with two columns: `id` referencing a user ID in the `auth.users` table, and `display_name`.

```sql
CREATE TABLE public.profile (
  id UUID REFERENCES auth.users NOT NULL,
  display_name TEXT NULL,
  PRIMARY KEY (id)
);
```

Supabase uses [PostgREST](https://supabase.io/docs/postgrest/server/about) to access the Postgres database via its API (i.e., the `supabase` API you use in your client application). By default, a table in the `public` schema can be accessed without restriction.

However, in our case, we want to restrict access on this table so that users can only select and update data that belongs to them. We are going to use [Row-Level Security](https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html) for this.

```sql
ALTER TABLE public.profile
  ENABLE ROW LEVEL SECURITY;
```

When Row-Level Security is enabled for a table, all access to this table must be allowed by a row security policy. The idea here is: "Everything is forbidden unless explicitly allowed."

The first row security policy we introduce allows a user to `SELECT` their own profile data:

```sql
CREATE POLICY
  "Can only view own profile data."
  ON public.profile
  FOR SELECT
  USING ( auth.uid() = id );
```

The second row security policy allows a user to `UPDATE` their own profile data:

```sql
CREATE POLICY
  "Can only update own profile data."
  ON public.profile
  FOR UPDATE
  USING ( auth.uid() = id );
```

The idea behind these policies is to specifically grant rights to `SELECT` or `UPDATE` rows where the `id` is set to the authenticated user's unique ID. Since `id` is the primary key of the `profile` table, this effectively leaves only one row: the user's own profile data.

If you want to know how to handle public and private access, I recommend reading the page [Managing User Data](https://supabase.io/docs/guides/auth/managing-user-data) from the Supabase documentation.

## Automatically Creating Profiles on Sign-Up

When a user signs up or signs in with a Third-Party provider (e.g. Twitter, GitHub, Google, etc.), a new row is inserted into the `auth.users` table. Ideally, we want to create a new profile at the same time. An easy way to do so is to use [triggers](https://www.postgresql.org/docs/9.1/sql-createtrigger.html).

The following statement creates a trigger called `create_profile_on_signup` that calls the function `create_profile_for_new_user()` whenever a new row is inserted into the `auth.users` table:

```sql
CREATE TRIGGER
  create_profile_on_signup
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE PROCEDURE
    public.create_profile_for_new_user();
```

The function `create_profile_for_new_user()` simply inserts a new row into our `profile` table:

```sql
CREATE FUNCTION
  public.create_profile_for_new_user()
  RETURNS TRIGGER AS
  $$
  BEGIN
    INSERT INTO public.profile (id)
    VALUES (NEW.id);
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql SECURITY DEFINER;
```

The [`NEW` keyword](https://www.postgresql.org/docs/current/plpgsql-trigger.html) refers to a variable holding the new database row (i.e., the one that was just inserted into the `auth.users` table).

There's one thing missing, though: The `display_name` isn't set. Technically, this isn't a problem, as the column is nullable. For Third-Party logins, however, we can access certain metadata that allows us to prefill this column with a sensible value.

The `auth.users` table has a `JSONB` column called `raw_user_meta_data` containing information on the user depending on the Third-Party provider used for signing in. For example, it [usually contains a `user_name` field](https://github.com/supabase/gotrue/pull/127#issuecomment-876525074).

Making use of this, we can prefill the `display_name` with this username like this:

```sql
CREATE FUNCTION
  public.create_profile_for_new_user()
  RETURNS TRIGGER AS
  $$
  BEGIN
    INSERT INTO public.profile (id, display_name)
    VALUES (
      NEW.id,
      NEW.raw_user_meta_data ->> 'user_name'
    );
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql SECURITY DEFINER;
```

Now, whenever a user signs up, a new `profile` is created with its `display_name` set to the username used with the Third-Party provider or to `NULL` when you use Supabase's sign in (here you can fill it from within the client application).

## Recap

- The `auth.users` table stores authentication-related information.
- You can't access or modify this table from within your application for (obvious) security reasons.
- We created a `profile` table with two columns `id` and `display_name`.
- We enabled Row-Level Security for this table and restricted access for users to their own data.
- We created a trigger and function to automatically insert a new row into `profile` when a new row is inserted into `auth.users` table.
- When a user signs up or signs in with a Third-Party provider, a new row is inserted into `auth.users`.

## References

- I can highly recommend the [Next.js Subscription Payments Starter](https://github.com/vercel/nextjs-subscription-payments), which uses this idea [here](https://github.com/vercel/nextjs-subscription-payments/blob/main/schema.sql#L22).