Home page Portfolio

Building a CRUD app with just a microframework

2024-01-05

A few days ago, I documented my thoughts on how I keep things manageable in my data engineering consultancies. My last point in that article was that I scrutinize dependencies. I acknowledged that while some dependencies are necessary to move quickly, a surprising number are wholly unnecessary and can even be liabilities.

This post is about my attempt to scrutinize a large dependency of most web projects: the web framework. A "framework" in this case means something full-featured like Django or Rails. I have never had to build a full website, i.e., with authentication/authorization (authn/authz) and state management. This blog is currently a simple Go website, and my most interesting project, the Receipt Scanner, was a FastAPI webhook server. Full frameworks have always seemed like overkill to me.

To the point: after building a CRUD web app without a framework, it seems that my opinion about frameworks being overkill was misguided. Frameworks handle a lot of stuff that just doesn't seem worthwhile to implement yourself.

Scope and restrictions

I gave myself a small web app to create. The basic requirement was to let app users CRUD metadata for official receipts, which are documents that Philippine regulations require service providers to issue for tax compliance.

This requirement implies a need for authn/authz and persistent data storage. I believe most frameworks help you out with both of these concerns with some authentication framework and an ORM. Thus, to see whether frameworks are helpful, I restricted myself to handle both of these with just the server and the database driver.

Here is the stack I assembled for the exercise.

Note that I do not have anything to handle migrations, object-relational mapping, authn/authz, or session management. I discuss how I tackled these things later in the post.

Schemas and migrations

I am of the opinion that your database is the heart of your application. As such, I started here. My first task was to define the schema of my receipt-tracker web app.

It's simple enough to write an SQL schema for this. Given how important I think the database is, I find it ridiculous that some developers fear SQL so much. Nothing about this exercise changed this view.

It quickly became apparent, though, that I needed to find some way to manage migrations. Even though writing individual up and down migrations in SQL files was simple, calling them was decidedly less simple. I found myself writing a set of scripts that would list the SQL files in my migrations directory and run them in order, but I gave up on this approach once it became clear that I would need to track the current migration and support migrating an arbitrary number of steps up and down.

I settled on simply calling psql on the appropriate files. This worked, but it was ugly and felt unpleasant to do. If your project is sufficiently large and quick-moving, I think adding a migration dependency is probably justified.

Authn

Authn is knowing who a user is. Authz is knowing whether a user is permitted to perform an action. Both are difficult, but let's focus on authn for now. I will save my thoughts on authz for a future post.

To simplify things, I decided to only implement the most basic authn strategy: identifying users with a username (in this case an email) and a password. I created a table app_user in my Postgres database and wrote a login form.

Passwords

Maybe the most common mistake beginners make when implementing their own username/password authn like this is simply storing the password in plain text, or, only slightly better, encrypting the password. Both of these are insufficient for security. Plain text is bad for obvious reasons, but encryption is also bad for passwords because if the database gets leaked, an attacker can eventually retrieve the password if they can break the encryption.

Passwords are supposed to be stored in the database as a hash, which is the result of a one-way cryptographic function. You cannot retrieve an original value from a hash. While encryption can be decrypted, there is no such thing as a de-hash.

I knew this from playing around with web apps before I did this exercise, so I looked up bcrypt to refresh my memory on how to implement password hashing. I was made aware that bcrypt was no longer recommended as of 2024-01-05, so I ended up implementing argon2 instead.

Sessions

Another part of authn is session management. It would be a poor experience if a user were asked to identify themselves before any protected action, so most web apps store some sort of "session token" in the browser as a cookie. Every time the user uses their browser to interact with the web app, the session token gets sent to the server, which can then use the session token to retrieve a user's identity.

To implement this, I added a session table to my database. In the login handler function, if a user entered a valid email and password combination, I issued the user a new session token and stored the token in my database. Going forward, if I wanted to protect a route, I could check for a valid session in the request cookies and redirect the user to the login page if they did not have a valid session.

This all felt tough to implement because there seemed to be so many ways to do it wrong. I had a complete lack of confidence in whether my implementation was secure.

If I am building an app that handles any sort of sensitive information, a single problem with my setup will mean exposing that sensitive data to loss or theft, which can easily translate to death by lawsuit. Given this, I think bringing in an authentication dependency is highly justifiable. This is especially true if, in the case of an audit, you can demonstrate that you took the most prudent steps possible to ensure the security of your users and their data.

Psycopg3, dictionary rows, and the connection pool

I mentioned in the sections above that I persisted data to Postgres. I would normally use SQLAlchemy for this, but for this exercise, I restricted myself to only using the database driver.

The most straightforward way to use Psycopg, if you follow the documentation, is to:

Joben mentioned that working with databases like this can be tricky. At a certain scale, opening and closing a connection for every transaction becomes a significant drain on performance. Drivers and ORMs thus often offer some sort of "connection pool" from which connections can be borrowed and to which connections can be returned to avoid opening and closing connections all the time.

I first implemented the database interface for this exercise as a Psycopg3 ConnectionPool, but I quickly ran into an issue. By default Psycopg3 cursors return data as tuples, but I wanted them to return dictionaries. Though Psycopg3 supports returning rows as dictionaries, it only seems to support it for its basic connections, not their connection pools.

Since this wasn't a serious project, I decided to simply replace the connection pool with a connection. I figured that if each Gunicorn worker was a separate Python process and that if each Python process is single-threaded, any given worker will not be using the connection in more than one place at a time. In hindsight, this was probably a bad idea, but I was impatient with Psycopg3 at the time.

I can't be sure whether my problems with Psycopg3 were inherent to the software or merely self-inflicted, but regardless, I was slowed down. I'm not as confident in recommending taking a database interface dependency like SQLAlchemy as I was recommending a migration dependency or an authentication dependency, but if I were forced to choose, I would probably accept the database interface dependency.

CSRF

Before I end this post, I want to briefly discuss yet another security concern for web apps: cross-site request forgery. The OWASP project describes it better than I can:

A Cross-Site Request Forgery (CSRF) attack occurs when a malicious web site, email, blog, instant message, or program tricks an authenticated user's web browser into performing an unwanted action on a trusted site. If a target user is authenticated to the site, unprotected target sites cannot distinguish between legitimate authorized requests and forged authenticated requests.

Since browser requests automatically include all cookies including session cookies, this attack works unless proper authorization is used, which means that the target site's challenge-response mechanism does not verify the identity and authority of the requester. In effect, CSRF attacks make a target system perform attacker-specified functions via the victim's browser without the victim's knowledge (normally until after the unauthorized actions have been committed).

To protect against this, I generated a CSRF token and attached it as another field on a user's session. This CSRF token would then be injected as a hidden form field on every form the user generates. Any sensitive route can then check for the CSRF token before approving the action.

It wasn't too difficult to implement this, but had I not previously used Rails and Phoenix for toy projects, I would have completely missed this vulnerability. It represents what I think is the most compelling argument for using a framework: chances are that you do not want to keep up with the ever-evolving universe of web vulnerabilities alone. To use a framework is to join a community that can help you with this.

Takeaways

I have several: