User Name Maps seem broken in 11.1 on CentOS 7
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.n...@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@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@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@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.
Any ideas?
Regards,
Viktor
Re: 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.n...@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@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@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@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.kla...@aklaver.com
Re: User Name Maps seem broken in 11.1 on CentOS 7
Adrian Klaver 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
Re: User Name Maps seem broken in 11.1 on CentOS 7
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.
