Managing users from external LDAP on Netezza Performance Server - deprecated

This procedure is deprecated. It only applies if your NPS system is on version 11.0.2.0 or earlier.

For 11.0.3.0 and later versions, see Managing users from external LDAP on Netezza Performance Server 11.0.3.1 or later.

If you want the users from external LDAP to access the Netezza Performance Server database, you need to add them manually and configure authentication.

Before you begin

  • The administrator must ensure that each NPS user is also defined within the NPS system catalog. The NPS user names must match the user names that are defined in the LDAP/AD server.
  • Make sure there is a communication between NPS host and the LDAP/AD server. For this append the line below in /etc/hosts on the NPS host with root user, for example:
    cat /etc/hosts 
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
<LDAP Server IP> <LDAP FQDN> <LDAP Server short name>

    You can cross check connection using ping <LDAP/AD server> or ssh <LDAP/AD server> .

  • Collect the following information from an LDAP administrator:
    Name Required Default value Description
    LDAP server Yes   Host on which LDAP is working
    base Yes   Node of the LDAP tree in which LDAP users will be searched (i.e. OU=ForNetezza,DC=KSL,DC=lab)
    port Yes   Port on which LDAP server is listening (default 389 for non-SSL)
    BINDDN No   Full name of user which will have access to base sub-tree on LDAP server. Must be specified if anonymous access to LDAP server is not allowed (i.e. "cn=netezza_user,cn=Users,dc=KSL,dc=lab")
    BINDPW No   Password for BINDDN user
    ATTRNAME No “cn” LDAP attribute which will be used to define userid. Commonly used is 'sAMAccountName' (unique account name, like jkowalski)
  • Verify server name and port on which LDAP is running. To verify if port is not blocked by firewall you can run the following command:
    telnet <host_name> <port>

ldapsearch -v -h <ldap_server> -b <base> -D <binddn> -p <port> -x -w <bindpw>
    Example command: ldapsearch -v -h <ldap_server> -p 389 -D "cn=admin,dc=somedomain,dc=com" -x -w adminpass

Steps to perform on the LDAP server

The following procedure adds an exemplary test_ldap user on the LDAP server. These steps are required only if LDAP users are not already present and you want to create a new one.

Procedure

  1. Create a ldif file at any location, as in the following example: 
    root@LDAP_SERVER:~#cat test_ldap.ldif

dn: uid=test_ldap,ou=users,dc=somedomain,dc=com
uid: test_ldap
cn: test_ldap
objectClass: account
objectClass: posixAccount
objectClass: top
loginShell: /bin/bash
uidNumber: 5036
gidNumber: 3002
homeDirectory: /mnt/blumeta0/home/test_ldap
gecos: test_ldap

dn: cn=bluusers,ou=groups,dc=somedomain,dc=com
changetype: modify
add: memberuid
memberuid: test_ldap
memberuid: uid=test_ldap,ou=users,dc=somedomain,dc=com
  2. Add the LDAP user: 
    root@LDAP_SERVER:~# ldapadd -h localhost -p 389 -D cn=admin,dc=somedomain,dc=com -w adminpass -c < test_ldap.ldif
    where:
    -D
    Specifies distinguished name.
    -w
    Specifies the password associated with the DN.
    Output follows:
    adding new entry "uid=test_ldap,ou=users,dc=somedomain,dc=com"

modifying entry "cn=bluusers,ou=groups,dc=somedomain,dc=com"
  3. Add password for the user: 
    ldappasswd -h localhost -p 389 -D cn=admin,dc=somedomain,dc=com -w adminpass -S "uid=test_ldap,ou=users,dc=somedomain,dc=com" -s netezzaips
    where:
    -S
    Specifies the user to change the password for. If you do not use -s <password> option to specify the password, the user will be prompted for new password at their first login.
    -s
    Specifies a new password for the user.
  4. Verify if the user is added successfully: 
    root@LDAP_SERVER:~#ldapsearch -x -b "dc=somedomain,dc=com" | grep test_ldap
    Output follows:
    memberUid: test_ldap
memberUid: uid=test_ldap,ou=users,dc=somedomain,dc=com
# test_ldap, users, somedomain.com
dn: uid=test_ldap,ou=users,dc=somedomain,dc=com
uid: test_ldap
cn: test_ldap
homeDirectory: /mnt/blumeta0/home/test_ldap
gecos: test_ldap

Changes in pam.d files

Modify the following files manually to use the pam.sss.so service on client system.

Procedure

Change the following pam files on the NPS host manually to use the pam.sss.so service:
The lines in bold need to be changed.
  • /etc/pam.d/system-auth 
    #%PAM-1.0
# This file is auto-generated.
# User changes will be destroyed the next time authconfig is run.
auth        required      pam_env.so
auth        required      pam_faildelay.so delay=2000000
auth        [success=1 default=ignore] pam_succeed_if.so service notin login:gdm:xdm:kdm:xscreensaver:gnome-screensaver:kscreensaver quiet use_uid
auth        [success=done authinfo_unavail=ignore ignore=ignore default=die] pam_pkcs11.so nodebug
auth        [default=1 ignore=ignore success=ok] pam_succeed_if.so uid >= 1000 quiet
auth        [default=1 ignore=ignore success=ok] pam_localuser.so
auth        sufficient    pam_unix.so try_first_pass
auth        requisite     pam_succeed_if.so uid >= 1000 quiet_success
auth        sufficient    pam_sss.so forward_pass
auth        required      pam_deny.so

account     required      pam_unix.so
account     sufficient    pam_localuser.so
account     sufficient    pam_succeed_if.so uid < 1000 quiet
account     [default=bad success=ok user_unknown=ignore] pam_sss.so
account     required      pam_permit.so

password    requisite     pam_pwquality.so try_first_pass retry=3 authtok_type=
password    sufficient    pam_unix.so sha512 shadow remember=5 try_first_pass use_authtok
password    sufficient    pam_sss.so sha512 remember=5 use_authtok
password    required      pam_deny.so

session     optional      pam_keyinit.so revoke
session     required      pam_limits.so
-session     optional      pam_systemd.so
session     optional      pam_mkhomedir.so umask=0077
session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session     required      pam_unix.so
session     optional      pam_sss.so
  • /etc/pam.d/password-auth 
    #%PAM-1.0
# This file is auto-generated.
# User changes will be destroyed the next time authconfig is run.
auth        required      pam_env.so
auth        required      pam_faildelay.so delay=2000000
auth        [default=1 ignore=ignore success=ok] pam_succeed_if.so uid >= 1000 quiet
auth        [default=1 ignore=ignore success=ok] pam_localuser.so
auth        sufficient    pam_unix.so nullok try_first_pass
auth        requisite     pam_succeed_if.so uid >= 1000 quiet_success
auth        sufficient    pam_sss.so forward_pass
auth        required      pam_deny.so

account     required      pam_unix.so
account     sufficient    pam_localuser.so
account     sufficient    pam_succeed_if.so uid < 1000 quiet
account     [default=bad success=ok user_unknown=ignore] pam_sss.so
account     required      pam_permit.so

password    requisite     pam_pwquality.so try_first_pass retry=3 authtok_type=
password    sufficient    pam_unix.so sha512 shadow remember=5 try_first_pass use_authtok
password    sufficient    pam_sss.so sha512 remember=5 use_authtok


password    required      pam_deny.so

session     optional      pam_keyinit.so revoke
session     required      pam_limits.so
-session     optional      pam_systemd.so
session     optional      pam_mkhomedir.so umask=0077
session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session     required      pam_unix.so
session     optional      pam_sss.so
  • /etc/pam.d/smartcard-auth
    #%PAM-1.0
# This file is auto-generated.
# User changes will be destroyed the next time authconfig is run.
auth        required      pam_env.so
auth        [success=done ignore=ignore default=die] pam_pkcs11.so nodebug wait_for_card
auth        required      pam_deny.so

account     required      pam_unix.so
account     sufficient    pam_localuser.so
account     sufficient    pam_succeed_if.so uid < 1000 quiet
account     [default=bad success=ok user_unknown=ignore] pam_sss.so
account     required      pam_permit.so

password    required      pam_pkcs11.so

session     optional      pam_keyinit.so revoke
session     required      pam_limits.so
-session     optional      pam_systemd.so
session     optional      pam_mkhomedir.so umask=0077
session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session     required      pam_unix.so
session     optional      pam_sss.so
  • /etc/pam.d/ fingerprint-auth
    #%PAM-1.0
# This file is auto-generated.
# User changes will be destroyed the next time authconfig is run.
auth        required      pam_env.so
auth        sufficient    pam_fprintd.so
auth        required      pam_deny.so

account     required      pam_unix.so
account     sufficient    pam_localuser.so
account     sufficient    pam_succeed_if.so uid < 1000 quiet
account     [default=bad success=ok user_unknown=ignore] pam_sss.so
account     required      pam_permit.so

password    required      pam_deny.so

session     optional      pam_keyinit.so revoke
session     required      pam_limits.so
-session     optional      pam_systemd.so
session     optional      pam_mkhomedir.so umask=0077
session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session     required      pam_unix.so
session     optional      pam_sss.so
  • /etc/nsswitch.conf
    passwd:     files sss ldap
shadow:     files sss ldap
group:      files sss ldap
hosts:      files dns myhostname
bootparams: nisplus [NOTFOUND=return] files

ethers:     files
netmasks:   files
networks:   files
protocols:  files
rpc:        files
services:   files sss
netgroup:   files sss ldap
publickey:  nisplus
automount:  files sss ldap
aliases:    files nisplus

Steps to perform on Netezza Performance Server

With these steps, you can check for current authentication.

Procedure

  1. Log in to Cloud Pak for Data System platform.
  2. Log in to NPS: 
    docker exec -it <ips hostname> bash
  3. Switch the user to nz user: 
     su - nz
  4. Verify the authentication: 
     nzsql -c "show authentication all"
    Example:
    [nz@node0201 ~]$ nzsql -c "show authentication all"
AUTH_OPTION            | AUTH_VALUE
-----------------------+------------
AUTHENTICATION METHOD  | LOCAL
(1 row)
  5. Depending on which LDAP server you use:
    • OpenLDAP
      1. Add the LDAP users to database by connecting to the NPS host:
        create user <user>   password  <password>
        Define the password as per your password policy.
        Example:
        nzsql -c “create user test_ldap password 'password';“
        Note:

        Known issue: If the users in a database were created/copied from another database and they do not have a password defined, the AD user authentication for NPS database might crash the database service.

        Workaround: Provide password either during user creation, or use ALTER user to add the password (if user already exists).

      2. Set authentication type to LDAP with SSL off:
        SET AUTHENTICATION LDAP
        Example output for SSL off:
        nzsql -c " SET AUTHENTICATION LDAP BASE 'dc=somedomain,dc=com' NAMECASE lowercase SERVER 
'<ldap_server>' SSL 'off' BINDDN 'cn=admin,dc=somedomain,dc=com' BINDPW adminpass;"
        where:
        CN
        Common Name
        DC
        Domain Component
        BINDDN
        Distinguished Name
        BINDPW
        Password associated with the Distinguished Name
        Note: Skip BINDDN 'cn=admin,dc=somedomain,dc=com' if bind password is not set on LDAP server. In this case, the command example would be:
        nzsql -c " SET AUTHENTICATION LDAP BASE 'dc=somedomain,dc=com' NAMECASE lowercase SERVER '<ldap_server>' SSL 'off'
      3. Verify the authentication type:
        nzsql -c "show authentication all;"
        Examples:
        • [nz@node0201 ~]$ nzsql -c "show authentication all;"
      AUTH_OPTION       |          AUTH_VALUE
------------------------+-------------------------------
 AUTHENTICATION METHOD  | LDAP
 AUTHMTHD LDAP BASE     | dc=somedomain,dc=com
 AUTHMTHD LDAP BINDDN   | cn=admin,dc=somedomain,dc=com
 AUTHMTHD LDAP NAMECASE | LOWERCASE
 AUTHMTHD LDAP SERVER   | <LDAP server IP>
 AUTHMTHD LDAP SSL      | off
(6 rows)
        • if BINDPW not used in the set authentication command:
          [nz@node0201 ~]$ nzsql -c "show authentication all;"
      AUTH_OPTION       |          AUTH_VALUE
------------------------+-------------------------------
 AUTHENTICATION METHOD  | LDAP
 AUTHMTHD LDAP BASE     | dc=somedomain,dc=com
 AUTHMTHD LDAP NAMECASE | LOWERCASE
 AUTHMTHD LDAP SERVER   | <LDAP server IP>
 AUTHMTHD LDAP SSL      | off
(5 rows)

        Note that /etc/ldap.conf is deprecated on NPS. You must use /etc/sssd/sssd.conf to set up LDAP configuration.

        ===========================/etc/sssd/sssd.conf==================================

[domain/external_ldap]

ldap_tls_reqcert = never
ldap_id_use_start_tls = False
ldap_schema = rfc2307
auth_provider = ldap
id_provider = ldap
ldap_rfc2307_fallback_to_local_users = True
ldap_referrals = False
ldap_id_mapping = False
override_homedir = /home/%u
ldap_network_timeout = 3
ldap_opt_timeout = 60
cache_credentials = True
ldap_user_search_base = dc=somedomain,dc=com
entry_cache_group_timeout = 0
entry_cache_user_timeout = 0
ldap_search_timeout = 30
entry_cache_timeout = 600
ldap_default_bind_dn = cn=admin,dc=somedomain,dc=com
ldap_uri = ldap:/<LDAP Server>:389
debug_level = 10

ldap_default_authtok_type = obfuscated_password
ldap_default_authtok = AAAQAGjPMpPlaKyKfPLvmpE1FSolHcAHS/0KoEaJ7k8yPZaphWL2hLYyjKBXO4C9aAgAIGKgBWONT+5nOITCVv6tTlwAAQID
[sssd]
services = nss, ifp, sudo, ssh, pam
domains = external_ldap

[nss]
memcache_timeout = 600
homedir_substring = /home

[pam]
debug_level = 10

[sudo]
[autofs]
[ssh]
[pac]
[ifp]
    • Windows Active Directory:
      1. Add the AD users to the database:
        create user <user>  password  <password>
        Define the password as per your password policy. Example:
        nzsql -c "create user ad_user1 password 'password';"
      2. Set authentication type to LDAP with SSL off:
        SET AUTHENTICATION LDAP
        Example:
        nzsql -c " SET AUTHENTICATION LDAP BASE 'dc=nzdevelopment,dc=com' NAMECASE lowercase SERVER '<ad_server>' 
SSL 'off' BINDPW Netezza26 BINDDN 'cn=ad_user1,cn=Users,dc=nzdevelopment,dc=com'; "
        where:
        cn
        Common Name
        dc
        Domain component
      3. Update /etc/sssd/sssd.conf file on NPS host manually as below:
        ===========================/etc/sssd/sssd.conf==================================

[domain/external_ldap]

ldap_tls_reqcert = never
ldap_id_use_start_tls = True
ldap_schema = AD
ignore_group_members = True
auth_provider = ldap
id_provider = ldap
ldap_rfc2307_fallback_to_local_users = True
ldap_referrals = False
ldap_id_mapping = True
override_homedir = /home/%u
ldap_network_timeout = 3
ldap_opt_timeout = 60
cache_credentials = True
ldap_user_search_base = dc=nzdevelopment,dc=com
entry_cache_group_timeout = 0
entry_cache_user_timeout = 0
ldap_search_timeout = 30
entry_cache_timeout = 600
ldap_default_bind_dn = cn=ad_user1,cn=Users,dc=nzdevelopment,dc=com
ldap_uri = ldap://<AD Server>:389
debug_level = 10
case_sensitive = False
ldap_group_name = CN
ldap_user_name = sAMAccountName

ldap_default_authtok_type = obfuscated_password
ldap_default_authtok = AAAQANEC2d5xigH0d4w9phf5F9zD4PvXWvxwz61LYsoQq5BM/M7WslokdqbS6CnOyTzGp4XmzCC7owRfYq8sALPYypoAAQID
[sssd]
services = nss, ifp, sudo, ssh, pam
domains = external_ldap

[nss]
memcache_timeout = 600
homedir_substring = /home

[pam]
debug_level = 10

[sudo]
[autofs]
[ssh]
[pac]
[ifp]
      4. Restart sssd service with root user: 
        systemctl restart sssd
  6. Verify the LDAP/AD user authentication setup:
    1. Log in as nz user and run the command: 
      su – nz 
 
nzsql -u <LDAP/AD user>   -pw <LDAP/AD user password>
      Note:

      This password should be the password defined on LDAP server for the LDAP user.

      Example: 
      [nz@node0201 ~]$ nzsql -db system -u test_ldap -pw netezzaips
Welcome to nzsql, the IBM Netezza SQL interactive terminal.

Type:  \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

SYSTEM.ADMIN(TEST_LDAP)=> show authentication all;
      AUTH_OPTION       |          AUTH_VALUE
------------------------+-------------------------------
 AUTHENTICATION METHOD  | LDAP
 AUTHMTHD LDAP BASE     | dc=somedomain,dc=com
 AUTHMTHD LDAP BINDDN   | cn=admin,dc=somedomain,dc=com
 AUTHMTHD LDAP NAMECASE | LOWERCASE
 AUTHMTHD LDAP SERVER   | <ldap_server> AUTHMTHD LDAP SSL      | off
(6 rows) (6 rows)

[nz@node0201 ~]$

Troubleshooting

If you encounter the following error in PostgreSQL log while authenticating with BINDPW parameter:
ERROR: pam_authenticate failed: User not known to the underlying authentication module
perform the following workaround steps on NPS host with root user:
  1. echo -n <bindpassword> |sss_obfuscate --domain=external_ldap -s
  2. systemctl restart sssd
To handle mixed cases in the LDAP/AD authentication process, do not use NAMESPACE in the SET AUTHENTICATION LDAP command. Example:
nzsql -c " SET AUTHENTICATION LDAP BASE 'dc=nzdevelopment,dc=com' SERVER '<LDAP/AD Server>' SSL 'off' BINDPW Ipspass26BINDDN 'cn=ad_user1,cn=Users,dc=nzdevelopment,dc=com'; "