Pivotal Knowledge Base

Follow

How To Setup Ident Authentication in Pivotal Greenplum (Postgres) for a Remote User

Environment

Product  Version              
Pivotal Greenplum All Versions

Purpose

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

encounters the below authentication error.

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

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

Procedure

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

In order to match this criteria both the OS and the database should 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 Pivotal Greenplum client software from the product download website, or refer to the article "Where to download Pivotal Greenplum Software or Pivotal Products" for instructions about downloading products from the Pivotal website.

NOTE: If Greenplum binaries are present on the client server, you can 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, begin installing 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 below commands.

rpm -qa oidentd
netstat -lanp | grep 113

5. If the oidentp rpm is not installed you must install it to enable the oidentd demon that listen for Ident connection request.

For example, since the OS version is RedHat 5 (64 Bit), my 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 your client server can connect to the internet, you can use the command below to auto download the necessary rpm specifically to 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: If you try to connect to the database now you will be treated with the below error message because you must also set up the database server.

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

On 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 )

flightdata=# 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 ( 172.28.4.5 is the ip of the client server)

host    all     testident       172.28.4.5/32   ident

3. Reload the configuration

gpstop -u

Test the Connection

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

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

[testident@sdw5 ~]$ psql -p 4340 -h 172.28.4.250 flightdata
psql (8.2.15)
Type "help" for help.

flightdata=>

Comments

Powered by Zendesk