Jelte Fennema-Nio:
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.
Right, I should have clarified: My proposal wasn't mean to be taken
literally as an SQL command. Passwords should not be sent as plain text,
no question. This needs to happen on the protocol level.
What my proposal is about is this: All other suggestions just seem to
fight the symptoms of an underlying problem. Which is, that connection
poolers / PostgREST need to run with a very high privileged role,
because they need to be able to switch into all possible roles that
could come in via that connection.
Of course, authentication should still happen - but it doesn't happen
with PostgreSQL anymore. It has to be implemented in the application
layer / pooler. That kind of defeats some of the purpose of using the
PostgreSQL role system for users' roles.
I don't want to give any privileges to the connection pooler /
application and I don't want to outsource authentication.
Once the role to connect with is unprivileged, all the other problems
become obsolete. RESET ROLE is just fine - you can't win anything.
If we want something like this, we'd want to allow
users to re-trigger SCRAM authentication. Which clearly requires a
protocol change.
Yes. This. Re-authenticating without re-connecting.
I'd hope that this would also work around problems building up the role
cache when doing SET ROLE with a lot of granted roles [1].
Best,
Wolfgang
[1]:
https://www.postgresql.org/message-id/7d32e088-34a7-421a-9398-80958acb3f64%40technowledgy.de
