LDAP Authentication Best Practices

For: Vertica 8.x and higher

LDAP Authentication Best Practices

This Best Practices document demonstrates how to use LDAP’s ldapsearch tool to ensure that your LDAP authentication works properly in Vertica. Use ldapsearch to validate the settings you want to use when creating LDAP authentication in Vertica.

This document covers the following:

  • LDAP Bind Authentication—Involves validating LDAP Bind authentication with ldapsearch, then implementing the LDAP Bind authentication in Vertica.
  • LDAP Search and Bind Authentication—Involves validating LDAP Search and Bind authentication with ldapsearch, then implementing the LDAP Search and Bind authentication in Vertica.
  • Secure LDAP with SSL and TLS—Includes the following:
    • Validating LDAP with SSL with ldapsearch
    • Configuring LDAP Search and Bind with SSL on Vertica
    • Configuring LDAP Search and Bind with TLS on Vertica
    • Configuring LDAP Bind with TLS on Vertica

LDAP Requirements

Use Lightweight Directory Access Protocol (LDAP) to authenticate clients trying to access your Vertica database against an LDAP or Active Directory server. Before beginning, you must have the following:

  • IP address and host name for the LDAP server. Vertica supports IPv4 and IPv6 addresses.
  • Your organization's Active Directory information.
  • An LDAP service account with read and search access to the LDAP server, only if you are going to use Search and Bind authentication.
  • Database administration access to your Vertica database.
  • Installation of the openldap-clients package (ldap-utils for Debian/Ubuntu, openldap2-client for SUSE) on at least one node (which includes the ldapsearch utility).

Before you Begin

Consider that a new or fresh database installation implies a default password authentication method that applies to all users. However, after you create an authentication scheme, the default/implied authentication method is no longer available to users. If you want to maintain the password authentication for non-LDAP users, you must explicitly create the default authentication method and grant it to all users.

Take the following steps to create default authentication for default users.

=> CREATE AUTHENTICATION default_network METHOD 'hash' HOST '0.0.0.0/0';
=> CREATE AUTHENTICATION default_local METHOD 'hash' LOCAL;
=> GRANT AUTHENTICATION default_network to public;
=> GRANT AUTHENTICATION default_local to public;

LDAP Bind Authentication

This section explains how to validate information for LDAP Bind using ldapsearch and then create and configure an LDAP Bind authentication in Vertica using that information.

Validating LDAP Bind Authentication with ldapsearch

When a user logs into Vertica, Vertica attempts to bind the user name to a matching user name in the LDAP Server. If the LDAP bind succeeds, the user is allowed access. You can use LDAP’s ldapsearch tool to connect to the server and locate users based on specific search criteria.

This example uses the ldapsearch tool to validate LDAP Bind Authentication using the distinguished name. Do this prior to configuring the client authentication:

$ ldapsearch -x -v -s base -h 10.10.10.60 -D "CN=James Smith, 
  OU=Vertica Users, DC=Vertica,DC=com" -b "OU=Vertica Users,
  dc=vertica,dc=com" -w 'USERS_LDAP_PASSWORD' -LLL  'objectClass=none' none
  ldap_initialize( ldap://10.10.10.60 )
  filter: objectClass=none
  requesting: none 

You use the –D parameter to specify the distinguished name of the user "CN=James Smith,OU=Vertica Users,DC=Vertica,DC=com".

Implementing LDAP Bind Authentication in Vertica

Configuring LDAP Bind authentication in Vertica must be based on the parameters you used in ldapsearch in the previous section. There is a one-to-one relation between the LDAP settings from ldapsearch (-D, -b, -s, and –h) and the Vertica parameters you use here.

To set up binding of a Vertica user to the LDAP distinguished name used in the previous section:

  1. Create an LDAP authentication method: 
    => CREATE AUTHENTICATION LDAP2 METHOD 'ldap' HOST '0.0.0.0/0';
  2. Alter LDAP2 to use the ldapsearch settings from the previous section:
     => ALTER AUTHENTICATION LDAP2 SET host='ldap://10.10.10.60', 
    binddn_prefix='CN=', binddn_suffix=', OU=Vertica Users, 
    DC=Vertica, DC=com', basedn='OU=Vertica Users, dc=vertica, dc=com';
  3. Enable the authentication:
     => ALTER AUTHENTICATION LDAP2 enable;
  4. Verify that the LDAP authentication parsed all of your parameters successfully:
    => SELECT * FROM client_auth_params WHERE auth_name='LDAP2'; 
      auth_oid    | auth_name | auth_parameter_name | auth_parameter_value
------------------+-----------+---------------------+---------------------
45035996273751694 | LDAP2     | host               | ldap://10.10.10.60
45035996273751694 | LDAP2     | basedn             | OU=Vertica Users,dc=vertica,dc=com
45035996273751694 | LDAP2     | binddn_prefix      | CN=
45035996273751694 | LDAP2     | binddn_suffix      | ,OU=Vertica Users,DC=Vertica,DC=com
(4 rows)

    Vertica constructs the –D argument as follows: 
    binddn_prefix  <Vertica user ID>  binddn_suffix
  5. Create a Vertica user: 
    => CREATE USER "James Smith";
    Creating the Vertica user named James Smith makes the distinguished name (-D argument in ldapsearch): 
    "CN=James Smith,OU=Vertica Users,DC=Vertica,DC=com”
  6. Grant the LDAP authentication to the new user:
     => GRANT AUTHENTICATION LDAP2 to "James Smith";
  7. Verify that the user was granted authentication correctly: 
    => SELECT object_name, grantee FROM grants 
   WHERE object_type ='CLIENTAUTHENTICATION':
 object_name | grantee
 ------------+-----------
 LDAP2       | James Smith
 (1 row)

The bind to the LDAP server occurs when you log into Vertica:

$ vsql -U "James Smith" -h <VERTICA IP> -w 'USERS_LDAP_PASSWORD'
  -c "select user_name, client_authentication_name from sessions;
 user_name   | client_authentication_name
-------------+-----------------------------
 James Smith | LDAP2
 (1 row)

You must use –h <VERTICA IP> because when you created the authentication method in Step 1 the specified client host was 0.0.0.0/0.

After successfully binding and authenticating a user, you’ll see something similar to this in the Vertica logs:

LDAP authentication succeeded for user DN "CN=James Smith, OU=Vertica Users, 
DC=Vertica, DC=com" on server ldap://10.10.10.60
ldap://10.10.10.60

LDAP Search and Bind Authentication

This section explains how to validate information for LDAP Search and Bind using ldapsearch and then create and configure an LDAP Search and Bind authentication in Vertica using that information.

Validating LDAP Search and Bind Authentication with ldapsearch

LDAP Search and Bind Authentication allows you to use other user identifiers rather than the distinguished name, domain name, or email used in simple Bind Authentication.

With LDAP Search and Bind authentication you can choose any other attribute (only one) on which to authenticate a user. For example, you can use attributes such as firstName, employeeID, or sAMAccountName. Search and Bind requires your Active Directory information.

As its name suggests, Search and Bind requires two steps:

  • Searching for the appropriate user
  • Performing a bind on that user using the users’s DN to allow access.

Search

When a Vertica user logs in, Vertica connects to the LDAP server and searches for an LDAP user that matches a specified LDAP attribute. The examples in this section illustrate using ldapsearch to validate the two-step search and bind authentication. In step 1, LDAP search, you search for a user whose Vertica login is the login_name attribute:

search_attribute=vertica_user_login_id

After locating the vertica_user_login_id, step 2 is the same as the DN binding described previously.

This example uses an LDAP service account called vertica\SearchServiceUser. This has read and search access to the LDAP server. The LDAP service account cannot be a database user account.  If you change the password for the Service Account, LDAP authentication fails.

$ ldapsearch -x -v -s one -h 10.10.10.60 -D "vertica\SearchServiceUser" 
-b "OU=Vertica Users,dc=vertica,dc=com" -w 'SAVED_SECRET' 
-LLL sAMAccountName=jsmith dn 
ldap_initialize( ldap://10.10.10.60 )
filter: sAMAccountName=jsmith
requesting: dn
dn: CN=James Smith,OU=Vertica Users,DC=Vertica,DC=com   

In the above example:

  • -h is the LDAP host to which you are connecting
  • -D is the binddn used for authentication
  • -b is the basedn to which you are connecting of the LDAP server
  • -w is the binddn password
  • sAMAccountName is the ldap attribute that should match the login name.
  • dn is the distinguished name returned by the LDAP server that matches sAMAccountName
  • jsmith is the login id of the user logging into Vertica

This previous example searches for sAMAccountName, which must match the Vertica user login name. Only a single match can be made. If multiple users match, the login fails. The LDAP bind occurs upon a successful search.

Bind

The following is the corresponding ldapsearch command to set up the bind operation after a successful search using the sAMAccountName:

ldapsearch -x -v -h 10.10.10.60 -D "CN=James Smith, OU=Vertica Users, DC=Vertica,
DC=com" -b "dc=vertica,dc=com" -w 'USERS_LDAP_PASSWORD' sAMAccountName=jsmith

Implementing LDAP Search and Bind Authentication on Vertica

Search

Remember, just as in simple bind authentication, there is a one-to-one relation between the ldap settings from ldapsearch and the Vertica parameters used here.

To set up a search on Vertica do the following:

  1. Create an LDAP authentication method: 
    => CREATE AUTHENTICATION LDAP1 METHOD 'ldap' HOST '0.0.0.0/0';
    The host setting of 0.0.0.0/0 allows the user to log in from any IP address.
  2. Alter LDAP1 to set the distinguished name:
     => ALTER AUTHENTICATION LDAP1 SET host='ldap://10.10.10.60' , 
    basedn='OU=Vertica Users, DC=Vertica,DC=com',
    binddn='vertica\SearchServiceUser', 
    search_attribute='sAMAccountName',
    bind_password='SAVED_SECRET;
  3. Enable the authentication: 
    => ALTER AUTHENTICATION LDAP1 enable;
  4. Verify that LDAP authentication parsed all of your parameters successfully: 
    => SELECT * FROM client_auth_params WHERE auth_name='LDAP1';
 auth_oid          | auth_name | auth_parameter_name | auth_parameter_value
-------------------+-----------+---------------------+----------------------
 45035996273935258 | LDAP1     | host                | ldap://10.10.10.60
 45035996273935258 | LDAP1     | basedn              | OU=Vertica Users, dc=vertica.com,dc=com                           
 45035996273935258 | LDAP1     | binddn              | vertica\SearchServiceUser
 45035996273935258 | LDAP1     | bind_password       | SAVED_SECRET
 45035996273935258 | LDAP1     | search_attribute    | sAMAccountName
 (5 rows)

Bind

After a successful search, Vertica performs a bind with the LDAP user’s DN. You must do the following to set up the authentication, Search and then Bind occurs on login:

  1. Create a Vertica user: 
    => CREATE USER "jsmith";
  2. Grant the LDAP authentication to the new user: 
    => GRANT AUTHENTICATION LDAP1 to "jsmith";
  3. Verify that the user was granted authentication correctly: 
    => SELECT object_name, grantee FROM grants 
   WHERE object_type ='CLIENTAUTHENTICATION':
 object_name | grantee
-------------+-----------
 LDAP1       | jsmith
 (1 row)

The bind connection to the LDAP server occurs when you log into Vertica:

$ vsql -U "jsmith" -h <VERTICA IP> -w 'USERS_LDAP_PASSWORD' -c 
  "select user_name, client_authentication_name from sessions;
  user_name   | client_authentication_name
 -------------+----------------------------
  Jsmith      | LDAP1
 (1 row)

You must use –h <VERTICA IP>, because when creating the authentication method, the specified host was 0.0.0.0/0. In addition, the USERS_LDAP_PASSWORD is not stored in Vertica, but Vertica validates the LDAP password each time the user logs in.

LDAP Link Service

LDAP Link enables synchronization between the LDAP and Vertica servers. This eliminates the need for you to manage two sets of users and groups or roles, one on the LDAP server and another on the Vertica server. With LDAP synchronization, the Vertica server becomes a replication database for the LDAP server.

Automatic Synchronization

With LDAP Link the Vertica server closely integrates with an existing directory service such as MS Active Directory or OpenLDAP. The Vertica server automatically synchronizes:

  • LDAP users to Vertica users
  • LDAP groups to Vertica roles

You manage all user and group properties in the LDAP server. If you are the Vertica database administrator, you need only to set up permissions for Vertica Analytic Database access on the users and groups.

Configure LDAP Link with LDAP Link connection parameters that reside in the catalog. See Set LDAP Link Parameters for more information.

Configuring LDAP Link

Before configuring LDAP Link, first validate the user and group filters that you plan to use in order to import users and groups (roles).

Here is an example of a group filter, which imports a windows group called dba:

$ ldapsearch -h vertica.com -D 'CN=bind ldap,OU=QA,DC=vertica,DC=com' -w password -b 'dc=vertica,dc=com' '(&(objectClass=group)(cn=dba))' samaccountname

Here is an example of a user filter that selects the sAMAccountName of all users in the Windows dba group and creates corresponding Vertica users:

$ ldapsearch -h vertica.com -D 'CN=bind ldap,OU=QA,DC=vertica,DC=com' -w password -b 'dc=vertica,dc=com' '(&(objectClass=user)(cn=*)(memberof=CN=dba, OU=IT,dc=vertica,dc=com))' samaccountname

Verify that the preceding command completes successfully, returning the attributes for the users and groups that you want.

Based on the arguments passed to the preceding ldapsearch command, starting with Vertica 9.3.x, you can construct equivalent SQL test commands to dry run LDAPLINK (results of the following commands can be found in the LDAP_LINK_DRYRUN_EVENTS system table).

= > SELECT LDAP_LINK_DRYRUN_CONNECT('ldap://vertica.com',CN=bind ldap, OU=QA, DC=vertica,DC=com','password');

= > SELECT LDAP_LINK_DRYRUN_SEARCH('ldap://vertica.com',CN=bind ldap, OU=QA, DC=vertica,DC=com','password', 'dc=vertica,dc=com', 'sub', '(&(objectClass=user)(cn=*)(memberof=CN=dba,OU=IT,dc=vertica,dc=com))', '(&(objectClass=group)(cn=dba))', 'sAMAccountName', 'sAMAccountName', 'member');

= > SELECT LDAP_LINK_DRYRUN_SYNC('ldap://vertica.com',CN=bind ldap, OU=QA, DC=vertica,DC=com','password', 'dc=vertica,dc=com', 'sub', '(&(objectClass=user)(cn=*)(memberof=CN=dba,OU=IT,dc=vertica,dc=com))', '(&(objectClass=group)(cn=dba))', 'sAMAccountName', 'sAMAccountName', 'member');

Regardless of versions, you can also construct an equivalent command to configure the LDAP Link service in Vertica as follows:

=> ALTER DATABASE verticadb SET PARAMETER
LDAPLinkDryRun=1, 
LDAPLinkURL='ldap://vertica.com',
LDAPLinkSearchBase='dc=vertica,dc=com',
LDAPLinkBindDN='CN=bind ldap, OU=QA, DC=vertica,DC=com',
LDAPLinkBindPswd='password',
LDAPLinkFilterGroup='(&(objectClass=group)(cn=dba))',
LDAPLinkFilterUser='(&(objectClass=user)(cn=*)(memberof=CN=dba,OU=IT,dc=vertica,dc=com))',
LDAPLinkGroupName='sAMAccountName',
LDAPLinkUserName='sAMAccountName',
LDAPLinkOn=1;

=> SELECT LDAP_LINK_SYNC_START();

Note  

  • LDAP Link only does user provisioning. You still have to configure user authentication based on the relevant section of this document.

  • LDAPLinkDryRun tests the connection to the LDAP server and logs the response without doing a synchronization. Also, tests if parameters are correctly set.

Here is a simple example on how to configure the follow-on authentication after configuring the LdapLink service:

=> CREATE AUTHENTICATION LDAP2 METHOD 'ldap' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION LDAP2 SET host='ldap://vertica.com', domain_prefix='vertica',basedn='dc=vertica,dc=com' ;
=> ALTER AUTHENTICATION LDAP2 enable;

When using LDAP Link, you may assign the user authentication to a role (synched from an LDAP group) instead of a per-ldap-user basis.

=> GRANT AUTHENTICATION LDAP2 to dba;

Managing LDAP Users in Vertica

To give additional LDAP users access to Vertica you need to create Vertica users with the Vertica login name that matches the LDAP sAMAccountName. Then you grant the authentication to the Vertica user in Vertica:

=> CREATE USER apowell;
=> GRANT AUTHENTICATION LDAP1 to apowell;

You can also deny an LDAP user access to Vertica by revoking the authentication in Vertica:

=> REVOKE Authentication LDAP1 from apowell;

Alternatively, you can disable a specific LDAP authentication method to prevent any users assigned that method from accessing Vertica:

=> ALTER AUTHENTICATION LDAP1 disable; 

Securing LDAP Authentication with SSL and TLS

This section explains how to implement security on your LDAP authentication using SSL or TLS. The procedures here are similar to the ones documented above, but include some differences to enable security.

Validating LDAP with SSL with ldapsearch

This section contains information on configuring a secure LDAP authentication using SSL.

Important You should not proceed with SSL configuration without successfully configuring Vertica to use LDAP without SSL as described earlier in this document.

The settings in this section provide a more secure LDAP authentication using SSL.

LDAP with SSL or TLS on LDAP Server

Validating secure LDAP involves the same commands as performing a non-secure validation, but with some modifications that use security-specific attributes.

This example adds the -Z parameter to the search and bind command to enable startTLS. Authentication fails to non-tls if tls does not work.

ldapsearch -Z -x -v -s one -H ldap://ldap-server-prod.vertica.com 
-D "vertica\SearchServiceUser" -b "OU=Vertica Users, dc=vertica, 
dc=com" -w 'SAVED_SECRET' -LLL sAMAccountName=jsmith dn

This example enables SSL to the simple bind command by adding s to ldap:

ldapsearch -x -v -s base -H ldaps://ldap-server-prod.vertica.com -D 
"CN=James Smith, OU=Vertica Users, DC=Vertica, DC=com" 
-b "OU=Vertica Users, dc=vertica, dc=com" 
-w 'USERS_LDAP_PASSWORD' -LLL  'objectClass=none' non

Note that each of the above examples uses the hostname rather than the IP.

Before you begin, note the following about the host name and IP address:

  • You cannot use IP addresses interchangeably with host names. You must use
  •  ldap(s)://ldap-server-prod.vertica.com, not ldap://10.10.10.60.
  • The host name must be the subject or subject alternate name on the certificate.
  • The hostname must be resolvable in forward dns lookups and reverse dns lookups: 
     $ nslookup ldap-server-prod.vertica.com
 Name:   ldap-server-prod.vertica.com
 Address: 10.10.10.60

 $ nslookup 10.10.10.60
 Name:   ldap-server-prod.vertica.com
 Address: 10.10.10.60
    This is important because the SSL certificate usually contains the hostname. If you use the IP address, the hostname and IP string may not match causing the authentication to fail.

To validate LDAP with SSL or TLS do the following:

  1. Run the following command to view the certificate presented by the LDAP server:
     $ openssl s_client -showcerts 
   -connect ldap-server-prod.vertica.com:636 | openssl x509 -noout –date
 
   notBefore=Apr 21 17:35:18 2014 GMT
   notAfter=Apr 21 17:45:18 2015 GMT
    This is the date range for which the certificate is valid. Authentication fails if your certificate has expired or is valid at a future date.
  2. Run the following command to perform further verification:
     $ openssl s_client -showcerts -connect ldap-server-prod.vertica.com:636
  CONNECTED(00000003)
  depth=0 CN = glw2k8-64.dc.com
  verify error:num=20:unable to get local issuer certificate  
  verify return:1
  depth=0 CN = glw2k8-64.dc.com
  verify error:num=27:certificate not trusted
  verify return:1
  depth=0 CN = glw2k8-64.dc.com
  verify error:num=21:unable to verify the first certificate
  verify return:1
  ---
  Certificate chain
  0 s:/CN=glw2k8-64.dc.com
     i:/DC=com/DC=dc/CN=dc-GLW2K8-64-CA

  ---
  Server certificate
  subject=/CN=ldap-server-prod.vertica.com
  issuer=/DC=com/DC=dc/CN=dc-GLW2K8-64-CA
  ---

  Verify return code: 21 (unable to verify the first certificate)  
  ---
    This failed example demonstrates the importance of the --CAFile.  In order to verify the certificate, you need to trust the issuer, for example the Verisign issuer you see on secure websites.
  3. The issuer parameter in Step 2 above indicates the issuer of the certificate (Certificate Authority). Run the following command to explicitly verify the certificate: 
    $ openssl s_client -showcerts -connect ldap-server-prod.vertica.com:636 
  -CAfile /path/to/ca.cer | grep Verify
  depth=1 DC = com, DC = dc, CN = dc-GLW2K8-64-CA
  verify return:1
  depth=0 CN = ldap-server-prod.vertica.com
  verify return:1     Verify return code: 0 (ok) 
    The above example shows a successful final verification. This is required before proceeding to the next step.
  4. Run the following commands to validate the SSL or TLS LDAP configuration.

    This shows a search example with TLS:
     ldapsearch -Z -x -v -s one -H ldap://ldap-server-prod.vertica.com 
 -D "vertica\SearchServiceUser" -b "OU=Vertica Users,dc=vertica,
 dc=com" -w 'SAVED_SECRET' -LLL sAMAccountName=jsmith dn
    This shows a search example with SSL: 
    ldapsearch -x -v -s base -H ldaps://ldap-server-prod.vertica.com -D 
"CN=James Smith, OU=Vertica Users, DC=vertica, DC=com" 
-b "OU=Vertica Users, dc=vertica, dc=com" 
-w 'USERS_LDAP_PASSWORD' -LLL  'objectClass=none' none
    Note the above commands use a host name rather than an IP address as in non-secure LDAP authentication.
  5. Use the command line to set one of the following LDAP parameters found in ldap.conf:

    TLS_CACERT—The path where the CA certificate resides
    TLS_CADIR—If there are many certificates, the directory where they reside

    For example: 
     $ cat ldap.conf
 TLS_CACERT /path/to/ca.crt
 TLS_REQCERT hard
  6. Set the TLS_REQCERT LDAP parameter in Vertica to indicate what the system should do if the certificate verification fails. Note that this Vertica-specific parameter performs the same function as a similarly named LDAP parameter which is specified in an ldap.conf file (An example below shows how you can check if this parameter is configured correctly and works as expected.) Values are:
    • try—Attempts TLS but proceeds with authentication in clear text if TLS is not supported
    • allow—Uses SSL/TLS but proceeds with authentication and connection if the certificate cannot be verified or has expired.
    • hard—Authentication fails if the certificate cannot be verified.
  7. Set the LDAP starttls parameter to define the StartTLS behavior. Values are:
    • soft—If the server does not support TLS, continue authenticating the user in plain text. This value is equivalent to the -Z option in ldapsearch.
    • hard—If server does not support TLS, authentication should fail. This value is equivalent to the -ZZ option in ldapsearch.

    For more information, see Using LDAP over SSL/TLS in the Vertica product documentation.

  8. Use the examples below to verify your SSL/TLS setup. These commands acts as a debugging tool to determine why an authentication fails. Run these commands as the database administrator (dbadmin) to avoid permissions issues.

    Example of incorrect configuration

    In this example, TLS_REQCERT hard indicates that authentication fails if the certificate cannot be recognized. This authentication fails as noted in TLS error -8179:Peer’s Certificate issuer is not recognized. For example, ldapserver uses a certificate issued by Microsoft but you provided a Verisign certificate in bad.crt.
    $ cat ldap.conf
 TLS_CACERT /path/to/badca.crt
 TLS_REQCERT hard
$ LDAPCONF=/path/to/ldap.conf ldapsearch -ZZ -x -v -s one 
  -H ldap://ldap-server-prod.vertica.com 
  -D "vertica\SearchServiceUser" -b "OU=Vertica Users, 
  dc=vertica, dc=com" -w 'SAVED_SECRET' 
  -LLL sAMAccountName=jsmith dn
  ldap_initialize( ldap://ldap-server-prod.vertica.com/??base )
  ldap_start_tls: Connect error (-11)
       additional info: TLS error -8179:Peer's Certificate issuer is 
       not recognized
  ldap_result: Can't contact LDAP server (-1)
    In this example, TLS_REQCERT hard indicates that authentication fails if the certificate cannot be recognized. This authentication fails as noted in TLS error -8179:Peer’s Certificate issuer is not recognized.  For example, ldapserver uses a certificate issued by Microsoft but you provided a Verisign certificate in bad.crt.
    Example of correct configuration 
     $ cat ldap.conf
 TLS_CACERT /path/to/ca.crt
		TLS_REQCERT hard

Correct configuration with TLS verification

Verify search and bind:

Search $ LDAPCONF=/path/to/ldap.conf ldapsearch -ZZ -x -v -s one -H ldap://ldap-server-prod.vertica.com -D "vertica\SearchServiceUser" b "OU=Vertica Users, dc=vertica,dc=com" -w 'SAVED_SECRET' -LLL sAMAccountName=jbond dn ldap_initialize( ldap://ldap-server-prod.vertica.com/??base ) filter: sAMAccountName=jsmith requesting: dn dn: CN=James Smith,OU=Vertica Users,DC=Vertica,DC=com
Bind$ LDAPCONF=/path/to/ldap.conf ldapsearch -ZZ -x -v -s one -H ldap://ldap-server-prod.vertica.com -D "CN=James Smith, OU=Vertica Users, DC=Vertica,DC=com" -b "dc=vertica,dc=com" -w 'USERS_LDAP_PASSWORD' sAMAccountName=jsmith

Verify bind for simple LDAP authentication:

Bind $ LDAPCONF=/path/to/ldap.conf ldapsearch -ZZ -x -v -s base -H ldap://ldap-server-prod.vertica.com -D "CN=James Smith, OU=Vertica Users, DC=Vertica,DC=com" -b "OU=Vertica Users, dc=vertica,dc=com" -w 'USERS_LDAP_PASSWORD' -LLL  'objectClass=none' none

Verifying SSL/LDAPS

Verify search and bind:

Search $ LDAPCONF=/path/to/ldap.conf ldapsearch  -x -v -s one -H ldaps://ldap-server-prod.vertica.com -D "vertica\SearchServiceUser" -b "OU=Vertica Users, dc=vertica,dc=com" -w 'SAVED_SECRET' -LLL sAMAccountName=jsmith dn ldap_initialize(ldaps://ldap-server-prod.vertica.com:636/??base ) filter: sAMAccountName=jsmith requesting: dn dn: CN=James Smith, OU=Vertica Users, DC=Vertica,DC=com
Bind$ LDAPCONF=/path/to/ldap.conf ldapsearch  -x -v -s one -H ldaps://ldap-server-prod.vertica.com -D "CN=James Smith, OU=Vertica Users,DC=Vertica,DC=com" -b "OU= Vertica Users, dc=vertica,dc=com" -w 'USERS_LDAP_PASSWORD' sAMAccountName=jsmith

Verify bind for simple LDAP authentication:

Bind $ LDAPCONF=/path/to/ldap.conf ldapsearch  -x -v -s base -H ldaps://ldap-server-prod.vertica.com -D "CN=James Smith, OU=Vertica Users,DC=Vertica,DC=com" -b "OU=Vertica Users, dc=vertica,dc=com" -w 'USERS_LDAP_PASSWORD' -LLL  'objectClass=none' none

Before configuring secure LDAP on Vertica, note the following:

  • Do not use TLS_REQCERT=hard if the certificate presented by the LDAP server cannot be verified. This results in a failed authentication. Examples of non-verification are:
    • The certificate is expired.
    • The issuer of the certificate is not a trusted issuer.
    • The hostname does not match the certificate subject name.
    • The certificate does not resolve via DNS (forward and reverse).
  • Using these examples, make a note of the valid settings of TLS_CACERT, TLS_REQCERT, the ldapsearch arguments (-Z or –ZZ), and the –H argument. You need these when configuring LDAP SSL with Vertica.
  • When you set the path for TLS_CACERT or TLS_CADIR:
    • The path must be the same on all nodes.
    • The file or path must be readable and owned by the database administrator
    • The file or path cannot be writeable by any other group or user.

Configuring LDAP Search and Bind with SSL on Vertica

The ldaps port is typically port 636 denoted by ldaps://. Set up LDAP Search and Bind with SSL on Vertica as follows:

  1. Create an authentication: 
    => CREATE AUTHENTICATION LDAP1 METHOD 'ldap' HOST '0.0.0.0/0';
  2. Alter LDAP1 to set the distinguished name and other parameters:
    => ALTER AUTHENTICATION LDAP1 SET 
   host='ldaps://ldap-server-prod.vertica.com',
   basedn='DC=Vertica, DC=com', 
   binddn='vertica\SearchServiceUser',
   search_attribute='sAMAccountName', bind_password='SAVED_SECRET';

    Starting with Vertica 11.0.1, LDAP SSL is managed via the LDAPAuth TLS CONFIGURATION. See, TLS for LDAP Authentication.

    For example:

    => \set ldap_ca '\''`cat ldap_ca.crt`'\''
=> CREATE CA CERTIFICATE ldap_ca AS :ldap_ca;
CREATE CERTIFICATE
ALTER TLS CONFIGURATION LDAPAuth ADD CA CERTIFICATES ldap_ca;

    ALTER TLS CONFIGURATION LDAPAUTH TLSMODE 'enable';

    For previous versions, set the following parameters:

    ALTER AUTHENTICATION LDAP1 SET tls_cacert='/path/to/ca.crt',  tls_reqcert='allow';
  3. Enable the authentication: 
    => ALTER AUTHENTICATION LDAP1 enable;
  4. Verify that the LDAP authentication parsed all of your parameters successfully:
    => SELECT * from client_auth_params where auth_name='LDAP1';
    auth_oid      | auth_name | auth_parameter_name | auth_parameter_value
------------------+-----------+---------------------+-------------------------
45035996273935258 | LDAP1     | host                | ldaps://ldap-server-prod.vertica.com
45035996273935258 | LDAP1     | basedn              | OU=Vertica Users, dc=vertica.com,dc=com      
45035996273935258 | LDAP1     | binddn              | vertica\SearchServiceUser
45035996273935258 | LDAP1     | bind_password       | SAVED_SECRET
45035996273935258 | LDAP1     | search_attribute    | sAMAccountName
45035996273935258 | LDAP1     | tls_reqcert         | allow
45035996273935258 | LDAP1     | tls_cacert          | /path/to/ca.crt
 (7 rows)

Configuring LDAP Search and Bind with TLS on Vertica

The TLS port is the regular LDAP port 389. Set up LDAP Search and Bind with TLS on Vertica as follows:

  1. Set the starttls parameter to soft or hard depending on your requirements. 
    starttls='soft | hard'
  2. Create an authentication: 
    => CREATE AUTHENTICATION LDAP1 METHOD 'ldap' HOST '0.0.0.0/0';
  3. Alter LDAP1 to set the distinguished name:
     => ALTER AUTHENTICATION LDAP1 SET 
    host='ldap://ldap-server-prod.vertica.com',
    basedn='OU=Vertica Users, DC=Vertica,DC=com',
    binddn='vertica\SearchServiceUser',
    search_attribute='sAMAccountName',
    bind_password='SAVED_SECRET';

    Starting with Vertica 11.0.1, LDAP TLS is managed via the LDAPAuth TLS CONFIGURATION. See, TLS for LDAP Authentication.

    For example:

    => \set ldap_ca '\''`cat ldap_ca.crt`'\''
=> CREATE CA CERTIFICATE ldap_ca AS :ldap_ca;
CREATE CERTIFICATE
ALTER TLS CONFIGURATION LDAPAuth ADD CA CERTIFICATES ldap_ca;

    ALTER TLS CONFIGURATION LDAPAUTH TLSMODE 'enable';

    For previous versions, set the following parameters:

    => ALTER AUTHENTICATION LDAP1 SET 
    tls_cacert='/path/to/ca.crt', 
    starttls='soft',

    tls_reqcert='allow';
  4. Enable the authentication: 
    => ALTER AUTHENTICATION LDAP1 enable;
  5. Verify that the LDAP authentication parsed all of your parameters successfully: 
    => SELECT * FROM client_auth_params WHERE auth_name='LDAP1';
   auth_oid       | auth_name | auth_parameter_name | auth_parameter_value
------------------+-----------+---------------------+---------------------
45035996273935258 | LDAP1     | host                | ldap://ldap-server-prod.vertica.com
45035996273935258 | LDAP1     | basedn              | OU=Vertica Users, dc=vertica.com,dc=com      
45035996273935258 | LDAP1     | binddn              | vertica\SearchServiceUser
45035996273935258 | LDAP1     | bind_password       | SAVED_SECRET
45035996273935258 | LDAP1     | search_attribute    | sAMAccountName
45035996273935258 | LDAP1     | tls_reqcert         | allow
45035996273935258 | LDAP1     | tls_cacert          | /path/to/ca.crt
45035996273935258 | LDAP1     | starttls            | soft
 (8 rows)

Configuring LDAP with TLS on Vertica Using Simple Bind Authentication

To set up secure LDAP for simple bind authentication do the following:

  1. Create an authentication: 
    => CREATE AUTHENTICATION LDAP2 METHOD 'ldap' HOST '0.0.0.0/0';
  2. Alter LDAP2 to set the distinguished name: 
    => ALTER AUTHENTICATION LDAP2 SET 
   host='ldap://ldap-server-prod.vertica.com' , 
   domain_prefix='vertica', 
   basedn='OU=Vertica Users, dc=vertica';

    Starting with Vertica 11.0.1, LDAP TLS is managed via the LDAPAuth TLS CONFIGURATION. See, TLS for LDAP Authentication.

    For example:

    => \set ldap_ca '\''`cat ldap_ca.crt`'\''
=> CREATE CA CERTIFICATE ldap_ca AS :ldap_ca;
CREATE CERTIFICATE
ALTER TLS CONFIGURATION LDAPAuth ADD CA CERTIFICATES ldap_ca;

    ALTER TLS CONFIGURATION LDAPAUTH TLSMODE 'enable';

    For previous versions, set the following parameters:

    dc=com' tls_cacert='/path/to/ca.crt', starttls='soft', 
   tls_reqcert='allow';
  3. Enable the authentication: 
    => ALTER AUTHENTICATION LDAP2 enable;
  4. Verify that the LDAP authentication parsed all of your parameters successfully: 
    => SELECT * FROM client_auth_params WHERE auth_name='LDAP2';
   auth_oid       | auth_name | auth_parameter_name | auth_parameter_value
------------------+-----------+---------------------+---------------------
45035996273935258 | LDAP2     | host                | ldaps://ldap-server-prod.vertica.com
45035996273935258 | LDAP2     | basedn              | OU=Vertica Users, dc=vertica.com,dc=com      
45035996273935258 | LDAP2     | domain_prefix       | vertica
45035996273935258 | LDAP2     | tls_reqcert         | allow
45035996273935258 | LDAP2     | tls_cacert          | /path/to/ca.crt
45035996273935258 | LDAP2     | starttls            | soft
 (6 rows)

Configuring LDAP Link with TLS or SSL

Starting with Vertica 11.0.1, LDAP Link and LDAP Link DryRun are configured via the LdapLink TLS CONFIGURATION. See, TLS for LDAP Link.

Appendix

This appendix provides examples of implementing LDAP bind authentication in Vertica using a user’s email and Windows domain name.

LDAP Bind Authentication with Windows domain name

The following example uses the ldapsearch command with a Windows domain name:

$ ldapsearch -x -v -s base -h 10.10.10.60 -D "vertica\jsmith,"
  -b "OU=Vertica Users, dc=Vertica,dc=com" -w 'USERS_LDAP_PASSWORD' 
  -LLL 'objectClass=none' none
  ldap_initialize( ldap://10.10.10.60 )
  filter: objectClass=none
  requesting: none 

The following example shows implementing LDAP bind in Vertica using a Windows domain name:

=> CREATE AUTHENTICATION LDAP2 METHOD 'ldap' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION LDAP2 SET host='ldap://10.10.10.60',
   domain_prefix='vertica', 
   basedn='OU=Vertica Users,dc=vertica,dc=com' ;
=> ALTER AUTHENTICATION LDAP2 enable;
=> CREATE USER jsmith; (note the use of the user name)
=> GRANT AUTHENTICATION LDAP2 to jsmith;

LDAP Bind Authentication with a user’s email address

The following example uses the ldapsearch command with an email address:

$ ldapsearch -x -v -s base -h 10.10.10.60 -D "jsmith@vertica.com" -b
  "OU=Vertica Users,dc=vertica,dc=com" -w 'USERS_LDAP_PASSWORD' -LLL
  'objectClass=none' none
  ldap_initialize( ldap://10.10.10.60 )
  filter: objectClass=none
  requesting: none

The following example shows implementing LDAP bind in Vertica using an email address:

=> CREATE AUTHENTICATION LDAP2 METHOD 'ldap' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION LDAP2 SET host='ldap://10.10.10.60', 
   email_suffix='vertica.com', basedn='OU=Vertica Users,
   dc=vertica,dc=com' ;
=> ALTER AUTHENTICATION LDAP2 enable;
=> CREATE USER jsmith; 
=> GRANT AUTHENTICATION LDAP2 to jsmith;

For More Information

Best Practices for Using LDAP Link with Vertica