Re: Non-superuser subscription owners

On Tue, 2023-02-28 at 08:37 -0500, Robert Haas wrote:
> The existing SECURITY_RESTRICTED_OPERATION flag basically prevents
> you
> from tinkering with the session state.

Currently, every time we set that flag we also run all the code as the
table owner.

You're suggesting using the SECURITY_RESTRICTED_OPERATION flag, along
with the new security flags, but not switch to the table owner, right?

> If we also had a similar flags
> like DATABASE_READS_PROHIBITED and DATABASE_WRITES_PROHIBITED (or
> just
> a combined DATABASE_ACCESS_PROHIBITED flag) I think that would be
> pretty close to what we need. The idea would be that, when a user
> executes a function or procedure 

Or default expressions, I presume. If we at least agree on this point,
then I think we should try to find a way to treat these other hunks of
code in a secure way (which I think is what Andres was suggesting).

> owned by a user that they don't trust
> completely, we'd set
> SECURITY_RESTRICTED_OPERATION|DATABASE_READS_PROHIBITED|DATABASE_WRIT
> ES_PROHIBITED.

It seems like you're saying to basically just keep the user ID the
same, and maybe keep USAGE privileges, but not be able to do anything
else? Might be useful. Kind of like running it as a nobody user but
without the problems you mentioned. Some details to think about, I'm
sure.

> And we could provide a user with a way to express the degree of trust
> they have in some other user or perhaps even some specific function,
> e.g.
>
> SET trusted_roles='alice:read';
>
> ...could mean that I trust alice to read from the database with my
> permissions, should I happen to run code provided by her in SECURITY
> INVOKER modacke.

I'm not very excited about inventing a new privilege language inside a
GUC, but perhaps a simpler form could be a reasonable mitigation (or at
least a starting place).

> I'm sure there's some details to sort out here, e.g. around security
> related to the trusted_roles GUC itself. But I don't really see a
> fundamental problem. We can invent arbitrary flags that prohibit
> classes of operations that are of concern, set them by default in
> cases where concern is justified, and then give users who want the
> current behavior some kind of escape hatch that causes those flags to
> not get set after all. Not only does such a solution not seem
> impossible, I can possibly even imagine back-patching it, depending
> on
> exactly what the shape of the final solution is, how important we
> think it is to get a fix out there, and how brave I'm feeling that
> day.

Unless the trusted roles defaults to '*', then I think it will still
break some things.

One of my key tests for user-facing proposals is whether the
documentation will be reasonable or not. Most of these proposals to
make SECURITY INVOKER less bad fail that test.

Each of these ideas and sub-ideas affect the semantics, and should be
documented. But how do we document that some code runs as you, some as
the person who wrote it, sometimes we obey SECURITY INVOKER and
sometimes we ignore it and use DEFINER semantics, some code is outside
a function and always executes as the invoker, some code has some
security flags, and some code has more security flags, code can change
between the time you look at it and the time it runs, and it's all
filtered through GUCs with their own privilege sub-language?

OK, let's assume that we have all of that documented, then how do we
guide users on what reasonable best practices are for the GUC settings,
etc.? Or do we just say "this is mechanically how all these parts work,
good luck assembling it into a secure system!". [ Note: I feel like
this is the state we are in now. Even if technically we don't have live
security bugs that I'm aware of, we are setting users up for security
problems. ]

On the other hand, if we focus on executing code as the user who wrote
it in most places, then the documentation will be something like: "you
defined the table, you wrote the code, it runs as you, here are some
best practices for writing secure code". And we have some different
documentation for writing a cool SECURITY INVOKER function and how to
get other users to trust you enough to run it. That sounds a LOT more
understandable for users.

Regards,
Jeff Davis