Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }

On Mon, Sep 25, 2023 at 1:56 PM Jeff Davis <pgsql@j-davis.com> wrote:
> Do users like Bob do that today? If not, what causes you to expect them
> to do so in the future?

What I would say is that if there's a reasonable way of securing your
stuff and you don't make use of it, that's your problem. If securing
your stuff is unreasonably difficult, that's a product problem. I
think that setting the search_path on your own functions is a basic
precaution that you should take if you are worried about multi-user
security. I do not believe it is realistic to eliminate that
requirement, and if people like Bob don't do that today and can't be
made to do that in the future, then I think it's just hopeless. In
contrast, I think that the precautions that you need to take when
doing anything to a table owned by another user are unreasonably
complex and not very realistic for anyone to take on a routine basis.
Even if you validate that there's nothing malicious before you access
the table, the table owner can change that at any time, so it's very
hard to reliably protect yourself.

In terms of whether people like Bob actually DO do that today, I'd say
probably some do and others don't. I think that the overwhelming
majority of PostgreSQL users simply aren't concerned about multi-user
security. They either have a single user account that is used for
everything, or say one account for the application and another for
interactive access, or they have a bunch of users but basically all of
those people are freely accessing each other's stuff and they're not
really concerned with firewalling them from each other. Among the
small percentage of users who are really concerned with making sure
that users can't get into each others accounts, I would expect that
knowing that you need to control search_path is fairly common, but
it's hard to say. I haven't actually met many such users.

> > I think it's self-evident that a SQL function's behavior is
> > not guaranteed to be invariant under all possible values of
> > search_path.
>
> It's certainly not self-evident in a literal sense. I think you mean
> that it's "obvious" or something, and perhaps that narrow question is,
> but it's also not terribly helpful.
>
> If the important behaviors here were so obvious, how did we end up in
> this mess in the first place?

I feel like this isn't really responsive to the argument that I was
and am making, and I'm worried that we're going down a rat-hole here.

I wondered after reading this whether I had misused the term
self-evident, but when I did a Google search for "self-evident" the
definition that comes up is "not needing to be demonstrated or
explained; obvious."

I am not saying that everyone is going to realize that you probably
ought to be setting search_path on all of your functions in any kind
of multi-user environment, and maybe even in a single-user environment
just to avoid weird failures if you ever change your default
search_path. What I am saying is that if you stop to think about what
search_path does while looking at any SQL function you've ever
written, you should probably realize pretty quickly that the behavior
of your function in search_path-dependent, and indeed that the
behavior of every other SQL function you've ever written is probably
search_path-dependent, too. I think the problem here isn't really that
this is hard to understand, but that many people have not stopped to
think about it.

Maybe it is obvious to you what we ought to do about that, but it is
not obvious to me. As I have said, I think that changing the behavior
of CREATE FUNCTION or CREATE PROCEDURE so that some search_path
control is the default is worth considering. However, I think that
such a change inevitably breaks backward compatibility, and I don't
think we have enough people weighing in on this thread to think that
we can just go do that even if everyone agreed on precisely what was
to be done, and I think it is pretty clear that we do not have
unanimous agreement.

> > Respectfully, I find this position unreasonable, to the point of
> > finding it difficult to take seriously.
>
> Which part exactly is unreasonable?

I interpreted you to be saying that we can't expect people to set
search_path on their functions. And I just don't buy that. We have
made mistakes in that area in PostgreSQL itself and had to fix them
later, and we may make more mistakes again in the future, so if you
think we need better documentation or better defaults, I think you
might be right. But if you think it's a crazy idea for people running
PostgreSQL in multi-user environments to understand that setting
search_path on all of their functions and procedures is essential, I
disagree. They've got to understand that, because it's not that
complicated, and there's no real alternative.

> I think analogies to unix are what caused a lot of the problems we have
> today, because postgres is a very different world. In unix-like
> environments, a file is just a file; in postgres, we have all kinds of
> code attached in interesting ways.

Yeah. That's another area where I find it very unclear how to do
better. From a security point of view, I think that the fact that
there are so many interesting places to attach code is completely
insane. It makes running a secure multi-user environment very
difficult, bordering on impossible. But is there any way we can really
fix that without just removing a whole bunch of functionality? I think
that some of the ideas that have been proposed here could help, but
I'm extremely doubtful that they or anything else are a complete
solution, and I'm pretty sure that there is no "easy button" here --
given the number of "interesting" ways to execute code, I think
security will always be tough to get right, regardless of what we
change.

My emails on this thread seem to have made you frustrated. For that, I am sorry.

--
Robert Haas
EDB: http://www.enterprisedb.com