allow_by_query setting for configuring permissions with a SQL statement

[simonw]

Idea: an "allow_sql" key with a SQL query that gets passed the actor JSON as :actor and can extract the relevant keys from it and return 1 or 0.

Originally posted by @simonw in #698 (comment)

See also #800

[simonw]

Now that I'm expanding permission checks to everything else too (#811), not just canned queries, I think it makes sense to re-prioritize this.

[simonw]

I'm going to pass the entire actor object as a dictionary of available named query parameters. So if the actor looks like this:

{
    "id": "simonw",
    "roles": ["staff", "developer"]
}

Then the SQL query will be called like this:

conn.execute(sql, {
  "id": "simonw",
  "roles: '["staff", "developer"]',
})

[simonw]

I'm going to call this key "allow_by_query" - I think I need allow_sql for something else (for configuring if users are allowed to execute arbitrary SQL queries).

[simonw]

Example. This will only allow users to access the fixtures database if the logged-in actor's ID value appears for a record in the users table which has admin = 1.

{
    "databases": {
        "fixtures": {
            "allow_by_query": "select * from users where id = :id and admin = 1"
        }
    }
}

[simonw]

Dropping this out of Datasette 0.44 again - I have enough other stuff to finish, this can wait.

[simonw]

I've implemented this in a plugin instead: https://github.com/simonw/datasette-permissions-sql