Jaap Bresser - Beyond Role Based Auth: Discretionary Access Control with Postgres & SQLAlchemy

See the PyGrunn website for more info about this one-day Python conference in Groningen, The Netherlands.

I work at http://www.profileermij.nl and this talk is about what we did there. It is an online career platform.

Role based authentication: grant access control based on a role, instead of on a user. Generally it is quite simple and coarse grained: you can edit either all products or none. [I would call it authorization: what you are allowed to do instead of who you are.]

The core part of our website is the user profile, with hard skills (education, positions) and soft skills (personality, preferences). Sensitive data.

In the first version, we used role based authentication. Each customer has an own sub domain. Roles were: user, hr (human resources), admin.

Wanted was: allow users to share their profiles with others. So we looked to discretionary access control (DAC). The first mention I found was from the Department of Defense in 1985. Biggest thing: pass on permission to another subject. So you get authorization per object.

The Linux file system works like this, in a very basic way, where you can give access on a file to one user as well as one group or everyone.

Amazon Web services have resource based permissions and identity based permissions, and lots more options. Quite complex, needed for their wildly varying use cases.

For the profileermij case, we created an ACL (access control list) in a Postgres database. The app uses Flask. The ACL:

• a principal uuid
• actions: read, write, read_acl, write_acl
• paths: dot separated, including wildcard and glob characters
• a unique key for external reference

For implementing the ACL, we use the JSONB data type and the JSONB subset operator @>, a Gin index. You need Postgres version 9.4 or higher.

Sample query:

SELECT * FROM profile WHERE profile.acl @> {...};

In Python we use classes to wrap JSON, an SQLAlchemy TypeDecorator. We need to handle paths, and integratie the ACL in an existing application of framework. Processing a request would look like this:

• incoming write request
• validate against schema
• convert to SQLAlchemy model
• load ACL and check permissions
• save SQLAlchemy object
• return response

Take aways from our experience:

• DAC meets our requirements
• Designing a UX that is understandable for lots of people is hard
• The complexity can be managed.
• Don't apply it everywhere. You don't need it everywhere. For our profiles we needed it, for most other data not.

[Audience: Django REST framework has this.]