[brownkonas]

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.

[jzelinskie]

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

[1]: https://github.com/authzed/spicedb

[2]: https://www.osohq.com

[3]: https://www.openpolicyagent.org

[ewuhic]

And how exactly does one approach those 3 outlined questions?

[RedShift1]

You don't need 10 different connections, you can switch roles in a transaction. You connect using a role that can impersonate other roles and then run your queries like this:

begin;

set local role myrole; -- the important part

SELECT * FROM page;

commit;

[WinterMount223]

Can you refer to roles? Something like “set local role (SELECT name FROM roles WHERE id = 7)”

[grschafer]

Good questions! Regarding maintaining the definition of what a role can or can't do -- I think this comes down to how you organize your SQL. If you keep authz declarations in one place, it's going to be more maintainable than if they're spread across many database migrations. One way you can keep those authz declarations in one place is by doing development/maintenance on that one place then using a database-diffing tool[1] to generate migrations based on whatever changes you made.

Regarding database connections -- one way to avoid needing a connection per user is to use something like PostgREST[2] to handle incoming requests, identify the user making the request, and use an existing db pool connection to switch roles and execute whatever queries are requested. EDIT: RedShift1 beat me to this explanation by a little bit! :)

RLS certainly isn't the answer for every domain or problem size, but I've been surprised by how powerful it is compared with how relatively unknown it is.

[1]: https://supabase.com/blog/2021/03/31/supabase-cli#migrations

[2]: https://postgrest.org/en/stable/auth.html

[ctxc]

You can get pretty far with RLS. First discovered this when I started working with Supabase.