4 ms·
Often you do not want users to have access to a whole table, but only posts made by the user, or posts to to user. I could however see this replace Excel apps.
by z3t4 7y ago
Often you do not want users to have access to a whole table, but only posts made by the user, or posts to to user. I could however see this replace Excel apps. But then you will also have to generate the user interface for it to be useful. The developer should only have to specify the views, the rest can be automated. I once made such a tool in order to save a few hundred man-hours on a tight budget, and it worked fairly well. But for most apps you want to customize every layer.
- wichert 7y agoYou can do that with row-level security. The PostgREST documentation has examples for that specific use case: https://postgrest.org/en/v6.0/auth.html#roles-for-each-web-user https://postgrest.org/en/v6.0/auth.html#roles-for-each-web-u...
- fauigerzigerk 7y agoIt sounds like this would limit scalability quite a bit because you'd either have to keep a DB connection open for each active user or close connections rather aggressively.
- ruslan_talpa 7y agoThe row level security is a feature of the database (postgresql), those rules are written and enforced by the database, they have nothing to do with PostgREST and how it connects to the database
- deleted 7y ago[deleted]
- fauigerzigerk 7y agoI am aware of that but I thought that this approach would effectively prevent sharing pooled connections between different users. But taffer says otherwise, so that solves the problem I was wondering about.
- taffer 7y agoPostgREST performs authorization at transaction level, not at connection or session level, so a connection can be used by thousands of active users simultaneously.
- fauigerzigerk 7y agoOh that's very interesting indeed! I thought each open connection remained linked to exactly one authenticated user account until it is closed.
- z3t4 7y agoThis is very nice! For many CRUD apps you could skip the back-end side entirely!
- yoloClin 7y agoIs column based authorisation possible? What about group/role based security concepts?
- chishaku 7y agoYou can create a view with a subset of columns and grant permissions on the view.
- yoloClin 7y agoI feel like this is just moving business logic /back/ into the database. It' very similar to what we were doing with stored procs 15 years ago and just moves the problem from business logic back to database layer. Given the choice, I'd prefer to write constraints in !SQL, personally.
- dragonwriter 7y ago> Is column based authorisation possible Yes. > What about group/role based security concepts? Yes, those have been standard in RDBMSs for decades.