Re: [PoC] Federated Authn/z with OAUTHBEARER
Hi Jacob, Wanted to follow up on one of the topics discussed here in the past: Do you plan to support adding an extension hook to validate the token? It would allow a more efficient integration, then spinning a separate process. Thanks! Andrey. On Wed, Nov 8, 2023 at 11:00 AM Jacob Champion wrote: > On Fri, Nov 3, 2023 at 4:48 PM Jacob Champion > wrote: > > Thanks for the nudge! Looks like I need to reconcile with the changes > > to JsonLexContext in 1c99cde2. I should be able to get to that next > > week; in the meantime I'll mark it Waiting on Author. > > v13 rebases over latest. The JsonLexContext changes have simplified > 0001 quite a bit, and there's probably a bit more minimization that > could be done. > > Unfortunately the configure/Makefile build of libpq now seems to be > pulling in an `exit()` dependency in a way that Meson does not. (Or > maybe Meson isn't checking?) I still need to investigate that > difference and fix it, so I recommend Meson if you're looking to > test-drive a build. > > Thanks, > --Jacob >
Re: [PoC] Federated Authn/z with OAUTHBEARER
> > - Parameters are strings, so callback is not provided yet. > > 2. Client gets PgConn from PQconnectStart return value and updates > > conn->async_auth to its own callback. > > This is where some sort of official authn callback registration (see > above reply to Daniele) would probably come in handy. +1 > > 3. Client polls PQconnectPoll and checks conn->sasl_state until the > > value is SASL_ASYNC > > In my head, the client's custom callback would always be invoked > during the call to PQconnectPoll, rather than making the client do > work in between calls. That way, a client can use custom flows even > with a synchronous PQconnectdb(). The way I see this API working is the asynchronous client needs at least 2 PQConnectPoll calls: 1. To be notified of what the authentication requirements are and get parameters. 2. When it acquires the token, the callback is used to inform libpq of the token and return PGRES_POLLING_OK. For the synchronous client, the callback implementation would need to be aware of the fact that synchronous implementation invokes callback frequently and be implemented accordingly. Bottom lime, I don't see much problem with the current proposal. Just the way of callback to know that OAUTH token is requested and get parameters relies on PQconnectPoll being invoked after corresponding parameters of conn object are populated. > > > 5. Expectations on async_auth: > > > a. It returns PGRES_POLLING_READING while token acquisition is going on > > > b. It returns PGRES_POLLING_OK and sets conn->sasl_state->token > > > when token acquisition succeeds. > > > > Yes. Though the token should probably be returned through some > > explicit part of the callback, now that you mention it... > > > 6. Is the client supposed to do anything with the altsock parameter? > > The callback needs to set the altsock up with a select()able > descriptor, which wakes up the client when more work is ready to be > done. Without that, you can't handle multiple connections on a single > thread. Ok, thanks for clarification. > > On a separate note: > > The backend code currently spawns an external command for token validation. > > As we discussed before, an extension hook would be a more efficient > > extensibility option. > > We see clients make 10k+ connections using OAuth tokens per minute to > > our service, and stating external processes would be too much overhead > > here. > > +1. I'm curious, though -- what language do you expect to use to write > a production validator hook? Surely not low-level C...? For the server side code, it would likely be identity providers publishing extensions to validate their tokens. Those can do that in C too. Or extensions now can be implemented in Rust using pgrx. Which is developer friendly enough in my opinion. > Yeah, I'm really interested in seeing which existing high-level flows > can be mixed in through a driver. Trying not to get too far ahead of > myself :D I can think of the following as the most common: 1. Authorization code with PKCE. This is by far the most common for the user login flows. Requires to spin up a browser and listen to redirect URL/Port. Most high level platforms have libraries to do both. 2. Client Certificates. This requires an identity provider specific library to construct and sign the token. The providers publish SDKs to do that for most common app development platforms.
Re: [PoC] Federated Authn/z with OAUTHBEARER
Thanks Jacob for making progress on this. > 3) Is the current conn->async_auth() entry point sufficient for an > application to implement the Microsoft flows discussed upthread? Please confirm my understanding of the flow is correct: 1. Client calls PQconnectStart. - The client doesn't know yet what is the issuer and the scope. - Parameters are strings, so callback is not provided yet. 2. Client gets PgConn from PQconnectStart return value and updates conn->async_auth to its own callback. 3. Client polls PQconnectPoll and checks conn->sasl_state until the value is SASL_ASYNC 4. Client accesses conn->oauth_issuer and conn->oauth_scope and uses those info to trigger the token flow. 5. Expectations on async_auth: a. It returns PGRES_POLLING_READING while token acquisition is going on b. It returns PGRES_POLLING_OK and sets conn->sasl_state->token when token acquisition succeeds. 6. Is the client supposed to do anything with the altsock parameter? Is the above accurate understanding? If yes, it looks workable with a couple of improvements I think would be nice: 1. Currently, oauth_exchange function sets conn->async_auth = pg_fe_run_oauth_flow and starts Device Code flow automatically when receiving challenge and metadata from the server. There probably should be a way for the client to prevent default Device Code flow from triggering. 2. The current signature and expectations from async_auth function seems to be tightly coupled with the internal implementation: - Pieces of information need to be picked and updated in different places in the PgConn structure. - Function is expected to return PostgresPollingStatusType which is used to communicate internal state to the client. Would it make sense to separate the internal callback used to communicate with Device Code flow from client facing API? I.e. introduce a new client facing structure and enum to facilitate callback and its return value. --- On a separate note: The backend code currently spawns an external command for token validation. As we discussed before, an extension hook would be a more efficient extensibility option. We see clients make 10k+ connections using OAuth tokens per minute to our service, and stating external processes would be too much overhead here. --- > 5) Does this maintenance tradeoff (full control over the client vs. a > large amount of RFC-governed code) seem like it could be okay? It's nice for psql to have Device Code flow. Can be made even more convenient with refresh tokens support. And for clients on resource constrained devices to be able to authenticate with Client Credentials (app secret) without bringing more dependencies. In most other cases, upstream PostgreSQL drivers written in higher level languages have libraries / abstractions to implement OAUTH flows for the platforms they support. On Fri, Jul 7, 2023 at 11:48 AM Jacob Champion wrote: > > On Thu, Jul 6, 2023 at 1:48 PM Thomas Munro wrote: > > On Fri, Jul 7, 2023 at 4:57 AM Jacob Champion > > wrote: > > > Something analogous to libcurl's socket and timeout callbacks [1], > > > then? Or is there an existing libpq API you were thinking about using? > > > > Yeah. Libpq already has an event concept. > > Thanks -- I don't know how I never noticed libpq-events.h before. > > Per-connection events (or callbacks) might bring up the same > chicken-and-egg situation discussed above, with the notice hook. We'll > be fine as long as PQconnectStart is guaranteed to return before the > PQconnectPoll engine gets to authentication, and it looks like that's > true with today's implementation, which returns pessimistically at > several points instead of just trying to continue the exchange. But I > don't know if that's intended as a guarantee for the future. At the > very least we would have to pin that implementation detail. > > > > > Or, more likely in the > > > > first version, you just can't do it at all... Doesn't seem that bad > > > > to me. > > > > > > Any initial opinions on whether it's worse or better than a worker thread? > > > > My vote is that it's perfectly fine to make a new feature that only > > works on some OSes. If/when someone wants to work on getting it going > > on Windows/AIX/Solaris (that's the complete set of no-epoll, no-kqueue > > OSes we target), they can write the patch. > > Okay. I'm curious to hear others' thoughts on that, too, if anyone's lurking. > > Thanks! > --Jacob
Re: [PoC] Federated Authn/z with OAUTHBEARER
> This really doesn't feel like a great area to try and do hooks or > similar in, not the least because that approach has been tried and tried > again (PAM, GSSAPI, SASL would all be examples..) and frankly none of > them has turned out great (which is why we can't just tell people "well, > install the pam_oauth2 and watch everything work!") and this strikes me > as trying to do that yet again but worse as it's not even a dedicated > project trying to solve the problem but more like a side project. In this case it's not intended to be an open-ended hook, but rather an implementation of a specific rfc (rfc-7628) which defines a client-server communication for the authentication flow. The rfc itself does leave a lot of flexibility on specific parts of the implementation. Which do require hooks: (1.) Server side hook to validate the token, which is specific to the OAUTH provider. (2.) Client side hook to request the client to obtain the token. On (1.), we would need a hook for the OAUTH provider extension to do validation. We can though do some basic check that the credential is indeed a JWT token signed by the requested issuer. Specifically (2.) is where we can provide a layer in libpq to simplify the integration. i.e. implement some OAUTH flows. Though we would need some flexibility for the clients to bring their own token: For example there are cases where the credential to obtain the token is stored in a separate secure location and the token is returned from a separate service or pushed from a more secure environment. > another new "generic" set of hooks/APIs that will just cause DBAs and > our users headaches trying to make work. As I mentioned above, it's an rfc implementation, rather than our invention. When it comes to DBAs and the users. Builtin libpq implementations which allows psql and pgadmin to seamlessly connect should suffice those needs. While extensibility would allow the ecosystem to be open for OAUTH providers, SAAS developers, PAAS providers and other institutional players. Thanks! Andrey. On Thu, Feb 23, 2023 at 10:47 AM Stephen Frost wrote: > > Greetings, > > * Jacob Champion (jchamp...@timescale.com) wrote: > > On Mon, Feb 20, 2023 at 2:35 PM Stephen Frost wrote: > > > Having skimmed back through this thread again, I still feel that the > > > direction that was originally being taken (actually support something in > > > libpq and the backend, be it with libiddawc or something else or even > > > our own code, and not just throw hooks in various places) makes a lot > > > more sense and is a lot closer to how Kerberos and client-side certs and > > > even LDAP auth work today. > > > > Cool, that helps focus the effort. Thanks! > > Great, glad to hear that. > > > > That also seems like a much better answer > > > for our users when it comes to new authentication methods than having > > > extensions and making libpq developers have to write their own custom > > > code, not to mention that we'd still need to implement something in psql > > > to provide such a hook if we are to have psql actually usefully exercise > > > this, no? > > > > I don't mind letting clients implement their own flows... as long as > > it's optional. So even if we did use a hook in the end, I agree that > > we've got to exercise it ourselves. > > This really doesn't feel like a great area to try and do hooks or > similar in, not the least because that approach has been tried and tried > again (PAM, GSSAPI, SASL would all be examples..) and frankly none of > them has turned out great (which is why we can't just tell people "well, > install the pam_oauth2 and watch everything work!") and this strikes me > as trying to do that yet again but worse as it's not even a dedicated > project trying to solve the problem but more like a side project. SCRAM > was good, we've come a long way thanks to that, this feels like it > should be more in line with that rather than trying to invent yet > another new "generic" set of hooks/APIs that will just cause DBAs and > our users headaches trying to make work. > > > > In the Kerberos test suite we have today, we actually bring up a proper > > > Kerberos server, set things up, and then test end-to-end installing a > > > keytab for the server, getting a TGT, getting a service ticket, testing > > > authentication and encryption, etc. Looking around, it seems like the > > > equivilant would perhaps be to use Glewlwyd and libiddawc or libcurl and > > > our own code to really be able to test this and show that it works and > > > that we're doing it correctly, and to let us know if we break something. > > > > The original patchset includes a test server in Python -- a major > > advantage being that you can test the client and server independently > > of each other, since the implementation is so asymmetric. Additionally > > testing against something like Glewlwyd would be a great way to stack > > coverage. (If we *only* test against a packaged server, though, it'll > > be harder to
Re: [PoC] Federated Authn/z with OAUTHBEARER
Thanks for the feedback, > Having skimmed back through this thread again, I still feel that the > direction that was originally being taken (actually support something in > libpq and the backend, be it with libiddawc or something else or even > our own code, and not just throw hooks in various places) makes a lot > more sense and is a lot closer to how Kerberos and client-side certs and > even LDAP auth work today. That also seems like a much better answer > for our users when it comes to new authentication methods than having > extensions and making libpq developers have to write their own custom > code, not to mention that we'd still need to implement something in psql > to provide such a hook if we are to have psql actually usefully exercise > this, no? libpq implementation is the long term plan. However, our intention is to start with the protocol implementation which allows us to build on top of. While device code is the right solution for psql, having that as the only one can result in incentive to use it in the cases it's not intended to. Reasonably good implementation should support all of the following: (1.) authorization code with pkce (for GUI applications) (2.) device code (for console user logins) (3.) client secret (4.) some support for client certificate flow (1.) and (4.) require more work to get implemented, though necessary for encouraging the most secure grant types. As we didn't have those pieces, we're proposing starting with the protocol, which can be used by the ecosystem to build token flow implementations. Then add the libpq support for individual grant types. We originally looked at starting with bare bone protocol for PG16 and adding libpq support in PG17. That plan won't happen, though still splitting the work into separate stages would make more sense in my opinion. Several questions to follow up: (a.) Would you support committing the protocol first? or you see libpq implementation for grants as the prerequisite to consider the auth type? (b.) As of today, the server side core does not validate that the token is actually a valid jwt token. Instead relies on the extensions to do the validation. Do you think server core should do the basic validation before passing to extensions to prevent the auth type being used for anything other than OAUTH flows? Tests are the plan for the commit-ready implementation. Thanks! Andrey. On Tue, Feb 21, 2023 at 2:24 PM Jacob Champion wrote: > > On Mon, Feb 20, 2023 at 2:35 PM Stephen Frost wrote: > > Having skimmed back through this thread again, I still feel that the > > direction that was originally being taken (actually support something in > > libpq and the backend, be it with libiddawc or something else or even > > our own code, and not just throw hooks in various places) makes a lot > > more sense and is a lot closer to how Kerberos and client-side certs and > > even LDAP auth work today. > > Cool, that helps focus the effort. Thanks! > > > That also seems like a much better answer > > for our users when it comes to new authentication methods than having > > extensions and making libpq developers have to write their own custom > > code, not to mention that we'd still need to implement something in psql > > to provide such a hook if we are to have psql actually usefully exercise > > this, no? > > I don't mind letting clients implement their own flows... as long as > it's optional. So even if we did use a hook in the end, I agree that > we've got to exercise it ourselves. > > > In the Kerberos test suite we have today, we actually bring up a proper > > Kerberos server, set things up, and then test end-to-end installing a > > keytab for the server, getting a TGT, getting a service ticket, testing > > authentication and encryption, etc. Looking around, it seems like the > > equivilant would perhaps be to use Glewlwyd and libiddawc or libcurl and > > our own code to really be able to test this and show that it works and > > that we're doing it correctly, and to let us know if we break something. > > The original patchset includes a test server in Python -- a major > advantage being that you can test the client and server independently > of each other, since the implementation is so asymmetric. Additionally > testing against something like Glewlwyd would be a great way to stack > coverage. (If we *only* test against a packaged server, though, it'll > be harder to test our stuff in the presence of malfunctions and other > corner cases.) > > Thanks, > --Jacob
Re: Proposal: Support custom authentication methods using hooks
Greetings, Want to resurface the OAUTH support topic in the context of the concerns raised here. > How about- if we just added OAUTH support directly into libpq and the > backend, would that work with Azure's OIDC provider? If not, why not? > If it does, then what's the justification for trying to allow custom > backend-only authentication methods? We've explored this option, and prepared a patch which has bare-bone OAUTHBEARER mechanism implementation on both server- and libpq- sides. Based on existing SASL exchange support used for SCRAM. Most of the work has been done by @Jacob Champion and was referenced in this thread before. We validated the approach would work with Azure AD and other OIDC providers with token validation logic delegated to provider-specific extension. The thread link is here: https://www.postgresql.org/message-id/flat/CABkiuWo4fJQ7dhqgYLtJh41kpCkT6iXOO8Eym3Rdh5tx2RJCJw%40mail.gmail.com#f94c36969a68a07c087fa9af0f5401e1 With the client- and server- side support, the proposed patch would allow: - Identity providers to publish PG extensions to validate their specific token format. - Client ecosystem to build generic OAUTH flows using OIDC provider metadata defined in RFCs - Cloud providers to support a combination of Identity providers they work with. Looking forward to bring the discussion to that thread, and decide if we can proceed with the OAUTH support. Thanks, Andrey. On Wed, Jan 25, 2023 at 10:55 AM Stephen Frost wrote: > > Greetings, > > * Andres Freund (and...@anarazel.de) wrote: > > On 2022-08-03 17:21:58 -0400, Stephen Frost wrote: > > > * Andres Freund (and...@anarazel.de) wrote: > > > > On 2022-08-03 16:28:08 -0400, Stephen Frost wrote: > > > > > Again, server-side only is not interesting and not a direction that > > > > > makes sense to go in because it doesn't provide any way to have > > > > > trust established in both directions, which is what all modern > > > > > authentication methods do (certificates, kerberos, scram) and is what > > > > > we > > > > > should expect from anything new in this space. > > > > > > > > As explained repeatedly before, that's plainly not true. The patch > > > > allows > > > > e.g. to use the exact scram flow we already have, with the code we have > > > > for > > > > that (e.g. using a different source of secret). In fact the example > > > > extension > > > > does so (starting in v3, from 2022-03-15): > > > > > > Sure, thanks to the bespoke code in libpq for supporting SCRAM. I don't > > > think it makes sense to move the server-side code for that into an > > > extension as it just makes work for a bunch of people. Having a way to > > > have the authenticator token for scram exist elsewhere doesn't strike me > > > as unreasonable but that's not the same thing and is certainly more > > > constrained in terms of what it's doing. > > > > The question is: Why is providing that ability not a good step, even if > > somewhat constrainted? One argument would be that a later "protocol level > > extensibility" effort would require significant changes to the API - but I > > don't think that'd be the case. And even if, this isn't a large > > extensibility > > surface, so API evolution wouldn't be a problem. > > I'm quite confused by this as I feel like I've said multiple times that > having a way to have the SCRAM authenticator token come from somewhere > else seems reasonable. If that's not what you're referring to above by > 'that ability' then it'd really help if you could clarify what you mean > there. > > > > Further, I outlined exactly how extensability in this area could be > > > achieved: use some good third party library that provides multiple SASL > > > methods then just add support for that library to *PG*, on both the > > > libpq and the server sides. > > > > > What I don't think makes sense is adding extensibility to the server > > > side, especially if it's through a 3rd party library, but then not > > > adding support for that to libpq. I don't follow what the rationale is > > > for explicitly excluding libpq from this discussion. > > > > The rationale is trivial: Breaking down projects into manageable, separately > > useful, steps is a very sensible way of doing development. Even if we get > > protocol extensibility, we're still going to need an extension API like it's > > provided by the patchset. After protocol extensibility the authentication > > extensions would then have more functions it could call to control the auth > > flow with the client. > > > > > To be clear- I'm not explicitly saying that we can only add > > > extensibility with SCRAM, I'm just saying that whatever we're doing here > > > to add other actual authentication methods we should be ensuring is done > > > on both sides with a way for each side to authenticate the other side, > > > as all of the modern authentication methods we have already do. > > > > But *why* do these need to be tied together? > > I'm also confused by this. Surely you'd need
Re: [PoC] Federated Authn/z with OAUTHBEARER
> All of this points at a bigger question to the community: if we choose > not to provide a flow implementation in libpq, is adding OAUTHBEARER > worth the additional maintenance cost? > My personal vote would be "no". I think the hook-only approach proposed > here would ensure that only larger providers would implement it in > practice Flow implementations in libpq are definitely a long term plan, and I agree that it would democratise the adoption. In the previous posts in this conversation I outlined the ones I think we should support. However, I don't see why it's strictly necessary to couple those. As long as the SASL exchange for OAUTHBEARER mechanism is supported by the protocol, the Client side can evolve at its own pace. At the same time, the current implementation allows clients to start building provider-agnostic OAUTH support. By using iddawc or OAUTH client implementations in the respective platforms. So I wouldn't refer to "larger providers", but rather "more motivated clients" here. Which definitely overlaps, but keeps the system open. > I'm not understanding the concern in the final point -- providers > generally require you to opt into device authorization, at least as far > as I can tell. So if you decide that it's not appropriate for your use > case... don't enable it. (And I haven't seen any claims that opting into > device authorization weakens the other flows in any way. So if we're > going to implement a flow in libpq, I still think device authorization > is the best choice, since it works on headless machines as well as those > with browsers.) I agree with the statement that Device code is the best first choice if we absolutely have to pick one. Though I don't think we have to. While device flow can be used for all kinds of user-facing applications, it's specifically designed for input-constrained scenarios. As clearly stated in the Abstract here - https://www.rfc-editor.org/rfc/rfc8628 The authorization code with pkce flow is recommended by the RFSc and major providers for cases when it's feasible. The long term goal is to provide both, though I don't see why the backbone protocol implementation first wouldn't add value. Another point is user authentication is one side of the whole story and the other critical one is system-to-system authentication. Where we have Client Credentials and Certificates. With the latter it is much harder to get generically implemented, as provider-specific tokens need to be signed. Adding the other reasoning, I think libpq support for specific flows can get in the further iterations, after the protocol support. > in that case I'd rather spend cycles on generic SASL. I see 2 approaches to generic SASL: (a). Generic SASL is a framework used in the protocol, with the mechanisms implemented on top and exposed to the DBAs as auth types to configure in hba. This is the direction we're going here, which is well aligned with the existing hba-based auth configuration. (b). Generic SASL exposed to developers on the server- and client- side to extend on. It seems to be a much longer shot. The specific points of large ambiguity are libpq distribution model (which you pointed to) and potential pluggability of insecure mechanisms. I do see (a) as a sweet spot with a lot of value for various participants with much less ambiguity. > Additionally, the "issuer" field added here is not part of the RFC. I've > written my thoughts about unofficial extensions upthread but haven't > received a response, so I'm going to start being more strident: Please, > for the sake of reviewers, call out changes you've made to the spec, and > why they're justified. Thanks for your feedback on this. We had this discussion as well, and added that as a convenience for the client to identify the provider. I don't see a reason why an issuer would be absolutely necessary, so we will get your point that sticking to RFCs is a safer choice. > The patches seem to be out of order now (and the documentation in the > commit messages has been removed). Feedback taken. Work in progress. On Tue, Jan 17, 2023 at 2:44 PM Jacob Champion wrote: > > On Sun, Jan 15, 2023 at 12:03 PM Andrey Chudnovsky > wrote: > > 2. Removed Device Code implementation in libpq. Several reasons: > >- Reduce scope and focus on the protocol first. > >- Device code implementation uses iddawc dependency. Taking this > > dependency is a controversial step which requires broader discussion. > >- Device code implementation without iddaws would significantly > > increase the scope of the patch, as libpq needs to poll the token > > endpoint, setup different API calls, e.t.c. > >- That flow should canonically only be used for clients which can't > > invoke browsers. If it is the only flow to be implemented, it can be > > use
Re: [PoC] Federated Authn/z with OAUTHBEARER
More information on the latest patch. 1. We aligned the implementation with the barebone SASL for OAUTH described here - https://www.rfc-editor.org/rfc/rfc7628 The flow can be explained in the diagram below: +--+ +--+ | +---+ | Postgres | | PQconnect ->| | | | | | | | +---+ | | | -- Empty Token-> | > | | | | libpq | <-- Error(Discovery + Scope ) -- | < | Pre-Auth | | +--+ | | | Hook | | +- < | Hook | | | +---+ | |+--+ | | | | v | | | | | [get token]| | | | | | | | | | | + | | | +---+ | PQconnect > | | - Access Token > | > | Validator | | | | <-- Auth Result | < | Hook| | | | | +---+ | +---+ | | +--+ +--+ 2. Removed Device Code implementation in libpq. Several reasons: - Reduce scope and focus on the protocol first. - Device code implementation uses iddawc dependency. Taking this dependency is a controversial step which requires broader discussion. - Device code implementation without iddaws would significantly increase the scope of the patch, as libpq needs to poll the token endpoint, setup different API calls, e.t.c. - That flow should canonically only be used for clients which can't invoke browsers. If it is the only flow to be implemented, it can be used in the context when it's not expected by the OAUTH protocol. 3. Temporarily removed test suite. We are actively working on aligning the tests with the latest changes. Will add a patch with tests soon. We will change the "V3" prefix to make it the next after the previous iterations. Thanks! Andrey. On Thu, Jan 12, 2023 at 11:08 AM mahendrakar s wrote: > > Hi All, > > Changes added to Jacob's patch(v2) as per the discussion in the thread. > > The changes allow the customer to send the OAUTH BEARER token through psql > connection string. > > Example: > psql -U u...@example.com -d 'dbname=postgres oauth_bearer_token=abc' > > To configure OAUTH, the pg_hba.conf line look like: > local all all oauth > provider=oauth_provider issuer="https://example.com; scope="openid email" > > We also added hook to libpq to pass on the metadata about the issuer. > > Thanks, > Mahendrakar. > > > On Sat, 17 Dec 2022 at 04:48, Jacob Champion wrote: > > > > On Mon, Dec 12, 2022 at 9:06 PM Andrey Chudnovsky > > wrote: > > > If your concern is extension not honoring the DBA configured values: > > > Would a server-side logic to prefer HBA value over extension-provided > > > resolve this concern? > > > > Yeah. It also seals the role of the extension here as "optional". > > > > > We are definitely biased towards the cloud deployment scenarios, where > > > direct access to .hba files is usually not offered at all. > > > Let's find the middle ground here. > > > > Sure. I don't want to make this difficult in cloud scenarios -- > > obviously I'd like for Timescale Cloud to be able to make use of this > > too. But if we make this easy for a lone DBA (who doesn't have any > > institutional power with the providers) to use correctly and securely, > > then it should follow that the providers who _do_ have power and > > resources will have an easy time of it as well. The reverse isn't > > necessarily true. So I'm definitely planning to focus on the DBA case > > first. > > > > > A separate reason for creating this pre-authentication hook is further > > > extensibility to support more metadata. > > > Specifically when we add support for OAUTH flows to libpq, server-side > > > extensions can help bridge the gap between the identity provider > > > implementation and OAUTH/OIDC specs. > > > For example, that could allow the Github extension to provide an OIDC > > > discovery document. > > > > > > I definitely see identity providers as
Re: [PoC] Federated Authn/z with OAUTHBEARER
> The server hook is the right place to check the scopes, yes, but I think > the DBA should be able to specify what those scopes are to begin with. > The provider of the extension shouldn't be expected by the architecture > to hardcode those decisions, even if Azure AD chooses to short-circuit > that choice and provide magic instead. Hardcode is definitely not expected, but customization for identity provider specific, I think, should be allowed. I can provide a couple of advanced use cases which happen in the cloud deployments world, and require per-role management: - Multi-tenant deployments, when root provider URL would be different for different roles, based on which tenant they come from. - Federation to multiple providers. Solutions like Amazon Cognito which offer a layer of abstraction with several providers transparently supported. If your concern is extension not honoring the DBA configured values: Would a server-side logic to prefer HBA value over extension-provided resolve this concern? We are definitely biased towards the cloud deployment scenarios, where direct access to .hba files is usually not offered at all. Let's find the middle ground here. A separate reason for creating this pre-authentication hook is further extensibility to support more metadata. Specifically when we add support for OAUTH flows to libpq, server-side extensions can help bridge the gap between the identity provider implementation and OAUTH/OIDC specs. For example, that could allow the Github extension to provide an OIDC discovery document. I definitely see identity providers as institutional actors here which can be given some power through the extension hooks to customize the behavior within the framework. > I maintain that the hook doesn't need to hand back artifacts to the > client for a second PQconnect call. It can just use those artifacts to > obtain the access token and hand that right back to libpq. (I think any > requirement that clients be rewritten to call PQconnect twice will > probably be a sticking point for adoption of an OAuth patch.) Obtaining a token is an asynchronous process with a human in the loop. Not sure if expecting a hook function to return a token synchronously is the best option here. Can that be an optional return value of the hook in cases when a token can be obtained synchronously? On Thu, Dec 8, 2022 at 4:41 PM Jacob Champion wrote: > > On Wed, Dec 7, 2022 at 3:22 PM Andrey Chudnovsky > wrote: > > > >> I think it's okay to have the extension and HBA collaborate to > >> provide discovery information. Your proposal goes further than > >> that, though, and makes the server aware of the chosen client flow. > >> That appears to be an architectural violation: why does an OAuth > >> resource server need to know the client flow at all? > > > > Ok. It may have left there from intermediate iterations. We did > > consider making extension drive the flow for specific grant_type, > > but decided against that idea. For the same reason you point to. Is > > it correct that your main concern about use of grant_type was that > > it's propagated to the server? Then yes, we will remove sending it > > to the server. > > Okay. Yes, that was my primary concern. > > >> Ideally, yes, but that only works if all identity providers > >> implement the same flows in compatible ways. We're already seeing > >> instances where that's not the case and we'll necessarily have to > >> deal with that up front. > > > > Yes, based on our analysis OIDC spec is detailed enough, that > > providers implementing that one, can be supported with generic code > > in libpq / client. Github specifically won't fit there though. > > Microsoft Azure AD, Google, Okta (including Auth0) will. > > Theoretically discovery documents can be returned from the extension > > (server-side) which is provider specific. Though we didn't plan to > > prioritize that. > > As another example, Google's device authorization grant is incompatible > with the spec (which they co-authored). I want to say I had problems > with Azure AD not following that spec either, but I don't remember > exactly what they were. I wouldn't be surprised to find more tiny > departures once we get deeper into implementation. > > >> That seems to be restating the goal of OAuth and OIDC. Can you > >> explain how the incompatible change allows you to accomplish this > >> better than standard implementations? > > > > Do you refer to passing grant_type to the server? Which we will get > > rid of in the next iteration. Or other incompatible changes as well? > > Just the grant type, yeah. > > >> Why? I claim that standard OAUTHBEARER can handle all of that. > >> What
Re: [PoC] Federated Authn/z with OAUTHBEARER
That being said, the Diagram 2 would look like this with our proposal: +--+ +--+ | +---+ | Postgres | | PQconnect ->| | | | | | | | +---+ | | | -- Empty Token > | > | | | | libpq | <- Error(w\ Root URL + Audience ) -- | < | Pre-Auth | | +--+ | | | Hook | | +- < | Hook | | | +---+ | |+--+ | | | | v | | | | | [get token]| | | | | | | | | | | + | | | +---+ | PQconnect > | | -- Access Token ---> | > | Validator | | | | < Authorization Success/Failure | < | Hook| | | | | +---+ | +---+ | | +--+ +--+ With the application taking care of all Token acquisition logic. While the server-side hook is participating in the pre-authentication reply. That is definitely a required scenario for the long term and the easiest to implement in the client core. And if we can do at least that flow in PG16 it will be a strong foundation to provide more support for specific grants in libpq going forward. Does the diagram above look good to you? We can then start cleaning up the patch to get that in first. Thanks! Andrey. On Wed, Dec 7, 2022 at 3:22 PM Andrey Chudnovsky wrote: > > > I think it's okay to have the extension and HBA collaborate to provide > > discovery information. Your proposal goes further than that, though, > > and makes the server aware of the chosen client flow. That appears to > > be an architectural violation: why does an OAuth resource server need > > to know the client flow at all? > > Ok. It may have left there from intermediate iterations. We did > consider making extension drive the flow for specific grant_type, but > decided against that idea. For the same reason you point to. > Is it correct that your main concern about use of grant_type was that > it's propagated to the server? Then yes, we will remove sending it to > the server. > > > Ideally, yes, but that only works if all identity providers implement > > the same flows in compatible ways. We're already seeing instances > > where that's not the case and we'll necessarily have to deal with that > > up front. > > Yes, based on our analysis OIDC spec is detailed enough, that > providers implementing that one, can be supported with generic code in > libpq / client. > Github specifically won't fit there though. Microsoft Azure AD, > Google, Okta (including Auth0) will. > Theoretically discovery documents can be returned from the extension > (server-side) which is provider specific. Though we didn't plan to > prioritize that. > > > That seems to be restating the goal of OAuth and OIDC. Can you explain > > how the incompatible change allows you to accomplish this better than > > standard implementations? > > Do you refer to passing grant_type to the server? Which we will get > rid of in the next iteration. Or other incompatible changes as well? > > > Why? I claim that standard OAUTHBEARER can handle all of that. What > > does your proposed architecture (the third diagram) enable that my > > proposed hook (the second diagram) doesn't? > > The hook proposed on the 2nd diagram effectively delegates all Oauth > flows implementations to the client. > We propose libpq takes care of pulling OpenId discovery and coordination. > Which is effectively Diagram 1 + more flows + server hook providing > root url/audience. > > Created the diagrams with all components for 3 flows: > 1. Authorization code grant (Clients with Browser access): > +--+ > +--+ > | +---+ | >| > | PQconnect | | | >| > | [auth_code] | | | > +---+ > |
Re: [PoC] Federated Authn/z with OAUTHBEARER
ke a remote call to pull some information. The way we implement Azure AD auth today in PAAS PostgreSQL offering: - Server administrator uses special extension functions to create Azure AD enabled PostgreSQL roles. - PostgreSQL extension maps Roles to unique identity Ids (UID) in the Directory. - Connection flow: If the token is valid and Role => UID mapping matches, we authenticate as the Role. - Then its native PostgreSQL role based access control takes care of privileges. This is the same for both User- and System-to-system authorization. Though I assume different providers may treat user- and system- identities differently. So their extension would handle that. Thanks! Andrey. On Wed, Dec 7, 2022 at 11:06 AM Jacob Champion wrote: > > On Mon, Dec 5, 2022 at 4:15 PM Andrey Chudnovsky > wrote: > > I think we can focus on the roles and responsibilities of the components > > first. > > Details of the patch can be elaborated. Like "flow type code" is a > > mistake on our side, and we will use the term "grant_type" which is > > defined by OIDC spec. As well as details of usage of refresh_token. > > (For the record, whether we call it "flow type" or "grant type" > doesn't address my concern.) > > > Basically Yes. We propose an increase of the server side hook > > responsibility. > > From just validating the token, to also return the provider root URL > > and required audience. And possibly provide more metadata in the > > future. > > I think it's okay to have the extension and HBA collaborate to provide > discovery information. Your proposal goes further than that, though, > and makes the server aware of the chosen client flow. That appears to > be an architectural violation: why does an OAuth resource server need > to know the client flow at all? > > > Which is in our opinion aligned with SASL protocol, where the server > > side is responsible for telling the client auth requirements based on > > the requested role in the startup packet. > > You've proposed an alternative SASL mechanism. There's nothing wrong > with that, per se, but I think it should be clear why we've chosen > something nonstandard. > > > Our understanding is that in the original patch that information came > > purely from hba, and we propose extension being able to control that > > metadata. > > As we see extension as being owned by the identity provider, compared > > to HBA which is owned by the server administrator or cloud provider. > > That seems reasonable, considering how tightly coupled the Issuer and > the token validation process are. > > > 2. Server Owners / PAAS providers (On premise admins, Cloud providers, > > multi-cloud PAAS providers). > >- Install extensions and configure HBA to allow clients to > > authenticate with the identity providers of their choice. > > (For a future conversation: they need to set up authorization, too, > with custom scopes or some other magic. It's not enough to check who > the token belongs to; even if Postgres is just using the verified > email from OpenID as an authenticator, you have to also know that the > user authorized the token -- and therefore the client -- to access > Postgres on their behalf.) > > > 3. Client Application Developers (Data Wis, integration tools, > > PgAdmin, monitoring tools, e.t.c.) > >- Independent from specific Identity providers or server providers. > > Write one code for all identity providers. > > Ideally, yes, but that only works if all identity providers implement > the same flows in compatible ways. We're already seeing instances > where that's not the case and we'll necessarily have to deal with that > up front. > > >- Rely on application deployment owners to configure which OIDC > > provider to use across client and server setups. > > 4. Application Deployment Owners (End customers setting up applications) > >- The only actor actually aware of which identity provider to use. > > Configures the stack based on the Identity and PostgreSQL deployments > > they have. > > (I have doubts that the roles will be as decoupled in practice as you > have described them, but I'd rather defer that for now.) > > > The critical piece of the vision is (3.) above is applications > > agnostic of the identity providers. Those applications rely on > > properly configured servers and rich driver logic (libpq, > > com.postgresql, npgsql) to allow their application to popup auth > > windows or do service-to-service authentication with any provider. In > > our view that would significantly democratize the deployment of OAUTH > > authentication in the community. >
Re: [PoC] Federated Authn/z with OAUTHBEARER
Jacob, Thanks for your feedback. I think we can focus on the roles and responsibilities of the components first. Details of the patch can be elaborated. Like "flow type code" is a mistake on our side, and we will use the term "grant_type" which is defined by OIDC spec. As well as details of usage of refresh_token. > Rather than decouple things, I think this proposal drives a spike > through the client app, libpq, and the server. Please correct me if I've > misunderstood pieces of the patch, but the following is my view of it: > What used to be a validator hook on the server side now actively > participates in the client-side flow for some reason. (I still don't > understand what the server is supposed to do with that knowledge. > Changing your authz requirements based on the flow the client wants to > use seems like a good way to introduce bugs.) > The client-side hook is now coupled to the application logic: you have > to know to expect an error from the first PQconnect*() call, then check > whatever magic your hook has done for you to be able to set up the > second call to PQconnect*() with the correctly scoped bearer token. So > if you want to switch between the internal libpq OAuth implementation > and your own hook, you have to rewrite your app logic. Basically Yes. We propose an increase of the server side hook responsibility. >From just validating the token, to also return the provider root URL and required audience. And possibly provide more metadata in the future. Which is in our opinion aligned with SASL protocol, where the server side is responsible for telling the client auth requirements based on the requested role in the startup packet. Our understanding is that in the original patch that information came purely from hba, and we propose extension being able to control that metadata. As we see extension as being owned by the identity provider, compared to HBA which is owned by the server administrator or cloud provider. This change of the roles is based on the vision of 4 independent actor types in the ecosystem: 1. Identity Providers (Okta, Google, Microsoft, other OIDC providers). - Publish open source extensions for PostgreSQL. - Don't have to own the server deployments, and must ensure their extensions can work in any environment. This is where we think additional hook responsibility helps. 2. Server Owners / PAAS providers (On premise admins, Cloud providers, multi-cloud PAAS providers). - Install extensions and configure HBA to allow clients to authenticate with the identity providers of their choice. 3. Client Application Developers (Data Wis, integration tools, PgAdmin, monitoring tools, e.t.c.) - Independent from specific Identity providers or server providers. Write one code for all identity providers. - Rely on application deployment owners to configure which OIDC provider to use across client and server setups. 4. Application Deployment Owners (End customers setting up applications) - The only actor actually aware of which identity provider to use. Configures the stack based on the Identity and PostgreSQL deployments they have. The critical piece of the vision is (3.) above is applications agnostic of the identity providers. Those applications rely on properly configured servers and rich driver logic (libpq, com.postgresql, npgsql) to allow their application to popup auth windows or do service-to-service authentication with any provider. In our view that would significantly democratize the deployment of OAUTH authentication in the community. In order to allow this separation, we propose: 1. HBA + Extension is the single source of truth of Provider root URL + Required Audience for each role. If some backfill for missing OIDC discovery is needed, the provider-specific extension would be providing it. 2. Client Application knows which grant_type to use in which scenario. But can be coded without knowledge of a specific provider. So can't provide discovery details. 3. Driver (libpq, others) - coordinate the authentication flow based on client grant_type and identity provider metadata to allow client applications to use any flow with any provider in a unified way. Yes, this would require a little more complicated flow between components than in your original patch. And yes, more complexity comes with more opportunity to make bugs. However, I see PG Server and Libpq as the places which can have more complexity. For the purpose of making work for the community participants easier and simplify adoption. Does this make sense to you? On Tue, Nov 29, 2022 at 1:20 PM Jacob Champion wrote: > > On 11/24/22 00:20, mahendrakar s wrote: > > I had validated Github by skipping the discovery mechanism and letting > > the provider extension pass on the endpoints. This is just for > > validation purposes. > > If it needs to be supported, then need a way to send the discovery > > document from extension. > > Yeah. I had originally bounced around the idea that we could send
Re: [PoC] Federated Authn/z with OAUTHBEARER
> How does this differ from the previous proposal? The OAUTHBEARER SASL > mechanism already relies on OIDC for discovery. (I think that decision > is confusing from an architectural and naming standpoint, but I don't > think they really had an alternative...) Mostly terminology questions here. OAUTHBEARER SASL appears to be the spec about using OAUTH2 tokens for Authentication. While any OAUTH2 can generally work, we propose to specifically highlight that only OIDC providers can be supported, as we need the discovery document. And we won't be able to support Github under that requirement. Since the original patch used that too - no change on that, just confirmation that we need OIDC compliance. > 0) The original hook proposal upthread, I thought, was about allowing > libpq's flow implementation to be switched out by the application. I > don't see that approach taken here. It's fine if that turned out to be a > bad idea, of course, but this patch doesn't seem to match what we were > talking about. We still plan to allow the client to pass the token. Which is a generic way to implement its own OAUTH flows. > 1) I'm really concerned about the sudden explosion of flows. We went > from one flow (Device Authorization) to six. It's going to be hard > enough to validate that *one* flow is useful and can be securely > deployed by end users; I don't think we're going to be able to maintain > six, especially in combination with my statement that iddawc is not an > appropriate dependency for us. > I'd much rather give applications the ability to use their own OAuth > code, and then maintain within libpq only the flows that are broadly > useful. This ties back to (0) above. We consider the following set of flows to be minimum required: - Client Credentials - For Service to Service scenarios. - Authorization Code with PKCE - For rich clients,including pgAdmin. - Device code - for psql (and possibly other non-GUI clients). - Refresh code (separate discussion) Which is pretty much the list described here: https://oauth.net/2/grant-types/ and in OAUTH2 specs. Client Credentials is very simple, so does Refresh Code. If you prefer to pick one of the richer flows, Authorization code for GUI scenarios is probably much more widely used. Plus it's easier to implement too, as interaction goes through a series of callbacks. No polling required. > 2) Breaking the refresh token into its own pseudoflow is, I think, > passing the buck onto the user for something that's incredibly security > sensitive. The refresh token is powerful; I don't really want it to be > printed anywhere, let alone copy-pasted by the user. Imagine the > phishing opportunities. > If we want to support refresh tokens, I believe we should be developing > a plan to cache and secure them within the client. They should be used > as an accelerator for other flows, not as their own flow. It's considered a separate "grant_type" in the specs / APIs. https://openid.net/specs/openid-connect-core-1_0.html#RefreshTokens For the clients, it would be storing the token and using it to authenticate. On the question of sensitivity, secure credentials stores are different for each platform, with a lot of cloud offerings for this. pgAdmin, for example, has its own way to secure credentials to avoid asking users for passwords every time the app is opened. I believe we should delegate the refresh token management to the clients. >3) I don't like the departure from the OAUTHBEARER mechanism that's > presented here. For one, since I can't see a sample plugin that makes > use of the "flow type" magic numbers that have been added, I don't > really understand why the extension to the mechanism is necessary. I don't think it's much of a departure, but rather a separation of responsibilities between libpq and upstream clients. As libpq can be used in different apps, the client would need different types of flows/grants. I.e. those need to be provided to libpq at connection initialization or some other point. We will change to "grant_type" though and use string to be closer to the spec. What do you think is the best way for the client to signal which OAUTH flow should be used? On Wed, Nov 23, 2022 at 12:05 PM Jacob Champion wrote: > > On 11/23/22 01:58, mahendrakar s wrote: > > We validated on libpq handling OAuth natively with different flows > > with different OIDC certified providers. > > > > Flows: Device Code, Client Credentials and Refresh Token. > > Providers: Microsoft, Google and Okta. > > Great, thank you! > > > Also validated with OAuth provider Github. > > (How did you get discovery working? I tried this and had to give up > eventually.) > > > We propose using OpenID Connect (OIDC) as the protocol, instead of > > OAuth, as it is: > > - Discovery mechanism to bridge the differences and provide metadata. > > - Stricter protocol and certification process to reliably identify > > which providers can be supported. > > - OIDC is designed for authentication, while the main
Re: [PoC] Federated Authn/z with OAUTHBEARER
> I think we can probably prototype a callback hook for approach (1) > pretty quickly. (2) is a lot more work and investigation, but it's > work that I'm interested in doing (when I get the time). I think there > are other very good reasons to consider a third-party SASL library, > and some good lessons to be learned, even if the community decides not > to go down that road. Makes sense. We will work on (1.) and do some check if there are any blockers for a shared solution to support github and google. On Fri, Sep 30, 2022 at 1:45 PM Jacob Champion wrote: > > On Fri, Sep 30, 2022 at 7:47 AM Andrey Chudnovsky > wrote: > > > How should we communicate those pieces to a custom client when it's > > > passing a token directly? The easiest way I can see is for the custom > > > client to speak the OAUTHBEARER protocol directly (e.g. SASL plugin). > > > If you had to parse the libpq error message, I don't think that'd be > > > particularly maintainable. > > > > I agree that parsing the message is not a sustainable way. > > Could you provide more details on the SASL plugin approach you propose? > > > > Specifically, is this basically a set of extension hooks for the client > > side? > > With the need for the client to be compiled with the plugins based on > > the set of providers it needs. > > That's a good question. I can see two broad approaches, with maybe > some ability to combine them into a hybrid: > > 1. If there turns out to be serious interest in having libpq itself > handle OAuth natively (with all of the web-facing code that implies, > and all of the questions still left to answer), then we might be able > to provide a "token hook" in the same way that we currently provide a > passphrase hook for OpenSSL keys. By default, libpq would use its > internal machinery to take the provider details, navigate its builtin > flow, and return the Bearer token. If you wanted to override that > behavior as a client, you could replace the builtin flow with your > own, by registering a set of callbacks. > > 2. Alternatively, OAuth support could be provided via a mechanism > plugin for some third-party SASL library (GNU libgsasl, Cyrus > libsasl2). We could provide an OAuth plugin in contrib that handles > the default flow. Other providers could publish their alternative > plugins to completely replace the OAUTHBEARER mechanism handling. > > Approach (2) would make for some duplicated effort since every > provider has to write code to speak the OAUTHBEARER protocol. It might > simplify provider-specific distribution, since (at least for Cyrus) I > think you could build a single plugin that supports both the client > and server side. But it would be a lot easier to unknowingly (or > knowingly) break the spec, since you'd control both the client and > server sides. There would be less incentive to interoperate. > > Finally, we could potentially take pieces from both, by having an > official OAuth mechanism plugin that provides a client-side hook to > override the flow. I have no idea if the benefits would offset the > costs of a plugin-for-a-plugin style architecture. And providers would > still be free to ignore it and just provide a full mechanism plugin > anyway. > > > > Well... I don't quite understand why we'd go to the trouble of > > > providing a provider-agnostic communication solution only to have > > > everyone write their own provider-specific client support. Unless > > > you're saying Microsoft would provide an officially blessed plugin for > > > the *server* side only, and Google would provide one of their own, and > > > so on. > > > > Yes, via extensions. Identity providers can open source extensions to > > use their auth services outside of first party PaaS offerings. > > For 3rd party Postgres PaaS or on premise deployments. > > Sounds reasonable. > > > > The server side authorization is the only place where I think it makes > > > sense to specialize by default. libpq should remain agnostic, with the > > > understanding that we'll need to make hard decisions when a major > > > provider decides not to follow a spec. > > > > Completely agree with agnostic libpq. Though needs validation with > > several major providers to know if this is possible. > > Agreed. > > > > Specifically it delivers that message to an end user. If you want a > > > generic machine client to be able to use that, then we'll need to talk > > > about how. > > > > Yes, that's what needs to be decided. > > In both Device code and Authorization code scenarios, libpq and the > > client would need to exch
Re: [PoC] Federated Authn/z with OAUTHBEARER
> The most concrete example I can see is with the OAUTHBEARER error > response. If you want to eventually handle differing scopes per role, > or different error statuses (which the proof-of-concept currently > hardcodes as `invalid_token`), then the client can't assume it knows > what the server is going to say there. I think that's true even if you > control both sides and are hardcoding the provider. Ok, I see the point. It's related to the topic of communication between libpq and the upstream client. > How should we communicate those pieces to a custom client when it's > passing a token directly? The easiest way I can see is for the custom > client to speak the OAUTHBEARER protocol directly (e.g. SASL plugin). > If you had to parse the libpq error message, I don't think that'd be > particularly maintainable. I agree that parsing the message is not a sustainable way. Could you provide more details on the SASL plugin approach you propose? Specifically, is this basically a set of extension hooks for the client side? With the need for the client to be compiled with the plugins based on the set of providers it needs. > Well... I don't quite understand why we'd go to the trouble of > providing a provider-agnostic communication solution only to have > everyone write their own provider-specific client support. Unless > you're saying Microsoft would provide an officially blessed plugin for > the *server* side only, and Google would provide one of their own, and > so on. Yes, via extensions. Identity providers can open source extensions to use their auth services outside of first party PaaS offerings. For 3rd party Postgres PaaS or on premise deployments. > The server side authorization is the only place where I think it makes > sense to specialize by default. libpq should remain agnostic, with the > understanding that we'll need to make hard decisions when a major > provider decides not to follow a spec. Completely agree with agnostic libpq. Though needs validation with several major providers to know if this is possible. > Specifically it delivers that message to an end user. If you want a > generic machine client to be able to use that, then we'll need to talk > about how. Yes, that's what needs to be decided. In both Device code and Authorization code scenarios, libpq and the client would need to exchange a couple of pieces of metadata. Plus, after success, the client should be able to access a refresh token for further use. Can we implement a generic protocol like for this between libpq and the clients?
Re: [PoC] Federated Authn/z with OAUTHBEARER
>>> Libpq passing toked directly from an upstream client is useful in other >>> scenarios: >>> 1. Enterprise clients, built with .Net / Java and using provider-specific >>> authentication libraries, like MSAL for AAD. Those can also support more >>> advanced provider-specific token acquisition flows. > I can see that providing a token directly would help you work around > limitations in libpq's "standard" OAuth flows, whether we use iddawc or > not. And it's cheap in terms of implementation. But I have a feeling it > would fall apart rapidly with error cases, where the server is giving > libpq information via the OAUTHBEARER mechanism, but libpq can only > communicate to your wrapper through human-readable error messages on stderr. For the providing token directly, that would be primarily used for scenarios where the same party controls both the server and the client side wrapper. I.e. The client knows how to get a token for a particular principal and doesn't need any additional information other than human readable messages. Please clarify the scenarios where you see this falling apart. I can provide an example in the cloud world. We (Azure) as well as other providers offer ways to obtain OAUTH tokens for Service-to-Service communication at IAAS / PAAS level. on Azure "Managed Identity" feature integrated in Compute VM allows a client to make a local http call to get a token. VM itself manages the certificate livecycle, as well as implements the corresponding OAUTH flow. This capability is used by both our 1st party PAAS offerings, as well as 3rd party services deploying on VMs or managed K8S clusters. Here, the client doesn't need libpq assistance in obtaining the token. > This seems like clear motivation for client-side SASL plugins (which > were also discussed on Samay's proposal thread). That's a lot more > expensive to implement in libpq, but if it were hypothetically > available, wouldn't you rather your provider-specific code be able to > speak OAUTHBEARER directly with the server? I generally agree that pluggable auth layers in libpq could be beneficial. However, as you pointed out in Samay's thread, that would require a new distribution model for libpq / clients to optionally include provider-specific logic. My optimistic plan here would be to implement several core OAUTH flows in libpq core which would be generic enough to support major enterprise OAUTH providers: 1. Client Credentials flow (Client_id + Client_secret) for backend applications. 2. Authorization Code Flow with PKCE and/or Device code flow for GUI applications. (2.) above would require a protocol between libpq and upstream clients to exchange several messages. Your patch includes a way for libpq to deliver to the client a message about the next authentication steps, so planned to build on top of that. A little about scenarios, we look at. What we're trying to achieve here is an easy integration path for multiple players in the ecosystem: - Managed PaaS Postgres providers (both us and multi-cloud solutions) - SaaS providers deploying postgres on IaaS/PaaS providers' clouds - Tools - pg_admin, psql and other ones. - BI, ETL, Federation and other scenarios where postgres is used as the data source. If we can offer a provider agnostic solution for Backend <=> libpq <=> Upstreal client path, we can have all players above build support for OAUTH credentials, managed by the cloud provider of their choice. For us, that would mean: - Better administrator experience with pg_admin / psql handling of the AAD (Azure Active Directory) authentication flows. - Path for integration solutions using Postgres to build AAD authentication in their management experience. - Ability to use AAD identity provider for any Postgres deployments other than our 1st party PaaS offering. - Ability to offer github as the identity provider for PaaS Postgres offering. Other players in the ecosystem above would be able to get the same benefits. Does that make sense and possible without provider specific libpq plugin? - On resource constrained scenarios. > I want to dig into this much more; resource-constrained systems are near > and dear to me. I can see two cases here: I just referred to the ability to compile libpq without extra dependencies to save some kilobytes. Not sure if OAUTH is widely used in those cases. It involves overhead anyway, and requires the device to talk to an additional party (OAUTH provider). Likely Cert authentication is easier. If needed, it can get libpq with full OAUTH support and use a client code. But I didn't think about this scenario. On Fri, Sep 23, 2022 at 3:39 PM Jacob Champion wrote: > > On Fri, Mar 25, 2022 at 5:00 PM Jacob Champion wrote: > > v4 rebases over the latest version of the pluggable auth patchset > > (included as 0001-4). Note that there's a recent conflict as > > of d4781d887; use an older commit as the base (or wait for the other > > thread to be updated). > > Here's a newly
Re: [EXTERNAL] Re: [PoC] Federated Authn/z with OAUTHBEARER
First, My message from corp email wasn't displayed in the thread, That is what Jacob replied to, let me post it here for context: > We can support both passing the token from an upstream client and libpq > implementing OAUTH2 protocol to obtain one. > > Libpq implementing OAUTHBEARER is needed for community/3rd party tools to > have user-friendly authentication experience: > > 1. For community client tools, like pg_admin, psql etc. > Example experience: pg_admin would be able to open a popup dialog to > authenticate customers and keep refresh tokens to avoid asking the user > frequently. > 2. For 3rd party connectors supporting generic OAUTH with any provider. > Useful for datawiz clients, like Tableau or ETL tools. Those can support both > user and client OAUTH flows. > > Libpq passing toked directly from an upstream client is useful in other > scenarios: > 1. Enterprise clients, built with .Net / Java and using provider-specific > authentication libraries, like MSAL for AAD. Those can also support more > advanced provider-specific token acquisition flows. > 2. Resource-tight (like IoT) clients. Those can be compiled without the > optional libpq flag not including the iddawc or other dependency. - On this: > What I don't understand is how the OAUTHBEARER mechanism helps you in > this case. You're short-circuiting the negotiation where the server > tells the client what provider to use and what scopes to request, and > instead you're saying "here's a secret string, just take it and > validate it with magic." > > I realize the ability to pass an opaque token may be useful, but from > the server's perspective, I don't see what differentiates it from the > password auth method plus a custom authenticator plugin. Why pay for > the additional complexity of OAUTHBEARER if you're not going to use > it? Yes, passing a token as a new auth method won't make much sense in isolation. However: 1. Since OAUTHBEARER is supported in the ecosystem, passing a token as a way to authenticate with OAUTHBEARER is more consistent (IMO), then passing it as a password. 2. Validation on the backend side doesn't depend on whether the token is obtained by libpq or transparently passed by the upstream client. 3. Single OAUTH auth method on the server side for both scenarios, would allow both enterprise clients with their own Token acquisition and community clients using libpq flows to connect as the same PG users/roles. On Wed, Sep 21, 2022 at 8:36 PM Jacob Champion wrote: > > On Wed, Sep 21, 2022 at 3:10 PM Andrey Chudnovskiy > wrote: > > We can support both passing the token from an upstream client and libpq > > implementing OAUTH2 protocol to obtaining one. > > Right, I agree that we could potentially do both. > > > Libpq passing toked directly from an upstream client is useful in other > > scenarios: > > 1. Enterprise clients, built with .Net / Java and using provider-specific > > authentication libraries, like MSAL for AAD. Those can also support more > > advance provider-specific token acquisition flows. > > 2. Resource-tight (like IoT) clients. Those can be compiled without > > optional libpq flag not including the iddawc or other dependency. > > What I don't understand is how the OAUTHBEARER mechanism helps you in > this case. You're short-circuiting the negotiation where the server > tells the client what provider to use and what scopes to request, and > instead you're saying "here's a secret string, just take it and > validate it with magic." > > I realize the ability to pass an opaque token may be useful, but from > the server's perspective, I don't see what differentiates it from the > password auth method plus a custom authenticator plugin. Why pay for > the additional complexity of OAUTHBEARER if you're not going to use > it? > > --Jacob > > > >
