Re: Non-superuser subscription owners

On Mon, Feb 27, 2023 at 7:37 PM Jeff Davis <pgsql@j-davis.com> wrote:
> > Yeah. That's the idea I was floating, at least.
>
> Isn't that a hard problem; maybe impossible?

It doesn't seem that hard to me; maybe I'm missing something.

The existing SECURITY_RESTRICTED_OPERATION flag basically prevents you
from tinkering with the session state. 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 owned by a user that they don't trust
completely, we'd set
SECURITY_RESTRICTED_OPERATION|DATABASE_READS_PROHIBITED|DATABASE_WRITES_PROHIBITED.
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 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.

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