User Name Maps seem broken in 11.1 on CentOS 7

On 1/29/19 1:11 PM, Viktor Berke wrote:

Hi,

After some talk with the helpful folks of #postgresql I see no other
option but to ask here. I'm trying to set up proper authentication for
our corprorate users. They'll access postgres both from their
workstations via TCP, and also locally. Locally, they're authenticated
using SSSD which in turn is using LDAP to talk to our Active Directory
DCs. That's not very relevant, but I just wanted to explain precisely.

Anyhow, we try to enforce the "user.name@company.com" login wherever we
can, so this is how I set up LDAP auth:

hostssl all all 10.1.0.1/16 ldap ldapserver=dc2.ad.foobar.com
ldapport=636 ldapscheme=ldaps ldaptls=0
ldapbinddn="CN=ldap,OU=Helpers,OU=Foobar,DC=ad,DC=foobar,DC=com"
ldapbindpasswd=*** ldapsearchattribute=mail
ldapbasedn="OU=Users,OU=Foobar,DC=ad,DC=foobar,DC=com"

This works perfectly fine. I create the role, e.g.:

CREATE ROLE "jane.doe@foobar.com" CREATEDB CREATEROLE LOGIN;

Then she can log in fine via pgAdmin or whatever, using her email address.

Now I want to set up peer authentication locally, so that they don't
have to enter their passwords all the time when they're already
authenticated to the OS. The idea is that I map the local "jane.doe" OS
user to the "jane.doe@foobar.com" role already present in postgres. This
way I don't have to CREATE ROLE and manage permissions both for jane.doe
and jane.doe@foobar.com. So the map would look something like this, I guess:

foo /^(.*)$ \1@foobar\.com (or something like that?)

And here comes the problem: user name maps seem completely
non-functional. First I suspected it's a problem with the dot in
usernames, but even if I create a local Unix user ("foobar") and set

local all all peer map=foo

in pg_hba.conf and

foo foobar postgres

In pg_ident.conf, all I see in the log is that

2019-01-29 21:44:45.095 CET [41929] LOG: �no match in usermap "foo" for
user "foobar" authenticated as "foobar"
2019-01-29 21:44:45.095 CET [41929] FATAL: �Peer authentication failed
for user "foobar"
2019-01-29 21:44:45.095 CET [41929] DETAIL: �Connection matched
pg_hba.conf line 79: "local all all peer map=foo"

Bummer. I also tried various regexes, even the likes of /^(.*)$, but the
log ALWAYS says no match. The weird thing is that this is the log
content even if there's nothing in pg_ident.conf, so it's like postgres
doesn't even care about what's in there.

Is ident_file set to something else?:

https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-IDENT-FILE

Any ideas?

Regards,

Viktor

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 1/29/19 1:11 PM, Viktor Berke wrote:

And here comes the problem: user name maps seem completely
non-functional.

Is ident_file set to something else?:
https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-IDENT-FILE

Also: have you been reloading the server configuration after
modifying the file? The postmaster only re-reads that file
after getting SIGHUP. ("pg_ctl reload" is the usual way to
send the signal.)

regards, tom lane

Hey!

I think I've figured out what was your problem.

You have created a mapping to allow OS user "foobar" auth as pg role
"postgres".

What happens though (and error message actually indicates that) is you are
trying to authenticate as pg role "foobar". This is probably due to you
executing `psql` in terminal without specifying an actual user name.
The way to do this properly would be to execute `psql -U postgres`.

Unfortunately, you've left before I was able to tell you this.

Kind regards,
Nick.