On 10/27/21 12:14 PM, Jacob Champion wrote:
On Tue, 2021-10-26 at 18:16 -0400, Tom Lane wrote:Per "21.2. User Name Maps", I think that the map parameter is supposed to translate from the startup packet's user name to the SQL role name.I may have misunderstood what you wrote, but IIUC the startup packet's user name _is_ the SQL role name, even when using a map. The map is just determining whether or not the authenticated ID (pulled from a certificate, or from Kerberos, or etc.) is authorized to use that role name. It's not a translation, because you can have a one-to-many user mapping (where m...@example.com is allowed to log in as `me` or `postgres` or `admin` or...). Please correct me if I've missed something -- I need to have it right in my head, given my other patches in this area...
To Tom's earlier point, I understand why we may want to pause and think about this.
I don't know the whole history of the "pg_ident.conf" file, but judging by the name, my guess is that the mapping functionality started with the "ident" authentication support, and then it was used for other auth types that could benefit from mapping (cert/gssapi etc.). The documentation referenced also skews towards describing what the original functionality for ident does.
That said, the existing functionality does match what Jacob is describing and what my own understanding is.
The patch I propose just layers on top of the existing functionality -- you could even argue that it's "fixing a bug" that we did not add the current "map" support for the case of "clientcert=verify-full" given we do introspect the certificate CN to see if it matches the SQL role name.
In terms of other user mapping functionality, we have ad hoc support for FDWs when trying to map to a user in a different server:
https://www.postgresql.org/docs/current/sql-createusermapping.htmlI'm unsure if there is anything we'd want to leverage here, as the overall goal of this is to provide the ability to establish a connection with a remote server.
I think in the context of doing any new work, I'd step back and ask what problem is this solving? The main one I think of is an integration with a SSO system has a credential with an identifier that does not match it's credential in PostgreSQL? (That would be the case I was working on, though said case was borrowed from our docs). Are there other cases?
That said, what would make it easier to manage it then? Maybe a lot of this is documenting and some expansion on what the pg_ident.conf file can do (per Andrew's suggestion). And maybe a new name for said file.
I don't know if we would want to bring any of this into the catalog or not -- but perhaps there may be some advantages to that from an administration standpoint.
Anyway, those are my initial thoughts on the challenge to think a bit more deeply about this. I'd still suggest considering the patch I propose as an "immediate fix" for existing versions as, at least to myself, I can argue it's a bug. We can then do some more work to make the overall system a bit easier/clearer to use and maintain.
Thanks, Jonathan
OpenPGP_signature
Description: OpenPGP digital signature
