Re: Proposal: Role Sandboxing for Secure Impersonation - Mailing list pgsql-hackers

On 12/5/24 16:12, Robert Haas wrote:
> On Wed, Dec 4, 2024 at 5:54 PM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
>> On Wed, 4 Dec 2024 at 22:05, Robert Haas <robertmhaas@gmail.com> wrote:
>>> I do think the protocol change is better.
>>
>> Fully agreed. But I now also know the herculean amount of effort
>> that's necessary for that to happen, and I personally don't have the
>> bandwidth to push that through anymore. So I'm definitely open to a
>> SQL way if there's a safe way to achieve that.
> 
> I'm not convinced it has to be a Herculean amount of effort. Why do
> you think otherwise?
> 

I'm also interested in why would this be a Herculean effort.

I know next to nothing about the protocol code, but I'd imagine the
amount of new code to handle this new operation (change role + return
reset token) would be relatively modest. I assume the complexity would
come from having to do this in a backwards-compatible way, right?

Could the recent protocol versioning improvements help with this? Or am
I missing some fundamental issue here?


In any case, I think it'd be very useful to have some sort of protected
SET ROLE command, safe for use cases like connection poolers. Be it a
new protocol message, or the SET ROLE variant returning a token. It's a
common pattern we recommend, and a fair fraction of users is unaware of
this weakness.

I agree handling this at the protocol level seems better, but if that
not happened yet. And "something" beats "nothing".



Now, let me go off on a tangent for a bit ...

I started looking at SET ROLE not because of connection pooling, but
because of RLS (row-level security). Which does not really need to be
tied to roles, but the canonical way to write policies is to reference
current_user - just look at [1]).

So in a way, RLS is also affected by this, because if an application
relies on RLS for data protection, but gets connections from a pool that
user SET ROLE, it's vulnerable to the same RESET ROLE thing. A fair
number of developers do not realize this.

But even if they were aware of it, what could they do about it? Stop
using the connection pool, or use something like set_user(). Not always
possible / practical.

But I also realized they often don't *want* to use roles. Managing a
huge number of roles (one for each user) is no fun, really. They'd often
choose something like TENANTID, or something like that. But roles are
the only "trusted" context they have access to, so they use that (and
map it to the features they actually need).

ACAIFS there's no good way to define stuff the policies could rely on.
GUCs access control is quite crude, and it's hard to ensure users can't
change a GUC to something arbitrary (a bit like the RESET ROLE thing).

But what if we provided a way to define such trusted context?

Imagine a context that could be "installed" in a controlled way, either
by the user or by the connection pool. But it could not be modified (at
any point), only "reset". I suspected this might be doable by digital
signing, so I gave it a try and I wrote signed_context [2].

The basic idea is that the user is handed a "context", which is pretty
much just a string encoding key/value pairs. And it's signed by a key
unknown to the user (or to the DB), so that the user can't modify it.
But the public key is known, so the DB can verify that the context is
valid when "setting" it for the session.

It's backed by a GUC, so the user can do:

  SET signed_context.context = '... signed context ...';

and magic happens, and the context is defined. The connection pool could
do this on behalf of the user, and the user wouldn't even know the
signed value. The RLS policies then can call signed_context_ge('X') to
lookup keys in the context, etc.


The reason why I'm mentioning it here is that it seems to face some of
the same challenges mentioned in this thread for the SET ROLE changes.

For example, the signed context is a bit of a sensitive value - in the
simplest form it's a bit like a password. If I learn your token (e.g.
because it leaked into server log), I can impersonate you. There are
ways to mitigate this (could be tied to session, allows to be used only
once, have built-in expiration, ...).


But it occurred to me maybe this is something we could handle at the
protocol level too. Possibly by making the SET ROLE protocol "thing"
generic enough to cover this kind of use case too.

The other thing is that out-of-core extensions are not great for managed
systems (e.g. set_user is nice, but how many systems can use that?).
While that's really a problem of providers of those systems, I wonder if
we should have something like this built-in.



regards

[1] https://www.postgresql.org/docs/current/ddl-rowsecurity.html

[2] https://github.com/tvondra/signed_context

-- 
Tomas Vondra