Pivotal Knowledge Base

Follow

How to provide Single Sign-On to the Greenplum Database with Microsoft Active Directory

Environment

Product Version

Pivotal Greenplum (GPDB)

4.3.x

OS RHEL 6.x

Purpose

This article provides an example of how to provide Single Sign-On to the Greenplum Database for Microsoft Active Directory users. Single Sign-On is based on Kerberos tickets being recognized by the Greenplum master. It requires the client to work with Single Sign-On as well.  Windows applications like PGadminIII and ODBC work with Active Directory, but the Greenplum clients like gpload, require MIT Kerberos installed on the workstation, see the the related article here

Procedure

The Greenplum master requires a Kerberos login to Active Directory (AD).  This can be created as a Managed Service Account in AD, but you will need to add the Service Principal Name (SPN) attribute because that is what the MIT Kerberos utilities look for. Also, as Greenplum database have unattended startups, you must also provide the account login details in a keytab file.

An Active Directory administrator will be needed to complete this procedure as setting the SPN and creating the keytab require administrative permissions.

Before you start, you must confirm the fully qualified hostname of the Greenplum master server.

hostname --fqdn

It should include the domain portion of the name.  If you do not have a domain, you will have to give it one.

You must also confirm that the Greenplum database cluster has the same date and time as the Active Directory Domain.  You could set your NTP time source to be an AD Domain Controller, or perhaps the same external time source that the AD is using.

Active Directory Setup

You should implement a naming convention for accounts that support multiple Greenplum clusters.  In this example, we will create a new Managed Service Account svcPostresProd1 for our prod1 greenplum master server.  

The Active Directory domain is emea.local.

The greenplum master hostname is prod1.emea.local.

We will add the SPN postgres/prod1.emea.local to this account. Service accounts for other greenplum clusters will all be in the form postgres/fully.qualified.hostname.

The account password will only ever be used when creating the keytab file and there is no requirement to provide it to a database administrator. For this reason, it is set to never expire and cannot be changed by the user.

  

 

An administrator must then add the Service Principal Name attribute to the account from the command line:

setspn -A postgres/prod1.emea.local svcPostgresProd1

This SPN is now visible if Advanced Features are set in the Active Directory Users and Computers view. Find servicePrincipalName in the Attribute Editor tab and edit it if you need to make changes.

The next step is to create a keytab file. You can select a specific cryptography method if your security requirements require it, but in the absence of that, it is best to get it to work first and then remove any cryptography methods you do not want.

To list the cryptography systems that your AD supports use the following:

ktpass /? 

The following ktpass command will create:

a keytab: svcPostgresProd1.keytab
for the ServicePrincipalName: postgres/prod1.emea.local@EMEA.LOCAL
for the AD user: svcPostgresProd1
with the crypto methods: ALL available on AD.
for the Principal Type: KRB5_NT_PRINCIPAL

Note that the AD domain is appended to the SPN.

ktpass -out svcPostgresProd1.keytab -princ postgres/prod1.emea.local@EMEA.LOCAL -mapUser svcPostgresProd1 -pass your_secret_password -crypto all -ptype KRB5_NT_PRINCIPAL

This -out file will need to be copied to the Greenplum master.

As an alternative to running ktpass as a Domain Administrator, if you have the JAVA JRE installed on your desktop, you can run the Java ktab.exe utility to generate a keytab.  If you require AES256-CTS-HMAC-SHA1-96 encryption, you will need to download the "Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files for JDK/JRE" from Oracle. Be aware that when you enter the password, it will be visible on the screen, as it is also visible in the ktpass command ling arguments.

"c:\Program Files\Java\jre1.8.0_77\bin\ktab.exe" -a svcPostgresprod1 -k svcPostgresProd1.keytab
Password for svcPostgresprod1@EMEA.LOCAL:you_secret_password_here_is_visible
Done!
Service key for svcPostgresprod1 is saved in svcPostgresProd1.keytab

Greenplum Setup

If you do not have the Kerberos workstation utilities installed, you should do that with the following:

yum install krb5-workstation

Then update /etc/krb5.conf and configure it with your AD domain name details and the location of an AD Domain Controller.  

An example is shown below:

[logging]
default = FILE:/var/log/krb5libs.log
kdc = FILE:/var/log/krb5kdc.log
admin_server = FILE:/var/log/kadmind.log

[libdefaults]
default_realm = EMEA.LOCAL
dns_lookup_realm = false
dns_lookup_kdc = false
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable = true

[realms]
EMEA.LOCAL = {
kdc = bocdc.emea.local
admin_server = bocdc.emea.local
}

[domain_realm]
.emea.local = EMEA.LOCAL
example.com = EMEA.LOCAL

Copy the keytab file to the Greenplum master directory:

mv svcPostgresProd1.keytab $MASTER_DATA_DIRECTORY
chown gpadmin:gpadmin $MASTER_DATA_DIRECTORY/svcPostgresProd1.keytab
chmod 600 $MASTER_DATA_DIRECTORY/svcPostgresProd1.keytab

Update pg_hba.conf to add the following as the last line in the file.  This is a "catch-all" so that any connection attempt not already matched will be matched with Active Directory for authentication.

host all all 0.0.0.0/0 gss include_realm=0

Update postgresql.conf to provide the location details for the keytab file and the principal name to use. The fully qualified hostname and the default realm from /etc/krb5.conf will then form the full service principal name.

krb_server_keyfile = '/data/master/gpseg-1/svcPostgresProd1.keytab'
krb_srvname = 'postgres'

Create a database role for a user in AD.

psql
create role dev1 with login superuser;

Restart the database:

gpstop -M fast
gpstart

Note: The Greenplum libraries may conflict with the MIT Kerberos workstation utilities like kinit. If you are using these utilities on a Greenplum master, you can either run a gpadmin shell that does not source the $GPHOME/greenplum_path.sh script, or do something like the following:

unset LD_LIBRARY_PATH

kinit

source $GPHOME/greenplum_path.sh

Confirm Kerberos access:

kinit dev1
psql -h prod1.emea.local -U dev1

Single Sign-On example

This is an example of a Windows desktop logged in as the user, dev1, to the AD domain.  

For pgAdminIII to connect with Single Sign-On, you must specify the fully qualified hostname when completing the new server registration.  

Note that pgAdmin requires you to provide a Username. Also, it will prompt for a password when you reconnect to the database. You do not need to supply a password, just leave the field blank. 

 

 Aginity Workbench for Pivotal Greenplum also supports it as "Integrated Security".

 

Using the latest Postgres psqlodbc drivers, you can configure an ODBC source without specifying a username/password.  This DSN can then be used by applications.

 

 An example using the R client is shown here:

library("RODBC")
conn <- odbcDriverConnect("testdata")
sql <- "select * from public.data1"
my_data <- sqlQuery(conn,sql)
print(my_data)

Troubleshooting

Kerberos tickets contain a version number that must match the version number held in Active Directory.

To confirm the version number in your keytab, use the following:

klist -ket svcPostgresProd1.keytab

To get the corresponding value from AD, you will need your AD Administrator to provide the following:

kvno postgres/prod1.emea.local@EMEA.LOCAL

Some errors that can occur are shown here:

  • Here, a user dev22 is attempting to login from a Windows desktop that is logged in as a different user.
2016-03-29 14:30:54.041151 PDT,"dev22","gpadmin",p15370,th2040321824,"172.28.9.181","49283",2016-03-29 14:30:53 PDT,1917,con32,,seg-1,,,x1917,sx1,"FATAL","28000","authentication failed for user ""dev22"": valid until timestamp expired",,,,,,,0,,"auth.c",628,
  • It can also occur when the user can be authenticated, but does not have a database user role. 
  • This error can occur when the postgres keytab does not contain a matching cryptographic type to a client attempting a connection.  
psql -h `hostname` template1
psql: GSSAPI continuation error: Unspecified GSS failure. Minor code may provide more information
GSSAPI continuation error: Key version is not available

The resolution is to add the additional encryption types to the keytab using ktutil or recreating the postgres keytab with all crypto systems from AD. 

Comments

Powered by Zendesk