Canned query permissions mechanism

[simonw]

Idea: default is anyone can execute a query.

Or you can specify the following:

{
    "databases": {
       "my-database": {
           "queries": {
               "add_twitter_handle": {
                   "sql": "insert into twitter_handles (username) values (:username)",
                   "write": true,
                   "allow": {
                       "id": ["simon"],
                       "role": ["staff"]
                   }
               }
           }
       }
    }
}

These get matched against the actor JSON. If any of the fields in any of the keys of "allow" match a key on the actor, the query is allowed.

"id": "*" matches any actor with an id key.

Originally posted by @simonw in #698 (comment)

[simonw]

Maybe #801 (configuring permissions with a SQL query) is enough here - might not need this mechanism at all, since that mechanism covers it.

[simonw]

It's a bit obscure though. I'll try building both and see how they feel in practice.

[simonw]

I like this mechanism better than the SQL query one. Constructing SQL queries that return true if a particular string is embedded inside a JSON list in a larger object is decidedly non-trivial.

[simonw]

I'm going to implement this documentation-first.

[simonw]

Docs here: https://github.com/simonw/datasette/blob/d4c7b85f556230923d37ff327a068ed08aa9b62b/docs/authentication.rst#setting-permissions-for-canned-queries

[simonw]

I should add the '*' bit to the docs.

[simonw]

Next step: a utility function and tests for matching actors to allow blocks.

[simonw]

Documentation for actor_matches_allow: https://github.com/simonw/datasette/blob/14f6b4d200f24940a795ddc0825319ab2891bde2/docs/authentication.rst#actor_matches_allow

[simonw]

Now the actual permission checks. I need these in two places: the code that generates the list of available queries on https://latest.datasette.io/fixtures#queries and the query page itself at https://latest.datasette.io/fixtures/pragma_cache_size

[simonw]

In the code that's:

datasette/datasette/views/database.py

Lines 56 to 64 in 9c563d6

	return (
	{
	"database": database,
	"size": db.size,
	"tables": tables,
	"hidden_count": len([t for t in tables if t["hidden"]]),
	"views": views,
	"queries": self.ds.get_canned_queries(database),
	},

And:

datasette/datasette/views/database.py

Lines 98 to 112 in 9c563d6

	class QueryView(DataView):
	async def data(
	self,
	request,
	database,
	hash,
	sql,
	editable=True,
	canned_query=None,
	metadata=None,
	_size=None,
	named_parameters=None,
	write=False,
	):
	params = {key: request.args.get(key) for key in request.args}

[simonw]

I'm also going to add an indicator to the UI next to queries that you can only execute because you are signed in:

[simonw]

This is implemented and documented: https://datasette.readthedocs.io/en/latest/authentication.html

[simonw]

I didn't build this quite right: it should be using the permissions plugin hook.

[simonw]

See #810 for work to finish this.