Have you ever received your neighbor’s package at your front door? (Maybe even accidentally opened it?) Maybe you’ve had a sensitive voicemail left for someone else? As application developers, it is your job to ensure that sensitive data stored in your application does not get accidentally delivered to another party.
There are many techniques available to securely store customer data, and many are highly complex and difficult to implement. Ideally, one could secure all of the customer data in a single database – keeping the design of the feature simple and secure.
Row-level security (RLS) is the ability to secure and control access to specific rows of data inside a database table. It is a powerful tool that allows you to store all your customer data in a single database without any concern about data leakage across accounts. However, correctly implementing RLS can be a tricky process involving combining login details with your database permissions. Neon Authorize streamlines this process by automatically integrating authentication from your OAuth provider with your PostgreSQL database.
Neon Authorize utilizes your existing authentication layer to identify every logged-in user and associates all the data in your database with their login credentials. This ensures that the data stored in the database can only be accessed by logged-in users—and that only logged-in users can see their data.
This tutorial will walk you through how to build a Remix app using Clerk as an authentication layer. Clerk is a popular user authentication and management tool. You’ll use Neon Postgres as your data layer and leverage Neon Authorize to secure all the data for each logged-in customer. Each row in the table will designate a userID, which is provided by Clerk. Only those authenticated with the userID can interact with data in the row.
Our sample application is simple—it records every login into the RLS database, using the userID. When the page is loaded, the last 10 logins for the authenticated user will be displayed, and no other user’s data (stored in the same PostgreSQL table) will appear. Let’s get started!
Creating the Remix app
Start by creating a Remix application and installing dependencies using the code snippet below. For more detailed instructions, refer to the Remix quick start guide.
##make a directory and initialise your NPM project
mkdir neon-authorize-remix-clerk-app
cd neon-authorize-remix-clerk-app
npm init -y
## install runtime dependecies for Remix
npm i @remix-run/node @remix-run/react @remix-run/serve isbot@4 react react-dom @remix-run/router drizzle-orm
npm install @neondatabase/serverless
npm install @clerk/remix
npm i -D @remix-run/dev vite
Since Remix uses Vite, a Javascript build tool, create vite.config.js in the root directory:
import { vitePlugin as remix } from "@remix-run/dev";
import { defineConfig } from "vite";
export default defineConfig({
plugins: [remix()],
});
Before doing any development, we need to create accounts at Clerk and Neon to utilize their services:
Creating Clerk and Neon instances
Clerk
Sign in to the Clerk dashboard to create a new project.
-
In the left navigation, choose API Keys.
- In the Quick Copy box, choose Remix, and copy the environmental variables.
- Paste them into a
.envfile in your code.
-
In the left navigation, select “JWT templates.”
- Create a template (I named mine “neon-remix”).
- Copy the JWKS Endpoint URL for later use.
Neon
-
Log into the Neon console and create a new project.
-
From the left navigation menu, select Authorize.
-
Create a new provider and paste the Clerk JWKS URL that you copied from Clerk earlier.
Once you have created the instance, click “Get Started.” A side panel will open with a series of steps to complete your Neon Authorize integration.
The get started setup provides you with steps to set up a basic Authorize project with Clerk.
1. Set up Neon Extension and Roles Privileges. Run these steps in the Dashboard.
2. Grant privileges to the roles in the neondb database.
Setting up Row Level Security
The provided code is for a todos app. Instead of using the provided boilerplate code from Neon for a todos app, we will create a login_history table and set up RLS on it. Open the SQL Editor in the Neon dashboard and run the code below. The login_history table will be used to store login times for each user.
Note that
login_historyhas just three columns: the id, user_id, and login_at. The last two columns will display the most recent logins in the application.
CREATE TABLE login_history (
id bigint generated by default as identity primary key,
user_id text not null default (auth.user_id()),
login_at timestamp not null default now()
);
-- 1st enable row level security for your table
ALTER TABLE login_history ENABLE ROW LEVEL SECURITY;
-- 2nd create policies for your table
CREATE POLICY "Individuals can add login." ON login_history FOR INSERT
TO authenticated
WITH CHECK ((select auth.user_id()) = user_id);
CREATE POLICY "Individuals can view their own logins. " ON login_history FOR SELECT
TO authenticated
USING ((select auth.user_id()) = user_id);
Add the provided environmental variables to your .env
Once these setup steps are completed, your .env should have four variables: two from Clerk and two from Neon:
CLERK_PUBLISHABLE_KEY=pk_test_....
CLERK_SECRET_KEY=sk_test_...
# Database owner connection string
DATABASE_URL='postgresql://neondb_owner:...'
# Neon "authenticated" role connection string
DATABASE_AUTHENTICATED_URL='postgresql://authenticated@ep-...
Building the Remix app
The application is now ready to be built. The full code is available on GitHub, but the most important features are highlighted here. The core of the application is in app/routes/_index.tsx:
export const loader: LoaderFunction = async (args) => {
const { userId, getToken } = await getAuth(args);
if (!userId) {
return redirect("/sign-in");
}
const authToken = await getToken();
console.log(userId);
if (!authToken) {
return null;
}
const DATABASE_AUTHENTICATED_URL=
process.env.NEXT_PUBLIC_DATABASE_AUTHENTICATED_URL;
try {
const sql = neon(DATABASE_AUTHENTICATED_URL ?? '', {
authToken,
});
const loginResponse = await sql(`INSERT INTO login_history ("user_id") VALUES ($1) RETURNING *`,[userId]);
// Retrieve last 10 logins
const last10LoginsResponse = await sql(`SELECT * FROM login_history WHERE user_id = $1 ORDER BY login_at DESC LIMIT 10`, [userId]);
console.log(`loginResponse: ${JSON.stringify(loginResponse)}`);
return last10LoginsResponse as Array<LoginHistory>;
}
catch (error) {
console.error(`Error inserting into login_history table: ${error.message}`);
console.error(`Error details: ${JSON.stringify(error)}`);
throw error;
}
}
The LoaderFunction in the _index.tsx file completes tasks on the server before rendering the page for the client. In this application, the loader does much of the heavy lifting of the app.
The function first checks if the user is not logged in and then redirects the user to the /sign-in page. The sign-in page can be configured in the Clerk dashboard to accept different login types, such as Google and email logins:
To create the sign-in page, navigate to the Clerk dashboard and set up the necessary login methods for the project.
If the user is logged in, the function retrieves the userId and authToken from Clerk. These values are essential to ensure the user is logged in, and then you can use the userId to populate each row in your database.
In order to make changes to the RLS-secured database, you need to pull the DATABASE_AUTHENTCATED_URL from the environment variables.
The core logic for implementing RLS security lies within the LoaderFunction. An SQL Neon instance is initialized using the environmental variables and the auth token. The loginResponse function makes an SQL call and inserts the user_id (and the current time) into the PostgreSQL database, after which the last10LoginsResponse function queries the DB for the 10 most recent logins.
Finally, the last10LoginsResponse is returned from the loader function.
The Index() function in _index.tsx file renders the layout of the page as shown in the snippet below:
export default function Index() {
const logins = useLoaderData();
return (
<div>
<h1>Signed in</h1>
<p>You are signed in!</p>
<p> <UserButton /></p>
<div>
<h1>Recent Logins</h1>
{logins?.map((logins) => (
<li key={logins.id}>
{logins.user_id} login at: {logins.login_at}
</li>
))}
</div>
<p>< SignOutButton > Sign Out</ SignOutButton ></p>
</div>
);
}
The code above retrieves the response from the LoaderFunction, which contains the last 10 login entries. This response builds a page that tells the user that they are signed in, lists their last 10 logins, and shows a Sign Out button as shown below:
In this example, the user_id is also displayed to clearly indicate that only the login data for the logged-in user is visible.
Using an incognito window, you can log in with a second Google account, and view side-by-side data for different users:
Note that the login times overlap, but by using Row-Level Security in the database, you’ll prevent data leakage across accounts. Rows can only be extracted and displayed for the authenticated user.
Conclusion
Keeping data private is a critical use case. As applications often store private information, it must be secured to keep the data in the right hands. Consumers have more and more legal protections like GDPR, and tools like Neon Authorize make it easy to implement Row Level Security to protect your customer’s data.
In this post, we have walked through the steps required to enable Row Level Security in a Neon database. Using RLS with our customer’s data ensures that only the logged-in user has the credentials to extract their own data.
Add Row Layer Security to your app today with Neon.
