Datasette plugin for configuring permission checks using SQL queries
Project description
datasette-permissions-sql
Datasette plugin for configuring permission checks using SQL queries
This only works with the next, unreleased version of Datasette
Installation
Install this plugin in the same environment as Datasette.
$ pip install datasette-permissions-sql
Usage
First, read up on how Datasette's authentication and permissions system works.
This plugin lets you define SQL queries that are executed to see if the currently authenticated actor has permission to perform certain actions.
Consider a canned query which authenticated users should only be able to execute if a row in the users
table says that they are a member of staff.
That users
table in the mydatabase.db
database could look like this:
id | username | is_staff |
---|---|---|
1 | cleopaws | 0 |
2 | simon | 1 |
Authenticated users have an actor
that looks like this:
{
"id": 2,
"username": "simon"
}
To configure the canned query to only be executable by staff users, add the following to your metadata.json
:
{
"plugins": {
"datasette-permissions-sql": [
{
"action": "view-query",
"resource": ["mydatabase", "promote_to_staff"],
"sql": "SELECT * FROM users WHERE is_staff = 1 AND id = :actor_id"
}
]
},
"databases": {
"mydatabase": {
"queries": {
"promote_to_staff": {
"sql": "UPDATE users SET is is_staff=1 WHERE id=:id",
"write": true
}
}
}
}
}
The "datasette-permissions-sql"
key is a list of SQL matching rules. Each of those rules has the following shape:
{
"action": "name-of-action",
"resource": ["resource identifier to run this on"],
"sql": "SQL query to execute",
"database": "mydatabas"
}
Both "action"
and "resource"
are optional. If present, the SQL query will only be executed on permission checks that match the action and, if present, the resource indicators.
"database"
is also optional: it specifies the named database that the query should be executed against. If it is not present the first connected database will be used.
The Datasette documentation includes a list of built-in permissions that you might want to use here.
The SQL query
If the SQL query returns any rows the permission will be allowed. If it returns no rows, the plugin hook will return None
which means other plugins can have a go at checking permissions.
If the SQL query returns a single value of -1
it well be treated as an explicit "deny permission" response to the permission check.
The SQL query is called with a number of named parameters. You can use any of these as part of the query.
The list of parameters is as follows:
action
- the action, e.g."view-database"
resource_1
- the first component of the resource, if one was passedresource_2
- the second component of the resource, if availableactor_*
- a parameter for every key on the actor. Usuallyactor_id
is present.
The SQL query can return any of three different types of result:
- No rows at all means "I don't have an opinion about this permission" - which allows the default permission to apply.
- One or more rows means "allow" - unless...
- A single row with a single value of
-1
- which means "deny"
Another example table, this time granting explicit access to individual tables. Consider a table called table_access
that looks like this:
user_id | database | table | access_level |
---|---|---|---|
1 | mydb | dogs | 1 |
2 | mydb | dogs | 1 |
1 | mydb | cats | 1 |
2 | mydb | cats | -1 |
The following SQL query would grant access to the dogs
ttable in the mydb.db
database to users 1 and 2 - but would forbid access for user 2 to the cats
table:
SELECT
access_level
FROM
table_access
WHERE
user_id = :actor_id
AND "database" = :resource_1
AND "table" = :resource_2
In a metadata.yaml
configuration file that would look like this:
databases:
mydb:
allow_sql: {}
plugins:
datasette-permissions-sql:
- action: view-table
sql: |-
SELECT
access_level
FROM
table_access
WHERE
user_id = :actor_id
AND "database" = :resource_1
AND "table" = :resource_2
We're using allow_sql: {}
here to disable arbitrary SQL queries to prevent users from running select * from cats
directly to work around the permissions limits.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distributions
Built Distribution
Hashes for datasette_permissions_sql-0.1a0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 60e22c43839dd63ae5fc9b92b853553ec4e6a52036b66ce9f60bd8c1feaf0ee1 |
|
MD5 | 602a8c3ce290a488127ed9d1dc6d32f0 |
|
BLAKE2b-256 | 6009b98c2508086c9690a448e2eee2aecb4298a13d7d88013910758efacc41e1 |