Re: pg_ident mapping Kerberos Usernames

On 09/09/2017 09:28 PM, rob stone wrote:
>
>
> On Sat, 2017-09-09 at 20:44 -0500, 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 md5
>> host all all ::1/128 md5
>> host all all 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:
>>
>> /^([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 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?
>>
>> Thanks in advance,
>> Ryan
>>
>>
>
>
> Hello,
> I think you need a line in your pg_hba.conf file along the lines of:-
>
> testnet all all 192.168.1.0/24 gss
>
> as the error message says it can't find this relationship.
>
> HTH,
> Rob
>
>

Hi Rob,

How would that work? I was under the impression the first column was for socket type and limited to
local, host, hostssl, and hostnossl?

Thunderbird's config has been fixed, so here is the line from pg_hba.conf line without the
formatting issues:

host all all 192.168.1.0/24 gss include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD

Thanks,
Ryan