Use the JSON field datatype to make authentication flexible

Authentication is the bugbear of so many web applications. Aside from the security issues, which have plagued such giants as LinkedIn, I’ve personally seen application development stall entirely as new requirements throw a wrench into the established authentication solution.

With that in mind, I endorse using the JSON datatype to simplify the many possibilities around security and roles which can pop up during the development lifetime of an application, and reduce the need for database migrations. I’ll use Flask with the Flask-Login plugin as the basic for my examples, but this should be extendable to other web frameworks or your entirely-from-scratch solution.

Let’s say you’ve built a web application, and you’ve allowed for two roles: user and admin. Here’s your User  table (shown as a SQLAlchemy class):

Everything’s going great. You can easily restrict admin-only resources by doing a simple column check.

Your application grows and grows and scope creep starts to happen: pretty soon HR comes in with the complaint that there are too many admins and there should be another layer of authority which has access to resources unavailable to admins and users. Let’s say they want a “supervisor” role. Well okay, you can just add another field to your User  table and the requisite logic to your routes:

But wait, in order to pass regulatory compliance, users should have to change their password every 30 days, and they can’t use any of the previous three passwords they’ve chosen. Well, okay, we can solve the first requirement by adding a new field:

But the second field requires us to add a new table and some relationships, unless we want to use something like the “Array” type in Postgres. Totally doable, but it’s annoying, especially because the password hashes aren’t interesting in a relational context. In other words, the password hashes for a given user aren’t ever compared to those of other users. When you see this pattern you should at least consider whether a JSON type can provide a better solution.

Oh, and the COO wants to keep track of when people last logged in. The project manager thinks there should be email activation with an activation key and expiration date. The compliance director thinks roles should be more granular, and that there might be a need to add an “auditor” role which has access to some supervisor and admin sections, but not all. This would throw a huge monkey wrench into the “numerical rank” authentication scheme which is common from Rails-era applications, where a user of a given rank gets access to all resources at or below the rank number. Pretty soon, you’re looking at something like this:


Using the JSON datatype, you can easily include these various necessary components into a single field. So in your User  database table:

And this field for a given user might look something like this:

and you can make flexible rules accordingly. Here’s an example rule for checking if a user’s account has been activated:

So if you add the activation feature after users already exist in the application, then the lack of an activated  key in the JSON for the meta  field is indicative of that. Otherwise, return the value of the key for investigation.

A natural question that comes about is: “well, surely I’ll be asked to get a list of people with supervisor permissions.” There are plenty of scenarios where queries across the User  table will be necessary.  If you’re storing last login info, there might be a question of “how many people haven’t authenticated in the last sixty days?” Or “how many people are past due for a password change?” These are valid concerns, but all are manageable because you can run queries on the JSON properties.

JSON fields can be convenient by reducing the complexity of your database schema. They allow for easy addition of new features in a flexible fashion. User authentication is notoriously tricky because it’s central to an application but yet can be subject to many possible requirements and scope adjustments over the application’s lifetime. You should carefully consider the frequency and various ways in which the data you want to encapsulate within the JSON field will possibly need to be queried, and you should never exploit the convenience and cool factor of JSON just to be lazy or to make an end-run around something that would be better kept strictly relational.

Leave a Reply

Your email address will not be published. Required fields are marked *