3 ms·

We don't use django, but we basically do this, we put python in the DB (PL/pythonu) and it's hosted in a VCS repo and use liquibase[0] to handle getting it into

by zie 8y ago

We don't use django, but we basically do this, we put python in the DB (PL/pythonu) and it's hosted in a VCS repo and use liquibase[0] to handle getting it into the DB, along with our schema's, etc. Debugging can be a little harder, but testing generally isn't you know the calling signature(s) PG uses so writing a class for UnitTest/pytest is straightforward. Our APP logins ARE DB logins for our desktop product(s), but for web we have a single DB user that web apps log into the DB as. We'd love for web logins to become PG logins as well, but we haven't found a very performant way of doing that yet. PG sessions are a heavy startup cost. The functionality for us, isn't the same between the web and desktop product(s) which helps a lot. We push access control into the DB for the most part, using row, column and table level access controls PG provides us. So our desktop product has basically no access controls in it, since the DB provides all of that at login time. We have a few power users that connect directly with SQL, and we have zero issues just handing them access, since the DB controls what they can/can not see just fine. Sadly so many tools just assume their DB connection is the equivalent to root, and can just go do whatever it wants, which makes lots of tools, especially reporting tools, hard to just hand directly to end users. [0] http://www.liquibase.org/ http://www.liquibase.org/

camel_Snake 8y ago
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.

by zie 8y ago

camel_Snake 8y ago

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.