Hello Magnus
Thank you for your prompt reply.
I’m not sure I understand your last statement. I want to achieve that regardless of the case of the entered username is logged into the same Postgres user (whose name is created in all
lowercase).
In other words, Windows usernames one day entered as XYz, the next day entered as xYz, should logon to Postgres user xyz.
Niels
Fra: Magnus Hagander <magnus@xxxxxxxxxxxx>
Dato: 30. august 2019 kl. 13.31.33 CEST
Til: Niels Jespersen <NJN@xxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Emne: Re: SSPI auth and mixed case usernames
On Fri, Aug 30, 2019 at 1:27 PM Niels Jespersen <NJN@xxxxxx> 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.
