mirror of
https://github.com/simonw/datasette.git
synced 2026-05-28 12:56:18 +02:00
Compare commits
No commits in common. "codex/operation-sql-analysis" and "main" have entirely different histories.
codex/oper
...
main
12 changed files with 156 additions and 1809 deletions
|
|
@ -58,16 +58,6 @@ class Resource(ABC):
|
||||||
self.child = child
|
self.child = child
|
||||||
self._private = None # Sentinel to track if private was set
|
self._private = None # Sentinel to track if private was set
|
||||||
|
|
||||||
def __str__(self) -> str:
|
|
||||||
return "/".join(
|
|
||||||
str(part) for part in (self.parent, self.child) if part is not None
|
|
||||||
)
|
|
||||||
|
|
||||||
def __repr__(self) -> str:
|
|
||||||
return "{}(parent={!r}, child={!r})".format(
|
|
||||||
self.__class__.__name__, self.parent, self.child
|
|
||||||
)
|
|
||||||
|
|
||||||
@property
|
@property
|
||||||
def private(self) -> bool:
|
def private(self) -> bool:
|
||||||
"""
|
"""
|
||||||
|
|
|
||||||
|
|
@ -2,26 +2,14 @@ from __future__ import annotations
|
||||||
|
|
||||||
from dataclasses import dataclass
|
from dataclasses import dataclass
|
||||||
import json
|
import json
|
||||||
from typing import Any, Iterable, TYPE_CHECKING
|
from typing import Any, Iterable
|
||||||
|
|
||||||
from .resources import DatabaseResource, TableResource
|
from .resources import TableResource
|
||||||
from .permissions import Resource
|
|
||||||
from .utils import named_parameters, sqlite3, tilde_encode, urlsafe_components
|
from .utils import named_parameters, sqlite3, tilde_encode, urlsafe_components
|
||||||
from .utils.asgi import Forbidden
|
from .utils.asgi import Forbidden
|
||||||
from .utils.sql_analysis import Operation, SQLAnalysis
|
|
||||||
|
|
||||||
if TYPE_CHECKING:
|
|
||||||
from .app import Datasette
|
|
||||||
|
|
||||||
UNCHANGED = object()
|
UNCHANGED = object()
|
||||||
|
|
||||||
|
|
||||||
class QueryWriteRejected(Exception):
|
|
||||||
def __init__(self, message: str):
|
|
||||||
self.message = message
|
|
||||||
super().__init__(message)
|
|
||||||
|
|
||||||
|
|
||||||
QUERY_OPTION_FIELDS = (
|
QUERY_OPTION_FIELDS = (
|
||||||
"hide_sql",
|
"hide_sql",
|
||||||
"fragment",
|
"fragment",
|
||||||
|
|
@ -595,158 +583,20 @@ async def list_queries(
|
||||||
)
|
)
|
||||||
|
|
||||||
|
|
||||||
@dataclass(frozen=True)
|
|
||||||
class PermissionRequirement:
|
|
||||||
action: str
|
|
||||||
resource: Resource
|
|
||||||
|
|
||||||
|
|
||||||
def row_mutation_requirements(
|
|
||||||
database: str, table: str
|
|
||||||
) -> tuple[PermissionRequirement, ...]:
|
|
||||||
resource = TableResource(database=database, table=table)
|
|
||||||
return tuple(
|
|
||||||
PermissionRequirement(action=action, resource=resource)
|
|
||||||
for action in ("insert-row", "update-row", "delete-row")
|
|
||||||
)
|
|
||||||
|
|
||||||
|
|
||||||
def permission_requirements_for_operation(
|
|
||||||
operation: Operation,
|
|
||||||
) -> tuple[PermissionRequirement, ...]:
|
|
||||||
if (
|
|
||||||
operation.operation == "read"
|
|
||||||
and operation.target_type == "table"
|
|
||||||
and operation.database is not None
|
|
||||||
and operation.table is not None
|
|
||||||
):
|
|
||||||
return (
|
|
||||||
PermissionRequirement(
|
|
||||||
action="view-table",
|
|
||||||
resource=TableResource(
|
|
||||||
database=operation.database, table=operation.table
|
|
||||||
),
|
|
||||||
),
|
|
||||||
)
|
|
||||||
if (
|
|
||||||
operation.operation in {"insert", "update"}
|
|
||||||
and operation.target_type == "table"
|
|
||||||
and operation.database is not None
|
|
||||||
and operation.table is not None
|
|
||||||
):
|
|
||||||
return row_mutation_requirements(
|
|
||||||
database=operation.database,
|
|
||||||
table=operation.table,
|
|
||||||
)
|
|
||||||
if (
|
|
||||||
operation.operation == "delete"
|
|
||||||
and operation.target_type == "table"
|
|
||||||
and operation.database is not None
|
|
||||||
and operation.table is not None
|
|
||||||
):
|
|
||||||
return (
|
|
||||||
PermissionRequirement(
|
|
||||||
action="delete-row",
|
|
||||||
resource=TableResource(
|
|
||||||
database=operation.database, table=operation.table
|
|
||||||
),
|
|
||||||
),
|
|
||||||
)
|
|
||||||
if operation.operation == "create" and operation.target_type == "table":
|
|
||||||
if operation.database is None:
|
|
||||||
return ()
|
|
||||||
return (
|
|
||||||
PermissionRequirement(
|
|
||||||
action="create-table",
|
|
||||||
resource=DatabaseResource(database=operation.database),
|
|
||||||
),
|
|
||||||
)
|
|
||||||
if (
|
|
||||||
operation.operation == "alter"
|
|
||||||
and operation.target_type == "table"
|
|
||||||
and operation.database is not None
|
|
||||||
and operation.table is not None
|
|
||||||
):
|
|
||||||
return (
|
|
||||||
PermissionRequirement(
|
|
||||||
action="alter-table",
|
|
||||||
resource=TableResource(
|
|
||||||
database=operation.database, table=operation.table
|
|
||||||
),
|
|
||||||
),
|
|
||||||
)
|
|
||||||
if (
|
|
||||||
operation.operation == "drop"
|
|
||||||
and operation.target_type == "table"
|
|
||||||
and operation.database is not None
|
|
||||||
and operation.table is not None
|
|
||||||
):
|
|
||||||
return (
|
|
||||||
PermissionRequirement(
|
|
||||||
action="drop-table",
|
|
||||||
resource=TableResource(
|
|
||||||
database=operation.database, table=operation.table
|
|
||||||
),
|
|
||||||
),
|
|
||||||
)
|
|
||||||
if (
|
|
||||||
operation.operation in {"create", "drop"}
|
|
||||||
and operation.target_type == "index"
|
|
||||||
and operation.database is not None
|
|
||||||
and operation.table is not None
|
|
||||||
):
|
|
||||||
return (
|
|
||||||
PermissionRequirement(
|
|
||||||
action="alter-table",
|
|
||||||
resource=TableResource(
|
|
||||||
database=operation.database, table=operation.table
|
|
||||||
),
|
|
||||||
),
|
|
||||||
)
|
|
||||||
return ()
|
|
||||||
|
|
||||||
|
|
||||||
def operation_should_be_ignored(operation: Operation) -> bool:
|
|
||||||
return operation.internal or operation.operation == "select"
|
|
||||||
|
|
||||||
|
|
||||||
def operation_forbidden_message(operation: Operation) -> str | None:
|
|
||||||
if operation.operation == "vacuum":
|
|
||||||
return "VACUUM is not allowed in user-supplied SQL"
|
|
||||||
return None
|
|
||||||
|
|
||||||
|
|
||||||
def operation_is_write(operation: Operation) -> bool:
|
|
||||||
return operation.operation in {
|
|
||||||
"insert",
|
|
||||||
"update",
|
|
||||||
"delete",
|
|
||||||
"create",
|
|
||||||
"alter",
|
|
||||||
"drop",
|
|
||||||
"begin",
|
|
||||||
"commit",
|
|
||||||
"rollback",
|
|
||||||
"savepoint",
|
|
||||||
"attach",
|
|
||||||
"detach",
|
|
||||||
"pragma",
|
|
||||||
"analyze",
|
|
||||||
"reindex",
|
|
||||||
"vacuum",
|
|
||||||
"unknown",
|
|
||||||
}
|
|
||||||
|
|
||||||
|
|
||||||
async def ensure_query_write_permissions(
|
async def ensure_query_write_permissions(
|
||||||
datasette: Datasette,
|
datasette: Any,
|
||||||
database: str,
|
database: str,
|
||||||
sql: str,
|
sql: str,
|
||||||
*,
|
*,
|
||||||
actor: dict[str, object] | None = None,
|
actor: dict[str, Any] | None = None,
|
||||||
params: dict[str, object] | None = None,
|
params: dict[str, Any] | None = None,
|
||||||
analysis: SQLAnalysis | None = None,
|
analysis: Any = None,
|
||||||
) -> SQLAnalysis:
|
) -> Any:
|
||||||
|
write_actions = {
|
||||||
|
"insert": "insert-row",
|
||||||
|
"update": "update-row",
|
||||||
|
"delete": "delete-row",
|
||||||
|
}
|
||||||
db = datasette.get_database(database)
|
db = datasette.get_database(database)
|
||||||
if analysis is None:
|
if analysis is None:
|
||||||
if params is None:
|
if params is None:
|
||||||
|
|
@ -756,29 +606,18 @@ async def ensure_query_write_permissions(
|
||||||
except sqlite3.DatabaseError as ex:
|
except sqlite3.DatabaseError as ex:
|
||||||
raise Forbidden(f"Could not analyze query: {ex}") from ex
|
raise Forbidden(f"Could not analyze query: {ex}") from ex
|
||||||
|
|
||||||
for operation in analysis.operations:
|
for access in analysis.table_accesses:
|
||||||
if operation_should_be_ignored(operation):
|
action = write_actions.get(access.operation)
|
||||||
|
if action is None:
|
||||||
continue
|
continue
|
||||||
forbidden_message = operation_forbidden_message(operation)
|
if access.database != database:
|
||||||
if forbidden_message is not None:
|
raise Forbidden("Writable queries may not write to attached databases")
|
||||||
raise QueryWriteRejected(forbidden_message)
|
|
||||||
permissions = permission_requirements_for_operation(operation)
|
|
||||||
if not permissions:
|
|
||||||
raise Forbidden(
|
|
||||||
"Unsupported SQL operation: {} {}".format(
|
|
||||||
operation.operation, operation.target_type
|
|
||||||
)
|
|
||||||
)
|
|
||||||
if operation.database != database:
|
|
||||||
raise Forbidden("Writable queries may not access attached databases")
|
|
||||||
for permission in permissions:
|
|
||||||
if not await datasette.allowed(
|
if not await datasette.allowed(
|
||||||
action=permission.action,
|
action=action,
|
||||||
resource=permission.resource,
|
resource=TableResource(database=access.database, table=access.table),
|
||||||
actor=actor,
|
actor=actor,
|
||||||
):
|
):
|
||||||
raise Forbidden(
|
raise Forbidden(
|
||||||
f"Permission denied: need {permission.action} "
|
f"Permission denied: need {action} on {access.database}/{access.table}"
|
||||||
f"on {permission.resource}"
|
|
||||||
)
|
)
|
||||||
return analysis
|
return analysis
|
||||||
|
|
|
||||||
|
|
@ -106,6 +106,9 @@ form.sql .query-create-sql textarea#sql-editor {
|
||||||
.query-create-analysis-note {
|
.query-create-analysis-note {
|
||||||
margin: 0;
|
margin: 0;
|
||||||
}
|
}
|
||||||
|
.query-create-action {
|
||||||
|
margin: 0.35rem 0 1rem;
|
||||||
|
}
|
||||||
.query-create-analysis {
|
.query-create-analysis {
|
||||||
margin-top: 0.8rem;
|
margin-top: 0.8rem;
|
||||||
}
|
}
|
||||||
|
|
@ -168,6 +171,10 @@ form.sql .query-create-sql textarea#sql-editor {
|
||||||
<label><input type="checkbox" name="is_private" value="1"{% if is_private %} checked{% endif %}> Private</label>
|
<label><input type="checkbox" name="is_private" value="1"{% if is_private %} checked{% endif %}> Private</label>
|
||||||
<span class="query-create-option-note">Queries marked private can only be seen by you, their creator.</span>
|
<span class="query-create-option-note">Queries marked private can only be seen by you, their creator.</span>
|
||||||
</p>
|
</p>
|
||||||
|
{% if sql and analysis_is_write %}
|
||||||
|
<p class="query-create-action"><a href="{{ urls.database(database) }}/-/execute-write?{{ {'sql': sql}|urlencode|safe }}">Execute write SQL</a></p>
|
||||||
|
{% endif %}
|
||||||
|
|
||||||
<p class="query-create-submit"><input type="submit" value="Save query" data-query-create-submit{% if save_disabled %} disabled{% endif %}></p>
|
<p class="query-create-submit"><input type="submit" value="Save query" data-query-create-submit{% if save_disabled %} disabled{% endif %}></p>
|
||||||
|
|
||||||
<div class="query-create-analysis" id="query-create-analysis-section"{% if not has_sql %} hidden{% endif %}>
|
<div class="query-create-analysis" id="query-create-analysis-section"{% if not has_sql %} hidden{% endif %}>
|
||||||
|
|
|
||||||
|
|
@ -3,76 +3,22 @@ from typing import Literal
|
||||||
|
|
||||||
from datasette.utils.sqlite import sqlite3
|
from datasette.utils.sqlite import sqlite3
|
||||||
|
|
||||||
SQLOperation = Literal[
|
|
||||||
"read",
|
|
||||||
"insert",
|
|
||||||
"update",
|
|
||||||
"delete",
|
|
||||||
"select",
|
|
||||||
"function",
|
|
||||||
"create",
|
|
||||||
"alter",
|
|
||||||
"drop",
|
|
||||||
"begin",
|
|
||||||
"commit",
|
|
||||||
"rollback",
|
|
||||||
"savepoint",
|
|
||||||
"attach",
|
|
||||||
"detach",
|
|
||||||
"pragma",
|
|
||||||
"analyze",
|
|
||||||
"reindex",
|
|
||||||
"vacuum",
|
|
||||||
"unknown",
|
|
||||||
]
|
|
||||||
SQLTargetType = Literal[
|
|
||||||
"table",
|
|
||||||
"index",
|
|
||||||
"view",
|
|
||||||
"trigger",
|
|
||||||
"virtual-table",
|
|
||||||
"schema",
|
|
||||||
"statement",
|
|
||||||
"transaction",
|
|
||||||
"database",
|
|
||||||
"pragma",
|
|
||||||
"function",
|
|
||||||
"unknown",
|
|
||||||
]
|
|
||||||
SQLTableOperation = Literal["read", "insert", "update", "delete"]
|
SQLTableOperation = Literal["read", "insert", "update", "delete"]
|
||||||
SQLSchemaOperation = Literal["create", "drop"]
|
|
||||||
SQLSchemaTargetType = Literal["index", "table", "trigger", "view", "virtual-table"]
|
|
||||||
|
|
||||||
|
|
||||||
@dataclass(frozen=True)
|
@dataclass(frozen=True)
|
||||||
class Operation:
|
class SQLTableAccess:
|
||||||
operation: SQLOperation
|
operation: SQLTableOperation
|
||||||
target_type: SQLTargetType
|
|
||||||
database: str | None
|
database: str | None
|
||||||
table: str | None
|
table: str
|
||||||
sqlite_schema: str | None
|
sqlite_schema: str | None
|
||||||
target: str | None = None
|
|
||||||
columns: tuple[str, ...] = ()
|
columns: tuple[str, ...] = ()
|
||||||
source: str | None = None
|
source: str | None = None
|
||||||
internal: bool = False
|
|
||||||
|
|
||||||
|
|
||||||
@dataclass(frozen=True)
|
@dataclass(frozen=True)
|
||||||
class SQLAnalysis:
|
class SQLAnalysis:
|
||||||
operations: tuple[Operation, ...]
|
table_accesses: tuple[SQLTableAccess, ...]
|
||||||
|
|
||||||
|
|
||||||
# Hashable dict key for grouping repeated authorizer callbacks while collecting columns.
|
|
||||||
@dataclass(frozen=True)
|
|
||||||
class OperationKey:
|
|
||||||
operation: SQLOperation
|
|
||||||
target_type: SQLTargetType
|
|
||||||
database: str | None
|
|
||||||
table: str | None
|
|
||||||
sqlite_schema: str | None
|
|
||||||
target: str | None
|
|
||||||
source: str | None
|
|
||||||
internal: bool
|
|
||||||
|
|
||||||
|
|
||||||
_ACTION_TO_OPERATION: dict[int, SQLTableOperation] = {
|
_ACTION_TO_OPERATION: dict[int, SQLTableOperation] = {
|
||||||
|
|
@ -82,114 +28,6 @@ _ACTION_TO_OPERATION: dict[int, SQLTableOperation] = {
|
||||||
sqlite3.SQLITE_DELETE: "delete",
|
sqlite3.SQLITE_DELETE: "delete",
|
||||||
}
|
}
|
||||||
|
|
||||||
# Values are (operation, target_type) pairs used to construct Operation objects.
|
|
||||||
_CREATE_ACTIONS: dict[int, tuple[SQLSchemaOperation, SQLSchemaTargetType]] = {
|
|
||||||
sqlite3.SQLITE_CREATE_INDEX: ("create", "index"),
|
|
||||||
sqlite3.SQLITE_CREATE_TABLE: ("create", "table"),
|
|
||||||
sqlite3.SQLITE_CREATE_TRIGGER: ("create", "trigger"),
|
|
||||||
sqlite3.SQLITE_CREATE_VIEW: ("create", "view"),
|
|
||||||
}
|
|
||||||
_DROP_ACTIONS: dict[int, tuple[SQLSchemaOperation, SQLSchemaTargetType]] = {
|
|
||||||
sqlite3.SQLITE_DROP_INDEX: ("drop", "index"),
|
|
||||||
sqlite3.SQLITE_DROP_TABLE: ("drop", "table"),
|
|
||||||
sqlite3.SQLITE_DROP_TRIGGER: ("drop", "trigger"),
|
|
||||||
sqlite3.SQLITE_DROP_VIEW: ("drop", "view"),
|
|
||||||
}
|
|
||||||
|
|
||||||
|
|
||||||
def _add_schema_action(
|
|
||||||
action_name: str,
|
|
||||||
operation: SQLSchemaOperation,
|
|
||||||
target_type: SQLSchemaTargetType,
|
|
||||||
) -> None:
|
|
||||||
action_value = getattr(sqlite3, action_name, None)
|
|
||||||
if action_value is not None:
|
|
||||||
actions = _CREATE_ACTIONS if operation == "create" else _DROP_ACTIONS
|
|
||||||
actions[action_value] = (operation, target_type)
|
|
||||||
|
|
||||||
|
|
||||||
_TEMP_SCHEMA_ACTIONS: tuple[
|
|
||||||
tuple[str, SQLSchemaOperation, SQLSchemaTargetType], ...
|
|
||||||
] = (
|
|
||||||
("SQLITE_CREATE_TEMP_INDEX", "create", "index"),
|
|
||||||
("SQLITE_CREATE_TEMP_TABLE", "create", "table"),
|
|
||||||
("SQLITE_CREATE_TEMP_TRIGGER", "create", "trigger"),
|
|
||||||
("SQLITE_CREATE_TEMP_VIEW", "create", "view"),
|
|
||||||
("SQLITE_DROP_TEMP_INDEX", "drop", "index"),
|
|
||||||
("SQLITE_DROP_TEMP_TABLE", "drop", "table"),
|
|
||||||
("SQLITE_DROP_TEMP_TRIGGER", "drop", "trigger"),
|
|
||||||
("SQLITE_DROP_TEMP_VIEW", "drop", "view"),
|
|
||||||
)
|
|
||||||
for schema_action in _TEMP_SCHEMA_ACTIONS:
|
|
||||||
_add_schema_action(*schema_action)
|
|
||||||
|
|
||||||
_VTABLE_SCHEMA_ACTIONS: tuple[
|
|
||||||
tuple[str, SQLSchemaOperation, SQLSchemaTargetType], ...
|
|
||||||
] = (
|
|
||||||
("SQLITE_CREATE_VTABLE", "create", "virtual-table"),
|
|
||||||
("SQLITE_DROP_VTABLE", "drop", "virtual-table"),
|
|
||||||
)
|
|
||||||
for schema_action in _VTABLE_SCHEMA_ACTIONS:
|
|
||||||
_add_schema_action(*schema_action)
|
|
||||||
|
|
||||||
_SQLITE_SCHEMA_TABLES = {
|
|
||||||
"sqlite_master",
|
|
||||||
"sqlite_schema",
|
|
||||||
"sqlite_temp_master",
|
|
||||||
"sqlite_temp_schema",
|
|
||||||
}
|
|
||||||
_SQLITE_INTERNAL_SCHEMA_FUNCTIONS = {
|
|
||||||
"length",
|
|
||||||
"like",
|
|
||||||
"printf",
|
|
||||||
"sqlite_drop_column",
|
|
||||||
"sqlite_rename_column",
|
|
||||||
"sqlite_rename_quotefix",
|
|
||||||
"sqlite_rename_table",
|
|
||||||
"sqlite_rename_test",
|
|
||||||
"substr",
|
|
||||||
}
|
|
||||||
|
|
||||||
_AUTHORIZER_ACTION_NAMES = {
|
|
||||||
getattr(sqlite3, name): name
|
|
||||||
for name in (
|
|
||||||
"SQLITE_CREATE_INDEX",
|
|
||||||
"SQLITE_CREATE_TABLE",
|
|
||||||
"SQLITE_CREATE_TEMP_INDEX",
|
|
||||||
"SQLITE_CREATE_TEMP_TABLE",
|
|
||||||
"SQLITE_CREATE_TEMP_TRIGGER",
|
|
||||||
"SQLITE_CREATE_TEMP_VIEW",
|
|
||||||
"SQLITE_CREATE_TRIGGER",
|
|
||||||
"SQLITE_CREATE_VIEW",
|
|
||||||
"SQLITE_DELETE",
|
|
||||||
"SQLITE_DROP_INDEX",
|
|
||||||
"SQLITE_DROP_TABLE",
|
|
||||||
"SQLITE_DROP_TEMP_INDEX",
|
|
||||||
"SQLITE_DROP_TEMP_TABLE",
|
|
||||||
"SQLITE_DROP_TEMP_TRIGGER",
|
|
||||||
"SQLITE_DROP_TEMP_VIEW",
|
|
||||||
"SQLITE_DROP_TRIGGER",
|
|
||||||
"SQLITE_DROP_VIEW",
|
|
||||||
"SQLITE_INSERT",
|
|
||||||
"SQLITE_PRAGMA",
|
|
||||||
"SQLITE_READ",
|
|
||||||
"SQLITE_SELECT",
|
|
||||||
"SQLITE_TRANSACTION",
|
|
||||||
"SQLITE_UPDATE",
|
|
||||||
"SQLITE_ATTACH",
|
|
||||||
"SQLITE_DETACH",
|
|
||||||
"SQLITE_ALTER_TABLE",
|
|
||||||
"SQLITE_REINDEX",
|
|
||||||
"SQLITE_ANALYZE",
|
|
||||||
"SQLITE_CREATE_VTABLE",
|
|
||||||
"SQLITE_DROP_VTABLE",
|
|
||||||
"SQLITE_FUNCTION",
|
|
||||||
"SQLITE_SAVEPOINT",
|
|
||||||
"SQLITE_RECURSIVE",
|
|
||||||
)
|
|
||||||
if hasattr(sqlite3, name)
|
|
||||||
}
|
|
||||||
|
|
||||||
|
|
||||||
def analyze_sql_tables(
|
def analyze_sql_tables(
|
||||||
conn,
|
conn,
|
||||||
|
|
@ -200,13 +38,15 @@ def analyze_sql_tables(
|
||||||
schema_to_database: dict[str, str] | None = None,
|
schema_to_database: dict[str, str] | None = None,
|
||||||
) -> SQLAnalysis:
|
) -> SQLAnalysis:
|
||||||
"""
|
"""
|
||||||
Return operations performed by a SQL statement according to SQLite's authorizer.
|
Return tables accessed by a SQL statement according to SQLite's authorizer.
|
||||||
|
|
||||||
This function is synchronous and connection-based. It temporarily installs a
|
This function is synchronous and connection-based. It temporarily installs a
|
||||||
SQLite authorizer, prepares ``EXPLAIN <sql>``, and returns the operation
|
SQLite authorizer, prepares ``EXPLAIN <sql>``, and returns the table access
|
||||||
callbacks observed while SQLite compiles the statement.
|
callbacks observed while SQLite compiles the statement.
|
||||||
"""
|
"""
|
||||||
operations: dict[OperationKey, set[str]] = {}
|
accesses: dict[
|
||||||
|
tuple[SQLTableOperation, str | None, str, str | None, str | None], set[str]
|
||||||
|
] = {}
|
||||||
|
|
||||||
def database_for_schema(sqlite_schema):
|
def database_for_schema(sqlite_schema):
|
||||||
if schema_to_database and sqlite_schema in schema_to_database:
|
if schema_to_database and sqlite_schema in schema_to_database:
|
||||||
|
|
@ -215,304 +55,45 @@ def analyze_sql_tables(
|
||||||
return database_name
|
return database_name
|
||||||
return sqlite_schema
|
return sqlite_schema
|
||||||
|
|
||||||
def record(
|
|
||||||
operation: SQLOperation,
|
|
||||||
target_type: SQLTargetType,
|
|
||||||
*,
|
|
||||||
database: str | None,
|
|
||||||
table: str | None,
|
|
||||||
sqlite_schema: str | None,
|
|
||||||
target: str | None,
|
|
||||||
source: str | None,
|
|
||||||
column: str | None = None,
|
|
||||||
internal: bool = False,
|
|
||||||
):
|
|
||||||
key = OperationKey(
|
|
||||||
operation=operation,
|
|
||||||
target_type=target_type,
|
|
||||||
database=database,
|
|
||||||
table=table,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=target,
|
|
||||||
source=source,
|
|
||||||
internal=internal,
|
|
||||||
)
|
|
||||||
columns = operations.setdefault(key, set())
|
|
||||||
if column is not None:
|
|
||||||
columns.add(column)
|
|
||||||
|
|
||||||
def authorizer(action, arg1, arg2, sqlite_schema, source):
|
def authorizer(action, arg1, arg2, sqlite_schema, source):
|
||||||
operation = _ACTION_TO_OPERATION.get(action)
|
operation = _ACTION_TO_OPERATION.get(action)
|
||||||
if operation is not None and arg1 is not None:
|
if operation is None or arg1 is None:
|
||||||
target_type = "schema" if arg1 in _SQLITE_SCHEMA_TABLES else "table"
|
return sqlite3.SQLITE_OK
|
||||||
column = (
|
|
||||||
arg2 if operation in ("read", "update") and arg2 is not None else None
|
key = (
|
||||||
)
|
|
||||||
record(
|
|
||||||
operation,
|
operation,
|
||||||
target_type,
|
database_for_schema(sqlite_schema),
|
||||||
database=database_for_schema(sqlite_schema),
|
arg1,
|
||||||
table=arg1 if target_type == "table" else None,
|
sqlite_schema,
|
||||||
sqlite_schema=sqlite_schema,
|
source,
|
||||||
target=arg1,
|
|
||||||
source=source,
|
|
||||||
column=column,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
create_operation = _CREATE_ACTIONS.get(action)
|
|
||||||
if create_operation is not None and arg1 is not None:
|
|
||||||
operation, target_type = create_operation
|
|
||||||
related_table = arg2 if target_type in {"index", "trigger"} else arg1
|
|
||||||
record(
|
|
||||||
operation,
|
|
||||||
target_type,
|
|
||||||
database=database_for_schema(sqlite_schema),
|
|
||||||
table=related_table,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=arg1,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
drop_operation = _DROP_ACTIONS.get(action)
|
|
||||||
if drop_operation is not None and arg1 is not None:
|
|
||||||
operation, target_type = drop_operation
|
|
||||||
related_table = arg2 if target_type in {"index", "trigger"} else arg1
|
|
||||||
record(
|
|
||||||
operation,
|
|
||||||
target_type,
|
|
||||||
database=database_for_schema(sqlite_schema),
|
|
||||||
table=related_table,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=arg1,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_ALTER_TABLE and arg2 is not None:
|
|
||||||
record(
|
|
||||||
"alter",
|
|
||||||
"table",
|
|
||||||
database=database_for_schema(arg1),
|
|
||||||
table=arg2,
|
|
||||||
sqlite_schema=arg1,
|
|
||||||
target=arg2,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_TRANSACTION and arg1 is not None:
|
|
||||||
record(
|
|
||||||
arg1.lower(),
|
|
||||||
"transaction",
|
|
||||||
database=None,
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=None,
|
|
||||||
target=arg1,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_ATTACH and arg1 is not None:
|
|
||||||
record(
|
|
||||||
"attach",
|
|
||||||
"database",
|
|
||||||
database=None,
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=None,
|
|
||||||
target=arg1,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_DETACH and arg1 is not None:
|
|
||||||
record(
|
|
||||||
"detach",
|
|
||||||
"database",
|
|
||||||
database=None,
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=None,
|
|
||||||
target=arg1,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_PRAGMA and arg1 is not None:
|
|
||||||
record(
|
|
||||||
"pragma",
|
|
||||||
"pragma",
|
|
||||||
database=None,
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=arg1,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_ANALYZE:
|
|
||||||
record(
|
|
||||||
"analyze",
|
|
||||||
"database" if arg1 is None else "table",
|
|
||||||
database=database_for_schema(sqlite_schema),
|
|
||||||
table=arg1,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=arg1,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_REINDEX and arg1 is not None:
|
|
||||||
record(
|
|
||||||
"reindex",
|
|
||||||
"index",
|
|
||||||
database=database_for_schema(sqlite_schema),
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=arg1,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_SELECT:
|
|
||||||
record(
|
|
||||||
"select",
|
|
||||||
"statement",
|
|
||||||
database=None,
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=None,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_FUNCTION and arg2 is not None:
|
|
||||||
record(
|
|
||||||
"function",
|
|
||||||
"function",
|
|
||||||
database=None,
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=arg2,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
if action == sqlite3.SQLITE_SAVEPOINT and arg1 is not None:
|
|
||||||
record(
|
|
||||||
"savepoint",
|
|
||||||
"transaction",
|
|
||||||
database=None,
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target="{} {}".format(arg1, arg2) if arg2 is not None else arg1,
|
|
||||||
source=source,
|
|
||||||
)
|
|
||||||
return sqlite3.SQLITE_OK
|
|
||||||
|
|
||||||
action_name = _AUTHORIZER_ACTION_NAMES.get(action, "SQLITE_{}".format(action))
|
|
||||||
record(
|
|
||||||
"unknown",
|
|
||||||
"unknown",
|
|
||||||
database=database_for_schema(sqlite_schema),
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=action_name,
|
|
||||||
source=source,
|
|
||||||
)
|
)
|
||||||
|
columns = accesses.setdefault(key, set())
|
||||||
|
if operation in ("read", "update") and arg2 is not None:
|
||||||
|
columns.add(arg2)
|
||||||
return sqlite3.SQLITE_OK
|
return sqlite3.SQLITE_OK
|
||||||
|
|
||||||
conn.set_authorizer(authorizer)
|
conn.set_authorizer(authorizer)
|
||||||
try:
|
try:
|
||||||
explain_rows = conn.execute(
|
conn.execute("EXPLAIN " + sql, params if params is not None else {}).fetchall()
|
||||||
"EXPLAIN " + sql, params if params is not None else {}
|
|
||||||
).fetchall()
|
|
||||||
finally:
|
finally:
|
||||||
conn.set_authorizer(None)
|
conn.set_authorizer(None)
|
||||||
|
|
||||||
if not operations:
|
|
||||||
vacuum_row = next((row for row in explain_rows if row[1] == "Vacuum"), None)
|
|
||||||
if vacuum_row is not None:
|
|
||||||
schema_by_index = {
|
|
||||||
row[0]: row[1] for row in conn.execute("PRAGMA database_list")
|
|
||||||
}
|
|
||||||
sqlite_schema = schema_by_index.get(vacuum_row[2])
|
|
||||||
database = database_for_schema(sqlite_schema)
|
|
||||||
record(
|
|
||||||
"vacuum",
|
|
||||||
"database",
|
|
||||||
database=database,
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=sqlite_schema,
|
|
||||||
target=database,
|
|
||||||
source=None,
|
|
||||||
)
|
|
||||||
else:
|
|
||||||
record(
|
|
||||||
"unknown",
|
|
||||||
"statement",
|
|
||||||
database=database_name,
|
|
||||||
table=None,
|
|
||||||
sqlite_schema=None,
|
|
||||||
target=None,
|
|
||||||
source=None,
|
|
||||||
)
|
|
||||||
|
|
||||||
has_schema_operation = any(
|
|
||||||
key.target_type in {"table", "index", "view", "trigger", "virtual-table"}
|
|
||||||
and key.operation in {"create", "alter", "drop"}
|
|
||||||
for key in operations
|
|
||||||
)
|
|
||||||
dropped_tables = {
|
|
||||||
(key.database, key.table)
|
|
||||||
for key in operations
|
|
||||||
if key.operation == "drop" and key.target_type == "table"
|
|
||||||
}
|
|
||||||
|
|
||||||
def key_is_drop_table_delete(key: OperationKey) -> bool:
|
|
||||||
return (
|
|
||||||
key.operation == "delete"
|
|
||||||
and key.target_type == "table"
|
|
||||||
and (key.database, key.table) in dropped_tables
|
|
||||||
)
|
|
||||||
|
|
||||||
has_user_table_access_in_schema_operation = any(
|
|
||||||
key.operation in {"read", "insert", "update", "delete"}
|
|
||||||
and key.target_type == "table"
|
|
||||||
and not key.internal
|
|
||||||
and not key_is_drop_table_delete(key)
|
|
||||||
for key in operations
|
|
||||||
)
|
|
||||||
|
|
||||||
def operation_is_internal(key: OperationKey) -> bool:
|
|
||||||
if key.internal or (has_schema_operation and key.target_type == "schema"):
|
|
||||||
return True
|
|
||||||
if has_schema_operation and key.operation == "reindex":
|
|
||||||
return True
|
|
||||||
if (
|
|
||||||
has_schema_operation
|
|
||||||
and not has_user_table_access_in_schema_operation
|
|
||||||
and key.operation == "function"
|
|
||||||
and key.target in _SQLITE_INTERNAL_SCHEMA_FUNCTIONS
|
|
||||||
):
|
|
||||||
return True
|
|
||||||
if key_is_drop_table_delete(key):
|
|
||||||
return True
|
|
||||||
return False
|
|
||||||
|
|
||||||
return SQLAnalysis(
|
return SQLAnalysis(
|
||||||
operations=tuple(
|
table_accesses=tuple(
|
||||||
Operation(
|
SQLTableAccess(
|
||||||
operation=key.operation,
|
operation=operation,
|
||||||
target_type=key.target_type,
|
database=database,
|
||||||
database=key.database,
|
table=table,
|
||||||
table=key.table,
|
sqlite_schema=sqlite_schema,
|
||||||
sqlite_schema=key.sqlite_schema,
|
|
||||||
target=key.target,
|
|
||||||
columns=tuple(sorted(columns)),
|
columns=tuple(sorted(columns)),
|
||||||
source=key.source,
|
source=source,
|
||||||
internal=operation_is_internal(key),
|
|
||||||
)
|
)
|
||||||
for key, columns in operations.items()
|
for (
|
||||||
|
operation,
|
||||||
|
database,
|
||||||
|
table,
|
||||||
|
sqlite_schema,
|
||||||
|
source,
|
||||||
|
), columns in accesses.items()
|
||||||
)
|
)
|
||||||
)
|
)
|
||||||
|
|
|
||||||
|
|
@ -13,7 +13,7 @@ import textwrap
|
||||||
from datasette.events import AlterTableEvent, CreateTableEvent, InsertRowsEvent
|
from datasette.events import AlterTableEvent, CreateTableEvent, InsertRowsEvent
|
||||||
from datasette.database import QueryInterrupted
|
from datasette.database import QueryInterrupted
|
||||||
from datasette.resources import DatabaseResource, QueryResource
|
from datasette.resources import DatabaseResource, QueryResource
|
||||||
from datasette.stored_queries import QueryWriteRejected, stored_query_to_dict
|
from datasette.stored_queries import stored_query_to_dict
|
||||||
from datasette.utils import (
|
from datasette.utils import (
|
||||||
add_cors_headers,
|
add_cors_headers,
|
||||||
await_me_maybe,
|
await_me_maybe,
|
||||||
|
|
@ -453,24 +453,9 @@ class QueryView(View):
|
||||||
):
|
):
|
||||||
raise Forbidden("You do not have permission to view this query")
|
raise Forbidden("You do not have permission to view this query")
|
||||||
|
|
||||||
try:
|
|
||||||
await _ensure_stored_query_execution_permissions(
|
await _ensure_stored_query_execution_permissions(
|
||||||
datasette, db, stored_query, request.actor
|
datasette, db, stored_query, request.actor
|
||||||
)
|
)
|
||||||
except QueryWriteRejected as ex:
|
|
||||||
if request.headers.get("accept") == "application/json" or request.args.get(
|
|
||||||
"_json"
|
|
||||||
):
|
|
||||||
return Response.json(
|
|
||||||
{
|
|
||||||
"ok": False,
|
|
||||||
"message": ex.message,
|
|
||||||
"redirect": None,
|
|
||||||
},
|
|
||||||
status=403,
|
|
||||||
)
|
|
||||||
datasette.add_message(request, ex.message, datasette.ERROR)
|
|
||||||
return Response.redirect(stored_query.on_error_redirect or request.path)
|
|
||||||
|
|
||||||
# If database is immutable, return an error
|
# If database is immutable, return an error
|
||||||
if not db.is_mutable:
|
if not db.is_mutable:
|
||||||
|
|
|
||||||
|
|
@ -99,7 +99,9 @@ class ExecuteWriteView(BaseView):
|
||||||
"parameter_names": parameter_names,
|
"parameter_names": parameter_names,
|
||||||
"parameter_values": parameter_values,
|
"parameter_values": parameter_values,
|
||||||
"analysis_error": analysis_error,
|
"analysis_error": analysis_error,
|
||||||
"analysis_rows": analysis_rows,
|
"analysis_rows": [
|
||||||
|
row for row in analysis_rows if row["operation"] != "read"
|
||||||
|
],
|
||||||
"execution_message": execution_message,
|
"execution_message": execution_message,
|
||||||
"execution_links": execution_links,
|
"execution_links": execution_links,
|
||||||
"execution_ok": execution_ok,
|
"execution_ok": execution_ok,
|
||||||
|
|
@ -163,15 +165,13 @@ class ExecuteWriteView(BaseView):
|
||||||
except QueryValidationError as ex:
|
except QueryValidationError as ex:
|
||||||
if _wants_json(request, is_json, data):
|
if _wants_json(request, is_json, data):
|
||||||
return _block_framing(_error([ex.message], ex.status))
|
return _block_framing(_error([ex.message], ex.status))
|
||||||
if ex.flash:
|
|
||||||
self.ds.add_message(request, ex.message, self.ds.ERROR)
|
|
||||||
return await self._render_form(
|
return await self._render_form(
|
||||||
request,
|
request,
|
||||||
db,
|
db,
|
||||||
sql=sql or "",
|
sql=sql or "",
|
||||||
parameter_values=provided_params,
|
parameter_values=provided_params,
|
||||||
analysis_error=None if ex.flash else ex.message,
|
analysis_error=ex.message,
|
||||||
execution_message=None if ex.flash else ex.message,
|
execution_message=ex.message,
|
||||||
execution_ok=False,
|
execution_ok=False,
|
||||||
status=ex.status,
|
status=ex.status,
|
||||||
)
|
)
|
||||||
|
|
@ -193,9 +193,6 @@ class ExecuteWriteView(BaseView):
|
||||||
status=400,
|
status=400,
|
||||||
)
|
)
|
||||||
|
|
||||||
if cursor.rowcount == -1:
|
|
||||||
message = "Query executed"
|
|
||||||
else:
|
|
||||||
message = "Query executed, {} row{} affected".format(
|
message = "Query executed, {} row{} affected".format(
|
||||||
cursor.rowcount, "" if cursor.rowcount == 1 else "s"
|
cursor.rowcount, "" if cursor.rowcount == 1 else "s"
|
||||||
)
|
)
|
||||||
|
|
|
||||||
|
|
@ -1,14 +1,8 @@
|
||||||
import json
|
import json
|
||||||
import re
|
import re
|
||||||
|
|
||||||
from datasette.resources import DatabaseResource
|
from datasette.resources import DatabaseResource, TableResource
|
||||||
from datasette.stored_queries import (
|
from datasette.stored_queries import StoredQuery
|
||||||
QueryWriteRejected,
|
|
||||||
StoredQuery,
|
|
||||||
operation_is_write,
|
|
||||||
operation_should_be_ignored,
|
|
||||||
permission_requirements_for_operation,
|
|
||||||
)
|
|
||||||
from datasette.utils import (
|
from datasette.utils import (
|
||||||
named_parameters as derive_named_parameters,
|
named_parameters as derive_named_parameters,
|
||||||
escape_sqlite,
|
escape_sqlite,
|
||||||
|
|
@ -18,7 +12,6 @@ from datasette.utils import (
|
||||||
InvalidSql,
|
InvalidSql,
|
||||||
)
|
)
|
||||||
from datasette.utils.asgi import Forbidden
|
from datasette.utils.asgi import Forbidden
|
||||||
from datasette.utils.sql_analysis import Operation, SQLAnalysis
|
|
||||||
|
|
||||||
_query_name_re = re.compile(r"^[^/\.\n]+$")
|
_query_name_re = re.compile(r"^[^/\.\n]+$")
|
||||||
|
|
||||||
|
|
@ -48,11 +41,9 @@ _query_write_fields = {
|
||||||
|
|
||||||
|
|
||||||
class QueryValidationError(Exception):
|
class QueryValidationError(Exception):
|
||||||
def __init__(self, message, status=400, *, flash=False):
|
def __init__(self, message, status=400):
|
||||||
self.message = message
|
self.message = message
|
||||||
self.status = status
|
self.status = status
|
||||||
self.flash = flash
|
|
||||||
super().__init__(message)
|
|
||||||
|
|
||||||
|
|
||||||
def _actor_id(actor):
|
def _actor_id(actor):
|
||||||
|
|
@ -132,8 +123,11 @@ def _coerce_query_parameters(value, derived):
|
||||||
return parameters
|
return parameters
|
||||||
|
|
||||||
|
|
||||||
def _analysis_is_write(analysis: SQLAnalysis) -> bool:
|
def _analysis_is_write(analysis):
|
||||||
return any(operation_is_write(operation) for operation in analysis.operations)
|
return any(
|
||||||
|
access.operation in {"insert", "update", "delete"}
|
||||||
|
for access in analysis.table_accesses
|
||||||
|
)
|
||||||
|
|
||||||
|
|
||||||
def _block_framing(response):
|
def _block_framing(response):
|
||||||
|
|
@ -197,8 +191,6 @@ async def _analyze_user_query(datasette, db, sql, *, actor):
|
||||||
await datasette.ensure_query_write_permissions(
|
await datasette.ensure_query_write_permissions(
|
||||||
db.name, sql, actor=actor, analysis=analysis
|
db.name, sql, actor=actor, analysis=analysis
|
||||||
)
|
)
|
||||||
except QueryWriteRejected as ex:
|
|
||||||
raise QueryValidationError(ex.message, status=403, flash=True) from ex
|
|
||||||
except Forbidden as ex:
|
except Forbidden as ex:
|
||||||
raise QueryValidationError(str(ex), status=403) from ex
|
raise QueryValidationError(str(ex), status=403) from ex
|
||||||
else:
|
else:
|
||||||
|
|
@ -209,51 +201,34 @@ async def _analyze_user_query(datasette, db, sql, *, actor):
|
||||||
return is_write, derived, analysis
|
return is_write, derived, analysis
|
||||||
|
|
||||||
|
|
||||||
def _display_operations(analysis: SQLAnalysis) -> list[Operation]:
|
def _analysis_rows(analysis):
|
||||||
operations = []
|
write_actions = {
|
||||||
for operation in analysis.operations:
|
"insert": "insert-row",
|
||||||
if operation_should_be_ignored(operation):
|
"update": "update-row",
|
||||||
continue
|
"delete": "delete-row",
|
||||||
operations.append(operation)
|
|
||||||
return operations
|
|
||||||
|
|
||||||
|
|
||||||
def _analysis_rows(analysis: SQLAnalysis) -> list[dict[str, object]]:
|
|
||||||
rows = []
|
|
||||||
for operation in _display_operations(analysis):
|
|
||||||
permissions = permission_requirements_for_operation(operation)
|
|
||||||
required_permission = ", ".join(permission.action for permission in permissions)
|
|
||||||
rows.append(
|
|
||||||
{
|
|
||||||
"operation": operation.operation,
|
|
||||||
"database": operation.database,
|
|
||||||
"table": operation.table or operation.target,
|
|
||||||
"required_permission": required_permission,
|
|
||||||
"source": operation.source,
|
|
||||||
}
|
}
|
||||||
)
|
return [
|
||||||
return rows
|
{
|
||||||
|
"operation": access.operation,
|
||||||
|
"database": access.database,
|
||||||
|
"table": access.table,
|
||||||
|
"required_permission": write_actions.get(access.operation, ""),
|
||||||
|
"source": access.source,
|
||||||
|
}
|
||||||
|
for access in analysis.table_accesses
|
||||||
|
]
|
||||||
|
|
||||||
|
|
||||||
async def _analysis_rows_with_permissions(
|
async def _analysis_rows_with_permissions(datasette, analysis, actor):
|
||||||
datasette, analysis: SQLAnalysis, actor
|
|
||||||
) -> list[dict[str, object]]:
|
|
||||||
rows = _analysis_rows(analysis)
|
rows = _analysis_rows(analysis)
|
||||||
is_write = _analysis_is_write(analysis)
|
for row in rows:
|
||||||
for row, operation in zip(rows, _display_operations(analysis)):
|
permission = row["required_permission"]
|
||||||
permissions = permission_requirements_for_operation(operation)
|
if permission:
|
||||||
if permissions:
|
row["allowed"] = await datasette.allowed(
|
||||||
row["allowed"] = True
|
action=permission,
|
||||||
for permission in permissions:
|
resource=TableResource(row["database"], row["table"]),
|
||||||
if not await datasette.allowed(
|
|
||||||
action=permission.action,
|
|
||||||
resource=permission.resource,
|
|
||||||
actor=actor,
|
actor=actor,
|
||||||
):
|
)
|
||||||
row["allowed"] = False
|
|
||||||
break
|
|
||||||
elif is_write:
|
|
||||||
row["allowed"] = False
|
|
||||||
else:
|
else:
|
||||||
row["allowed"] = None
|
row["allowed"] = None
|
||||||
return rows
|
return rows
|
||||||
|
|
@ -302,8 +277,6 @@ async def _prepare_execute_write(datasette, db, sql, params, actor):
|
||||||
await datasette.ensure_query_write_permissions(
|
await datasette.ensure_query_write_permissions(
|
||||||
db.name, sql, actor=actor, analysis=analysis
|
db.name, sql, actor=actor, analysis=analysis
|
||||||
)
|
)
|
||||||
except QueryWriteRejected as ex:
|
|
||||||
raise QueryValidationError(ex.message, status=403, flash=True) from ex
|
|
||||||
except Forbidden as ex:
|
except Forbidden as ex:
|
||||||
raise QueryValidationError(str(ex), status=403) from ex
|
raise QueryValidationError(str(ex), status=403) from ex
|
||||||
return parameter_names, params, analysis
|
return parameter_names, params, analysis
|
||||||
|
|
@ -353,7 +326,7 @@ async def _execute_write_analysis_data(datasette, db, sql, actor):
|
||||||
"ok": analysis_error is None,
|
"ok": analysis_error is None,
|
||||||
"parameters": parameter_names,
|
"parameters": parameter_names,
|
||||||
"analysis_error": analysis_error,
|
"analysis_error": analysis_error,
|
||||||
"analysis_rows": analysis_rows,
|
"analysis_rows": [row for row in analysis_rows if row["operation"] != "read"],
|
||||||
"execute_disabled": bool(
|
"execute_disabled": bool(
|
||||||
(not sql)
|
(not sql)
|
||||||
or analysis_error
|
or analysis_error
|
||||||
|
|
@ -367,7 +340,6 @@ async def _query_create_analysis_data(datasette, db, sql, actor):
|
||||||
parameter_names = []
|
parameter_names = []
|
||||||
analysis_rows = []
|
analysis_rows = []
|
||||||
analysis_error = None
|
analysis_error = None
|
||||||
analysis: SQLAnalysis | None = None
|
|
||||||
if has_sql:
|
if has_sql:
|
||||||
try:
|
try:
|
||||||
parameter_names = _derived_query_parameters(sql)
|
parameter_names = _derived_query_parameters(sql)
|
||||||
|
|
@ -384,7 +356,9 @@ async def _query_create_analysis_data(datasette, db, sql, actor):
|
||||||
"analysis_error": analysis_error,
|
"analysis_error": analysis_error,
|
||||||
"analysis_rows": analysis_rows,
|
"analysis_rows": analysis_rows,
|
||||||
"has_sql": has_sql,
|
"has_sql": has_sql,
|
||||||
"analysis_is_write": _analysis_is_write(analysis) if analysis else False,
|
"analysis_is_write": bool(
|
||||||
|
analysis_rows and any(row["required_permission"] for row in analysis_rows)
|
||||||
|
),
|
||||||
"save_disabled": bool(
|
"save_disabled": bool(
|
||||||
(not has_sql)
|
(not has_sql)
|
||||||
or analysis_error
|
or analysis_error
|
||||||
|
|
@ -424,19 +398,15 @@ async def _inserted_row_url(datasette, db, analysis, cursor):
|
||||||
if lastrowid is None:
|
if lastrowid is None:
|
||||||
return None
|
return None
|
||||||
direct_inserts = [
|
direct_inserts = [
|
||||||
operation
|
access
|
||||||
for operation in analysis.operations
|
for access in analysis.table_accesses
|
||||||
if operation.operation == "insert"
|
if access.operation == "insert"
|
||||||
and operation.target_type == "table"
|
and access.source is None
|
||||||
and not operation.internal
|
and access.database == db.name
|
||||||
and operation.source is None
|
|
||||||
and operation.database == db.name
|
|
||||||
]
|
]
|
||||||
if len(direct_inserts) != 1:
|
if len(direct_inserts) != 1:
|
||||||
return None
|
return None
|
||||||
table = direct_inserts[0].table
|
table = direct_inserts[0].table
|
||||||
if table is None:
|
|
||||||
return None
|
|
||||||
pks = await db.primary_keys(table)
|
pks = await db.primary_keys(table)
|
||||||
use_rowid = not pks
|
use_rowid = not pks
|
||||||
select = (
|
select = (
|
||||||
|
|
|
||||||
|
|
@ -505,68 +505,6 @@ The JSON write API
|
||||||
|
|
||||||
Datasette provides a write API for JSON data. This is a POST-only API that requires an authenticated API token, see :ref:`CreateTokenView`. The token will need to have the specified :ref:`authentication_permissions`.
|
Datasette provides a write API for JSON data. This is a POST-only API that requires an authenticated API token, see :ref:`CreateTokenView`. The token will need to have the specified :ref:`authentication_permissions`.
|
||||||
|
|
||||||
.. _ExecuteWriteView:
|
|
||||||
|
|
||||||
Executing write SQL
|
|
||||||
~~~~~~~~~~~~~~~~~~~
|
|
||||||
|
|
||||||
Actors with the :ref:`actions_execute_write_sql` permission can execute arbitrary writable SQL against a mutable database using ``/-/execute-write``.
|
|
||||||
|
|
||||||
::
|
|
||||||
|
|
||||||
POST /<database>/-/execute-write
|
|
||||||
Content-Type: application/json
|
|
||||||
Authorization: Bearer dstok_<rest-of-token>
|
|
||||||
|
|
||||||
The request body must include a ``"sql"`` string. Named SQL parameters can be provided using the optional ``"params"`` object:
|
|
||||||
|
|
||||||
.. code-block:: json
|
|
||||||
|
|
||||||
{
|
|
||||||
"sql": "insert into dogs (name) values (:name)",
|
|
||||||
"params": {
|
|
||||||
"name": "Cleo"
|
|
||||||
}
|
|
||||||
}
|
|
||||||
|
|
||||||
The SQL must be writable. Read-only ``select`` queries should use the regular :ref:`custom SQL query API <sql>` instead.
|
|
||||||
|
|
||||||
Datasette analyzes the SQL before executing it. The actor must have ``execute-write-sql`` permission for the database, and must also have any permissions required by the operations in the SQL. For example, inserts and updates against a table require ``insert-row``, ``update-row`` and ``delete-row`` permissions for that table. Reads performed as part of the write, such as ``insert into dogs select ... from other_table``, require ``view-table`` permission on the source table.
|
|
||||||
|
|
||||||
A successful response includes a message, the SQLite ``rowcount`` and a summary of the operations that were executed:
|
|
||||||
|
|
||||||
The shape of the ``"analysis"`` block is not yet considered a stable API and may change in future Datasette releases.
|
|
||||||
|
|
||||||
.. code-block:: json
|
|
||||||
|
|
||||||
{
|
|
||||||
"ok": true,
|
|
||||||
"message": "Query executed, 1 row affected",
|
|
||||||
"rowcount": 1,
|
|
||||||
"analysis": [
|
|
||||||
{
|
|
||||||
"operation": "insert",
|
|
||||||
"database": "data",
|
|
||||||
"table": "dogs",
|
|
||||||
"required_permission": "insert-row, update-row, delete-row",
|
|
||||||
"source": null
|
|
||||||
}
|
|
||||||
]
|
|
||||||
}
|
|
||||||
|
|
||||||
If SQLite reports ``-1`` for the row count, the message will be ``"Query executed"``.
|
|
||||||
|
|
||||||
Errors use the standard Datasette error format:
|
|
||||||
|
|
||||||
.. code-block:: json
|
|
||||||
|
|
||||||
{
|
|
||||||
"ok": false,
|
|
||||||
"errors": [
|
|
||||||
"Permission denied: need execute-write-sql"
|
|
||||||
]
|
|
||||||
}
|
|
||||||
|
|
||||||
.. _TableInsertView:
|
.. _TableInsertView:
|
||||||
|
|
||||||
Inserting rows
|
Inserting rows
|
||||||
|
|
|
||||||
|
|
@ -12,22 +12,10 @@ import pytest
|
||||||
import pytest_asyncio
|
import pytest_asyncio
|
||||||
from datasette.app import Datasette
|
from datasette.app import Datasette
|
||||||
from datasette.permissions import PermissionSQL
|
from datasette.permissions import PermissionSQL
|
||||||
from datasette.resources import DatabaseResource, QueryResource, TableResource
|
from datasette.resources import TableResource
|
||||||
from datasette import hookimpl
|
from datasette import hookimpl
|
||||||
|
|
||||||
|
|
||||||
def test_resource_string_representations():
|
|
||||||
assert str(DatabaseResource("content")) == "content"
|
|
||||||
assert repr(DatabaseResource("content")) == (
|
|
||||||
"DatabaseResource(parent='content', child=None)"
|
|
||||||
)
|
|
||||||
assert str(TableResource("content", "dogs")) == "content/dogs"
|
|
||||||
assert repr(TableResource("content", "dogs")) == (
|
|
||||||
"TableResource(parent='content', child='dogs')"
|
|
||||||
)
|
|
||||||
assert str(QueryResource("content", "insert-a-dog")) == "content/insert-a-dog"
|
|
||||||
|
|
||||||
|
|
||||||
# Test plugin that provides permission rules
|
# Test plugin that provides permission rules
|
||||||
class PermissionRulesPlugin:
|
class PermissionRulesPlugin:
|
||||||
def __init__(self, rules_callback):
|
def __init__(self, rules_callback):
|
||||||
|
|
|
||||||
|
|
@ -698,17 +698,14 @@ async def test_analyze_sql():
|
||||||
|
|
||||||
assert [
|
assert [
|
||||||
(
|
(
|
||||||
operation.operation,
|
access.operation,
|
||||||
operation.database,
|
access.database,
|
||||||
operation.sqlite_schema,
|
access.sqlite_schema,
|
||||||
operation.table,
|
access.table,
|
||||||
operation.columns,
|
access.columns,
|
||||||
operation.source,
|
access.source,
|
||||||
)
|
)
|
||||||
for operation in analysis.operations
|
for access in analysis.table_accesses
|
||||||
if operation.target_type == "table"
|
|
||||||
and operation.operation in {"read", "insert", "update", "delete"}
|
|
||||||
and not operation.internal
|
|
||||||
] == [
|
] == [
|
||||||
("read", "data", "main", "dogs", ("id", "name"), None),
|
("read", "data", "main", "dogs", ("id", "name"), None),
|
||||||
]
|
]
|
||||||
|
|
@ -725,17 +722,14 @@ async def test_analyze_sql_insert_select():
|
||||||
|
|
||||||
assert {
|
assert {
|
||||||
(
|
(
|
||||||
operation.operation,
|
access.operation,
|
||||||
operation.database,
|
access.database,
|
||||||
operation.sqlite_schema,
|
access.sqlite_schema,
|
||||||
operation.table,
|
access.table,
|
||||||
operation.columns,
|
access.columns,
|
||||||
operation.source,
|
access.source,
|
||||||
)
|
)
|
||||||
for operation in analysis.operations
|
for access in analysis.table_accesses
|
||||||
if operation.target_type == "table"
|
|
||||||
and operation.operation in {"read", "insert", "update", "delete"}
|
|
||||||
and not operation.internal
|
|
||||||
} == {
|
} == {
|
||||||
("insert", "data", "main", "dogs", (), None),
|
("insert", "data", "main", "dogs", (), None),
|
||||||
("read", "data", "main", "cats", ("name",), None),
|
("read", "data", "main", "cats", ("name",), None),
|
||||||
|
|
|
||||||
|
|
@ -508,8 +508,6 @@ async def test_analyze_write_query_requires_table_permissions():
|
||||||
"dogs": {
|
"dogs": {
|
||||||
"permissions": {
|
"permissions": {
|
||||||
"insert-row": {"id": "writer"},
|
"insert-row": {"id": "writer"},
|
||||||
"update-row": {"id": "writer"},
|
|
||||||
"delete-row": {"id": "writer"},
|
|
||||||
}
|
}
|
||||||
}
|
}
|
||||||
}
|
}
|
||||||
|
|
@ -1183,10 +1181,11 @@ async def test_create_query_ui_and_arbitrary_sql_save_link():
|
||||||
assert '<th scope="col">Required permission</th>' in create_response.text
|
assert '<th scope="col">Required permission</th>' in create_response.text
|
||||||
assert '<th scope="col">Source</th>' not in create_response.text
|
assert '<th scope="col">Source</th>' not in create_response.text
|
||||||
assert "<td><code>read</code></td>" in create_response.text
|
assert "<td><code>read</code></td>" in create_response.text
|
||||||
assert "<td><code>view-table</code></td>" in create_response.text
|
|
||||||
assert (
|
assert (
|
||||||
|
create_response.text.count(
|
||||||
'<td><span class="execute-write-analysis-na">n/a</span></td>'
|
'<td><span class="execute-write-analysis-na">n/a</span></td>'
|
||||||
not in create_response.text
|
)
|
||||||
|
== 2
|
||||||
)
|
)
|
||||||
assert create_response.text.index(
|
assert create_response.text.index(
|
||||||
'value="Save query"'
|
'value="Save query"'
|
||||||
|
|
@ -1256,9 +1255,9 @@ async def test_create_query_analyze_endpoint_uses_sql_only():
|
||||||
"operation": "read",
|
"operation": "read",
|
||||||
"database": "data",
|
"database": "data",
|
||||||
"table": "dogs",
|
"table": "dogs",
|
||||||
"required_permission": "view-table",
|
"required_permission": "",
|
||||||
"source": None,
|
"source": None,
|
||||||
"allowed": True,
|
"allowed": None,
|
||||||
}
|
}
|
||||||
]
|
]
|
||||||
|
|
||||||
|
|
@ -1376,8 +1375,7 @@ async def test_execute_write_get_prepopulates_without_executing():
|
||||||
assert '<th scope="col">Required permission</th>' in response.text
|
assert '<th scope="col">Required permission</th>' in response.text
|
||||||
assert "<td><code>insert</code></td>" in response.text
|
assert "<td><code>insert</code></td>" in response.text
|
||||||
assert "<td><code>update</code></td>" in response.text
|
assert "<td><code>update</code></td>" in response.text
|
||||||
assert "<td><code>read</code></td>" in response.text
|
assert "<td><code>read</code></td>" not in response.text
|
||||||
assert "<td><code>view-table</code></td>" in response.text
|
|
||||||
assert 'action="/data/-/execute-write"' in response.text
|
assert 'action="/data/-/execute-write"' in response.text
|
||||||
assert "insert into dogs (name) values ('Cleo')" in response.text
|
assert "insert into dogs (name) values ('Cleo')" in response.text
|
||||||
assert (await db.execute("select count(*) from dogs")).first()[0] == 0
|
assert (await db.execute("select count(*) from dogs")).first()[0] == 0
|
||||||
|
|
@ -1431,7 +1429,7 @@ async def test_execute_write_analyze_endpoint_uses_sql_only():
|
||||||
"operation": "insert",
|
"operation": "insert",
|
||||||
"database": "data",
|
"database": "data",
|
||||||
"table": "dogs",
|
"table": "dogs",
|
||||||
"required_permission": "insert-row, update-row, delete-row",
|
"required_permission": "insert-row",
|
||||||
"source": None,
|
"source": None,
|
||||||
"allowed": True,
|
"allowed": True,
|
||||||
}
|
}
|
||||||
|
|
@ -1629,40 +1627,6 @@ async def test_execute_write_post_requires_database_and_table_permissions():
|
||||||
}
|
}
|
||||||
}
|
}
|
||||||
}
|
}
|
||||||
missing_update_permission = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
json={
|
|
||||||
"sql": "insert into dogs (name) values (:name)",
|
|
||||||
"params": {"name": "Cleo"},
|
|
||||||
},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert missing_update_permission.status_code == 403
|
|
||||||
assert missing_update_permission.json()["errors"] == [
|
|
||||||
"Permission denied: need update-row on data/dogs"
|
|
||||||
]
|
|
||||||
|
|
||||||
ds.config["databases"]["data"]["tables"]["dogs"]["permissions"]["update-row"] = {
|
|
||||||
"id": "writer"
|
|
||||||
}
|
|
||||||
missing_delete_permission = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
json={
|
|
||||||
"sql": "insert into dogs (name) values (:name)",
|
|
||||||
"params": {"name": "Cleo"},
|
|
||||||
},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert missing_delete_permission.status_code == 403
|
|
||||||
assert missing_delete_permission.json()["errors"] == [
|
|
||||||
"Permission denied: need delete-row on data/dogs"
|
|
||||||
]
|
|
||||||
|
|
||||||
ds.config["databases"]["data"]["tables"]["dogs"]["permissions"]["delete-row"] = {
|
|
||||||
"id": "writer"
|
|
||||||
}
|
|
||||||
allowed = await ds.client.post(
|
allowed = await ds.client.post(
|
||||||
"/data/-/execute-write",
|
"/data/-/execute-write",
|
||||||
actor={"id": "writer"},
|
actor={"id": "writer"},
|
||||||
|
|
@ -1679,687 +1643,6 @@ async def test_execute_write_post_requires_database_and_table_permissions():
|
||||||
assert (await db.execute("select name from dogs")).first()[0] == "Cleo"
|
assert (await db.execute("select name from dogs")).first()[0] == "Cleo"
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_insert_or_replace_requires_delete_row_permission():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
},
|
|
||||||
"tables": {
|
|
||||||
"users": {
|
|
||||||
"permissions": {
|
|
||||||
"insert-row": {"id": "writer"},
|
|
||||||
"update-row": {"id": "writer"},
|
|
||||||
"view-table": {"id": "writer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
db = ds.add_memory_database("execute_write_insert_or_replace", name="data")
|
|
||||||
await db.execute_write(
|
|
||||||
"create table users (id integer primary key, email text unique)"
|
|
||||||
)
|
|
||||||
await db.execute_write(
|
|
||||||
"insert into users (id, email) values "
|
|
||||||
"(1, 'a@example.com'), (2, 'b@example.com')"
|
|
||||||
)
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
json={
|
|
||||||
"sql": (
|
|
||||||
"insert or replace into users(id, email) " "values (3, 'b@example.com')"
|
|
||||||
)
|
|
||||||
},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert denied_response.json()["errors"] == [
|
|
||||||
"Permission denied: need delete-row on data/users"
|
|
||||||
]
|
|
||||||
assert (await db.execute("select id, email from users order by id")).dicts() == [
|
|
||||||
{"id": 1, "email": "a@example.com"},
|
|
||||||
{"id": 2, "email": "b@example.com"},
|
|
||||||
]
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_update_or_replace_requires_delete_row_permission():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
},
|
|
||||||
"tables": {
|
|
||||||
"users": {
|
|
||||||
"permissions": {
|
|
||||||
"insert-row": {"id": "writer"},
|
|
||||||
"update-row": {"id": "writer"},
|
|
||||||
"view-table": {"id": "writer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
db = ds.add_memory_database("execute_write_update_or_replace", name="data")
|
|
||||||
await db.execute_write(
|
|
||||||
"create table users (id integer primary key, email text unique)"
|
|
||||||
)
|
|
||||||
await db.execute_write(
|
|
||||||
"insert into users (id, email) values "
|
|
||||||
"(1, 'a@example.com'), (2, 'b@example.com')"
|
|
||||||
)
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
json={
|
|
||||||
"sql": "update or replace users set email = 'b@example.com' where id = 1"
|
|
||||||
},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert denied_response.json()["errors"] == [
|
|
||||||
"Permission denied: need delete-row on data/users"
|
|
||||||
]
|
|
||||||
assert (await db.execute("select id, email from users order by id")).dicts() == [
|
|
||||||
{"id": 1, "email": "a@example.com"},
|
|
||||||
{"id": 2, "email": "b@example.com"},
|
|
||||||
]
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_update_requires_insert_row_permission():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
},
|
|
||||||
"tables": {
|
|
||||||
"users": {
|
|
||||||
"permissions": {
|
|
||||||
"update-row": {"id": "writer"},
|
|
||||||
"delete-row": {"id": "writer"},
|
|
||||||
"view-table": {"id": "writer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
db = ds.add_memory_database("execute_write_update_requires_insert", name="data")
|
|
||||||
await db.execute_write("create table users (id integer primary key, name text)")
|
|
||||||
await db.execute_write("insert into users (id, name) values (1, 'Alice')")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
json={"sql": "update users set name = 'Alicia' where id = 1"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert denied_response.json()["errors"] == [
|
|
||||||
"Permission denied: need insert-row on data/users"
|
|
||||||
]
|
|
||||||
assert (await db.execute("select name from users where id = 1")).first()[
|
|
||||||
0
|
|
||||||
] == "Alice"
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_insert_select_requires_view_table_on_source():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
},
|
|
||||||
"tables": {
|
|
||||||
"secret": {
|
|
||||||
"permissions": {"view-table": {"id": "someone-else"}}
|
|
||||||
},
|
|
||||||
"public_log": {
|
|
||||||
"permissions": {
|
|
||||||
"insert-row": {"id": "writer"},
|
|
||||||
"update-row": {"id": "writer"},
|
|
||||||
"delete-row": {"id": "writer"},
|
|
||||||
}
|
|
||||||
},
|
|
||||||
},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
db = ds.add_memory_database("execute_write_insert_select_source", name="data")
|
|
||||||
await db.execute_write("create table secret (value text)")
|
|
||||||
await db.execute_write("create table public_log (value text)")
|
|
||||||
await db.execute_write("insert into secret values ('sensitive')")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
json={"sql": "insert into public_log(value) select value from secret"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert denied_response.json()["errors"] == [
|
|
||||||
"Permission denied: need view-table on data/secret"
|
|
||||||
]
|
|
||||||
assert (await db.execute("select value from public_log")).dicts() == []
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_rejects_sqlite_master_reads():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
},
|
|
||||||
"tables": {
|
|
||||||
"secret": {
|
|
||||||
"permissions": {"view-table": {"id": "someone-else"}}
|
|
||||||
},
|
|
||||||
"log": {
|
|
||||||
"permissions": {
|
|
||||||
"insert-row": {"id": "writer"},
|
|
||||||
"update-row": {"id": "writer"},
|
|
||||||
"delete-row": {"id": "writer"},
|
|
||||||
}
|
|
||||||
},
|
|
||||||
},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
db = ds.add_memory_database("execute_write_sqlite_master_read", name="data")
|
|
||||||
await db.execute_write("create table secret (value text)")
|
|
||||||
await db.execute_write("create table log (value text)")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
json={
|
|
||||||
"sql": (
|
|
||||||
"insert into log " "select sql from sqlite_master where name = 'secret'"
|
|
||||||
)
|
|
||||||
},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert denied_response.json()["errors"] == [
|
|
||||||
"Unsupported SQL operation: read schema"
|
|
||||||
]
|
|
||||||
assert (await db.execute("select value from log")).dicts() == []
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_create_table_as_select_requires_view_table_on_source():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "creator"},
|
|
||||||
"execute-write-sql": {"id": "creator"},
|
|
||||||
"create-table": {"id": "creator"},
|
|
||||||
},
|
|
||||||
"tables": {
|
|
||||||
"secret": {
|
|
||||||
"permissions": {"view-table": {"id": "someone-else"}}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
db = ds.add_memory_database("execute_write_create_as_select_source", name="data")
|
|
||||||
await db.execute_write("create table secret (value text)")
|
|
||||||
await db.execute_write("insert into secret values ('sensitive')")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "creator"},
|
|
||||||
json={"sql": "create table copied_secret as select value from secret"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert denied_response.json()["errors"] == [
|
|
||||||
"Permission denied: need view-table on data/secret"
|
|
||||||
]
|
|
||||||
assert not await db.table_exists("copied_secret")
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_rejects_function_operations():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
},
|
|
||||||
"tables": {
|
|
||||||
"dogs": {
|
|
||||||
"permissions": {
|
|
||||||
"insert-row": {"id": "writer"},
|
|
||||||
"update-row": {"id": "writer"},
|
|
||||||
"delete-row": {"id": "writer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
db = ds.add_memory_database("execute_write_function_operation", name="data")
|
|
||||||
await db.execute_write("create table dogs (id integer primary key, name text)")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
json={"sql": "insert into dogs (name) values (upper('cleo'))"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert denied_response.json()["errors"] == [
|
|
||||||
"Unsupported SQL operation: function function"
|
|
||||||
]
|
|
||||||
assert (await db.execute("select name from dogs")).dicts() == []
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_rejects_vacuum_operation():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
ds.add_memory_database("execute_write_vacuum_operation", name="data")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
json={"sql": "vacuum"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert denied_response.json()["errors"] == [
|
|
||||||
"VACUUM is not allowed in user-supplied SQL"
|
|
||||||
]
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_form_rejects_vacuum_operation_with_flash_error():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
ds.add_memory_database("execute_write_vacuum_operation_form", name="data")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
data={"sql": "vacuum"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert (
|
|
||||||
'<p class="message-error">VACUUM is not allowed in user-supplied SQL</p>'
|
|
||||||
in denied_response.text
|
|
||||||
)
|
|
||||||
assert denied_response.text.count("VACUUM is not allowed in user-supplied SQL") == 1
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_untrusted_stored_write_query_rejects_vacuum_operation():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"view-query": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
ds.add_memory_database("stored_query_vacuum_operation", name="data")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
await ds.add_query(
|
|
||||||
"data",
|
|
||||||
"vacuum_db",
|
|
||||||
"vacuum",
|
|
||||||
is_write=True,
|
|
||||||
is_trusted=False,
|
|
||||||
source="user",
|
|
||||||
owner_id="writer",
|
|
||||||
)
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/vacuum_db?_json=1",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
data={},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 403
|
|
||||||
assert "VACUUM is not allowed in user-supplied SQL" in denied_response.text
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_untrusted_stored_write_query_rejects_vacuum_operation_with_flash_error():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"view-query": {"id": "writer"},
|
|
||||||
"execute-write-sql": {"id": "writer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
ds.add_memory_database("stored_query_vacuum_operation_form", name="data")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
await ds.add_query(
|
|
||||||
"data",
|
|
||||||
"vacuum_db",
|
|
||||||
"vacuum",
|
|
||||||
is_write=True,
|
|
||||||
is_trusted=False,
|
|
||||||
source="user",
|
|
||||||
owner_id="writer",
|
|
||||||
)
|
|
||||||
|
|
||||||
denied_response = await ds.client.post(
|
|
||||||
"/data/vacuum_db",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
data={},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert denied_response.status_code == 302
|
|
||||||
assert denied_response.headers["location"] == "/data/vacuum_db"
|
|
||||||
assert ds.unsign(denied_response.cookies["ds_messages"], "messages") == [
|
|
||||||
["VACUUM is not allowed in user-supplied SQL", ds.ERROR]
|
|
||||||
]
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_trusted_stored_write_query_skips_vacuum_filtering():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": "writer"},
|
|
||||||
"view-query": {"id": "writer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
)
|
|
||||||
ds.add_memory_database("trusted_stored_query_vacuum", name="data")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
await ds.add_query(
|
|
||||||
"data",
|
|
||||||
"trusted_vacuum",
|
|
||||||
"vacuum",
|
|
||||||
is_write=True,
|
|
||||||
is_trusted=True,
|
|
||||||
source="config",
|
|
||||||
)
|
|
||||||
|
|
||||||
response = await ds.client.post(
|
|
||||||
"/data/trusted_vacuum?_json=1",
|
|
||||||
actor={"id": "writer"},
|
|
||||||
data={},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert response.status_code == 200
|
|
||||||
assert response.json()["ok"] is True
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_create_table_uses_create_table_permission():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"permissions": {
|
|
||||||
"insert-row": {"id": "row-writer"},
|
|
||||||
"update-row": {"id": "row-writer"},
|
|
||||||
},
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": ["creator", "row-writer"]},
|
|
||||||
"execute-write-sql": {"id": ["creator", "row-writer"]},
|
|
||||||
"create-table": {"id": "creator"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
},
|
|
||||||
)
|
|
||||||
db = ds.add_memory_database("execute_write_create_table", name="data")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
analysis_response = await ds.client.get(
|
|
||||||
"/data/-/execute-write/analyze",
|
|
||||||
actor={"id": "creator"},
|
|
||||||
params={"sql": "create table foobar (id integer primary key, name text)"},
|
|
||||||
)
|
|
||||||
allowed_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "creator"},
|
|
||||||
json={"sql": "create table foobar (id integer primary key, name text)"},
|
|
||||||
)
|
|
||||||
row_permission_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "row-writer"},
|
|
||||||
json={"sql": "create table should_not_exist (id integer primary key)"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert analysis_response.status_code == 200
|
|
||||||
analysis_data = analysis_response.json()
|
|
||||||
assert analysis_data["ok"] is True
|
|
||||||
assert analysis_data["execute_disabled"] is False
|
|
||||||
assert analysis_data["analysis_rows"] == [
|
|
||||||
{
|
|
||||||
"operation": "create",
|
|
||||||
"database": "data",
|
|
||||||
"table": "foobar",
|
|
||||||
"required_permission": "create-table",
|
|
||||||
"source": None,
|
|
||||||
"allowed": True,
|
|
||||||
}
|
|
||||||
]
|
|
||||||
|
|
||||||
assert allowed_response.status_code == 200
|
|
||||||
assert allowed_response.json()["ok"] is True
|
|
||||||
assert allowed_response.json()["message"] == "Query executed"
|
|
||||||
assert await db.table_exists("foobar")
|
|
||||||
|
|
||||||
assert row_permission_response.status_code == 403
|
|
||||||
assert row_permission_response.json()["errors"] == [
|
|
||||||
"Permission denied: need create-table on data"
|
|
||||||
]
|
|
||||||
assert not await db.table_exists("should_not_exist")
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
|
||||||
async def test_execute_write_alter_and_drop_table_use_schema_permissions():
|
|
||||||
ds = Datasette(
|
|
||||||
memory=True,
|
|
||||||
default_deny=True,
|
|
||||||
config={
|
|
||||||
"permissions": {
|
|
||||||
"delete-row": {"id": "row-writer"},
|
|
||||||
"update-row": {"id": "row-writer"},
|
|
||||||
},
|
|
||||||
"databases": {
|
|
||||||
"data": {
|
|
||||||
"permissions": {
|
|
||||||
"view-database": {"id": ["alterer", "dropper", "row-writer"]},
|
|
||||||
"execute-write-sql": {
|
|
||||||
"id": ["alterer", "dropper", "row-writer"]
|
|
||||||
},
|
|
||||||
},
|
|
||||||
"tables": {
|
|
||||||
"dogs": {
|
|
||||||
"permissions": {
|
|
||||||
"alter-table": {"id": "alterer"},
|
|
||||||
"drop-table": {"id": "dropper"},
|
|
||||||
"view-table": {"id": "alterer"},
|
|
||||||
}
|
|
||||||
}
|
|
||||||
},
|
|
||||||
}
|
|
||||||
},
|
|
||||||
},
|
|
||||||
)
|
|
||||||
db = ds.add_memory_database("execute_write_alter_drop_table", name="data")
|
|
||||||
await db.execute_write("create table dogs (id integer primary key, name text)")
|
|
||||||
await db.execute_write("create table cats (id integer primary key, name text)")
|
|
||||||
await ds.invoke_startup()
|
|
||||||
|
|
||||||
alter_allowed_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "alterer"},
|
|
||||||
json={"sql": "alter table dogs add column age integer"},
|
|
||||||
)
|
|
||||||
alter_row_permission_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "row-writer"},
|
|
||||||
json={"sql": "alter table cats add column age integer"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert alter_allowed_response.status_code == 200
|
|
||||||
assert "age" in [column.name for column in await db.table_column_details("dogs")]
|
|
||||||
assert alter_row_permission_response.status_code == 403
|
|
||||||
assert alter_row_permission_response.json()["errors"] == [
|
|
||||||
"Permission denied: need alter-table on data/cats"
|
|
||||||
]
|
|
||||||
assert "age" not in [
|
|
||||||
column.name for column in await db.table_column_details("cats")
|
|
||||||
]
|
|
||||||
|
|
||||||
create_index_allowed_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "alterer"},
|
|
||||||
json={"sql": "create index idx_dogs_name on dogs(name)"},
|
|
||||||
)
|
|
||||||
create_index_row_permission_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "row-writer"},
|
|
||||||
json={"sql": "create index idx_cats_name on cats(name)"},
|
|
||||||
)
|
|
||||||
drop_index_allowed_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "alterer"},
|
|
||||||
json={"sql": "drop index idx_dogs_name"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert create_index_allowed_response.status_code == 200
|
|
||||||
assert create_index_row_permission_response.status_code == 403
|
|
||||||
assert create_index_row_permission_response.json()["errors"] == [
|
|
||||||
"Permission denied: need alter-table on data/cats"
|
|
||||||
]
|
|
||||||
assert drop_index_allowed_response.status_code == 200
|
|
||||||
|
|
||||||
drop_allowed_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "dropper"},
|
|
||||||
json={"sql": "drop table dogs"},
|
|
||||||
)
|
|
||||||
drop_row_permission_response = await ds.client.post(
|
|
||||||
"/data/-/execute-write",
|
|
||||||
actor={"id": "row-writer"},
|
|
||||||
json={"sql": "drop table cats"},
|
|
||||||
)
|
|
||||||
|
|
||||||
assert drop_allowed_response.status_code == 200
|
|
||||||
assert not await db.table_exists("dogs")
|
|
||||||
assert drop_row_permission_response.status_code == 403
|
|
||||||
assert drop_row_permission_response.json()["errors"] == [
|
|
||||||
"Permission denied: need drop-table on data/cats"
|
|
||||||
]
|
|
||||||
assert await db.table_exists("cats")
|
|
||||||
|
|
||||||
|
|
||||||
@pytest.mark.asyncio
|
@pytest.mark.asyncio
|
||||||
async def test_execute_write_insert_links_to_inserted_row():
|
async def test_execute_write_insert_links_to_inserted_row():
|
||||||
ds = Datasette(memory=True, default_deny=True)
|
ds = Datasette(memory=True, default_deny=True)
|
||||||
|
|
@ -2546,8 +1829,6 @@ async def test_user_writable_query_execution_rechecks_table_permissions():
|
||||||
"dogs": {
|
"dogs": {
|
||||||
"permissions": {
|
"permissions": {
|
||||||
"insert-row": {"id": "alice"},
|
"insert-row": {"id": "alice"},
|
||||||
"update-row": {"id": "alice"},
|
|
||||||
"delete-row": {"id": "alice"},
|
|
||||||
}
|
}
|
||||||
}
|
}
|
||||||
},
|
},
|
||||||
|
|
|
||||||
|
|
@ -26,20 +26,17 @@ def conn():
|
||||||
conn.close()
|
conn.close()
|
||||||
|
|
||||||
|
|
||||||
def table_operation_tuples(analysis):
|
def as_tuples(analysis):
|
||||||
return [
|
return [
|
||||||
(
|
(
|
||||||
operation.operation,
|
access.operation,
|
||||||
operation.database,
|
access.database,
|
||||||
operation.sqlite_schema,
|
access.sqlite_schema,
|
||||||
operation.table,
|
access.table,
|
||||||
operation.columns,
|
access.columns,
|
||||||
operation.source,
|
access.source,
|
||||||
)
|
)
|
||||||
for operation in analysis.operations
|
for access in analysis.table_accesses
|
||||||
if operation.target_type == "table"
|
|
||||||
and operation.operation in {"read", "insert", "update", "delete"}
|
|
||||||
and not operation.internal
|
|
||||||
]
|
]
|
||||||
|
|
||||||
|
|
||||||
|
|
@ -51,7 +48,7 @@ def test_analyze_select_tables(conn):
|
||||||
database_name="data",
|
database_name="data",
|
||||||
)
|
)
|
||||||
|
|
||||||
assert set(table_operation_tuples(analysis)) == {
|
assert set(as_tuples(analysis)) == {
|
||||||
("read", "data", "main", "cats", ("id", "name"), None),
|
("read", "data", "main", "cats", ("id", "name"), None),
|
||||||
("read", "data", "main", "dogs", ("age", "id", "name"), None),
|
("read", "data", "main", "dogs", ("age", "id", "name"), None),
|
||||||
}
|
}
|
||||||
|
|
@ -60,231 +57,11 @@ def test_analyze_select_tables(conn):
|
||||||
def test_analyze_uses_sqlite_schema_as_default_database(conn):
|
def test_analyze_uses_sqlite_schema_as_default_database(conn):
|
||||||
analysis = analyze_sql_tables(conn, "select name from dogs")
|
analysis = analyze_sql_tables(conn, "select name from dogs")
|
||||||
|
|
||||||
assert set(table_operation_tuples(analysis)) == {
|
assert set(as_tuples(analysis)) == {
|
||||||
("read", "main", "main", "dogs", ("name",), None),
|
("read", "main", "main", "dogs", ("name",), None),
|
||||||
}
|
}
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_user_schema_table_read_is_not_internal(conn):
|
|
||||||
analysis = analyze_sql_tables(
|
|
||||||
conn,
|
|
||||||
"insert into log select sql from sqlite_master where name = 'dogs'",
|
|
||||||
database_name="data",
|
|
||||||
)
|
|
||||||
|
|
||||||
assert {
|
|
||||||
"operation": "read",
|
|
||||||
"target_type": "schema",
|
|
||||||
"database": "data",
|
|
||||||
"sqlite_schema": "main",
|
|
||||||
"table": None,
|
|
||||||
"target": "sqlite_master",
|
|
||||||
"columns": ("name", "sql"),
|
|
||||||
"source": None,
|
|
||||||
"internal": False,
|
|
||||||
} in [operation_dict(operation) for operation in analysis.operations]
|
|
||||||
|
|
||||||
|
|
||||||
def operation_dict(operation):
|
|
||||||
return {
|
|
||||||
"operation": operation.operation,
|
|
||||||
"target_type": operation.target_type,
|
|
||||||
"database": operation.database,
|
|
||||||
"sqlite_schema": operation.sqlite_schema,
|
|
||||||
"table": operation.table,
|
|
||||||
"target": operation.target,
|
|
||||||
"columns": operation.columns,
|
|
||||||
"source": operation.source,
|
|
||||||
"internal": operation.internal,
|
|
||||||
}
|
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_create_table_operation():
|
|
||||||
conn = sqlite3.connect(":memory:")
|
|
||||||
try:
|
|
||||||
analysis = analyze_sql_tables(
|
|
||||||
conn,
|
|
||||||
"create table foobar (id integer primary key, name text)",
|
|
||||||
database_name="data",
|
|
||||||
)
|
|
||||||
finally:
|
|
||||||
conn.close()
|
|
||||||
|
|
||||||
assert {
|
|
||||||
"operation": "create",
|
|
||||||
"target_type": "table",
|
|
||||||
"database": "data",
|
|
||||||
"sqlite_schema": "main",
|
|
||||||
"table": "foobar",
|
|
||||||
"target": "foobar",
|
|
||||||
"columns": (),
|
|
||||||
"source": None,
|
|
||||||
"internal": False,
|
|
||||||
} in [operation_dict(operation) for operation in analysis.operations]
|
|
||||||
assert not [
|
|
||||||
operation
|
|
||||||
for operation in analysis.operations
|
|
||||||
if operation.table in {"sqlite_master", "sqlite_schema"}
|
|
||||||
and not operation.internal
|
|
||||||
]
|
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_vacuum_operation():
|
|
||||||
conn = sqlite3.connect(":memory:")
|
|
||||||
try:
|
|
||||||
analysis = analyze_sql_tables(conn, "vacuum", database_name="data")
|
|
||||||
finally:
|
|
||||||
conn.close()
|
|
||||||
|
|
||||||
assert [operation_dict(operation) for operation in analysis.operations] == [
|
|
||||||
{
|
|
||||||
"operation": "vacuum",
|
|
||||||
"target_type": "database",
|
|
||||||
"database": "data",
|
|
||||||
"sqlite_schema": "main",
|
|
||||||
"table": None,
|
|
||||||
"target": "data",
|
|
||||||
"columns": (),
|
|
||||||
"source": None,
|
|
||||||
"internal": False,
|
|
||||||
}
|
|
||||||
]
|
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_statement_with_no_authorizer_callbacks_is_unknown():
|
|
||||||
conn = sqlite3.connect(":memory:")
|
|
||||||
try:
|
|
||||||
analysis = analyze_sql_tables(conn, "reindex", database_name="data")
|
|
||||||
finally:
|
|
||||||
conn.close()
|
|
||||||
|
|
||||||
assert [operation_dict(operation) for operation in analysis.operations] == [
|
|
||||||
{
|
|
||||||
"operation": "unknown",
|
|
||||||
"target_type": "statement",
|
|
||||||
"database": "data",
|
|
||||||
"sqlite_schema": None,
|
|
||||||
"table": None,
|
|
||||||
"target": None,
|
|
||||||
"columns": (),
|
|
||||||
"source": None,
|
|
||||||
"internal": False,
|
|
||||||
}
|
|
||||||
]
|
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_transaction_operation(conn):
|
|
||||||
analysis = analyze_sql_tables(conn, "commit", database_name="data")
|
|
||||||
|
|
||||||
assert [operation_dict(operation) for operation in analysis.operations] == [
|
|
||||||
{
|
|
||||||
"operation": "commit",
|
|
||||||
"target_type": "transaction",
|
|
||||||
"database": None,
|
|
||||||
"sqlite_schema": None,
|
|
||||||
"table": None,
|
|
||||||
"target": "COMMIT",
|
|
||||||
"columns": (),
|
|
||||||
"source": None,
|
|
||||||
"internal": False,
|
|
||||||
}
|
|
||||||
]
|
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_savepoint_operation(conn):
|
|
||||||
analysis = analyze_sql_tables(conn, "savepoint s", database_name="data")
|
|
||||||
|
|
||||||
assert [operation_dict(operation) for operation in analysis.operations] == [
|
|
||||||
{
|
|
||||||
"operation": "savepoint",
|
|
||||||
"target_type": "transaction",
|
|
||||||
"database": None,
|
|
||||||
"sqlite_schema": None,
|
|
||||||
"table": None,
|
|
||||||
"target": "BEGIN s",
|
|
||||||
"columns": (),
|
|
||||||
"source": None,
|
|
||||||
"internal": False,
|
|
||||||
}
|
|
||||||
]
|
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_function_operation(conn):
|
|
||||||
analysis = analyze_sql_tables(
|
|
||||||
conn,
|
|
||||||
"insert into dogs (name) values (upper(:name))",
|
|
||||||
{"name": "Cleo"},
|
|
||||||
database_name="data",
|
|
||||||
)
|
|
||||||
|
|
||||||
assert {
|
|
||||||
(
|
|
||||||
operation.operation,
|
|
||||||
operation.target_type,
|
|
||||||
operation.target,
|
|
||||||
operation.database,
|
|
||||||
operation.table,
|
|
||||||
)
|
|
||||||
for operation in analysis.operations
|
|
||||||
} == {
|
|
||||||
("insert", "table", "dogs", "data", "dogs"),
|
|
||||||
("function", "function", "upper", None, None),
|
|
||||||
("read", "table", "dogs", "data", "dogs"),
|
|
||||||
("update", "table", "cats", "data", "cats"),
|
|
||||||
("read", "table", "cats", "data", "cats"),
|
|
||||||
("insert", "table", "log", "data", "log"),
|
|
||||||
}
|
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_create_virtual_table_operation():
|
|
||||||
conn = sqlite3.connect(":memory:")
|
|
||||||
try:
|
|
||||||
analysis = analyze_sql_tables(
|
|
||||||
conn,
|
|
||||||
"create virtual table docs using fts5(body)",
|
|
||||||
database_name="data",
|
|
||||||
)
|
|
||||||
finally:
|
|
||||||
conn.close()
|
|
||||||
|
|
||||||
assert {
|
|
||||||
"operation": "create",
|
|
||||||
"target_type": "virtual-table",
|
|
||||||
"database": "data",
|
|
||||||
"sqlite_schema": "main",
|
|
||||||
"table": "docs",
|
|
||||||
"target": "docs",
|
|
||||||
"columns": (),
|
|
||||||
"source": None,
|
|
||||||
"internal": False,
|
|
||||||
} in [operation_dict(operation) for operation in analysis.operations]
|
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_create_table_as_select_function_is_not_internal():
|
|
||||||
conn = sqlite3.connect(":memory:")
|
|
||||||
try:
|
|
||||||
conn.execute("create table secret(value text)")
|
|
||||||
analysis = analyze_sql_tables(
|
|
||||||
conn,
|
|
||||||
"create table copied as select substr(value, 1, 1) from secret",
|
|
||||||
database_name="data",
|
|
||||||
)
|
|
||||||
finally:
|
|
||||||
conn.close()
|
|
||||||
|
|
||||||
assert {
|
|
||||||
"operation": "function",
|
|
||||||
"target_type": "function",
|
|
||||||
"database": None,
|
|
||||||
"sqlite_schema": None,
|
|
||||||
"table": None,
|
|
||||||
"target": "substr",
|
|
||||||
"columns": (),
|
|
||||||
"source": None,
|
|
||||||
"internal": False,
|
|
||||||
} in [operation_dict(operation) for operation in analysis.operations]
|
|
||||||
|
|
||||||
|
|
||||||
def test_analyze_insert_tables(conn):
|
def test_analyze_insert_tables(conn):
|
||||||
analysis = analyze_sql_tables(
|
analysis = analyze_sql_tables(
|
||||||
conn,
|
conn,
|
||||||
|
|
@ -293,7 +70,7 @@ def test_analyze_insert_tables(conn):
|
||||||
database_name="data",
|
database_name="data",
|
||||||
)
|
)
|
||||||
|
|
||||||
assert set(table_operation_tuples(analysis)) == {
|
assert set(as_tuples(analysis)) == {
|
||||||
("insert", "data", "main", "dogs", (), None),
|
("insert", "data", "main", "dogs", (), None),
|
||||||
("read", "data", "main", "dogs", ("id", "name"), "dogs_after_insert"),
|
("read", "data", "main", "dogs", ("id", "name"), "dogs_after_insert"),
|
||||||
("update", "data", "main", "cats", ("name",), "dogs_after_insert"),
|
("update", "data", "main", "cats", ("name",), "dogs_after_insert"),
|
||||||
|
|
@ -310,7 +87,7 @@ def test_analyze_update_tables(conn):
|
||||||
database_name="data",
|
database_name="data",
|
||||||
)
|
)
|
||||||
|
|
||||||
assert set(table_operation_tuples(analysis)) == {
|
assert set(as_tuples(analysis)) == {
|
||||||
("update", "data", "main", "dogs", ("age",), None),
|
("update", "data", "main", "dogs", ("age",), None),
|
||||||
("read", "data", "main", "dogs", ("age", "name"), None),
|
("read", "data", "main", "dogs", ("age", "name"), None),
|
||||||
}
|
}
|
||||||
|
|
@ -324,7 +101,7 @@ def test_analyze_delete_tables(conn):
|
||||||
database_name="data",
|
database_name="data",
|
||||||
)
|
)
|
||||||
|
|
||||||
assert set(table_operation_tuples(analysis)) == {
|
assert set(as_tuples(analysis)) == {
|
||||||
("delete", "data", "main", "dogs", (), None),
|
("delete", "data", "main", "dogs", (), None),
|
||||||
("read", "data", "main", "dogs", ("name",), None),
|
("read", "data", "main", "dogs", ("name",), None),
|
||||||
}
|
}
|
||||||
|
|
@ -344,7 +121,7 @@ def test_analyze_insert_select_with_cte(conn):
|
||||||
database_name="data",
|
database_name="data",
|
||||||
)
|
)
|
||||||
|
|
||||||
assert set(table_operation_tuples(analysis)) == {
|
assert set(as_tuples(analysis)) == {
|
||||||
("insert", "data", "main", "cats", (), None),
|
("insert", "data", "main", "cats", (), None),
|
||||||
("read", "data", "main", "dogs", ("age", "name"), "old_dogs"),
|
("read", "data", "main", "dogs", ("age", "name"), "old_dogs"),
|
||||||
}
|
}
|
||||||
|
|
@ -358,7 +135,7 @@ def test_analyze_view_with_instead_of_trigger(conn):
|
||||||
database_name="data",
|
database_name="data",
|
||||||
)
|
)
|
||||||
|
|
||||||
assert set(table_operation_tuples(analysis)) == {
|
assert set(as_tuples(analysis)) == {
|
||||||
("update", "data", "main", "dog_names", ("name",), None),
|
("update", "data", "main", "dog_names", ("name",), None),
|
||||||
("read", "data", "main", "dogs", ("id", "name"), "dog_names"),
|
("read", "data", "main", "dogs", ("id", "name"), "dog_names"),
|
||||||
("read", "data", "main", "dog_names", ("id", "name"), "dog_names"),
|
("read", "data", "main", "dog_names", ("id", "name"), "dog_names"),
|
||||||
|
|
@ -386,7 +163,7 @@ def test_analyze_attached_database_tables(conn):
|
||||||
schema_to_database={"extra": "extra_db"},
|
schema_to_database={"extra": "extra_db"},
|
||||||
)
|
)
|
||||||
|
|
||||||
assert set(table_operation_tuples(analysis)) == {
|
assert set(as_tuples(analysis)) == {
|
||||||
("insert", "extra_db", "extra", "people", (), None),
|
("insert", "extra_db", "extra", "people", (), None),
|
||||||
("read", "data", "main", "dogs", ("name",), None),
|
("read", "data", "main", "dogs", ("name",), None),
|
||||||
}
|
}
|
||||||
|
|
|
||||||
Loading…
Add table
Add a link
Reference in a new issue