Pivotal Knowledge Base

Follow

How to configure user authentication using kerberos on HAWQ database?

This article will list the steps required to configure user authentication based on kerberos for accessing HAWQ database. 

Environment

- PHD 1.1.1
- HAWQ 1.1.4

Prerequisite:

-Secure HDFS
-Secure HAWQ over secure HDFS

Background: Authentication to HAWQ database is controlled using a configuration file named pg_hba.conf which is located under the data directory of HAWQ master. Ex: (/data/master/gpseg-1/pg_hba.conf). HAWQ administrator can configure pg_hba.conf to force some users to prove their identity using kerberos and at the same time allow full access to some users, but remember the file is read from top to bottom, the first entry matching the incoming request is picked up and associated authentication is performed. For example, if pg_hba.conf has entries like below, the entry with trust authentication type will be picked whenever foo user attempts to login using database hostname.

host all foo 0.0.0.0/0 trust
host all foo 0.0.0.0/0 restrict

Note: For more details, please refer to postgresql documentation @Postgresql 8.2 - The pg_hba.conf file

In order to configure HAWQ to authenticate a user using kerberos, the below key points must be performed.

- HAWQ master server kerberos keytab file must have the principal name of the user which needs to be authenticated
- There should be a database user and a corresponding OS user.
- A principal must exist in the KDC database for the user

Let's list down all the steps in details. Just for reference, Hostname and their roles as used in examples below:

- phd11-nn: Client node from where user will try to login using psql
- phd11-admin: KDC node
- phd11-client: HAWQ master server

Step 1: Create a OS user on the client node from where user will attempt to login to HAWQ.

[root@phd11-nn ~]# useradd foo
[root@phd11-nn ~]# passwd foo
Changing password for user foo.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

Step 2: Create a regular database user by logging on to HAWQ database.

gpdb=# create role foo with LOGIN RESOURCE QUEUE pg_default;
CREATE ROLE

Step 3: Add a principal to the KDC server corresponding to the HAWQ database user created above

[root@phd11-admin ~]# kadmin.local
kadmin.local:  addprinc -randkey foo@SATURN.LOCAL
WARNING: no policy specified for foo@SATURN.LOCAL; defaulting to no policy
Principal "foo@SATURN.LOCAL" created.

Step 4: Set the password for the user at KDC

[root@phd11-admin ~]# kadmin.local
cpw foo@SATURN.LOCAL
Enter password for principal "foo@SATURN.LOCAL":
Re-enter password for principal "foo@SATURN.LOCAL":
Password for "foo@SATURN.LOCAL" changed.

Step 5: Add keytab entries for principal foo@SATURN.LOCAL to HAWQ master server keytab file

Note: In this example, hawq.service.keytab is the key tab file used by HAWQ database server, krb_server_keyfile points to the keytab file and is listed in /data/hawq/master/gpseg-1/postgresql.conf. Any user configured for kerberos authentication must have it's principal added to the hawq.service.keytab file.

[root@phd11-admin keytab]# kadmin.local
Authenticating as principal root/admin@SATURN.LOCAL with password.
kadmin.local:  ktadd -k /etc/security/keytab/hawq.service.keytab foo@SATURN.LOCAL
Entry for principal foo@SATURN.LOCAL with kvno 2, encryption type aes256-cts-hmac-sha1-96 added to keytab WRFILE:/etc/security/keytab/hawq.service.keytab.
Entry for principal foo@SATURN.LOCAL with kvno 2, encryption type aes128-cts-hmac-sha1-96 added to keytab WRFILE:/etc/security/keytab/hawq.service.keytab.
Entry for principal foo@SATURN.LOCAL with kvno 2, encryption type des3-cbc-sha1 added to keytab WRFILE:/etc/security/keytab/hawq.service.keytab.
Entry for principal foo@SATURN.LOCAL with kvno 2, encryption type arcfour-hmac added to keytab WRFILE:/etc/security/keytab/hawq.service.keytab.

Step 6: Verify if postgres principal of the form postgres/hostname_fqdn@REALM is present in the keytab used by HAWQ master server

[root@phd11-admin keytab]# klist -ket /etc/security/keytab/hawq.service.keytab
   2 03/17/14 22:52:45 postgres/phd11-client.saturn.local@SATURN.LOCAL (aes256-cts-hmac-sha1-96)
   2 03/17/14 22:52:45 postgres/phd11-client.saturn.local@SATURN.LOCAL (aes128-cts-hmac-sha1-96)
   2 03/17/14 22:52:45 postgres/phd11-client.saturn.local@SATURN.LOCAL (des3-cbc-sha1)
   2 03/17/14 22:52:45 postgres/phd11-client.saturn.local@SATURN.LOCAL (arcfour-hmac)

Note: If postgres/phd11-client.saturn.local@SATURN.LOCAL is not there, please add the principal for it.
[root@phd11-admin keytab]# kadmin.local
kadmin.local:  addprinc -randkey postgres/phd11-client.saturn.local@SATURN.LOCAL
WARNING: no policy specified for postgres/phd11-client.saturn.local@SATURN.LOCAL; defaulting to no policy
Principal "postgres/phd11-client.saturn.local@SATURN.LOCAL" created.
kadmin.local:  ktadd -k /etc/security/keytab/hawq.service.keytab postgres/phd11-client.saturn.local@SATURN.LOCAL
Entry for principal postgres/phd11-client.saturn.local@SATURN.LOCAL with kvno 2, encryption type aes256-cts-hmac-sha1-96 added to keytab WRFILE:/etc/security/keytab/hawq.service.keytab.
Entry for principal postgres/phd11-client.saturn.local@SATURN.LOCAL with kvno 2, encryption type aes128-cts-hmac-sha1-96 added to keytab WRFILE:/etc/security/keytab/hawq.service.keytab.
Entry for principal postgres/phd11-client.saturn.local@SATURN.LOCAL with kvno 2, encryption type des3-cbc-sha1 added to keytab WRFILE:/etc/security/keytab/hawq.service.keytab.
Entry for principal postgres/phd11-client.saturn.local@SATURN.LOCAL with kvno 2, encryption type arcfour-hmac added to keytab WRFILE:/etc/security/keytab/hawq.service.keytab.

Step 7: Verify all the required principal are there in the hawq.service.keytab file required.

- postgres@SATURN.LOCAL - postgres is default service name configured during package build time (Mandatory)
- postgres/phd11-client.saturn.local@SATURN.LOCAL: Principal of the form postgres/hawqmaster_fqdn@REALM (Mandatory)
- foo@SATURN.LOCAL - Principal for the database user
[root@phd11-admin keytab]# klist -ket /etc/security/keytab/hawq.service.keytab
Keytab name: FILE:/etc/security/keytab/hawq.service.keytab
KVNO Timestamp         Principal
---- ----------------- --------------------------------------------------------
   2 03/01/14 15:00:19 postgres@SATURN.LOCAL (aes256-cts-hmac-sha1-96)
   2 03/01/14 15:00:19 postgres@SATURN.LOCAL (aes128-cts-hmac-sha1-96)
   2 03/01/14 15:00:19 postgres@SATURN.LOCAL (des3-cbc-sha1)
   2 03/01/14 15:00:19 postgres@SATURN.LOCAL (arcfour-hmac)
   2 03/17/14 22:22:10 foo@SATURN.LOCAL (aes256-cts-hmac-sha1-96)
   2 03/17/14 22:22:10 foo@SATURN.LOCAL (aes128-cts-hmac-sha1-96)
   2 03/17/14 22:22:10 foo@SATURN.LOCAL (des3-cbc-sha1)
   2 03/17/14 22:22:10 foo@SATURN.LOCAL (arcfour-hmac)
   2 03/17/14 22:52:45 postgres/phd11-client.saturn.local@SATURN.LOCAL (aes256-cts-hmac-sha1-96)
   2 03/17/14 22:52:45 postgres/phd11-client.saturn.local@SATURN.LOCAL (aes128-cts-hmac-sha1-96)
   2 03/17/14 22:52:45 postgres/phd11-client.saturn.local@SATURN.LOCAL (des3-cbc-sha1)
   2 03/17/14 22:52:45 postgres/phd11-client.saturn.local@SATURN.LOCAL (arcfour-hmac)

Step 8: Copy /etc/security/keytab/hawq.service.keytab file to HAWQ master server and ensure that it's owned by gpadmin user and has read permission only for gpadmin.

[root@phd11-client keytab]$ chown gpadmin:gpadmin hawq.service.keytab
[root@phd11-client keytab]$ chown 400 hawq.service.keytab 

Step 9: Make changes in pg_hba.conf file under HAWQ master data directory. pg_hba.conf file is tricky, please understand how entries get priorities and how does it behave. If not setup properly, you may end up into issues.

vi /data/hawq/master/gpseg-1/pg_hba.conf 
host all foo 0.0.0.0/0 gss include_realm=0 krb_realm=SATURN.LOCAL where: host = Connection attempts made using TCP/IP, (Example psql -h hawq-master-hostname) all = Applies to all the database (You can replace "all" with the list of database against which you want kerberos authentication to be applied) foo = Name of the user for which kerberos authentication is being setup gss/krb5 = It indicates to use GSSAPI/krb5 to authenticate the user. include_realm = If set to 1, the realm name from the authenticated user principal is included in the system user name that's passed through user name mapping. This is useful for handling users from multiple realms. If 0, the realm name is not included. krb_realm = Sets the realm to match user principal names against. If this parameter is set, only users of that realm will be accepted. If it is not set, users of any realm can connect, subject to whatever user name mapping is done.

Step 10: Reload pg_hba.conf configuration file changes

[gpadmin@phd11-client keytab]$ gpstop -u

Step 11: Use the client node to test the setup.

[gpadmin@phd11-nn ~]$ su - foo
Password:
[foo@phd11-nn ~]$ kinit
Password for foo@SATURN.LOCAL:
[foo@phd11-nn ~]$ psql -h phd11-client.saturn.local template1 -U foo
template1=>

Some common errors:

1) Unable to create a table.

gpdb=# create table test13s23 (a int);
WARNING: failed to login to Kerberos, command line: kinit -k -t /etc/security/keytab/hawq.service.keytab -c /tmp/postgres.ccname postgres
WARNING: failed to login to Kerberos, command line: kinit -k -t /etc/security/keytab/hawq.service.keytab -c /tmp/postgres.ccname postgres

Debug / Solution: Ensure that there is an entry with principal postgres@REALM in the HAWQ master server keytab file and you can do a kinit using that. If not regenerate the keytab file.
Also, you can check if /tmp/postgres.ccname file is available, if not you can set krb5_ccname=/tmp/postgres.ccname in postgresql.conf and restart the database.

Note:

2) Unable to connect to the database.

Case 1: Due to missing principals in HAWQ master server keytab file

a) When using gss authentication in pg_hba.conf
[foo@phd11-nn ~]$ psql -h phd11-client -d gpadmin
psql: FATAL: accepting GSS security context failed (auth.c:1130)
DETAIL: Unspecified GSS failure. Minor code may provide more information: Wrong principal in request
b) When using krb5 authentication in pg_hba.conf
[foo@phd11-nn ~]$ psql -h phd11-client -d gpadmin
psql: Kerberos 5 authentication rejected: Key table entry not found

Debug / Solution: Ensure that there is an entry with principal postgres/hawqmasterserver_fqdn@REALM in the HAWQ master server keytab file and you can do a kinit using that.If not regenerate the keytab file.

Case 2: Ticket has expried

[foo@phd11-nn ~]$ psql -h phd11-client.saturn.local -d gpadmin
psql: Kerberos 5 authentication rejected: Key version number for principal in key table is incorrect
[foo@phd11-nn ~]$ psql -h phd11-client.saturn.local -d gpadmin
psql: GSSAPI continuation error: Unspecified GSS failure. Minor code may provide more information
GSSAPI continuation error: Credentials cache file '/tmp/krb5cc_506' not found

Debug / Solution: Do a kinit as user. Example[foo@phd11-nn ~]$ kinit Password for foo@SATURN.LOCAL:[foo@phd11-nn ~]$ psql -h phd11-client.saturn.local -d gpadmin gpadmin=

Comments

  • Avatar
    Michael Forrest

    Excellent Stuff, thanks for sharing!

Powered by Zendesk