Skip to main content
Permissions
Overview
Figure 1: Figure 1: Concept Map
Outline
- Everyone can read everything
- Databases like PostgreSQL implement fine-grained permissions on tables and even rows
- But we need to do it ourself for SQLite
- Note that if someone has access to the
.db file, we have no protection
- If we're going to add create, update, and delete, we should add permissions
- The ability to perform an operation on a thing
- Standard approach:
- An actor (person or similar) has zero or more roles
- Each role is a set of permissions
- E.g., reader role has read permission for all tables
- Represent permissions with two tables in the database
- Worry about authentication later
- Add two new tables
role is a many-to-many join table
permission defines what roles mean (with wildcard for "all tables")
- Put these in a temporary copy of the database for now
- Could write a
require function to check permission before doing database query
- But what if someone modifies the database between our check and the main query?
- Use
join to only select rows where permission is available
- But this means we can't distinguish "no data" from "no permission"
explore_permissions.py explores ways to do what we want with PyPika
- Turns out that
join must have on
- Since we want everything, use
x == x
- Having explored ideas in the small, we can implement in the large
test_json.py uses a custom PyPika function to call SQLite's json_group_array
- Name the JSON field
something__json
- Modify
dict_factory to unpack and rename
- Go back to
test_permissions.py and add tests using this
- Hard to understand without building it up in steps, but it works