0

I use pg_ident.conf (PostgreSQL 12, OS Windows Server 2019) file to map users for SSPI this way:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
MapForSSPI      someone@COMPANY         someone

Recently I had to add new user, which I did exactly the same way as usual. I have created role "newsomeone" via pgAdmin, added membership properly, and added user into pg_ident.conf as:

MapForSSPI      newsomeone@COMPANY         newsomeone

But when this user tried to connect:

FATAL:  role "NewSomeone" does not exist

Please be aware of CASE. With further testing I realized the OS login is really set up as NewSomeone@COMPANY, but what I really do not understand is why is this login with capital letters not mapped to my lowercase login "newsomeone". When I've created new role "NewSomeone" via pgAdmin without any change to pg_ident.conf, the connection is successful.

How is it possible that with PG-USERNAME "newsomeone" specified in lowercase in pg_ident.conf it looks for role "NewSomeone" (as in OS login)?

Miro
  • 599
  • 10
  • 29

1 Answers1

1

pg_ident.conf is there to allow the system-authenticated user to login as a specific requested database user, when the spelling of the two doesn't match. It is not there to rewrite the requested database user into a different database user.

As long as your client is demanding to login as database user "NewSomeone", either it will succeed as that user, or it will fail as that user. It will not pick a different name to log in as.

You need to fix your client connection code (which you didn't show) so that it attempts to log in with the correct spelling.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Thank you. I hoped my question is pretty clear on this. I am mapping os user to existing pg user "newsomeone". And PostrgeSQL says role "NewSomeone" does not exist. As I wrote in my example I do use lowercase in my mapping and pg user is in lowercase. Still PostrgeSQL is looking for role with capitals. Looks like bug to me. – Miro Oct 01 '20 at 23:30
  • As I explained, the mapping isn't for changing the spelling of one database user to a different database user. Your client is somehow specifying the database user it wants to connect as--it is mandatory for it to do so. If you don't know how it is doing that, you need to figure it out, or show us the code so we can make suggestions. – jjanes Oct 01 '20 at 23:50
  • "Your client is somehow specifying the database user it wants to connect as--it is mandatory for it to do so" - I am leaving the user name empty, and I guess it automatically takes the system user name in such case.I think that is the problem. Need to do some more testing, thank you. – Miro Oct 02 '20 at 03:16
  • Ok, thank you, finally I understand what you mean. From pg_ident.conf "The existence of a record specifies that SYSTEM-USERNAME may connect as PG-USERNAME." I thought it is defining that SYSTEM-USERNAME will be connected to database as PG-USERNAME. But that is not right as you pointed out. Thanks again. – Miro Oct 02 '20 at 03:24