On Wed, Jan 11, 2023 at 4:00 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > If you want to make safe a SECURITY DEFINER function written using sql > > or plpgsql, you either have to schema-qualify every single reference > > or, more realistically, attach a SET clause to the function to set the > > search_path to a sane value during the time that the function is > > executing. The problem here can be handled the same way, except that > > it's needed in a vastly more limited set of circumstances: you have to > > be calling a SECURITY DEFINER function that will execute CREATE ROLE > > as a non-superuser (and that user then needs to be sensitive to the > > value of this GUC in some security-relevant way). It might be good to > > document this -- I just noticed that the CREATE FUNCTION page has a > > section on "Writing SECURITY DEFINER Functions Safely" which talks > > about dealing with the search_path issues, and it seems like it would > > be worth adding a sentence or two there to talk about this. > > OK, I'd be satisfied with that.
OK, I'll draft a patch tomorrow.
Justed wanted to chime in and say Robert has eloquently put into words much of what I have been thinking here, and that I concur that guiding the DBA to use care with the power they have been provided is a sane position to take.
