Ability to manage database-level permissions

[simonw]

This can be things like create-table, but it would also be good to enable configuring insert-row at the database level and have that user or group able to insert rows into any table in that database.

[simonw]

Problem with this table:

datasette-acl/datasette_acl/__init__.py

Lines 16 to 21 in 50afe76

	create table if not exists acl_resources (
	id integer primary key,
	database text not null,
	resource text,
	unique(database, resource)
	);

null does not work like that in unique indexes. Instead I'm going to store empty string '' in the resource column for ACL resources that correspond to databases, not tables.

[simonw]

Actually the first challenge is going to be figuring out how to represent database-level permissions in the DB such that this query can work:

datasette-acl/datasette_acl/__init__.py

Lines 290 to 313 in 50afe76

	@hookimpl
	def permission_allowed(datasette, actor, action, resource):
	if not resource or len(resource) != 2:
	return None
	async def inner():
	if not actor or not actor.get("id"):
	return None
	await update_dynamic_groups(
	datasette, actor, skip_cache=hasattr(sys, "_pytest_running")
	)
	db = datasette.get_internal_database()
	result = await db.execute(
	ACL_RESOURCE_PAIR_SQL,
	{
	"actor_id": actor["id"],
	"database": resource[0],
	"resource": resource[1],
	"action": action,
	},
	)
	return result.single_value() or None
	return inner

datasette-acl/datasette_acl/__init__.py

Lines 84 to 113 in 50afe76

	with actor_groups as (
	select group_id
	from acl_actor_groups
	where actor_id = :actor_id
	),
	target_resource as (
	select id
	from acl_resources
	where database = :database and resource = :resource
	),
	target_action as (
	select id
	from acl_actions
	where name = :action
	),
	combined_permissions as (
	select resource_id, action_id
	from acl
	where actor_id = :actor_id
	union
	select resource_id, action_id
	from acl
	where group_id in (select group_id from actor_groups)
	)
	select count(*)
	from combined_permissions
	where resource_id = (select id from target_resource)
	and action_id = (select id from target_action)
	"""

Especially if I want insert-row at the database level to be handled correctly.