[wichert]

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

[yoloClin]

Is column based authorisation possible?

What about group/role based security concepts?

[chishaku]

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

[yoloClin]

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.

[dragonwriter]

> Is column based authorisation possible

Yes.

> What about group/role based security concepts?

Yes, those have been standard in RDBMSs for decades.

[z3t4]

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

[fauigerzigerk]

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.

[taffer]

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

[fauigerzigerk]

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

[ruslan_talpa]

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

[fauigerzigerk]

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.