Re: Proposal: Role Sandboxing for Secure Impersonation

On Thu, Dec 5, 2024 at 4:29 PM Jelte Fennema-Nio wrote: > When PgBouncer is in transaction mode, the server connection will only > be unlinked, when PgBouncer receives a ReadyForQuery with the "idle" > flag from the server **and** there are no messages from the client in > flight anymore. It's tot

Re: Proposal: Role Sandboxing for Secure Impersonation

On Thu, 5 Dec 2024 at 16:35, Eric Hanson wrote: > When pgbouncer is in transaction mode, the pipeline doesn't stop when > the transaction ends? Mayhaps I have the common misunderstanding. When PgBouncer is in transaction mode, the server connection will only be unlinked, when PgBouncer receives

Re: Proposal: Role Sandboxing for Secure Impersonation

On Thu, Dec 5, 2024 at 12:47 AM Wolfgang Walther wrote: > > 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. The ability to reauthenticate would be

Re: Proposal: Role Sandboxing for Secure Impersonation

On Wed, Dec 4, 2024 at 4:54 PM Jelte Fennema-Nio wrote: > > > 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.

Re: Proposal: Role Sandboxing for Secure Impersonation

On Wed, Dec 4, 2024 at 5:54 PM Jelte Fennema-Nio wrote: > On Wed, 4 Dec 2024 at 22:05, Robert Haas 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 > ban

Re: Proposal: Role Sandboxing for Secure Impersonation

On Thu, 5 Dec 2024 at 09:47, Wolfgang Walther wrote: > 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. Thanks for clarifying. > I don't want t

Re: Proposal: Role Sandboxing for Secure Impersonation

Jelte Fennema-Nio: I am extremely skeptical of something like SET ROLE WITH . 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 \pas

Re: Proposal: Role Sandboxing for Secure Impersonation

Michał Kłeczek: PostgREST does not know alice's password as it performs JWT based authentication. Yes, that's why I proposed an extension to support JWTs in the next sentence. Then PostgREST would not need to do any auth at all anymore. Best, Wolfgang

Re: Proposal: Role Sandboxing for Secure Impersonation

On Wed, 4 Dec 2024 at 22:05, Robert Haas 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

Re: Proposal: Role Sandboxing for Secure Impersonation

> On 4 Dec 2024, at 17:13, Eric Hanson wrote: > > On Mon, Dec 2, 2024 at 10:31 AM Wolfgang Walther > wrote: >> Eric Hanson: >> > a) Transaction ("local") Sandbox: >> > - SET LOCAL ROLE alice NO RESET; >> > - SET LOCAL ROLE alice WITHOUT RESET; [snip] >> > c) "Gu

Re: Proposal: Role Sandboxing for Secure Impersonation

On Wed, Dec 4, 2024 at 2:02 PM Joe Conway wrote: > However on that thread[1] Jelte and Robert expressed a preference to > accomplishing the goal via protocol changes. That is not my preference, > but it would be worth hearing from them how firm they are in their > resolve -- i.e. if we went down t

Re: Proposal: Role Sandboxing for Secure Impersonation

On 12/4/24 11:13, Eric Hanson wrote: Thanks all for the input so far.  I think we are the "usual suspects" of advocating for this feature. :) Yeah, I looked at the old thread and came to the same conclusion ;-) However on that thread[1] Jelte and Robert expressed a preference to accomplishing

Re: Proposal: Role Sandboxing for Secure Impersonation

On Mon, Dec 2, 2024 at 10:31 AM Wolfgang Walther wrote: > Eric Hanson: > > a) Transaction ("local") Sandbox: > > - SET LOCAL ROLE alice NO RESET; > > - SET LOCAL ROLE alice WITHOUT RESET; > > - BEGIN AS ROLE alice; > > > > Transaction-level sandboxes have the benefit that a pooler can simply > >

Re: Proposal: Role Sandboxing for Secure Impersonation

Eric Hanson: a) Transaction ("local") Sandbox: - SET LOCAL ROLE alice NO RESET; - SET LOCAL ROLE alice WITHOUT RESET; - BEGIN AS ROLE alice; Transaction-level sandboxes have the benefit that a pooler can simply start a new sandboxed transaction for each request and never have to worry about re

Re: Proposal: Role Sandboxing for Secure Impersonation

On 12/2/24 08:41, Eric Hanson wrote: Hi all, I'd like to revisit a previously discussed feature [1] that PostgreSQL could benefit from a "role sandbox", a feature that would build on SET [LOCAL] ROLE, and prevent or restrict RESET ROLE. Rationale:  Connection pooling is widely used to optimi

Re: Proposal: Role Sandboxing for Secure Impersonation

> On 2 Dec 2024, at 18:39, Joe Conway wrote: > > I am very much in favor of functionality of this sort being built in to the > core database. Very similar functionality is available in an extension I > wrote years ago (without the SQL grammar support) -- see > https://github.com/pgaudit/set_u