Re: SSPI auth and mixed case usernames

On Fri, Aug 30, 2019 at 1:27 PM Niels Jespersen <NJN(at)dst(dot)dk> wrote:

> Hello
>
> Postgresql 11.2 on Windows.
>
> I have a user mapping i pg_hba.conf
>
> sspi map=domain
>
> In pg_ident.conf, I have the following:
>
> domain /^(([A-Z|a-z]{3}[A|a]?)|([Xx]\d{2}))@DOMAIN$ \1
>
> This maps windows logonname til a postgres username. Hower, for reasons I
> cannot explain, sometimes the username comes in all-lowercase, at other
> times it comes all-caps. This is dependant on the Windows host the client
> is connected to.
>

It is actually dependent on what the user typed into their login box when
they logged in to the machine. Yes, that's mostly insane, but that's how
those APIs in Windows work.

I do not want to create both XXX and xxx as users on Postgres. I would
> prefer to translate alle usernames to lowercase in the map.
>
> Is that possible, and if so, how?
>

No, PostgreSQL will not do that automatically for you.

What pg_ident.conf allows you to do is say that the user is allowed to log
in to the postgres user in lowercase even if the username retrieved using
sspi is not in lowercase. But the application still has to actually try to
log in with lowercase, and do so before it connects to PostgreSQL.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>