Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain

I think setting up PAM authentication with AD on Linux server joined to
domain via realm SSSD was much easier and transparent.

Something like this worked for me to create SPN mapping and keytab in one
command without need to use UPPERCASE for POSTGRES:
ktpass -out postgres.keytab -princ POSTGRES/UBUNTU(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)COM
-mapUser AD\POSTGRES -pass 'thepassword' -mapOp add -crypto ALL -ptype
KRB5_NT_PRINCIPAL

pg_hba.conf
host all all 0.0.0.0/0 gss gss include_realm=0 krb_realm=AD.CORP.COM
ktb_realm should not be needed since you have one in your krb5.conf

postgresql.conf
krb_server_keyfile = '/etc/postgresql/9.6/main/postgres.keytab'
#krb_caseins_users = off

kinit ubuntupg(at)AD(dot)CORP(dot)COM
psql.exe -h 192.168.1.143 -U ubuntupg

klist
Ticket cache: FILE:/tmp/krb5cc_0
Default principal: ubuntupg(at)AD(dot)CORP(dot)COM

Valid starting Expires Service principal
08/03/2018 22:28:47 08/04/2018 08:28:47 krbtgt/AD(dot)CORP(dot)COM(at)AD(dot)CORP(dot)COM
renew until 08/10/2018 22:28:42
08/03/2018 22:29:00 08/04/2018 08:28:47 POSTGRES/
UBUNTU(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)COM
renew until 08/10/2018 22:28:42

On Thu, Feb 28, 2019 at 2:54 PM Jean-Philippe Chenel <jp(dot)chenel(at)live(dot)ca>
wrote:

> I'm trying to configure authentication between PostgreSQL database server
> on linux and Windows Active Directory.
>
> *First part of configuration is working but when I'm trying to
> authenticate from Windows client, it is not working with message: Can't
> obtain database list from the server. SSPI continuation error. The
> specified target is unknown or unreachable (80090303)*
>
> *On Windows:*
>
> Domain is AD.CORP.COM
>
> Host is: WIN.AD.CORP.COM, IP is 192.168.1.173
>
> *On Linux (Ubuntu 16.04)*
>
> hostname is UBUNTU.ad.corp.com, IP is 192.168.1.143
>
> DNS are configured to reach the AD sytem (.173)
>
> PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu 9.6.9-2.pgdg16.04+1),
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
>
> I've created à service user called POSTGRES and a normal user in AD called
> ubuntupg.
>
> Finally I've created the SPN:
>
> setspn -A POSTGRES/UBUNTU.ad.corp.com POSTGRES
>
> Generated the keytab to put on the linux server:
>
> ktpass -out postgres.keytab -princ POSTGRES/UBUNTU(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)COM -mapUser POSTGRES -pass 'thepassword' -crypto all -ptype KRB5_NT_PRINCIPAL
>
> On the linux /etc/krb5.conf:
>
> [libdefaults]
> debug=true
> default_realm = AD.CORP.COM
> dns_lookup_realm = false
> dns_lookup_kdc = false
> ticket_lifetime = 24h
> renew_lifetime = 7d
> forwardable = true
>
> [realms]
> AD.CORP.COM = {
> kdc = WIN.AD.CORP.COM
> }
>
> [domain_realm]
> ad.corp.com = AD.CORP.COM
> .ad.corp.com = AD.CORP.COM
>
> Making this command work and klist return a ticket:
>
> kinit -V -k -t /etc/postgresql/9.6/main/postgres.keytab POSTGRES/UBUNTU(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)COM
>
> klist -k /etc/postgresql/9.6/main/postgres.keytab
>
> POSTGRES/UBUNTU(dot)ad(dot)corp(dot)com(at)AD(dot)CORP(dot)COM
>
> Here is the added onfiguration to postgresql.conf
>
> krb_server_keyfile = '/etc/postgresql/9.6/main/postgres.keytab'
>
> Here is the configuration of pg_hba.conf
>
> host all all 0.0.0.0/0 gss
>
> Up to here, all is working as expected, kinit with ubuntupg is also
> working well. ubuntupg and ubuntupg(at)ad(dot)corp(dot)com is also created on the
> database. The probleme is when I try, from a Windows client, connecting to
> the DB.
>
> psql.exe -h 192.168.1.143 -U ubuntupg
>
> *Can't obtain database list from the server. SSPI continuation error. The
> specified target is unknown or unreachable (80090303)*
>
> PostgreSQL log file show:
>
> 2019-02-28 14:02:54.178 EST [6747] [unknown](at)[unknown] LOG: 00000: connection received: host=192.168.1.176 port=57254
> 2019-02-28 14:02:54.178 EST [6747] [unknown](at)[unknown] LOCATION: BackendInitialize, postmaster.c:4188
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg(at)ubuntupg FATAL: 28000: GSSAPI authentication failed for user "ubuntupg"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg(at)ubuntupg DETAIL: Connection matched pg_hba.conf line 92: "host all all 0.0.0.0/0 gss"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg(at)ubuntupg LOCATION: auth_failed, auth.c:307
>
> psql.exe -h 192.168.1.143 -U ubuntupg(at)ad(dot)corp(dot)com
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown](at)[unknown] LOG: 00000: connection received: host=192.168.1.176 port=57282
> 2019-02-28 14:06:35.992 EST [6866] [unknown](at)[unknown] LOCATION: BackendInitialize, postmaster.c:4188
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg(at)ad(dot)corp(dot)com@ubuntupg(at)ad(dot)corp(dot)com FATAL: 28000: GSSAPI authentication failed for user "ubuntupg(at)ad(dot)corp(dot)com"
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg(at)ad(dot)corp(dot)com@ubuntupg(at)ad(dot)corp(dot)com DETAIL: Connection matched pg_hba.conf line 96: "host all all 0.0.0.0/0 gss"
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg(at)ad(dot)corp(dot)com@ubuntupg(at)ad(dot)corp(dot)com LOCATION: auth_failed, auth.c:307
>
> Thank you very much for your help.
>
> Best regards,
>

--