How to setup ident authentication in Greenplum Database (GPDB) for a remote user

Products

VMware Tanzu Greenplum Pivotal Data Suite Non Production Edition VMware Tanzu Data Suite VMware Tanzu Data Suite Greenplum

Issue/Introduction

A user who is created in the database and updated in the pg_hba.conf file as a remote user and authentication is set to Ident.

host <database-name> <user-name> <ip-address>/32 ident

The user encounters the authentication error below:

psql: FATAL: Ident authentication failed for user "<user-name>"

This article discusses how to setup ident authentication for a remote user.

Environment

All GPDB Environments

Resolution

Using the ident authentication method, the postmaster will send a request to the ident server. This request checks that the user is authorized to connect as the requested database user. The ident server is running on the client. The authorization request is done using TCP/IP connections for remote users. The authorization request uses the operating system for local connections.

In order to match this criterion, both the OS and the database must have the same username.

Use the procedure below to setup ident authentication for a remote user.

On the client side

1. Check the Unix or client OS version where the user connects to the database.

[root@sdw5 tmp]# uname -a
Linux sdw5 2.6.18-348.18.1.el5 #1 SMP Fri Sep 6 12:37:18 EDT 2013 x86_64 x86_64 x86_64 GNU/Linux
[root@sdw5 tmp]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.10 (Tikanga)

2. Download the Greenplum client software from the broadcom customer portal page, or refer to the article "Download packages and utilities for Greenplum" for instructions about downloading products from the Pivotal website.

Note: If Greenplum binaries are present on the client server, ignore the steps above and below and go to step 4 to set up the ident demon on the client server.

3. After downloading the client software, install the software.

The example below displays the installation of Pivotal Greenplum 4.3.4.0 on a client-server (sdw5):

[gpadmin@sdw5 tmp]$ unzip greenplum-clients-4.3.4.0-build-1-RHEL5-x86_64.zip
Archive:  greenplum-clients-4.3.4.0-build-1-RHEL5-x86_64.zip
  inflating: greenplum-clients-4.3.4.0-build-1-RHEL5-x86_64.bin
[gpadmin@sdw5 tmp]$ /bin/bash greenplum-clients-4.3.4.0-build-1-RHEL5-x86_64.bin
[.....]
[.....]
Installation complete.
Greenplum Clients is installed in: /usr/local/greenplum-clients-4.3.4.0-build-1

4. Check that the oidentp rpm is installed and listening on the port 113 by running the commands below:

rpm -qa oidentd
netstat -lanp | grep 113

5. If the oidentp rpm is not installed, install it to enable the oidentd demon. It listens for ident connection request.

For example, since the OS version is Red Hat 5 (64 Bit), the client server installs the necessary rpm specific to that version.

[root@sdw5 tmp]# rpm -ivh oidentd-2.0.8-1.el5.rf.x86_64.rpm
warning: oidentd-2.0.8-1.el5.rf.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 6b8d79e6
Preparing...                ########################################### [100%]
   1:oidentd                ########################################### [100%]

Note: If the client-server can connect to the internet, use the command below to auto download the necessary rpm specifically for your OS version.

sudo apt-get install oidentd

6. Once installed, start the oidentd demon using the commands below:

[root@sdw5 ~]# whereis oidentd
oidentd: /usr/sbin/oidentd /etc/oidentd.users /usr/share/man/man8/oidentd.8.gz
[root@sdw5 ~]# /usr/sbin/oidentd start
[root@sdw5 ~]# netstat -lanp | grep ident
tcp        0      0 0.0.0.0:113                 0.0.0.0:*                   LISTEN      480/oidentd
unix  2      [ ]         DGRAM                    22096770 480/oidentd
[root@sdw5 ~]#

7. Create the client side user that will connect to the database and source the environment using the steps below:

[root@sdw5 ~]# useradd -G gpadmin testident
[root@sdw5 local]# chown -R testident:gpadmin /usr/local/greenplum-clients-4.3.4.0-build-1
[root@sdw5 local]# su - testident
[testident@sdw5 ~]$ source /usr/local/greenplum-clients-4.3.4.0-build-1/greenplum_clients_path.sh

Note: Trying to connect to the database will produce the error message below. First, set up the database server.

[testident@sdw5 ~]$ psql -p 4340 -h xxx.xxx.xxx.xxx
psql: FATAL: no pg_hba.conf entry for host "xxx.xxx.xxx.xxx", user "testident", database "testdb", SSL off

On the master or database server

1. Connect to the database and create the user (The username should be the exact name on the client OS user).

testdb=# create user testident with password 'aa';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE

2. Edit the pg_hba.conf location $MASTER_DATA_DIRECTORY and add the entry like below (xxx.xxx.xxx.xxx is the IP address of the client-server) host all testidentxxx.xxx.xxx.xxx/32 ident.

host    all     testident      xxx.xxx.xxx.xxx/32   ident

3. Reload the configuration

gpstop -u

Test the connection

On the client side, connect to the database using the below commands. This should allow the connection to pass completely without errors:

psql -p <port-number-of-the-database> -h <host-or-ip-where-the-database-running> <database-name> 

[testident@sdw5 ~]$ psql -p 4340 -hxxx.xxx.xxx.xxx testdb
psql (8.2.15)
Type "help" for help.

testdb=>