[chrisdsaldivar]

Security is absolutely important. You’re right that most programmers don’t consider it and even my CS degree programming never emphasized security. However, I did a cybersec emphasis so I always consider the security implications of a system at the design stage. It’s significantly easier to build a secure system than it is to retrofit an insecure one.

One of the most important concepts is the principle of least privilege which is rarely ever discussed. Every tutorial I’ve seen, even paid ones, (this isn’t to say all of them) give their app master DB credentials. You could vastly improve your applications security just by leveraging your DB engine’s native access control.

For those using Postgres here’s a great resource for setting up RBAC: https://www.postgresql.org/docs/9.0/user-manag.html

[ralala]

How much of the typical application server authorization logic can we put into the database? Is it possible to setup authorization on a cell-level? Example: All users may change their own passwords, but only team admins may change the passwords of other users in their team.

[chrisdsaldivar]

I have never attempted this particular use case but, as far as I can tell this isn’t possible. While it is possible to grant privileges to change another user’s password, you would have to either make the team admin a super user (this grant all possible privileges) or by granting the Create Role privilege (which would allow them to create a new role with privileges they aren’t intended to have and switch to that role) so neither of these options are really any good.

A clarification on my previous comment: The way I’ve used Postgres RBAC is to create roles for each service in my application that needs DB access. For example say you have a service (in my case typically a lambda function) that only ever reads data from the DB and only from specific tables. I would create a role that only grants SELECT privileges for those specific tables. This also disallows UPDATE, DELETE, ALTER etc. privileges. Then assign this service that role. This mitigates the possible damage done if that service is compromised and shrinks your applications attack surface.