
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
import java.util.List;
import java.util.Map;
import java.util.function.Predicate;
import static java.util.Arrays.asList;
import static org.elasticsearch.xpack.esql.expression.NamedExpressions.mergeOutputExpressions;
import static org.elasticsearch.xpack.ql.expression.Expressions.asAttributes;
import static org.elasticsearch.xpack.ql.optimizer.OptimizerRules.FoldNull;
import static org.elasticsearch.xpack.ql.optimizer.OptimizerRules.PropagateEquals;
import static org.elasticsearch.xpack.ql.optimizer.OptimizerRules.PropagateNullable;
import static org.elasticsearch.xpack.ql.optimizer.OptimizerRules.ReplaceRegexMatch;
import static org.elasticsearch.xpack.ql.optimizer.OptimizerRules.TransformDirection;
public class LogicalPlanOptimizer extends RuleExecutor<LogicalPlan> {
    public LogicalPlan optimize(LogicalPlan verified) {
        return verified.optimized() ? verified : execute(verified);
    }
    @Override
    protected List<Batch<LogicalPlan>> batches() {
        return rules();
    }
    protected static List<Batch<LogicalPlan>> rules() {
        var substitutions = new Batch<>("Substitutions", Limiter.ONCE, new SubstituteSurrogates(), new ReplaceRegexMatch());
        var operators = new Batch<>(
            "Operator Optimization",
            new CombineProjections(),
            new PruneEmptyPlans(),
            new PropagateEmptyRelation(),
            new ConvertStringToByteRef(),
            new FoldNull(),
            new SplitInWithFoldableValue(),
            new ConstantFolding(),