Thread: 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.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.
Any ideas?
Regards,
Viktor
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.