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.
Hello HN! I made this tool to hopefully help computer vision and robotics folks play around with camera parameters and compare different commercial depth cameras. This tool was inspired by another, similar tool:
https://www.smeenk.com/webgl/kinectfovexplorer.html
Also shoutout to three.js for having an incredible variety of examples and great documentation to learn from. And credit to mixamo for the android-esque robot model.
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