Introduce concept of "owned resources" for permissions (for discussion)

[simonw]

Datasette has an odd wrinkle in its permission system at the moment.

The current permission system mostly works by saying "here is the list of resources this user can perform action X" on.

The wrinkle is when a resource is meant to be private to the "owning" user only.

We added this to core quite recently: stored queries have owner_id and is_private column. If a query is marked as private the restriction_sql= mechanism is used such that NO OTHER USER can view-query, update-query, delete-query no matter what permissions they have.

Even a root user can't do those things - if site admins want to poke around in a user's private queries they have to consult internal SQLite directly themselves.

Here's where that was implemented: 316daf9#diff-1c8a70aa01c60ea886843849f8b00f8d47966b7b7a1b58d71444ae37a4938f85

The datasette-apps plugin now replicates the exact same pattern: https://github.com/datasette/datasette-apps/blob/53c28fcbce18893a503614f72aaf1fdb5dbbe964/datasette_apps/permissions.py#L96-L107

This is the best solution I could come up with for the "private things only accessible by their owners" pattern. I think it's OK, but there are a few outstanding issues:

1. It's a weird pattern that different plugins end up duplicating, and they might differ in semantics or make mistakes.
2. The datasette-acl and datasette-acl-share plugins provide a mechanism and UI for controlling permissions. This mechanism is not aware of the owner-private mechanism, which is confusing. Users might see a "share" dialog on one of their private resources which would not have the desired effect if they used it.

I think the way forward is to make "owned resources" a stable, documented concept in Datasette core. This would allow other plugins to more easily implement the pattern consistently, and it would also allow datasette-acl to detect if something is an owned resource and present the UI/API in a way that takes that into account.

[simonw]

Here's a first draft of how this could work. We would have a new plugin hook, register_owned_resources(), which returns the SQL needed to answer the question "what is the full sit of resources that should be considered as "owned resources" and what is their ownership status"?

Something like this:

@dataclass
class OwnedResourceSet:
    resource_class: type[Resource]
    actions: set[str]
    sql: str


@hookimpl
def register_owned_resources(datasette):
    return OwnedResourceSet(
        resource_class=QueryResource,
        actions={"view-query", "update-query", "delete-query"},
        sql="""
            SELECT database_name AS parent,
                   name AS child,
                   owner_id,
                   is_private AS owner_private
            FROM queries
        """,
    )

Which returns:

parent	child	owner_id	owner_private
content	timeline	simonw	0
content	timeline_count	simonw	0
content	my_bookmarks	simonw	1

Then we can include a default permission_resources_sql() hook in Datasette core which calls that other hook, gathers together all of the SQL fragments for the resource/action that is being queried and stuffs them together in a PermissionSQL(source="owner-private", restriction_sql=...") object.

[simonw]

When brainstorming this with GPT-5.5 xhigh it suggested also having a Datasette utility method for checking status like this:

status = await datasette.owner_private_status(resource)

Returning something like this (though we'd use a dataclass):

{
    "owner_private": true,
    "owner_id": "alice",
    "actions": ["view-app", "edit-app", "delete-app", "manage-app-access"],
}

Then datasette-acl could use that to check if the resource in question has these rules applied to it, and reflect that in the share dialog somehow.

... which gives me an extra idea: we could include in that OwnedResourceSet() the SQL needed to both mark as owner_private and mark as not-owner_private, then datasette-acl could offer a standard API and UI for toggling it on and off as part of the datasette-acl-share dialog.

Actually Datasette core should probably serve that API, since it's going to have buttons for toggling that status on stored queries.