RLS sounds great until it isn't

Row Level Security (RLS) in Postgres provides a method for defining security policies at the database level, similar to assigning specific keys to areas within a house, to control access to data. While initially appealing for its promise of granular data protection, RLS introduces significant challenges, such as the complexity of testing and scaling policies, the difficulty of managing connections with tools like PgBouncer, and the risk of performance degradation due to repeated evaluations of security functions. The system also presents an increased attack surface where malicious users can exploit resource-intensive queries, potentially leading to a denial of service without proper application-level authorization checks. Additionally, managing RLS policies becomes cumbersome as they are not typically handled alongside code changes, causing potential drift and synchronization issues. This complexity often leads to the recommendation of implementing security at the application layer, using middleware or ORM-level scoping to ensure logic remains visible, testable, and efficient, thereby avoiding the pitfalls of RLS while maintaining robust data security.