Tutorials on Security

If you are using PostgreSQL for storing data of multiple users, you might want to apply row-level security, or RLS. It’s good practice even if you are manually writing all the queries you send to your database but it’s especially important if you have any type of LLM or similar generating queries for you! Let’s create a trivial data model. Users and items, whatever that might be. Each item belongs to a user. Now, per default, if you ask the database about any users items, it will just tell you. By introducing RLS, you can limit what the responses will be to add a layer of protection. Even if you should create a buggy query, you will not accidentally get the items belonging to someone else, just like you cannot accidentally change or delete items belonging to someone else. We do that like this: Note that app.curren user id isn’t something PostgreSQL knows about per default, it’s a variable that we have introduced. Now basically, we need to execute SET app.curren user id = '(user_id)' , before making some user-specific query. Or if you have a transaction, you could use SET LOCAL which will make the transaction scope function as “auth scope” as well. Depending on your setup, there are going to be different ways to set the app.curren user id setting. In this example, I will assume a setup based on Express or similar, and Knex for creating queries, but the pattern can easily be changed to fit whatever your setup requires. The app.curren user id needs to be set only once per session. This session variable is local to the database connection, meaning each connection has its own set of session variables. When using Knex or similar to execute a query, it acquires a connection from the pool. The session variables set in that connection are isolated from other connections. Now, with Express and similar frameworks, you will typically use the context variable to store state that is related to a given request. You might already have some code in middleware that checks a token and fetches the corresponding user from the database. What we could do is to put a knex instance into this context, and make sure that instance has set the user id. However, this forces us to do quite a bit of “prop drilling” as we need to pass this instance around as a parameter to any function that needs to access the database. What we can do instead is to use a class called AsyncLocalStorage which allows us to store state related to a specific async context. This is similar to “thread-local storage” in other languages, only we don’t have threads in NodeJS but async contexts. Apply the dbMiddleware to your server whereever you set up other middleware, and now you can use the getKnexInstance to get the authenticated database connection. Here is an example of a very simple server: As you can see, this endpoint selects every items row. I don’t recommend this, you should still create your queries as you normally do, but even if you released this, no items belonging to other users would leak anywhere. Mastering Row Level Security in NodeJS doesn't have to be intimidating. With this guide as a base, you can embark on your journey toward something bigger. To enhance your skills further, check out my course Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL . Happy learning!