Configuring LDAP link with dry runs

Vertica supports several meta-functions that let you tweak LDAP Link settings before syncing with Vertica.

Vertica supports several meta-functions that let you tweak LDAP Link settings before syncing with Vertica. Each meta-function takes LDAP Link parameters as arguments and tests a separate part of LDAP Link:

LDAP_LINK_DRYRUN_CONNECT connects to the LDAP server.
LDAP_LINK_DRYRUN_SEARCH searches for LDAP users and groups.
LDAP_LINK_DRYRUN_SYNC maps and synchronizes LDAP users and groups to their equivalents in Vertica, creating and orphaning them accordingly.

These meta-functions should be used and tested in succession, and their arguments are cumulative. That is, the parameters you use to configure LDAP_LINK_DRYRUN_CONNECT are used for LDAP_LINK_DRYRUN_SEARCH, and the arguments for those functions are used for LDAP_LINK_DRYRUN_SYNC.

The dryrun and LDAP_LINK_SYNC_START functions must be run from the clerk node. To determine the clerk node, query NODE_RESOURCES:

=> SELECT node_name, dbclerk FROM node_resources WHERE dbclerk='t';
    node_name     | dbclerk
------------------+---------
 v_vmart_node0001 | t
(1 row)

Be sure to query the LDAP_LINK_DRYRUN_EVENTS system table to verify the results of each dry run before moving to the next meta-function.

Configuring TLS for dry runs

Like the standard LDAP Link functions, LDAP Link dry-run functions pull from the 'LDAPLink' TLS Configuration for managing TLS connections. Query the TLS_CONFIGURATIONS system table to view existing TLS Configurations.

=> SELECT * FROM tls_configurations WHERE name='LDAPLink';
   name   |  owner  | certificate | ca_certificate | cipher_suites |  mode
----------+---------+-------------+----------------+---------------+---------
 LDAPLink | dbadmin | client_cert | ldap_ca        |               | DISABLE
(1 row)

For instructions on configuring TLS for LDAP Link and its dry run functions, see TLS for LDAP link.

Configuring LDAP link bind

Before configuring LDAP users and importing them to Vertica, you must first connect or "bind," with the LDAP server. Connections are managed with several parameters. For more information on each parameter, related functions, options, and default values, see LDAP link parameters.

LDAP_LINK_DRYRUN_CONNECT requires a Distinguished Name (DN), a password to authenticate with the LDAP server, and the URL to the LDAP server.

To encrypt the connection, configure the LDAPLink TLS Configuration.

By providing an empty string for the LDAPLinkBindPswd argument, you can also perform an anonymous bind if your LDAP server allows unauthenticated binds.

=> SELECT LDAP_LINK_DRYRUN_CONNECT('LDAPLinkURL','LDAPLinkBindDN','LDAPLinkBindPswd');

Dry run bind example

This tests the connection to an LDAP server at ldap://example.dc.com with the DN CN=amir,OU=QA,DC=dc,DC=com.

=> SELECT LDAP_LINK_DRYRUN_CONNECT('ldap://example.dc.com','CN=amir,OU=QA,DC=dc,DC=com','password');

                ldap_link_dryrun_connect
---------------------------------------------------------------------------------
Dry Run Connect Completed. Query v_monitor.ldap_link_dryrun_events for results.

To check the results of the bind, query the system table LDAP_LINK_DRYRUN_EVENTS.

=> SELECT event_timestamp, event_type, entry_name, role_name, link_scope, search_base from LDAP_LINK_DRYRUN_EVENTS;
        event_timestamp       |       event_type      |      entry_name      | link_scope | search_base
------------------------------+-----------------------+----------------------+------------+-------------
2019-12-09 15:41:43.589398-05 | BIND_STARTED          | -------------------- | ---------- | -----------
2019-12-09 15:41:43.590504-05 | BIND_FINISHED         | -------------------- | ---------- | -----------

Configuring LDAP link search

After a successful connection between Vertica and the LDAP server, you should configure and test your user and group search space for correctness and efficiency.

To search for users and groups on the LDAP server to import to your database, pass both the connection and search parameters to the LDAP_LINK_DRYRUN_SEARCH meta-function. The LDAP server responds with a list of users and groups that would be imported into Vertica with the given parameters.

By providing an empty string for the LDAPLinkBindPswd argument, you can also perform an anonymous search if your LDAP server's Access Control List (ACL) is configured to allow unauthenticated searches. The settings for allowing anonymous binds are different from the ACL settings for allowing anonymous searches.

=> SELECT LDAP_LINK_DRYRUN_SEARCH('LDAPLinkURL','LDAPLinkBindDN','LDAPLinkBindPswd','LDAPLinkSearchBase',
'LDAPLinkScope','LDAPLinkFilterUser','LDAPLinkFilterGroup','LDAPLinkUserName','LDAPLinkGroupName',
'LDAPLinkGroupMembers',[LDAPLinkSearchTimeout],['LDAPLinkJoinAttr']);

Dry run search example

This searches for users and groups in the LDAP server. In this case, the LDAPLinkSearchBase parameter specifies the dc.com domain and a sub scope, which replicates the entire subtree under the DN.

To further filter results, the function checks for users and groups with the person and group objectClass attributes. It then searches the group attribute cn, identifying members of that group with the member attribute, and then identifying those individual users with the attribute uid.

=> SELECT LDAP_LINK_DRYRUN_SEARCH('ldap://example.dc.com','CN=amir,OU=QA,DC=dc,DC=com','$vertica$','dc=DC,dc=com','sub',
'(objectClass=person)','(objectClass=group)','uid','cn','member',10,'dn');

                ldap_link_dryrun_search
--------------------------------------------------------------------------------
Dry Run Search Completed. Query v_monitor.ldap_link_dryrun_events for results.

To check the results of the search, query the system table LDAP_LINK_DRYRUN_EVENTS.

=> SELECT event_timestamp, event_type, entry_name, ldapurihash, link_scope, search_base from LDAP_LINK_DRYRUN_EVENTS;
        event_timestamp          |    event_type    |       entry_name       | ldapurihash | link_scope | search_base
---------------------------------+------------------+------------------------+-------------+------------+--------------
2020-01-03 21:03:26.411753+05:30 | BIND_STARTED     | ---------------------- |           0 | sub        | dc=DC,dc=com
2020-01-03 21:03:26.422188+05:30 | BIND_FINISHED    | ---------------------- |           0 | sub        | dc=DC,dc=com
2020-01-03 21:03:26.422223+05:30 | SYNC_STARTED     | ---------------------- |           0 | sub        | dc=DC,dc=com
2020-01-03 21:03:26.422229+05:30 | SEARCH_STARTED   | **********             |           0 | sub        | dc=DC,dc=com
2020-01-03 21:03:32.043107+05:30 | LDAP_GROUP_FOUND | Account Operators      |           0 | sub        | dc=DC,dc=com
2020-01-03 21:03:32.04312+05:30  | LDAP_GROUP_FOUND | Administrators         |           0 | sub        | dc=DC,dc=com
2020-01-03 21:03:32.043182+05:30 | LDAP_USER_FOUND  | user1                  |           0 | sub        | dc=DC,dc=com
2020-01-03 21:03:32.043186+05:30 | LDAP_USER_FOUND  | user2                  |           0 | sub        | dc=DC,dc=com
2020-01-03 21:03:32.04319+05:30  | SEARCH_FINISHED  | **********             |           0 | sub        | dc=DC,dc=com

Configuring LDAP link sync

After configuring the search space, you'll have a list of users and groups. LDAP sync maps LDAP users and groups to their equivalents in Vertica. The LDAPLinkUserName maps to the Vertica usernames and the LDAPLinkGroupName maps to Vertica roles.

=> SELECT LDAP_LINK_DRYRUN_SYNC('LDAPLinkURL','LDAPLinkBindDN','LDAPLinkBindPswd','LDAPLinkSearchBase',
'LDAPLinkScope','LDAPLinkFilterUser','LDAPLinkFilterGroup','LDAPLinkUserName','LDAPLinkGroupName',
'LDAPLinkGroupMembers',[LDAPLinkSearchTimeout],['LDAPLinkJoinAttr']);

Dry run sync example

To perform a dry run to map the users and groups returned from LDAP_LINK_DRYRUN_SEARCH, pass the same parameters as arguments to LDAP_LINK_DRYRUN_SYNC.

=> SELECT LDAP_LINK_DRYRUN_SYNC('ldap://example.dc.com','CN=amir,OU=QA,DC=dc,DC=com','$vertica$','dc=DC,dc=com','sub',
'(objectClass=person)','(objectClass=group)','uid','cn','member',10,'dn');

                          LDAP_LINK_DRYRUN_SYNC
------------------------------------------------------------------------------------------
Dry Run Connect and Sync Completed. Query v_monitor.ldap_link_dryrun_events for results.

To check the results of the sync, query the system table LDAP_LINK_DRYRUN_EVENTS.

=> SELECT event_timestamp, event_type, entry_name, ldapurihash, link_scope, search_base from LDAP_LINK_DRYRUN_EVENTS;
        event_timestamp          |     event_type      |       entry_name       | ldapurihash | link_scope | search_base
---------------------------------+---------------------+------------------------+-------------+------------+--------------
2020-01-03 21:08:30.883783+05:30 | BIND_STARTED        | ---------------------- |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:30.890574+05:30 | BIND_FINISHED       | ---------------------- |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:30.890602+05:30 | SYNC_STARTED        | ---------------------- |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:30.890605+05:30 | SEARCH_STARTED      | **********             |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939369+05:30 | LDAP_GROUP_FOUND    | Account Operators      |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939395+05:30 | LDAP_GROUP_FOUND    | Administrators         |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939461+05:30 | LDAP_USER_FOUND     | user1                  |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939463+05:30 | LDAP_USER_FOUND     | user2                  |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939468+05:30 | SEARCH_FINISHED     | **********             |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939718+05:30 | PROCESSING_STARTED  | **********             |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939887+05:30 | USER_CREATED        | user1                  |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939895+05:30 | USER_CREATED        | user2                  |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939949+05:30 | ROLE_CREATED        | Account Operators      |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.939959+05:30 | ROLE_CREATED        | Administrators         |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.940603+05:30 | PROCESSING_FINISHED | **********             |           0 | sub        | dc=DC,dc=com
2020-01-03 21:08:31.940613+05:30 | SYNC_FINISHED       | ---------------------- |           0 | sub        | dc=DC,dc=com