3 ms·

Hm, I'm curious. How do you implement database level access control for the generic case of website with hundreds of thousands users? Do you suggest, given a po

Hm, I'm curious. How do you implement database level access control for the generic case of website with hundreds of thousands users? Do you suggest, given a postgres database for example, to add the same amount of roles, and to have individual db connections initiated per each request?

mvindahl 10y ago
Hmm.. may depend upon the granularity that you need I guess. As far as I recall, databases do implement access control at table level, but often you need better control than that. In the system that I most recently worked on we had to implement our own access control logic as table level was too coarse. It was a datacenter management system which supported multiple users, each with a set of roles, and each user belonging to several companies. The data center inventory that would be returned by queries depended both upon your role (sysadm vs customer admin vs regular Joe) and upon your company affiliation. I guess I'm mostly writing this to illustrate how quickly this kind of thing becomes closely intertangled with the domain and with your business logic. Will try to answer your questions to the best of my ability: 1) The amount of users is not a scalability issue but the number of requests may be, of course. I'll assume that's what you mean. My best advice would be to profile your system to see how it behaves at great load. If access control turns out to be a problem, maybe you are doing too complex joins. One path forward may be to "denormalize" your database scheme, i.e. accept that the same information is stored in several places for the sake of efficiency. Another idea is to spin up an Elastic Search box to help with the query that is your #1 bottleneck. 2) As for the question about individual database connections, well, I think I'd pool them or I would build upon some framework or app server which pools them. It's an easy win with standardized solutions. Caveat: Bear in mind that although I seem to be posing as some kind of expert right now, I'm really not. I'm more of an all-round full stack kind of guy. So seek advice from multiple sources :)
ruslan_talpa 10y ago
You treat database roles as user groups (admin/employee) and based on that define table(view) level access. Then whenever a request is made, your api code switches to the appropriate role and sends to the database the specific user id (CUGs for example). Then you use views/RLS to filter access to specific rows.

mvindahl 10y ago

Hmm.. may depend upon the granularity that you need I guess. As far as I recall, databases do implement access control at table level, but often you need better control than that. In the system that I most recently worked on we had to implement our own access control logic as table level was too coarse. It was a datacenter management system which supported multiple users, each with a set of roles, and each user belonging to several companies. The data center inventory that would be returned by queries depended both upon your role (sysadm vs customer admin vs regular Joe) and upon your company affiliation. I guess I'm mostly writing this to illustrate how quickly this kind of thing becomes closely intertangled with the domain and with your business logic. Will try to answer your questions to the best of my ability: 1) The amount of users is not a scalability issue but the number of requests may be, of course. I'll assume that's what you mean. My best advice would be to profile your system to see how it behaves at great load. If access control turns out to be a problem, maybe you are doing too complex joins. One path forward may be to "denormalize" your database scheme, i.e. accept that the same information is stored in several places for the sake of efficiency. Another idea is to spin up an Elastic Search box to help with the query that is your #1 bottleneck. 2) As for the question about individual database connections, well, I think I'd pool them or I would build upon some framework or app server which pools them. It's an easy win with standardized solutions. Caveat: Bear in mind that although I seem to be posing as some kind of expert right now, I'm really not. I'm more of an all-round full stack kind of guy. So seek advice from multiple sources :)

ruslan_talpa 10y ago

You treat database roles as user groups (admin/employee) and based on that define table(view) level access. Then whenever a request is made, your api code switches to the appropriate role and sends to the database the specific user id (CUGs for example). Then you use views/RLS to filter access to specific rows.