Good article on what's possible and how to do it, but is row level security scalable in any way for a production application? Not so much on the performance impact of any one query but maintaining the definition of what a role can or can't do (if a db user = an application role). It also seems like it would complicate managing db connections as well, separate pools for each db user? If you have 10 roles, you have to open up at least 10 connections to avoid connection opening latency.
Leveraging most RDBMS security features seem to be geared for an ever shrinking set of use cases where a mostly static set of users are given direct access to a SQL prompt, or a simple record to GUI application interface.
Disclaimer: I am a founder of Authzed (W21)[0].
It always depends on the domain. If the data model for the app is simple enough, RLS can take you pretty far. Enterprise apps that require you to support the various vague interpretations of "RBAC" or domains that have more complex data models will eventually need some kind of more sophisticated authorization solution. There are a variety solutions at that point (e.g. SpiceDB[1], oso[2], OPA[3]) and you'll be making your decision based on not only the implementation of the technology, but concerns that have cropped in your business requirements:
- "How will additional microservices check permissions?"
- "How can we test and enforce that our authorization system is correct?"
- "Can I support user-defined permissions?"
[0]: https://authzed.com
And how exactly does one approach those 3 outlined questions?
> "How will additional microservices check permissions?"
SpiceDB is a database optimized for resolving subjects' access to resources. Being a database, it suggests storing the canonical authorization data within it and performing queries to it from various microservices. This is the strategy employed by most hyper-scalers and but also companies that have heavily invested in in-house authorization like like Airbnb and Carta.
> "How can we test and enforce that our authorization system is correct?"
SpiceDB has developers write schemas, but unlike other databases, it has tooling that can check assertions and audit all possible access. This tooling can be shared/explored via the Authzed Playground[1] or added to your CI/CD pipeline with GitHub Actions[2]
> "Can I support user-defined permissions?"
There are various ways to accomplish this with SpiceDB. User behavior can be used to pragmatically generate schemas or you can write very abstract schemas that push designs that are typically enforced at schema-validation/compile-time (think DDL) to runtime (think DML).