SSPI auth and mixed case usernames
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.
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?
Regards Niels Jespersen
On Fri, Aug 30, 2019 at 1:27 PM Niels Jespersen <NJN@dst.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/>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
</head>
<body>
<div style="">
<div dir="auto">
<div dir="auto">
<div id="signature-div-BA3B5AE5-4EBC-4395-BA68-7D8ECDC6EFA6" dir="auto">Hello Magnus</div>
<div id="signature-div-BA3B5AE5-4EBC-4395-BA68-7D8ECDC6EFA6" dir="auto"><br>
</div>
<div id="signature-div-BA3B5AE5-4EBC-4395-BA68-7D8ECDC6EFA6" dir="auto">Thank you for your prompt reply. </div>
<div id="signature-div-BA3B5AE5-4EBC-4395-BA68-7D8ECDC6EFA6" dir="auto"><br>
</div>
<div id="signature-div-BA3B5AE5-4EBC-4395-BA68-7D8ECDC6EFA6" dir="auto">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).</div>
<div id="signature-div-BA3B5AE5-4EBC-4395-BA68-7D8ECDC6EFA6" dir="auto"><br>
</div>
<div id="signature-div-BA3B5AE5-4EBC-4395-BA68-7D8ECDC6EFA6" dir="auto">In other words, Windows usernames one day entered as XYz, the next day entered as xYz, should logon to Postgres user xyz.</div>
<div id="signature-div-BA3B5AE5-4EBC-4395-BA68-7D8ECDC6EFA6" dir="auto"><br>
</div>
<div id="signature-div-BA3B5AE5-4EBC-4395-BA68-7D8ECDC6EFA6" dir="auto">Niels<br>
<br>
</div>
</div>
</div>
</div>
<hr>
<div dir="auto" style=""><br>
<b>Fra:</b> Magnus Hagander <magnus@hagander.net><br>
<b>Dato:</b> 30. august 2019 kl. 13.31.33 CEST<br>
<b>Til:</b> Niels Jespersen <NJN@dst.dk><br>
<b>Cc:</b> pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org><br>
<b>Emne:</b> Re: SSPI auth and mixed case usernames<br>
</div>
<br>
<div>
<div dir="ltr">
<div dir="ltr"><br>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Fri, Aug 30, 2019 at 1:27 PM Niels Jespersen <<a href="mailto:NJN@dst.dk">NJN@dst.dk</a>> wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex; border-left:1px solid rgb(204,204,204); padding-left:1ex">
Hello<br>
<br>
Postgresql 11.2 on Windows. <br>
<br>
I have a user mapping i pg_hba.conf <br>
<br>
sspi map=domain<br>
<br>
In pg_ident.conf, I have the following: <br>
<br>
domain /^(([A-Z|a-z]{3}[A|a]?)|([Xx]\d{2}))@DOMAIN$ \1<br>
<br>
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.
<br>
</blockquote>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div><br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex; border-left:1px solid rgb(204,204,204); padding-left:1ex">
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.
<br>
<br>
Is that possible, and if so, how? <br>
</blockquote>
<div><br>
</div>
<div>No, PostgreSQL will not do that automatically for you. </div>
<div><br>
</div>
<div>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.</div>
<div> </div>
</div>
-- <br>
<div dir="ltr" class="gmail_signature">
<div dir="ltr">
<div> Magnus Hagander<br>
Me: <a href="http://www.hagander.net/" target="_blank">https://www.hagander.net/</a><br>
Work: <a href="http://www.redpill-linpro.com/" target="_blank">https://www.redpill-linpro.com/</a></div>
</div>
</div>
</div>
</div>
</body>
</html>
On Fri, Aug 30, 2019 at 3:00 PM Niels Jespersen <NJN@dst.dk> wrote:
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.
Right. The client application needs to enforce that the usernamed passed to
PostgreSQL is lowercased before it tries to connect. The only thing
pg_ident mapping does is make sure that it actually works when the client
application does this, but it's the client that has to do it.
For example, if using psql you can't say "psql -h myserver.domain.com -d
mydatabase", you have to explicitly say "psql -h myserver.domain.com -d
mydatabase -U mylowercaseusername"
//Magnus
Greetings,
* Niels Jespersen (NJN@dst.dk) wrote:
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.
You just have to make sure that the users tell whatever program they're
using to connect to PG (like psql, pgAdmin, whatever) that their PG
username is 'xyz'. If they try to log in as 'XYZ' then that's gonna be
a different PG user.
If you have some other application that's being used to connect to PG
then you could do the lowercase in the app...
Thanks,
Stephen
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<meta name="Generator" content="Microsoft Exchange Server">
<!-- converted from text --><style><!-- .EmailQuote { margin-left: 1pt; padding-left: 4pt; border-left: #800000 2px solid; } --></style>
</head>
<body>
<div>
<div style="">
<div dir="auto">
<div dir="auto">
<div id="x_signature-div-AAF3C509-54F0-4EB8-9EA5-8FABFE13AF66" dir="auto">Ok, yes. It’s s server for analytics running R. So users speecify their oen connection string. My initial thought was to leave out username in the connection string, but I think now to
specify username in lowercase. </div>
<div id="x_signature-div-AAF3C509-54F0-4EB8-9EA5-8FABFE13AF66" dir="auto"><br>
</div>
<div id="x_signature-div-AAF3C509-54F0-4EB8-9EA5-8FABFE13AF66" dir="auto">Thank you, and Magnus also. </div>
<div id="x_signature-div-AAF3C509-54F0-4EB8-9EA5-8FABFE13AF66" dir="auto"><br>
</div>
<div id="x_signature-div-AAF3C509-54F0-4EB8-9EA5-8FABFE13AF66" dir="auto">Niels <br>
<br>
</div>
</div>
</div>
</div>
<hr>
<div dir="auto" style=""><br>
<b>Fra:</b> Stephen Frost <sfrost@snowman.net><br>
<b>Dato:</b> 30. august 2019 kl. 16.21.39 CEST<br>
<b>Til:</b> Niels Jespersen <NJN@dst.dk><br>
<b>Cc:</b> Magnus Hagander <magnus@hagander.net>, pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org><br>
<b>Emne:</b> Re: SSPI auth and mixed case usernames<br>
</div>
<br>
</div>
<font size="2"><span style="font-size:10pt;">
<div class="PlainText">Greetings,<br>
<br>
* Niels Jespersen (NJN@dst.dk) wrote:<br>
> Hello Magnus<br>
> Thank you for your prompt reply. <br>
> I’m not sure I understand your last statement. I want to achieve that<br>
> regardless of the case of the entered username is logged into the same<br>
> Postgres user (whose name is created in all lowercase).<br>
> In other words, Windows usernames one day entered as XYz, the next day<br>
> entered as xYz, should logon to Postgres user xyz.<br>
<br>
You just have to make sure that the users tell whatever program they're<br>
using to connect to PG (like psql, pgAdmin, whatever) that their PG<br>
username is 'xyz'. If they try to log in as 'XYZ' then that's gonna be<br>
a different PG user.<br>
<br>
If you have some other application that's being used to connect to PG<br>
then you could do the lowercase in the app...<br>
<br>
Thanks,<br>
<br>
Stephen<br>
</div>
</span></font>
</body>
</html>