Secure Your Next.js App: Email & Google Authentication with Supabase, PostgreSQL RLS, and Triggers - Part 4

In this part of the series, we will focus on building the Profiles table for our users using Prisma, Supabase, and PostgreSQL for our Next.js application. This combination offers a powerful backend solution, leveraging Prisma's advanced ORM capabilities and Supabase's PostgreSQL database, which provides a scalable and reliable data storage system. We will also be creating Triggers such that a user profile is created as soon as a user signs up on our application.

🔗 Check out the full code for this series here.

Enabling Row Level Security (RLS) is crucial for ensuring that users can only access their data. RLS allows us to define fine-grained access control policies directly within the database, enhancing security and privacy. By integrating RLS with Supabase's authentication system, we can create a secure environment where users can manage their profile data without compromising data integrity. This approach not only protects against unauthorized access but also simplifies the development process by reducing the need for complex server-side / middleware logic.

While the authentication table in the Supabase gives you basic information about the user, you may want to store some extra information in a separate table, such as the Profile to store more info about the user. In this section we will be doing the same.

Adding Prisma and Defining Schemas

1. Add Prisma to your project by using this command. This will install the Prisma ORM client that will make it easier for us to do CRUD operations in our Supabase DB.

pnpm add @prisma/client

1. To get the Database URL from the project in the Supabase, switch to the project that you’re working on. In the left pane, click “Database” and then click “Connect” on the top.

1. Copy your Database URL and in your .env file, paste it like this. Add these lines in the bottom of the file.

.env

DATABASE_URL=postgresql://postgres:[YOUR-PASSWORD]@db.phbngigehmegragywadf.supabase.co:5432/postgres?pgbouncer=true
DIRECT_URL=postgresql://postgres:[YOUR-PASSWORD]@db.phbngigehmegragywadf.supabase.co:5432/postgres

Note: The DIRECT_URL is the same as the DATABASE_URL, except that it contains the pgbouncer query.

Also, replace the “[YOUR-PASSWORD]” part with your Database password. If you forgot your Database password, you can always reset it by going to the Project Settings > Database > Reset Database Password. This won’t affect any other configuration in the Supabase project.

1. Create a folder named “prisma” in the root of your project, and inside that, create a file named “schema. prisma “.

Note: Try to figure out the Prisma Schema. It should be self explanatory. Since it’s a different language altogether, I won’t be able to explain it here. You can check out Prisma Docs for more.

prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

model Profile {
  id        String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  firstName String? @map("first_name")
  lastName  String? @map("last_name")
  email     String  @unique
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("profiles")
}

💡 Tip: Install the Prisma extension for the VS Code to get syntax highlighting and type safety features of Prisma.

1. Let’s add a script in our package.json file to make the migrations easier. Migration is a process in which you take your local schema and “migrate” it to make/modify the tables in your remote database. Add the following line under the scripts section in your package.json file.

/package.json

 "migrate": "pnpx prisma migrate dev --name init && bunx prisma generate"

1. Let’s try out our migration script. This should ideally create a “profiles” table in our Supabase Database. Run the following command in the terminal:

pnpm migrate

1. Check your Database in the Supabase again, and confirm that the tables are created.

Adding Triggers to Create and Delete a profile automatically when a new User Signs Up for our Application

Now that we created the Profiles table, it is time to ensure that whenever a new User signs up for our application, we must create a new Profile for that user, and delete that user from the authentication table when the user has deleted their profile. In simple terms

1. User signs up → A record is added to the auth table → We create a profile.

2. User deletes their profile → A record is removed from the profiles table → We delete their authentication info (account).

3. Head over to your Supabase project and select the SQL Editor in the left navigation pane.

1. Paste the following query in the editor and click “Run”

triggers.sql

-- Creates a function to run when user is authenticated (signed up)
create or replace function public.on_user_signup() 
returns trigger as $$
begin
  insert into public.profiles (id, email, updated_at, first_name, last_name)
  values (
    new.id, new.email, 
    now(),
    NEW.raw_user_meta_data->>'first_name',
    NEW.raw_user_meta_data->>'last_name'
  );
  return new;
end;
$$ language plpgsql security definer;

-- Creates a trigger to run the above function when a new user is authenticated (signed up)
create or replace trigger create_user_profile
  after insert on auth.users
  for each row execute procedure public.on_user_signup();

-- Create a function to delete a user
create or replace function public.on_profile_delete()
returns trigger as $$
begin
  delete from auth.users where id = old.id;
  return new;
end;
$$ language plpgsql security definer;

-- Creates a trigger to run when a profile is deleted
create or replace trigger delete_auth_user
  after delete on public.profiles
  for each row execute procedure public.on_profile_delete();

1. First we create a function to create a new profile in the Profiles table.
2. Then we create a trigger to execute the above function when a new user is authenticated (means a new user is added to the “auth” table).
3. Then we create a function to delete the user from the “auth” table.
4. Then we create a trigger to run that function whenever a profile is deleted from the “Profile” table.

Note: I have used create or replace such that it doesn’t cause any issues when you run this query again and there’s already a trigger or function with that name. Keep in mind this will replace the already existing function (if you have) of the same name, so be careful when executing this script.

1. Test these changes by signing up with a new account. A new record should appear in the Profile table. Go to Table Editor (in the left navigation pane) > profiles and see there’s a new record.

1. Now delete this record manually from here, and check the authentication table to verify if the records are being deleted there.

Adding Row Level Security RLS to your tables

Note: It is recommended that you only access your Database from server-side code only. But since supabase uses public keys which are exposed to the client side of the NextJS framework, we need to secure the table data with RLS.

Row Level Security (RLS) in PostgreSQL is a powerful feature that allows database administrators to control access to specific rows within a table based on user roles or other conditions. This is achieved by defining policies that act as filters, determining which rows are visible or modifiable by a user during operations like SELECT, INSERT, UPDATE, and DELETE

When using Supabase in client-side (our Next JS client) applications, enabling RLS is crucial for securing data. Even if authentication is handled through server actions, enabling RLS on tables prevents unauthorized access via the anonymous key, ensuring that data is not exposed to unauthorized users. This provides an additional layer of security, acting as a "defense in depth" mechanism to protect data from malicious actors.

If your application only manipulates data from the server side using service keys, RLS policies are not strictly necessary. Service keys can bypass RLS, allowing server-side operations to access data without restrictions. However, enabling RLS without policies still blocks unauthorized access attempts via the anonymous key, providing some security benefits even in server-side scenarios.

1. Paste the following query in the SQL Editor and run it:

-- Enable RLS on profiles table
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- Policies for profiles table
-- ============================================================================

-- Policy: Allow users to read their own profile
CREATE POLICY read_own_profile ON profiles
    FOR SELECT
    USING (auth.uid() = id);

-- Policy: Allow users to update their own profile
CREATE POLICY update_own_profile ON profiles
    FOR UPDATE
    USING (auth.uid() = id);

-- Policy: Allow users to delete their own profile
CREATE POLICY delete_own_profile ON profiles
    FOR DELETE
    USING (auth.uid() = id);

I’ve added comments for each line to describe what each line is doing.

1. In the table editor, you can see now the “Open Lock” icon is gone from the profile table. Which means our profile table has RLS enabled.

Congratulations!

As we wrap up our detailed guide on creating a robust full-stack application with Next.js and Supabase, let's reflect on what we've accomplished. We started by setting up a brand new Next.js app, complete with essential authentication pages like Signup, Login, Forgot Password, and Reset Password, all styled with ShadCN and Tailwind CSS. This not only gave our app a sleek look but also provided a solid foundation for user management.

Next, we integrated Supabase to handle authentication and protect our routes, ensuring that sensitive data is only accessible to authorized users. We also made logging in easier for our users by setting up Google authentication, providing a seamless experience.

One of the most powerful aspects of our application is its advanced database management. We used Prisma ORM to create, migrate, and query tables efficiently. Additionally, we implemented Row Level Security (RLS) in our Supabase PostgreSQL tables, which means that only authenticated users can manage data they own. This extra layer of security is crucial for maintaining data integrity.

Throughout this journey, we've shown how Next.js and Supabase can be combined to build applications that are both secure and scalable. Whether we're creating a user management system or a complex data-driven platform, the techniques outlined here provide a solid foundation for success. By following these steps, developers can ensure their applications meet the demands of modern web development while keeping data safe and secure.

🔗 Check out the full code for this series here.

If you found this article useful, like, comment, and share, or just buy me a coffee?