
Filing this issue to track a question/comment that was raised by @ywangd  here https://github.com/elastic/elasticsearch/pull/98309#discussion_r1294115440

One question I do have is why we chose to [execute the LookupRequest with the enrich origin](https://github.com/elastic/elasticsearch/blob/2e4440ff1c0decf79f8a40688566411f7d518aa7/x-pack/plugin/esql/src/main/java/org/elasticsearch/xpack/esql/enrich/EnrichLookupService.java#L141C2-L141C2). It means the following query works when the user has access to only the playlist index without access to either the songs enrich policy or its associated index.

```
FROM playlist | ENRICH songs ON song_id
```

It feels a bit weird to me that we have no control on who can use what enrich process. That said, we don't have a good story around enrich and ingest pipeline either, i.e. we have no control over who can use what pipeline where a pipeline can contain an enrich processor. There have been quite a few complaints from users and we have an https://github.com/elastic/elasticsearch/issues/57586 for it. I wonder whether we should not repeat the problem here with ES|QL. On the surface, it might be easier to tackle compared to ingest pipelines since ES|QL is read-only and has no "bulk" operations. What do you think?
x-pack/plugin/esql/src/main/java/org/elasticsearch/xpack/esql/optimizer/LogicalPlanOptimizer.java
@Override
        protected LogicalPlan rule(OrderBy plan) {
            var referencedAttributes = new ExpressionSet<Attribute>();
            var order = new ArrayList<Order>();
            for (Order o : plan.order()) {
                Attribute a = (Attribute) o.child();
                if (referencedAttributes.add(a)) {
                    order.add(o);
                }
            }
            return plan.order().size() == order.size() ? plan : new OrderBy(plan.source(), plan.child(), order);
        }
    }
    private static Project pushDownPastProject(UnaryPlan parent) {
        if (parent.child() instanceof Project project) {
            AttributeMap.Builder<Expression> aliasBuilder = AttributeMap.builder();
            project.forEachExpression(Alias.class, a -> aliasBuilder.put(a.toAttribute(), a.child()));
            var aliases = aliasBuilder.build();
            var expressionsWithResolvedAliases = (UnaryPlan) parent.transformExpressionsOnly(
                ReferenceAttribute.class,
                r -> aliases.resolve(r, r)
            );
            return project.replaceChild(expressionsWithResolvedAliases.replaceChild(project.child()));
        } else {
            throw new UnsupportedOperationException("Expected child to be instance of Project");
        }
    }
    static class CombineDisjunctionsToIn extends org.elasticsearch.xpack.ql.optimizer.OptimizerRules.CombineDisjunctionsToIn {