Re: pg_ident mapping Kerberos Usernames

On 09/10/2017 02:39 AM, Magnus Hagander wrote:
> On Sat, Sep 9, 2017 at 6:44 PM, <techmail+pgsql(at)dangertoaster(dot)com
> <mailto:techmail+pgsql(at)dangertoaster(dot)com>> wrote:
>
> Hi,
>
> I'm trying to get pg_ident to map "user1" and "user1(at)A(dot)DOMAIN(dot)TLD" to "user1" in postgres, or
> vice versa. I'm not picky about which way works.
>
> Kerberos authentication works. I've gotten "user1" to login successfully with a Kerberos ticket,
> but I'm not able to get "user1(at)A(dot)DOMAIN(dot)TLD" to match.
>
> Environment:
> * PostgreSQL 9.6 from PostgreSQL repos
> * CentOS 7
> * FreeIPA for Kerberos, LDAP, etc.
> * Realm A.DOMAIN.TLD
> * "user1" database exists
> * "user1" role exists
> * Logging into CentOS usernames are configured to drop the domain, so they appear as "user1"
> rather then "user1(at)a(dot)domain(dot)tld".
>
>
> pg_hba.conf:
>
> local all postgres peer
> host all all 127.0.0.1/32 <http://127.0.0.1/32> md5
> host all all ::1/128 md5
> host all all 192.168.1.0/24 <http://192.168.1.0/24> gss include_realm=1
> map=testnet krb_realm=A.DOMAIN.TLD #This is on one line. Thunderbird is truncating lines.
>
>
> pg_ident.conf:
>
> testnet /^([0-9A-Za-z_-]+)@A\.DOMAIN\.TLD$ \1
> testnet /^([0-9A-Za-z_-]+)$ \1
>
>
> Regex that works for both in regexr.com <http://regexr.com>:
>
> /^([0-9A-Za-z-_]+)(@A\.DOMAIN\.TLD)?$/gm
>
>
> Command and lines from pg_log:
>
> $ psql -h db0 # Logged in as user1 with Kerberos ticket
>
> < 2017-09-09 19:50:49.376 CDT - 192.168.1.201 [unknown] > LOG: connection received:
> host=192.168.1.201 port=44918
> < 2017-09-09 19:50:49.398 CDT - 192.168.1.201 user1 > LOG: connection authorized: user=user1
> database=user1
> < 2017-09-09 19:50:50.912 CDT - 192.168.1.201 user1 > LOG: disconnection: session time:
> 0:00:01.537 user=user1 database=user1 host=192.168.1.201 port=44918
>
> $ psql -h db0 -U user1(at)A(dot)DOMAIN(dot)TLD # Logged in as user1 with Kerberos ticket
>
> < 2017-09-09 19:50:54.959 CDT - 192.168.1.201 [unknown] > LOG: connection received:
> host=192.168.1.201 port=44920
> < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 user1(at)A(dot)DOMAIN(dot)TLD > LOG: no match in usermap
> "testnet" for user "user1(at)A(dot)DOMAIN(dot)TLD" authenticated as "user1(at)A(dot)DOMAIN(dot)TLD"
> < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 user1(at)A(dot)DOMAIN(dot)TLD > FATAL: GSSAPI authentication
> failed for user "user1(at)A(dot)DOMAIN(dot)TLD"
> < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 user1(at)A(dot)DOMAIN(dot)TLD > DETAIL: Connection matched
> pg_hba.conf line 87: "host all
> all 192.168.1.0/24 <http://192.168.1.0/24> gss include_realm=1 map=testnet
> krb_realm=A.DOMAIN.TLD"
>
>
> Is this something that is possible, or is it something where I need to pick one way to do it?
>
>
> This looks like you are trying to connect with the actual username user1¡A.DOMAIN.TLD. pg_ident only
> sets what you are allowed to log in as, not what it will attempt.
>
> If you are using psql, you are probably doing something like "psql -h myserver". You need to add the
> user, so "psql -h myserver -U user1", to instruct it of which username to actually use for the login.
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/ <http://www.hagander.net/>
> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Hi Magnus,

Yes, the system username is "user1", per the default ipa-client-install SSSD setup, and the map is
working for that. Without the map, I have to specify the full Kerberos username, user(at)DOMAIN(dot)TLD, in
the psql command.

Works with map:

$ psql -h db0 #Implied -U user1 -d user1
$ psql -h db0 -U user1 -d user1

Does not work with map:

$ psql -h db0 -U user1(at)A(dot)DOMAIN(dot)TLD -d user1

Works without map (provided I have a role created):

$ psql -h db0 -U user1(at)A(dot)DOMAIN(dot)TLD -d user1

Does not work without map:

$ psql -h db0 #Implied -U user1 -d user1
$ psql -h db0 -U user1 -d user1

I can get one style or the other to work, but I just can't get both to work a the same time.

If this is something that can't be done, I understand, but it looks like it should be possible per
the documentation.

Thanks,
Ryan