3 ms·

I think Postgrest[0] takes an approach to authorization you may want to look into. My understanding is they use one base user that is granted the ability to sw

I think Postgrest[0] takes an approach to authorization you may want to look into. My understanding is they use one base user that is granted the ability to switch roles to reflect the current user once they're authed by your app server. Have you transitioned your business logic into your DB from your app or is that approach something you started with? [0] http://postgrest.org/en/stable/auth.html http://postgrest.org/en/stable/auth.html

zie 8y ago
We started with it. It's always best to offload your security/privacy posture onto something well known and supported rather than rolling your own, if possible. It's hard stuff to get right, and PG does it really well, especially in more recent versions. Row/Column security in the 8.X series was a bit painful :) We looked into doing something similar, but it didn't scale to our workload and had a ton of edge cases we kept getting caught up in. If a breach on the PG connection happens with that approach, the world gets super bad, super fast, as if I remember right, you need to be a super user to switch roles like that. That's very, very worrisome exposing super-user credentials like that to a website. If I remember right, we had to throw the connection away after using it for some reason, so the startup-cost gets cut some, but if you have a mass influx of users and your pool size is not gigantic, it gets painful waiting for new PG connections to come up. Plus the cost of holding open so many PG connections if your pool size is too large.... And our product has a module where all users attack it pretty much all at once, twice a day... this was back in the 8.X series of PG, so maybe it's a lot better now... If postgrest manages to make it scale to our workload and took care of all the edge cases well, it would be super awesome. I'll have to check into how they handle that.. maybe it's something we can look into adding in the future, thanks!
- steve-chavez 8y ago
  > you need to be a super user to switch roles like that No need for superuser, only membership to the role that it's going to switch is required. i.e. a `GRANT web_user TO connector`. Besides that, only privilege the connector role requires is LOGIN.

zie 8y ago

We started with it. It's always best to offload your security/privacy posture onto something well known and supported rather than rolling your own, if possible. It's hard stuff to get right, and PG does it really well, especially in more recent versions. Row/Column security in the 8.X series was a bit painful :) We looked into doing something similar, but it didn't scale to our workload and had a ton of edge cases we kept getting caught up in. If a breach on the PG connection happens with that approach, the world gets super bad, super fast, as if I remember right, you need to be a super user to switch roles like that. That's very, very worrisome exposing super-user credentials like that to a website. If I remember right, we had to throw the connection away after using it for some reason, so the startup-cost gets cut some, but if you have a mass influx of users and your pool size is not gigantic, it gets painful waiting for new PG connections to come up. Plus the cost of holding open so many PG connections if your pool size is too large.... And our product has a module where all users attack it pretty much all at once, twice a day... this was back in the 8.X series of PG, so maybe it's a lot better now... If postgrest manages to make it scale to our workload and took care of all the edge cases well, it would be super awesome. I'll have to check into how they handle that.. maybe it's something we can look into adding in the future, thanks!

steve-chavez 8y ago
> you need to be a super user to switch roles like that No need for superuser, only membership to the role that it's going to switch is required. i.e. a `GRANT web_user TO connector`. Besides that, only privilege the connector role requires is LOGIN.