Spaces:
Running
Running
Download tools/flyway-team/sql_policy.py from taskpilot952476/taskpilot: direct link, hf CLI and curl.
- Browser
- Download file 6.06 kB
-
https://huggingface.co/spaces/taskpilot952476/taskpilot/resolve/main/tools/flyway-team/sql_policy.py
- Command line
-
hf download hf://spaces/taskpilot952476/taskpilot/tools/flyway-team/sql_policy.py
-
curl -L -o sql_policy.py https://huggingface.co/spaces/taskpilot952476/taskpilot/resolve/main/tools/flyway-team/sql_policy.py
6.06 kB
| """Fail-closed PostgreSQL 17 AST policy for NEW migrations, not a SQL sandbox. | |
| Accepted history is immutable and is never rewritten to satisfy this policy. | |
| Procedural/dynamic SQL and unrecognised statement forms need a policy review, | |
| not a comment, filename exemption, or regex-based bypass. | |
| """ | |
| class SqlPolicyError(ValueError): | |
| pass | |
| RELATION_TARGETS = { | |
| "CreateStmt": "relation", "IndexStmt": "relation", "AlterTableStmt": "relation", | |
| "CreateSeqStmt": "sequence", "AlterSeqStmt": "sequence", "ViewStmt": "view", | |
| "InsertStmt": "relation", "UpdateStmt": "relation", "DeleteStmt": "relation", | |
| } | |
| SUPPORTED = set(RELATION_TARGETS) | { | |
| "CreateEnumStmt", "AlterEnumStmt", "CreateTableAsStmt", "DropStmt", | |
| "CommentStmt", "SelectStmt", "VariableSetStmt", | |
| } | |
| FUNCTIONS = { | |
| "lower", "upper", "btrim", "ltrim", "rtrim", "length", "char_length", | |
| "replace", "concat", "concat_ws", "now", "to_char", "to_timestamp", | |
| "abs", "round", "ceil", "floor", "greatest", "least", "count", "sum", | |
| "min", "max", "avg", "jsonb_build_object", "jsonb_build_array", | |
| } | |
| def validate(sql): | |
| try: | |
| from pglast import ast, parse_sql | |
| from pglast.enums import ObjectType | |
| except ImportError: | |
| raise SqlPolicyError("install tools/flyway-team/requirements.txt (PostgreSQL 17 parser required)") from None | |
| try: | |
| statements = parse_sql(sql) | |
| except Exception: | |
| raise SqlPolicyError("PostgreSQL parser rejected SQL; details omitted") from None | |
| if not statements: | |
| raise SqlPolicyError("empty migration") | |
| def walk(value, visible_ctes=frozenset()): | |
| if isinstance(value, ast.Node): | |
| yield value, visible_ctes | |
| # WITH bindings belong to their query, not every nested query in | |
| # the statement. Non-recursive items see only earlier siblings. | |
| clause = getattr(value, "withClause", None) | |
| if clause is not None: | |
| current = set(visible_ctes) | |
| if clause.recursive: | |
| current.update(item.ctename for item in clause.ctes) | |
| for item in clause.ctes: | |
| yield from walk(item, frozenset(current)) | |
| current.add(item.ctename) | |
| visible_ctes = frozenset(current) | |
| for member in value: | |
| if member != "withClause": | |
| yield from walk(getattr(value, member), visible_ctes) | |
| elif isinstance(value, (tuple, list)): | |
| for child in value: | |
| yield from walk(child, visible_ctes) | |
| def name(parts, length=2): | |
| values = tuple(item.sval for item in (parts or ()) if isinstance(item, ast.String)) | |
| if len(values) != length or len(values) != len(parts or ()) or values[0] != "public": | |
| raise SqlPolicyError("object target must explicitly use public schema") | |
| def relation(value): | |
| if not isinstance(value, ast.RangeVar) or value.schemaname != "public" or value.catalogname: | |
| raise SqlPolicyError("relation target must explicitly use public schema") | |
| if value.relpersistence == "t": | |
| raise SqlPolicyError("temporary relations are outside the supported migration policy") | |
| allowed_objects = {ObjectType.OBJECT_TABLE, ObjectType.OBJECT_INDEX, ObjectType.OBJECT_SEQUENCE, | |
| ObjectType.OBJECT_TYPE, ObjectType.OBJECT_VIEW, ObjectType.OBJECT_MATVIEW} | |
| for raw in statements: | |
| statement = raw.stmt | |
| kind = type(statement).__name__ | |
| if kind not in SUPPORTED: | |
| raise SqlPolicyError("unsupported statement: " + kind + "; review policy before use") | |
| if kind in RELATION_TARGETS: | |
| relation(getattr(statement, RELATION_TARGETS[kind])) | |
| elif kind in {"CreateEnumStmt", "AlterEnumStmt"}: | |
| name(statement.typeName) | |
| elif kind == "CreateTableAsStmt": | |
| relation(statement.into.rel) | |
| elif kind == "DropStmt": | |
| if statement.removeType not in allowed_objects: | |
| raise SqlPolicyError("unsupported DROP object kind") | |
| for obj in statement.objects: | |
| name(obj) | |
| elif kind == "CommentStmt": | |
| if statement.objtype == ObjectType.OBJECT_COLUMN: | |
| name(statement.object, 3) | |
| elif statement.objtype in allowed_objects: | |
| name(statement.object) | |
| else: | |
| raise SqlPolicyError("unsupported COMMENT object kind") | |
| elif kind == "VariableSetStmt": | |
| if not statement.is_local or statement.name not in {"lock_timeout", "statement_timeout"}: | |
| raise SqlPolicyError("only SET LOCAL lock_timeout/statement_timeout is supported") | |
| for node, ctes in walk(statement): | |
| node_kind = type(node).__name__ | |
| # Nested DML in CTEs must still have an explicit target. | |
| if node_kind in RELATION_TARGETS: | |
| relation(getattr(node, RELATION_TARGETS[node_kind])) | |
| if isinstance(node, ast.RangeVar): | |
| if node.schemaname is None and node.relname in ctes: | |
| continue | |
| relation(node) | |
| elif isinstance(node, ast.IntoClause): | |
| relation(node.rel) | |
| elif isinstance(node, ast.DefElem) and node.defname == "owned_by": | |
| name(node.arg, 3) | |
| elif isinstance(node, ast.DefElem) and node.defname == "sequence_name": | |
| name(node.arg) | |
| elif isinstance(node, ast.FuncCall): | |
| names = tuple(part.sval for part in node.funcname) | |
| if names[-1] not in FUNCTIONS or (len(names) > 1 and names[:-1] != ("pg_catalog",)): | |
| raise SqlPolicyError("function outside reviewed builtin allowlist") | |
| elif node_kind in {"DoStmt", "CallStmt", "CreateFunctionStmt", "AlterObjectSchemaStmt", | |
| "ExecuteStmt", "MergeStmt"}: | |
| raise SqlPolicyError("procedural SQL/schema redirection requires explicit policy review") | |