Home > mailing lists

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

From	Andre Piwoni
Subject	Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain
Date	March 1, 2019 19:44:05
Msg-id	CAEC-+VH5RH9tyeryV_yti46BMzBELa=_h1PHM_n0XzmZsBgaeQ@mail.gmail.com Whole thread Raw
In response to	RE: PostgreSQL (linux) configuration with GSSAPI to a Windows domain (Jean-Philippe Chenel <jp.chenel@live.ca>)
Responses	RE: PostgreSQL (linux) configuration with GSSAPI to a Windows domain (Jean-Philippe Chenel <jp.chenel@live.ca>)
List	pgsql-general

Tree view

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:

Create AD user account for PostgreSQL UNIX service.
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
Generate keytab for postgres service principal.
Ensure Kerberos configuration file has been created on PostgreSQL server after joining server to AD domain using SSSD and realmd utility.
Configure PostgreSQL to use generated keytab file.
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

account         required        pam_sss.so

By joining domain using realm sssd you should have krb5.conf and sssd.conf generated for you automatically. You should remove existing krb5.conf before joining domain.

cat /etc/sssd/sssd.conf

[sssd]

domains = ad.corp.com

config_file_version = 2

services = nss, pam

[domain/ ad.corp.com]

ad_domain = ad.corp.com

krb5_realm = 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> 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 -mapUser AD\postgres -pass 'postgres' -mapOp add -crypto ALL -ptype KRB5_NT_PRINCIPAL

Changed pg_hba.conf to
host all all 0.0.0.0/0 gss gss include_realm=0 [http://krb_realm%3Dad.corp.com/]krb_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

Valid starting Expires Service principal
2019-03-01 10:21:50 2019-03-01 20:21:50 krbtgt/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 gss include_realm=0 krb_realm=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 user named ubuntupg and also 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>
Envoyé : 28 février 2019 20:19
À : Jean-Philippe Chenel
Cc : 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 -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@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

Valid starting Expires Service principal
08/03/2018 22:28:47 08/04/2018 08:28:47 krbtgt/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
renew until 08/10/2018 22:28:42
On Thu, Feb 28, 2019 at 2:54 PM Jean-Philippe Chenel <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 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.ad.corp.com@AD.CORP.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.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 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]@[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 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
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 failed for user "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 line 96: "host    all              all            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,
--

pgsql-general by date:

From: Ray O'Donnell
Date: 01 March 2019, 19:01:57
Subject: Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

From: Stephen Frost
Date: 01 March 2019, 19:54:49
Subject: Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain

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

Previous

Next