Restrict Access to the Messages Table in a Database with Row Level Security (RLS) Policies
Row Level Security denies all access to the database. Select, insert, update and delete queries will be blocked by default. This allows us to write access policies in the database itself, to allow only what our application needs to function.
In this lesson, we look at adding a user_id column to the messages table, which has a foreign key relationship to the auth.users table - what Supabase uses to manage authentication and sessions.
Additionally, we step through a common migration pattern to use when existing data conflicts with the constraints of a new change in structure - we want each message to belong to a user, but the existing data had no column for user_id. This requires a three-step process where we add the column without the not null constraint, update the existing data to belong to a user, and then add the not null constraint to the user_id column.
Lastly, we update our RLS policy to only allow read access to signed in users.
Code Snippets
SQL code snippets can be run against your Supabase database by heading over to your project's SQL Editor, pasting them into a new query, and clicking
RUN.
Add column with not null constraint
alter table public.messages
add user_id uuid references auth.users not null;
Add column without not null constraint
alter table public.messages
add user_id uuid references auth.users;
Add not null constraint to column
alter table public.messages
alter column user_id set not null;
Alter RLS policy to require authenticated user
begin;
alter policy "users can read messages" on "public"."messages" rename to "authenticated users can read messages";
alter policy "authenticated users can read messages" on "public"."messages" to authenticated;
commit;
Resources
Instructor: [0:00] Now that users can sign into our application, let's restrict our messages to only display if our user is currently logged in. Let's start by adding a new column to our messages table in Supabase, which is going to store the user that sent this message. [0:13] Let's add a new column to our messages table. The name of this is going to be user_id. Since this column is going to be a reference to the table that Supabase uses to manage Auth sessions, we're going to click add foreign key relation.
[0:27] Now, the table we would like to reference is in the Auth schema and it is called users and the column we would like to reference is the ID column. Let's click save to set up that relationship, and since a message should always belong to a particular user that sent it, we would like to untick this allow nullable tick box.
[0:45] However, when we click save, we'll get this error because the existing messages in our database don't yet belong to a user. In order to create our new column, we'll leave this allow nullable tick box enabled and click save. Then to get the ID for our currently signed-in user, we'll head over to authentication and then users.
[1:03] Then we'll see this is my account that I used GitHub to sign up for. Over here, I have my user ID which I can click to copy. We can then go back to our Table Editor and messages, and then right-click our field for user ID and say edit row.
[1:16] We can then add our user ID here. You can also click view data, and this will show us the full row that this user ID value is referencing. We can see all of the different fields that Supabase uses for managing the session of this user.
[1:28] Let's click close and then scroll down and save to write this value to the column. We'll then do the same thing for our second message. Now, we can edit this user ID column and untick this field for Allow Nullable, meaning that we must have a user for every single message in our database.
[1:46] Since we've now changed the structure of our database, our TypeScript types will actually be incorrect. TypeScript has no idea about this user ID column. We can use the same command as earlier to generate our TypeScript types.
[1:58] If we now look at the shape of our message, we'll see our user_id column, which is flowing through our entire application. If we restart our development server, tapping npm run dev, and then refresh our application in the browser, we'll see that our user_id column is also coming back from Supabase.
[2:16] If we log out our user, we're still seeing these results even if we refresh. Let's update our row-level security policy in Supabase, which is currently just enabling read access for anyone and let's instead set the target role to be authenticated. This means that the expression here will only be evaluated if the user is currently signed in.
[2:36] Otherwise, the default behavior of RLS will happen which is denying all actions by default. In other words, we are only allowing signed-in users to read messages.
[2:45] Let's click review to update our policy and save to run that SQL. Now, if we go back to our application and refresh, we'll see that we don't get any results from Supabase because that user is not currently signed in.