On Thu, 6 Jul 2023 at 21:39, Jeff Davis <pg...@j-davis.com> wrote: I apologize in advance if anything I’ve written below is either too obvious or too crazy or misinformed to belong here. I hope I have something to say that is on point, but feel unsure what makes sense to say.
* It might break for users who have a functional index where the > function implicitly depends on a search_path containing a namespace > other than pg_catalog. My opinion is that such functional indexes are > conceptually broken and we need to desupport them, and there will be > some breakage, but I'm open to suggestion about how we minimize that (a > compatibility GUC or something?). > I agree this is OK. If somebody has an index whole meaning depends on the search_path, then the best that can be said is that their database hasn't been corrupted yet. At the same time, I can see that somebody would get upset if they couldn't upgrade their database because of this. Maybe pg_upgrade could apply "SET search_path TO pg_catalog, pg_temp" to any function used in a functional index that doesn't have a search_path setting of its own? (BEGIN ATOMIC functions count, if I understand correctly, as having a search_path setting, because the lookups happen at definition time) Now I'm doing more reading and I'm worried about SET TIME ZONE (or more precisely, its absence) and maybe some other ones. * The fix might not go far enough or might be in the wrong place. I'm > open to suggestion here, too. Maybe we can make it part of the general > function call mechanism, and can be overridden by explicitly setting > the function search path? Or maybe we need new syntax where the > function can acquire the search path from the session explicitly, but > uses a safe search path by default? > Change it so by default each function gets handled as if "SET search_path FROM CURRENT" was applied to it? That's what I do for all my functions (maybe hurting performance?). Expand on my pg_upgrade idea above by applying it to all functions? I feel that this may tie into other behaviour issues where to me it is obvious that the expected behaviour should be different from the actual behaviour. If a view calls a function, shouldn't it be called in the context of the view's definer/owner? It's weird that I can write a view that filters a table for users of the view, but as soon as the view calls functions they run in the security context of the user of the view. Are views security definers or not? Similar comment for triggers. Also as far as I can tell there is no way for a security definer function to determine who (which user) invoked it. So I can grant/deny access to run a particular function using permissions, but I can't have the supposed security definer define security for different callers. Is the fundamental problem that we now find ourselves wanting to do things that require different defaults to work smoothly? On some level I suspect we want lexical scoping, which is what most of us have in our programming languages, in the database; but the database has many elements of dynamic scoping, and changing that is both a compatibility break and requires significant changes in the way the database is designed.
