On Thu, 2023-09-21 at 14:06 -0400, Robert Haas wrote:
> Also, in a case like this, I don't think it's unreasonable to ask
> whether, perhaps, Bob just needs to be a little more careful about
> setting search_path.
That's what this whole thread is about: I wish it was reasonable, but I
don't think the tools we provide today make it reasonable. You expect
Bob to do something like:
CREATE FUNCTION ... SET search_path = pg_catalog, pg_temp ...
for all functions, not just SECURITY DEFINER functions, is that right?
Up until now, we've mostly treated search_path as a problem for
SECURITY DEFINER, and specifying something like that might be
reasonable for a small number of SECURITY DEFINER functions.
But as my example showed, search_path is actually a problem for
SECURITY INVOKER too: an index expression relies on the function
producing the correct results, and it's hard to control that without
controlling the search_path.
> I think that there is a big difference between
> (a) defining a SQL-language function that is accessible to multiple
> users and (b) inserting a row into a table you don't own. When you
> define a function, you know people are potentially going to call it.
It's a bit problematic that (a) is the default:
CREATE FUNCTION f(INT) RETURNS INT IMMUTABLE
LANGUAGE plpgsql
AS $$ BEGIN RETURN 42+$1; END; $$;
CREATE TABLE x(i INT);
CREATE INDEX x_idx ON x(f(i));
GRANT INSERT ON TABLE x TO u2;
It's not obvious that f() is directly callable by u2 (though it is
documented).
I'm not disagreeing with the principle behind what you say above. My
point is that "accessible to multiple users" is the ordinary default
case, so there's no cue for the user that they need to do something
special to secure function f().
> Asking you, as the function author, to take some care to secure your
> function against a malicious search_path doesn't seem like an
> unsupportable burden.
What you are suggesting has been possible for quite some time. Do you
think users are taking care to do this today? If not, how can we
encourage them to do so?
> You can, I think, be expected to
> check that functions you define have SET search_path attached.
We've already established that even postgres hackers are having
difficulty keeping up with these nuances. Even though the SET clause
has been there for a long time, our documentation on the subject is
insufficient and misleading. And on top of that, it's extra typing and
noise for every schema file. Until we make some changes I don't think
we can expect users to do as you suggest.
Regards,
Jeff Davis