Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain - Mailing list pgsql-general

Greetings,

* Jean-Philippe Chenel (jp.chenel@live.ca) wrote:
> Thank you very much for your help.
> I think I was missing an important command in the equation.
>
>   sudo realm --verbose join ad.corp.com --user=Administrateur
--user-principal=postgres/UBUNTU.ad.corp.com@AD.CORP.COM
>
> The Linux server as joint the AD and now, psql connection work very well from the clients!

Great!  Glad to hear you got that working.

> It makes a lot of things that I'm not accustomed too, but was very interesting. I'll also look forward to the other
authenticationmethod that you've talked (PAM). 

I'm not sure what the discussion around PAM was getting to, though it
seemed like it was talking about using PAM at the Linux OS level to
allow access, which could be fine.

I will say that using PAM in PostgreSQL for authentication would be
worse from a security standpoint than using GSS since with PAM a
password would be sent from the client to the PG server and would be
visible to the PG server.

Thanks!

Stephen

> ________________________________
> De : Andre Piwoni <apiwoni@webmd.net>
> Envoyé : 1 mars 2019 11:44
> À : Jean-Philippe Chenel
> Cc : pgsql-general@lists.postgresql.org
> Objet : Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain
>
> Hi Philippe,
>
> I would increase logging level to debug since it is hard to tell from log what is happening.
> Have you verified kinit for the user on the server? Sounds like you did since you are running client on the server
> Also, my username in postgres database is lowercase without realm info.
>
> At the high level, here's the setup I had:
>
>   1.  Create AD user account for PostgreSQL UNIX service.
>   2.  Set up identity mapping for Service Principal Name (SPN) to postgres user account.
> Note: Multiple service instances cannot map to the same user account, so user account may be created as
postgres_shortHostName
>   3.  Generate keytab for postgres service principal.
>   4.  Ensure Kerberos configuration file has been created on PostgreSQL server after joining server to AD domain
usingSSSD and realmd utility. 
>   5.  Configure PostgreSQL to use generated keytab file.
>   6.  Configure PostgreSQL host-base authentication to use GSSAPI.
>
> My setup for PAM is using SSSD PAM module and is configured for AD:
>
> cat /etc/pam.d/postgresql
> #%PAM-1.0
> auth            required        pam_sss.so<https://whsconfluence.webmd.net/display/W1DP/pam_sss.so>
> account         required        pam_sss.so<https://whsconfluence.webmd.net/display/W1DP/pam_sss.so>
>
> By joining domain using realm sssd you should have krb5.conf and sssd.conf generated for you automatically. You
shouldremove existing krb5.conf before joining domain. 
>
>
> cat /etc/sssd/sssd.conf
> [sssd]
> domains = a<https://whsconfluence.webmd.net/display/W1DP/dataplatform.aws.webmd.net>d.corp.com<http://d.corp.com>
> config_file_version = 2
> services = nss, pam
>
> [domain/
a<https://whsconfluence.webmd.net/display/W1DP/dataplatform.aws.webmd.net>d.corp.com<http://d.corp.com>]<https://whsconfluence.webmd.net/display/W1DP/.aws.webmd.net]>
> ad_domain = a<https://whsconfluence.webmd.net/display/W1DP/dataplatform.aws.webmd.net>d.corp.com<http://d.corp.com>
> krb5_realm = AD.CORP.COM<mailto:UBUNTU.ad.corp.com@AD.CORP.COM>
> realmd_tags = manages-system joined-with-samba
> cache_credentials = True
> id_provider = ad
> krb5_store_password_if_offline = True
> default_shell = /bin/bash
> ldap_id_mapping = True
> use_fully_qualified_names = False
> fallback_homedir = /home/%u
> access_provider = ad
>
> On Fri, Mar 1, 2019 at 7:59 AM Jean-Philippe Chenel <jp.chenel@live.ca<mailto:jp.chenel@live.ca>> wrote:
> Hi Andre,
> Thank for the followup. Here are the tests and results:
>
> I've deleted and created service user postgres in lower case on the AD, and I've made this command.
> ktpass -out postgres.keytab -princ postgres/UBUNTU.ad.corp.com@AD.CORP.COM<mailto:UBUNTU.ad.corp.com@AD.CORP.COM>
-mapUserAD\postgres -pass 'postgres' -mapOp add -crypto ALL -ptype KRB5_NT_PRINCIPAL 
>
> Changed pg_hba.conf to
> host all all 0.0.0.0/0<http://0.0.0.0/0> gss gss include_realm=0
[http://krb_realm%3Dad.corp.com/]krb_realm=AD.CORP.COM<http://3Dad.corp.com/%5Dkrb_realm=AD.CORP.COM>
>
> kinit is working
> kinit ubuntupg(at)AD(dot)CORP(dot)COM
>
> Ticket cache: FILE:/tmp/krb5cc_0
> Default principal: ubuntupg@AD.CORP.COM<mailto:ubuntupg@AD.CORP.COM>
>
> Valid starting       Expires              Service principal
> 2019-03-01 10:21:50  2019-03-01 20:21:50  krbtgt/AD.CORP.COM@AD.CORP.COM<mailto:AD.CORP.COM@AD.CORP.COM>
>         renew until 2019-03-08 10:21:43
>
> Here are the bad:
> root@UBUNTU:~# psql -h 192.168.20.143 -U ubuntupg
> psql: erreur de suite GSSAPI: Unspecified GSS failure.  Minor code may provide more information
> erreur de suite GSSAPI: No Kerberos credentials available
>
> Postgresql log
> 2019-03-01 09:59:13.890 EST [8913] postgres@postgres LOG:  00000: connection authorized: user=postgres
database=postgres
> 2019-03-01 09:59:13.890 EST [8913] postgres@postgres LOCATION:  PerformAuthentication, postinit.c:272
> 2019-03-01 09:59:18.992 EST [8942] [unknown]@[unknown] LOG:  00000: connection received: host=192.168.20.143
port=40024
> 2019-03-01 09:59:18.992 EST [8942] [unknown]@[unknown] LOCATION:  BackendInitialize, postmaster.c:4188
> 2019-03-01 09:59:19.000 EST [8942] ubuntupg@ubuntupg FATAL:  28000: GSSAPI authentication failed for user "ubuntupg"
> 2019-03-01 09:59:19.000 EST [8942] ubuntupg@ubuntupg DETAIL:  Connection matched pg_hba.conf line 96: "host    all
         all            0.0.0.0/0<http://0.0.0.0/0> gss include_realm=0 krb_realm=AD.CORP.COM<http://AD.CORP.COM>" 
> 2019-03-01 09:59:19.000 EST [8942] ubuntupg@ubuntupg LOCATION:  auth_failed, auth.c:307
>
> User ubuntupg is created on the AD. In postgresql, does it need to have a naming convention? At this moment, i've a
usernamed ubuntupg and also ubuntupg@ad.corp.com<mailto:ubuntupg@ad.corp.com> 
>
> > I think setting up PAM authentication with AD on Linux server joined to
> > domain via realm SSSD was much easier and transparent.
> I don't know this kind of authentication, do you have more information on this? Maybe I can switch authentication
method.
>
> Best regards,
>
>
> ________________________________
> De : Andre Piwoni <apiwoni@webmd.net<mailto:apiwoni@webmd.net>>
> Envoyé : 28 février 2019 20:19
> À : Jean-Philippe Chenel
> Cc : pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
> Objet : 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.ad.corp.com@AD.CORP.COM<mailto:UBUNTU.ad.corp.com@AD.CORP.COM>
-mapUserAD\POSTGRES -pass 'thepassword' -mapOp add -crypto ALL -ptype KRB5_NT_PRINCIPAL 
>
> pg_hba.conf
> host all all 0.0.0.0/0<http://0.0.0.0/0> gss gss include_realm=0 krb_realm=AD.CORP.COM<http://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@AD.CORP.COM<mailto:ubuntupg@AD.CORP.COM>
> psql.exe -h 192.168.1.143 -U ubuntupg
>
> klist
> Ticket cache: FILE:/tmp/krb5cc_0
> Default principal: ubuntupg@AD.CORP.COM<mailto:ubuntupg@AD.CORP.COM>
>
> Valid starting       Expires              Service principal
> 08/03/2018 22:28:47  08/04/2018 08:28:47  krbtgt/AD.CORP.COM@AD.CORP.COM<mailto:AD.CORP.COM@AD.CORP.COM>
>     renew until 08/10/2018 22:28:42
> 08/03/2018 22:29:00  08/04/2018 08:28:47
POSTGRES/UBUNTU.ad.corp.com@AD.CORP.COM<mailto:UBUNTU.ad.corp.com@AD.CORP.COM>
>     renew until 08/10/2018 22:28:42
>
> On Thu, Feb 28, 2019 at 2:54 PM Jean-Philippe Chenel <jp.chenel@live.ca<mailto:jp.chenel@live.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
withmessage: 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<http://AD.CORP.COM>
>
> Host is: WIN.AD.CORP.COM<http://WIN.AD.CORP.COM>, IP is 192.168.1.173
>
> On Linux (Ubuntu 16.04)
>
> hostname is UBUNTU.ad.corp.com<http://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.020160609, 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.ad.corp.com@AD.CORP.COM -mapUser POSTGRES -pass 'thepassword'
-cryptoall -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.ad.corp.com@AD.CORP.COM
>
> klist -k /etc/postgresql/9.6/main/postgres.keytab
>
> POSTGRES/UBUNTU.ad.corp.com@AD.CORP.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@ad.corp.com<mailto:ubuntupg@ad.corp.com>is also created on the database. The probleme is when I try, from a
Windowsclient, 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]@[unknown] LOG:  00000: connection received: host=192.168.1.176
port=57254
> 2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOCATION:  BackendInitialize, postmaster.c:4188
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg FATAL:  28000: GSSAPI authentication failed for user "ubuntupg"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg DETAIL:  Connection matched pg_hba.conf line 92: "host    all
         all            0.0.0.0/0<http://0.0.0.0/0> gss" 
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg LOCATION:  auth_failed, auth.c:307
>
> psql.exe -h 192.168.1.143 -U ubuntupg@ad.corp.com<mailto:ubuntupg@ad.corp.com>
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOG:  00000: connection received: host=192.168.1.176
port=57282
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOCATION:  BackendInitialize, postmaster.c:4188
>
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg@ad.corp.com@ubuntupg@ad.corp.com FATAL:  28000: GSSAPI authentication
failedfor user "ubuntupg@ad.corp.com<mailto:ubuntupg@ad.corp.com>" 
>
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg@ad.corp.com@ubuntupg@ad.corp.com DETAIL:  Connection matched pg_hba.conf
line96: "host    all              all            0.0.0.0/0<http://0.0.0.0/0> gss" 
>
> 2019-02-28 14:06:36.148 EST [6866] ubuntupg@ad.corp.com@ubuntupg@ad.corp.com LOCATION:  auth_failed, auth.c:307
>
> Thank you very much for your help.
>
> Best regards,
>
>
> --
>
>