Hi everyone! I'm developing a CRUD web application designed to help organizations manage specific workflows. My app uses the T3 stack with Clerk for authentication, Drizzle for ORM, tRPC for API calls, and PostgreSQL hosted on Neon. Users are assigned to only one organization, and I'm currently implementing what I believe is API-level security. For instance, I utilize organization IDs from Clerk to filter data in my tRPC router. However, I'm worried about accidentally leaking data between organizations due to potentially missing the necessary filters in API calls. I've looked into options like Row Level Security (RLS), but I'm concerned about its complexity with connection pooling. I've also considered creating a wrapper for API calls to enforce organization ID filtering, but that seems tedious and may require a significant rewrite of my existing code. I'd love to hear your thoughts or experiences on how to effectively ensure data separation between organizations. Any advice would be greatly appreciated!
2 Answers
If keeping organization data separate is crucial, I'd recommend adding a layer of protection like Row Level Security (RLS). I faced similar challenges before, and while every approach has its downsides, RLS can help ensure that even if you forget to filter by organization IDs in your queries, the database won’t allow unauthorized access. It might add complexity, especially with managing session variables, but it’s worth it for critical business applications. If you're new to web development, consider using an adapter pattern with your database context to streamline this process. It could help without needing to rewrite too much code.
Before processing any data, it's vital to validate that users have access to it. Since users are tied to a single organization, you can load both the user and their associated organization through authentication. This way, regardless of the organization linked to the request, the user will only have access to their own data. For users with Role-Based Access Control (RBAC) across multiple organizations, ensure that your authentication system verifies their validity and permissions. In my experience, setting up layered security, including row and cell level checks, can really enhance security, but it takes some time to get right.
Absolutely! Adding RLS can bolster your security by ensuring that even the best coding practices can’t slip through the cracks. It's a safety net, just in case you miss a filter in your queries. It does add some setup complexity but can be worth it in the long run.
Thanks for the advice! I already have middleware that ensures users are authenticated and belong to an organization. My concern is mainly at the query level, where I check that the organization ID matches the data being accessed. Would you suggest implementing Row Level Security on top of this process to further secure it?