On Mon, Jul 31, 2023 at 6:10 PM Jeff Davis <pg...@j-davis.com> wrote: > Capturing the environment is not ideal either, in my opinion. It makes > it easy to carelessly depend on a schema that others might not have > USAGE privileges on, which would then create a runtime problem for > other callers. Also, I don't think we could just depend on the raw > search_path, we'd need to do some processing for $user, and there are > probably a few other annoyances. > > It's one possibility and we don't have a lot of great options, so I > don't want to rule it out though. If nothing else it could be a > transition path to something better.
Here is my thought about this. Right now, we basically do one of two things. In some cases, we parse statements when they're submitted, and then store the resulting node trees. In such cases, references are fixed: the statements will always refer to the objects to which they referred previously. In functions and procedures, except for the new BEGIN ATOMIC stuff, we just store the statements as a string and they get parsed at execution time. Then, the problem arises of statements being possibly parsed in an environment that differs from the original one. It can differ either by search_path being different so that we look in different schemas, or, as you point out here, if the contents of the schemas themselves have been modified. I think that a lot of people would like it if we moved more in the direction of parsing statements at object definition time. Possibly because EDB deals with a lot of people coming from Oracle, I've heard a lot of complaints about the PG behavior. However, there are some fairly significant problems with that idea. First, it would break some use cases, such as creating a temporary table and then running DML commands on it, or more generally any use case where a function or procedure might need to reference objects that don't exist at time of definition. Second, while we have clear separation of parsing and execution for queries, the same is not true for DDL commands; it's not the case, I believe, that you can parse an arbitrary DDL command such that all object references are resolved, and then later execute it. We'd need to change a bunch of code to get there. Third, we'd have to deal with dependency loops: right now, because functions and procedures don't parse their bodies at definition time, they also don't depend on the objects that they are going to end up accessing, which means that a function or procedure can be restored by pg_dump without worrying about whether those objects exist yet. That would have to change, and that would mean creating dependency loops for pg_dump, which we'd have to then find a way to break. I'm not trying to say that any of these problems are intractable, but I do think changing stuff like this would be quite a bit of work -- and that's assuming the user impact was judged to be acceptable, which I'm not at all sure that it would be. We'd certainly need to provide some workaround for people who want to do stuff like create and use temporary tables inside of a function or procedure. Now, if we don't go in the direction of resolving everything at parse time, then I think capturing search_path is probably the next best thing, or at least the next best thing that I've thought up so far. It doesn't hold constant the meaning of the code to the same degree that parsing at definition time would do, but it gets us closer to that than the status quo. Crucially, if the user is using a secure search_path, then any changes to the meaning of the code that captures that search_path will have to be made by that user or someone with a superset of their privileges, which is a lot less serious than what you get when there's no search_path setting at all, where the *caller* can change the meaning of the called code. That is not, however, to say that this idea is really good enough. To be honest, I think it's a bit of a kludge, and dropping a kludge on top of our entire user base and maybe also breaking a lot of things is not particularly where I want to be. I just don't have an idea I like better at the moment. -- Robert Haas EDB: http://www.enterprisedb.com
