postgresqlenterprisedb

LOG: connection failed during start up processing: user= database= FATAL: GSSAPI authentication failed for user "postgres"


I am trying to configure Kerberos for GSSAPI Currently I have two nodes One the KDC server (windows server 2016) and the the other is Postgres-server(Ubuntu). I have created Active directory on in kdc-server and create user with the name of postgres and selected the option "password will never expire".

Then I have installed a kerbrose client of MIT. here is krb5.ini on kdc server.

[libdefaults]
    default_realm = HIGHGO.CA

# The following krb5.conf variables are only for MIT Kerberos.
    kdc_timesync = 1
    ccache_type = 4
    forwardable = true
    proxiable = true

# The following encryption type specification will be used by MIT Kerberos
# if uncommented.  In general, the defaults in the MIT Kerberos code are
# correct and overriding these specifications only serves to disable new
# encryption types as they are added, creating interoperability problems.
#
# The only time when you might need to uncomment these lines and change
# the enctypes is if you have local software that will break on ticket
# caches containing ticket encryption types it doesn't know about (such as
# old versions of Sun Java).

#   default_tgs_enctypes = des3-hmac-sha1
#   default_tkt_enctypes = des3-hmac-sha1
#   permitted_enctypes = des3-hmac-sha1

# The following libdefaults parameters are only for Heimdal Kerberos.
    fcc-mit-ticketflags = true

[realms]
    HIGHGO.CA = {
        kdc = kdc.highgo.ca
        admin_server = kdc.highgo.ca
    }
    ATHENA.MIT.EDU = {
        kdc = kerberos.mit.edu
        kdc = kerberos-1.mit.edu
        kdc = kerberos-2.mit.edu:88
        admin_server = kerberos.mit.edu
        default_domain = mit.edu
    }
    ZONE.MIT.EDU = {
        kdc = casio.mit.edu
        kdc = seiko.mit.edu
        admin_server = casio.mit.edu
    }
    CSAIL.MIT.EDU = {
        admin_server = kerberos.csail.mit.edu
        default_domain = csail.mit.edu
    }
    IHTFP.ORG = {
        kdc = kerberos.ihtfp.org
        admin_server = kerberos.ihtfp.org
    }
    1TS.ORG = {
        kdc = kerberos.1ts.org
        admin_server = kerberos.1ts.org
    }
    ANDREW.CMU.EDU = {
        admin_server = kerberos.andrew.cmu.edu
        default_domain = andrew.cmu.edu
    }
        CS.CMU.EDU = {
                kdc = kerberos-1.srv.cs.cmu.edu
                kdc = kerberos-2.srv.cs.cmu.edu
                kdc = kerberos-3.srv.cs.cmu.edu
                admin_server = kerberos.cs.cmu.edu
        }
    DEMENTIA.ORG = {
        kdc = kerberos.dementix.org
        kdc = kerberos2.dementix.org
        admin_server = kerberos.dementix.org
    }
    stanford.edu = {
        kdc = krb5auth1.stanford.edu
        kdc = krb5auth2.stanford.edu
        kdc = krb5auth3.stanford.edu
        master_kdc = krb5auth1.stanford.edu
        admin_server = krb5-admin.stanford.edu
        default_domain = stanford.edu
    }
        UTORONTO.CA = {
                kdc = kerberos1.utoronto.ca
                kdc = kerberos2.utoronto.ca
                kdc = kerberos3.utoronto.ca
                admin_server = kerberos1.utoronto.ca
                default_domain = utoronto.ca
    }

[domain_realm]
    .mit.edu = ATHENA.MIT.EDU
    mit.edu = ATHENA.MIT.EDU
    .media.mit.edu = MEDIA-LAB.MIT.EDU
    media.mit.edu = MEDIA-LAB.MIT.EDU
    .csail.mit.edu = CSAIL.MIT.EDU
    csail.mit.edu = CSAIL.MIT.EDU
    .whoi.edu = ATHENA.MIT.EDU
    whoi.edu = ATHENA.MIT.EDU
    .stanford.edu = stanford.edu
    .slac.stanford.edu = SLAC.STANFORD.EDU
        .toronto.edu = UTORONTO.CA
        .utoronto.ca = UTORONTO.CA

created principle

setspn -A postgres/pg.highgo.ca@HIGHGO.CA postgres

after creating principle I have tested it with the following command

c:\Users\administrator\Desktop>kinit postgres
Password for postgres@HIGHGO.CA:

which is working fine.

that's how I have created key tab

ktpass -out pgkt.keytab -princ postgres/pg.highgo.ca@HIGHGI.CA
-mapUser enterprisedb -pass Casper@12 -crypto all -ptype KRB5_NT_PRINCIPAL

and cpoy this file in postgres server and replace it with the file /etc/krb5.keytab with following permission.

chmod 600 /etc/krb5.keytab

and here is my /etc/host entries on linux and windows.

192.168.100.112 pg.highgo.ca
192.168.100.114 kdc.highgo.ca

and I have put an entery in postgress.conf.

krb_server_keyfile = '/etc/krb5.keytab'

and here is pg_hba.conf entries.

 host     all             all              0.0.0.0/0               gss include_realm=0

after that I have tried to access postgress server with the following command .

psql -U postgres -d postgress -h 192.168.100.114

in responce I got the following error on windows.

psql: error: could not connect to server: SSPI continuation error: The specified target is unknown or unreachable
 (80090303)

and seen the logs on posgtes.

2020-08-18 05:49:36.534 PDT [5086] [unknown]@[unknown] LOG:  connection failed during start up processing: user= database=
2020-08-18 05:49:36.541 PDT [5087] postgres@postgres FATAL:  GSSAPI authentication failed for user "postgress"
2020-08-18 05:49:36.541 PDT [5087] postgres@postgres DETAIL:  Connection matched pg_hba.conf line 97: "host     all             all              0.0.0.0/0               gss include_realm=0 "

I have checked the lots of tutorials but did not get a chance to resolve it.

(Note : same commands works fine with MD5 authentication )

thanks advance.


Solution

  • I have resolve it with the help of my Colleagues this is done on the fresh environment. Steps:

    (Note : there no need of kerbrose client on the PG-Server machien (mine is Ubuntu 18.xx))

    1. Active Directory is setup on Windows 2016 MYDOMAIN.CA and EPAS Server 11 or 12 is installed on both machines. Active Directory Setup Link
    2. Make sure time zone and time on both machines are the same.
    192.168.100.19 client.mydomain.ca client
    192.168.100.17 pg.mydomain.ca  pg
    
    192.168.100.19 client.mydomain.ca
    192.168.100.17 pg.mydomain.ca
    

    Verify the host are communicating with the ping.

    Create User in Active Directory (Windows Machine)

    Create Keytab

      ktpass -out krb5.keytab -mapUser pguser@MYDOMAIN.CA +rndPass -mapOp set +DumpSalt -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL -princ POSTGRES/pg.mydomain.ca@MYDOMAIN.CA
    

    //Suppose file is on Desktop of user edb on Linux machine. su to become root.

    cd /etc/

    cp /home/edb/Desktop/krb5.keytab .

    chown enterprisedb:enterprisedb krb5.keytab

    chmod 600 krb5.keytab

    krb_server_keyfile = '/etc/krb5.keytab'

    local all enterprisedb trust

    host all all 0.0.0.0/0 gss

    CREATE USER "pg1postgres@HIGHGO.CA" SUPERUSER CREATEDB CREATEROLE;

    PSQL command from Windows

    Issue this command to connect to D on Linux

    psql -U pgUSER@MYDOMAIN.CA -d edb -h pg.mydomain.ca

    Regards,