Re: allowing for control over SET ROLE - Mailing list pgsql-hackers

On Tue, Jan 10, 2023 at 2:28 AM Jeff Davis <pgsql@j-davis.com> wrote:
> The risks of SECURITY INVOKER are more serious. It inherently means
> that one user is writing code, and another is executing it. And in the
> SQL world of triggers, views, expression indexes and logical
> replication, the invoker often doesn't know what they are invoking.
> There are search path risks, risks associated with resolving the right
> function/operator/cast, risks of concurrent DDL (i.e. changing a
> function definition right before a superuser executes it), etc. It
> severely limits the kinds of trust models you can use in logical
> replication. And SECURITY INVOKER weirdly inverts the trust
> relationship of a GRANT: if A grants to B, then B must *completely*
> trust A in order to exercise that new privilege because A can inject
> arbitrary SECURITY INVOKER code in front of the object.

Yes. I think it's extremely difficult to operate a PostgreSQL database
with mutually untrusting users. If the high-privilege users don't
trust the regular users, they must also make very little use of the
database and only in carefully circumscribed ways. If not, the whole
security model unravels really fast. It would certainly be nice to do
better here.

> UNIX basically operates on a SECURITY INVOKER model, so I guess that
> means that it can work. But then again, grepping a file doesn't execute
> arbitrary code from inside that file (though there are bugs
> sometimes... see [1]). It just seems like the wrong model for SQL.

I often think about the UNIX model to better understand the problems
we have in PostgreSQL. I don't think that there's any real theoretical
difference between the cases, but there are practical differences
nearly all of which are unfavorable to PostgreSQL. For example, when
you log into your UNIX account, you have a home directory which is
pre-created. Your path is likely configured to contain only root-owned
directories and perhaps directories within your home directory that
are controlled by you, and the permissions on the root-owned
directories are locked down tight. That's because people figured out
in the 1970s and 1980s that if other people could write executable
code into a path you were likely to search, your account was probably
going to get compromised.

Now, in PostgreSQL, the equivalent of a home directory is a user
schema. We set things up to search those by default if they exist, but
we don't create them by default. We also put the public schema in the
default search path and, up until very recently, it was writeable by
default. In practice, many users probably put back write permission on
that schema, partly because if they don't, unprivileged users can't
create database objects anywhere at all. The practical effect of this
is that, when you log into a UNIX system, you're strongly encouraged
to access only things that are owned by you or root, and any new stuff
you create will be in a location where nobody but you is likely to
touch it. On the other hand, when you log into a PostgreSQL system,
you're set up by default to access objects created by other
unprivileged users and you may have nowhere to put your own objects
where those users won't also be accessing your stuff.

So the risks, which in theory are all very similar, are in practice
far greater in the PostgreSQL context, basically because our default
setup is about 40 years behind the times in terms of implementing best
practices. At least we've locked down write permission on pg_catalog.
I think some early UNIX systems didn't even do that, or not well. But
that's about the end of the good things that I have to say about what
we're doing in this area.

To be fair, I think many security people also consider it wise to
assume that a local unprivileged UNIX user can probably find a way to
escalate to root. There are a lot of setuid binaries on a
normally-configured UNIX system, and you only need to find one of them
that has an exploitable vulnerability. Those are the equivalent of
SECURITY DEFINER privileges, and I don't think we ship any of those in
a default configuration. In that regard, we're perhaps better-secured
than UNIX. Unfortunately, I think it is probably still wise to assume
that an unprivileged PostgreSQL user can find some way of getting
superuser if they want -- not only because of Trojan horse attacks
based on leaving security-invoker functions or procedures or operators
lying around, but also because I strongly suspect there are more
escalate-to-superuser bugs in the code than we've found yet. Those
we've not found, or have found but have not fixed, may still be known
to bad actors.

> [ Aside: that probably explains why the SQL spec defaults to SECURITY
> DEFINER. ]

I doubt that SECURITY DEFINER is safer in general than SECURITY
INVOKER. That'd be the equivalent of having binaries installed setuid
by default, which would be insane. I think it is right to regard
SECURITY DEFINER as the bigger threat by far. The reason it doesn't
always seem that way with PostgreSQL, at least in my view, is because
we make it so atrociously easy to accidentally invoke executable code
somewhere. If you start by assuming that you're probably going to
execute some random other user's code by accident, well then in that
world yes you would prefer to at least have it be running as them, not
you. But that's not really safe anyway. Sure, if the code runs as
them, they can't so easily usurp your privileges, but they can still
log everything you do, or make it fail, or make it take forever. Those
things are less serious than outright account takeover, but nobody
stands up a web site and hopes that it only gets DDOS'd rather than
vandalized. What you want is for it to stay up.

> Brainstorming, I think we can do more to mitigate the risks of SECURITY
> INVOKER:
>
> * If running a command that would invoke a SECURITY INVOKER function
> that is not owned by superuser or a member of the invoker's role, throw
> an error instead. We could control this with a GUC for compatibility.
>
> * Have SECURITY PUBLIC which executes with minimal privileges, which
> would be good for convenience functions that might be used in an index
> expression or view.
>
> * Another idea is to separate out read privileges -- a SECURITY INVOKER
> that is read-only is sounds less dangerous (though not without some
> risk).
>
> * Prevent extension scripts from running SECURITY INVOKER functions.

It might be best to repost some of these ideas on a new thread with a
relevant subject line, but I agree that there's some potential here.
Your first idea reminds me a lot of the proposal Tom made in
https://www.postgresql.org/message-id/19327.1533748538@sss.pgh.pa.us
-- except that his mechanism is more general, since you can say whose
code you trust and whose code you don't trust. Noah had a competing
version of that patch, too. But we never settled on an approach. I
still think something like this would be a good idea, and the fact
that you've apparently-independently come up with a similar notion
just reinforces that.

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