Hacker News Reader

Comment by z3t4 | Hacker News Reader

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.

View on HN

You 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-u...

It 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.

The 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

I 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.

PostgREST performs authorization at transaction level, not at connection or session level, so a connection can be used by thousands of active users simultaneously.

Oh that's very interesting indeed! I thought each open connection remained linked to exactly one authenticated user account until it is closed.

This is very nice! For many CRUD apps you could skip the back-end side entirely!

Is column based authorisation possible?

What about group/role based security concepts?

You can create a view with a subset of columns and grant permissions on the view.

I 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.

> Is column based authorisation possible

Yes.

> What about group/role based security concepts?

Yes, those have been standard in RDBMSs for decades.

Reply on news.ycombinator.com