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

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 think we'd likely have it
> already if Jelte hadn't switched employers, but oh well.

That's definitely possible, but I'm not so sure. For now I'm trying to
focus on other/smaller things in the community that allow for more
immediate impact.

> I wouldn't oppose a command that does an absolutely irrevocable SET
> ROLE -- i.e. once you execute it, it is as if you logged in as the
> target role originally, and the only way to get your privileges back
> is a new connection.

Agreed, that seems fine to me.

> I am extremely skeptical of something like SET ROLE WITH <password>.

Totally agreed on the security concerns here. We don't want to provide
passwords in a SQL command. For the same reasons explained by Robert,
we also tell people not to set user passwords using SQL, but to use
the \password command instead which generates hashes client side.

I think there's a fairly easy safe alternative though, let's call this
option e):
Instead of letting the client/user provide a secret, the server could
generate it:

SET ROLE jelte WITH GUARD;
-> returns a single row with a 'random-token-abc'
RESET ROLE WITH TOKEN 'random-token-abc';

Such an approach would be totally usable by connection poolers to
multiplex different users on the same connection. All they'd have to
do is run the SET ROLE ... WITH GUARD command and save the token. Then
before it transfers the connection to another role it would need to
run:

DISCARD ALL; -- to clear any scary session state
RESET ROLE WITH TOKEN 'the-token';
SET ROLE some_other_user WITH GUARD;

A full response on all the options proposed so far:
a): This would not be usable by transaction poolers. Because the
design seems to be based on a common misunderstanding of how
transaction pooling works. PgBouncer does not parse the COMMIT, it
only knows that a transaction is finished because postgres will tell
it. Since poolers allow pipelining of messages for performance
reasons, a client can trivially bypass this by quickly sending another
command after the COMMIT message.
b) Fine, details explained above.
c) Very scary security wise, explained above.
d) Even scarier than c, now actual user passwords will start to end up
in logs, not just session local passwords. I don't think there's a
safe way to do this without extending the protocol. We don't want
users to send their plaintext passwords to Postgres, that's why we
have SCRAM auth. If we want something like this, we'd want to allow
users to re-trigger SCRAM authentication. Which clearly requires a
protocol change.

P.S. If we're going to move forward in this direction, then SET
SESSION AUTHORIZATION should have the same functionality. Poolers
probably would want to lock that instead of ROLE, that way users can
still use SET ROLE to change the role that the current SESSION
AUTHORIZATION is allowed to change to.