Re: allowing "map" for password auth methods with clientcert=verify-full

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 me(at)example(dot)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.html

I'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