11 ms·

I qualified it by saying "if you're building an OLTP app" though. I speak from personal experience. If you know a good usecase for ACL in database for OLTP wor

I qualified it by saying "if you're building an OLTP app" though. I speak from personal experience. If you know a good usecase for ACL in database for OLTP workflows, I'm all ears.

blowski 5y ago
Even then, it would be better phrased as a tradeoff. What's the downside of putting in the database? I'd guess it makes horizontally scaling harder, and it's less portable to other database vendors. Is there an upside, like you've got stronger data security guarantees?
- picardo 5y ago
  Good point. Phrasing it as a trade-off is the better approach. Agree with the downsides you listed, as well. Better security guarantees, for sure, but if an unauthorized user gains direct access to your database, you might have bigger problems from a security perspective.
- pfarrell 5y ago
  Agree with all points here. An upside to using db to handle data permissions is the data has the same protections if you are accessing with direct SQL or using an app. Also, those permissions would persist with the data in restored backups. I’m not advocating this and think for OLTP, building RBAC in the app layer is almost always a better idea, but these would be some benefits.
mcherm 5y ago
> If you know a good usecase for ACL in database for OLTP workflows, I'm all ears. Here's one that's meaningful to me. We have a single database with five different applications that access it, each of them managed by a separate team. By enforcing access constraints in the database we guarantee the access constraints will be applied in all cases. It is difficult to ensure that in application code managed by separate teams. (Just to be clear: I don't think your advice is poor, I just wanted to give an example of a case where it isn't universally applicable.)
- picardo 5y ago
  I see, thanks for your input. Most of the workflows I worked on involved provisioning one database per app, so I hadn't entertained this angle. Are there any risks to changing ACL rules on a production database server from a stability perspective?
  - sigstoat 5y ago
    > Are there any risks to changing ACL rules on a production database server from a stability perspective? i've never seen anything impact postgres stability. they test their code. i've made DDL changes on live systems; that seems far more concerning than ACLs.

I qualified it by saying "if you're building an OLTP app" though. I speak from personal experience. If you know a good usecase for ACL in database for OLTP workflows, I'm all ears.

blowski 5y ago

Even then, it would be better phrased as a tradeoff. What's the downside of putting in the database? I'd guess it makes horizontally scaling harder, and it's less portable to other database vendors. Is there an upside, like you've got stronger data security guarantees?

picardo 5y ago
Good point. Phrasing it as a trade-off is the better approach. Agree with the downsides you listed, as well. Better security guarantees, for sure, but if an unauthorized user gains direct access to your database, you might have bigger problems from a security perspective.
pfarrell 5y ago
Agree with all points here. An upside to using db to handle data permissions is the data has the same protections if you are accessing with direct SQL or using an app. Also, those permissions would persist with the data in restored backups. I’m not advocating this and think for OLTP, building RBAC in the app layer is almost always a better idea, but these would be some benefits.

mcherm 5y ago

> If you know a good usecase for ACL in database for OLTP workflows, I'm all ears. Here's one that's meaningful to me. We have a single database with five different applications that access it, each of them managed by a separate team. By enforcing access constraints in the database we guarantee the access constraints will be applied in all cases. It is difficult to ensure that in application code managed by separate teams. (Just to be clear: I don't think your advice is poor, I just wanted to give an example of a case where it isn't universally applicable.)

picardo 5y ago
I see, thanks for your input. Most of the workflows I worked on involved provisioning one database per app, so I hadn't entertained this angle. Are there any risks to changing ACL rules on a production database server from a stability perspective?
- sigstoat 5y ago
  > Are there any risks to changing ACL rules on a production database server from a stability perspective? i've never seen anything impact postgres stability. they test their code. i've made DDL changes on live systems; that seems far more concerning than ACLs.