Impossibly Lean Access Control with Postgres + Flask

One of the most common feature for a database-backed web application is to implement some kind of access control mechanism to regulate what different types of users (admins, guests, etc) can do. There are of course many ways to implement such a pattern: you can do it client-side, by modifying your UI dynamically (e.g. graying out certain widgets) according to the identity of the user, or server-side (as part of the so-called business rules maybe). Often a mix of both approaches will be used.

In this article, I will to show a way to implement such a pattern in a very simple way, using Postgres, Flask, Flask-Login and little_pger, a very thin SQL wrapper just above psycopg2 which I created.

Because the idea has received some criticism, I must say that (1) it is only a proof of concept (i.e. probably not robust enough for production in its current form) and (2) I don't think that such a pattern would scale well to a big userbase (it's rather meant for small-scale, intranet-style webapps with a limited number of users, which is what I've been mainly developing). The idea for the pattern was inspired by this blog post, that I read a while ago:

http://database-programmer.blogspot.ca/2009/02/comprehensive-database-security-model.html

while my skepticism toward ORMs in general stems from this even older (and admittedly controversial) post:

http://database-programmer.blogspot.ca/2008/06/why-i-do-not-use-orm.html

I say my pattern is "impossibly lean" because it introduces a minimal amount of code and concepts (not even the shadow of an ORM will be found here), by leveraging PG's role access management subsystem. These powerful features are already available the moment you create a PG database, so why would you need to reinvent the wheel?

Let's start by supposing that our application has a very simple database, with only one table:

create database pg_flask_ac;

\connect pg_flask_ac

create table foo (
  foo_id serial primary key
);

Suppose also that we have two users for it: an almighty admin (who can do whatever pleases him), and a shy guest (only allowed to look around). Although we could create an additional SQL "user" table, with a column dedicated to the representation of their privilege levels, another solution is to simply create corresponding PG roles:

create role joe_admin login superuser password '<secret>';
create role joe_guest password '<secret>';
grant select on all tables in schema public to joe_guest;

joe_admin, being a superuser can perform any SQL operation, while joe_guest is restricted to only being able to select things.. that seems fine, but is there a way to leverage these SQL-level constraints into our application? Sure we can, and here's all it takes to implement the pattern:

from flask import *
from flask.ext.login import *
import psycopg2, psycopg2.extras
import little_pger as pg

app = Flask(__name__)
app.secret_key = 'a secret!'
login_manager = LoginManager()
login_manager.init_app(app)

class User(UserMixin):
    def __init__(self, oid):
        self.id = oid

# called first: here we connect as an admin (with the login privilege)
@app.before_request
def before_request():
    g.db = psycopg2.connect("dbname=pg_flask_ac user=joe_admin",
                            connection_factory=psycopg2.extras.RealDictConnection)

# called next: from the admin connection, we fetch the name of the
# target role (identified with the unique (o)id encrypted in the session cookie)
@login_manager.user_loader
def load_user(id):
    cur = g.db.cursor()
    rn = pg.select1(cur, 'pg_authid', 'rolname', where={'oid': id})
    if rn:
        # executed on behalf of the admin
        cur.execute('set role %s', [rn])
        # from this point any command performed via g.db.cursor() will be
        # on the target role's behalf
        return User(id)
    return None

The interesting part is the interplay between before_request (Flask) and load_user (Flask-Login) which are both called automatically, before serving any view request. The first simply sets an admin connection from which the second will be able to switch user (by first fetching the appropriate role name in the pg_authid table with the id stored in the session cookie and then issuing the set role SQL command using it). We can view this mechanism in action by adding two views with different access policies:

# this read-only view should be available to anyone
@app.route('/look_at_something')
@login_required
def look_at_something():
    return jsonify({'success': True, 'count': pg.count(g.db.cursor(), 'foo')})

# this view should only be available to an admin; it will raise an exception
# (at the SQL level) if accessed by anyone else
@app.route('/change_something')
@login_required
def change_something():
    pg.insert(g.db.cursor(), 'foo')
    g.db.commit()
    return jsonify({'success': True})

which we can then drive using some Flask testing code:

if __name__ == '__main__':
    for user in ['joe_admin', 'joe_guest']:
        for view in ['/look_at_something', '/change_something']:
            with app.test_request_context(view):
                app.preprocess_request()
                oid = pg.select1(g.db.cursor(), 'pg_authid', 'oid',
                                 where={'rolname': user})
                login_user(User(oid))
                print '%s %s..' % (user, view),
                try:
                    app.dispatch_request()
                    print 'and succeeds!'
                except:
                    print 'but fails..'

# joe_admin /look_at_something.. and succeeds!
# joe_admin /change_something.. and succeeds!
# joe_guest /look_at_something.. and succeeds!
# joe_guest /change_something.. but fails..

Of course an access violation exception by itself wouldn't be terribly useful, so an easy way to use that pattern in a real environment would be to intercept it using Flask's errorhandler decorator, in order to return, for instance, a JSON formatted error message, along with a 403 response (upon which the client should presumably react appropriately):

@app.errorhandler(psycopg2.ProgrammingError)
def db_access_error(e):
    msg = "This user doesn't have the required privilege to perform this action."
    return jsonify({'success': False, 'message': msg}), 403

Finally, note that we didn't make use of the role passwords up to this point, because we assumed that a user had already been logged in, but of course it must occur at some point in the process, and here's a possible way to do it (which implies knowing about the particular way PG encrypts passwords in the pg_authid table):

@app.route("/login", methods=['POST'])
def login():
    user = request.form('user')
    pw = request.form('pw')
    cur = g.db.cursor()
    cur.execute("""select * from pg_authid where rolname = %s and
                                                 rolpassword = 'md5' || md5(%s || %s)""",
                [user, pw, user])
    u = cur.fetchone()
    if u:
        login_user(User(u['oid']))
    return jsonify({'success': u is not None})

Tweet