This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

LDAP link service

LDAP Link enables synchronization between the LDAP and Vertica servers.

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 General and Connection Parameters for more information.

The LDAP Link dry run meta-functions allow you to configure the service in discrete stages before making any changes to your database. These stages are:

  1. LDAP Link Bind: Establishing a connection between the LDAP server and the Vertica database

  2. LDAP Link Search: Searching the LDAP server for users and groups

  3. LDAP Link Sync: Mapping LDAP users and groups to their equivalents in Vertica

Query the system table LDAP_LINK_DRYRUN_EVENTS to view the results of each dry run.

For more information on dry runs and configuring LDAP Link, see Configuring LDAP link with dry runs.

Enable LDAP Link as shown:

=> ALTER DATABASE dbname SET PARAMETER LDAPLinkURL='ldap://example.dc.com',
    LDAPLinkSearchBase='dc=DC,dc=com', LDAPLinkBindDN='CN=jsmith,OU=QA,DC=dc,DC=com,
    LDAPLinkBindPswd='password',LDAPLinkFilterUser='(objectClass=inetOrgPerson)', LDAPLinkFilterGroup='(objectClass=group)', LDAPLinkOn=1;
=> SELECT ldap_link_sync_start();

See LDAP link parameters.

After you enable LDAP Link, synchronization occurs according to this workflow:

  1. The System Administrator creates users and user groups on the LDAP server.

  2. The System Administrator sets up LDAP Link service parameters as required and enables the service.

  3. Using the LDAP Link service, Vertica Analytic Database replicates the users and user groups from the Application LDAP to the Vertica database, creating Vertica users and roles.

  4. The LDAP server uses Kerberos (KDC) to authenticate the user logging in to Vertica.

    • The LDAP user can log in to Vertica if assigned the appropriate authentication type.

    • After logging in, you can grant users privileges using GRANT statements or as part of a Group.

1 - 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:

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.

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

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

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

2 - Using LDAP link

When you use LDAP Link, the following are directly affected and help you manage and monitor the LDAP Link - Vertica Analytic Database synchronization:.

When you use LDAP Link, the following are directly affected and help you manage and monitor the LDAP Link - Vertica Analytic Database synchronization:

  • User and Group management

  • LDAP Link User Flag

  • Blocked Commands

  • Client Authentication types

To cancel an in-progress synchronization, use LDAP_LINK_SYNC_CANCEL.

User and group management

Users and groups created on the LDAP server have a specific relationship with those users and roles replicated to the Vertica server:

  • The user-group relationship on the LDAP server is maintained when those users and groups (roles) are synchronized with Vertica Analytic Database.

  • If a user or group name exists on the Vertica database and a user or group with the same names is synchronized from the LDAP Server using LDAP Link, the users or groups become conflicted. Vertica cannot support multiple users with the same name. To resolve this, see User Conflicts.

  • If the LDAP server contains a circular relationship, Vertica accepts and creates roles for the first non-circular part of the relationship returned by the LDAP server and ignores the rest.

    For example, suppose the LDAP server contains groups A and B, where A contains B, and B contains A, creating a circular relationship.

    If the LDAP server first returns that A contains B, Vertica creates roles A and B, and grants role A to role B. Vertica then ignores the fact that group B also contains A.

LDAP Link uses the entries in the dn: section of the LDAP configuration file as the unique user identifier when synchronizing a user to the Vertica Analytic Database:

dn: cn=user1,ou=dev,dc=example,dc=com
cn: user1
ou: dev
id: user1

The uid parameter in the LDAP configuration file indicates the LDAP user name.

uid: user1

Upon synchronization, the dn: entry gets mapped to the uid: to identify the Vertica Analytic Database user.

If you change a setting in the dn: and do not change the uid:, LDAP Link interprets the user as a new user when re-synchronizing with the Vertica Analytic Database. In this case, the existing Vertica Analytic Database user with that uid: gets deleted from Vertica and a new Vertica Analytic Database user is created.

If you change the uid: and not the dn: on LDAP, the uid on the Vertica Analytic Database gets updated to the new uid. Since you did not change the dn: LDAP Link does not interpret the user as a new user.

As a dbadmin user, you can access the vs_users table to monitor user behavior on the Vertica Analytic Database. The users table contains an ldap_dn field that identifies whether or not the Vertica Analytic Database user is also an LDAP Link user. This example shows the ldap_dn field set to dn indicating the Vertica Analytic Database user is also an LDAP Link user:

=> SELECT * FROM vs_users;
-[ RECORD 1 ]---------+--------------------------------------------------
user_id               | 45035996273704962
user_name             | dbadmin
is_super_user         | t
profile_name          | default
is_locked             | f
lock_time             |
resource_pool         | general
memory_cap_kb         | unlimited
temp_space_cap_kb     | unlimited
run_time_cap          | unlimited
max_connections       | unlimited
connection_limit_mode | database
idle_session_timeout  | unlimited
all_roles             | dbduser*, dbadmin*, pseudosuperuser*
default_roles         | dbduser*, dbadmin*, pseudosuperuser*
search_path           |
ldap_dn               | dn
ldap_uri_hash         | 0
is_orphaned_from_ldap | f

Blocked commands

Be aware that the following SQL statements are blocked for Vertica users with ldapdn set to dn in the vs_users table:

Client authentication types

LDAP user and groups cannot log in to Vertica if client authentication is not assigned to the user or group. You can use the following valid authentication types for LDAP users and groups:

  • GSS

  • Ident

  • LDAP

  • Reject

  • Trust

3 - LDAP link parameters

Use LDAP Link parameters to determine:.

Use LDAP Link parameters to determine:

  • LDAP Link operations, such as enabling or disabling LDAP Link and how often to perform replication

  • Authentication parameters, including SSL authentication parameters

  • Users and groups that inherit unowned objects

  • How to resolve conflicts

To configure TLS for LDAP Link, see TLS for LDAP link.

This example shows how you can set:

  • LDAPLinkURL, the URL of the LDAP server.

  • LDAPLinkSearchBase, the base DN from which to start replication.

You also see how to set the LDAP Link Bind authentication parameters (LDAPLinkBindDN and LDAPLinkBindPswd) and enables LDAP Link (LDAPLinkOn).

=> ALTER DATABASE myDB1 SET PARAMETER LDAPLinkURL='ldap://10.60.55.128',
LDAPLinkSearchBase='dc=corp,dc=com',LDAPLinkBindDN='dc=corp,dc=com',LDAPLinkBindPswd='password';

=> ALTER DATABASE myDB1 SET PARAMETER LDAPLinkOn = '1';

General and connection parameters

Parameter Description
LDAPLinkOn

Enables or disables LDAP Link.

Valid Values:

0—LDAP Link disabled

1—LDAP Link enabled

Default: 0

LDAPLinkURL

The LDAP server URL.

To use a plaintext connection between Vertica and the LDAP server, begin the LDAPLinkURL with ldap:// and set the TLSMODE of LDAPLink to DISABLE.

To use StartTLS, begin the LDAPLinkURL with ldap:// and set the TLSMODE of LDAPLink to ENABLE or higher.

To use LDAPS, begin the LDAPLinkURL with ldaps:// and set the TLSMODE of LDAPLink to ENABLE or higher.

Example:

=> SET PARAMETER LDAPLinkURL='ldap://example.dc.com';

LDAPLinkCron

A cron expression, the exact time at which the LDAP and Vertica servers should synchronize. Unlike LDAPLinkInterval, the runtime of the synchronization does not affect the next scheduled synchronization. Setting this parameter overrides LDAPLinkInterval.

New synchronizations are only scheduled after the current one ends. This means that if a synchronization runs for long enough to reach the start of what would be the "next" synchronization according to the cron expression, that "next" synchronization will not run, and in fact will not be scheduled until after the current one finishes.

Default: None (empty).

LDAPLinkInterval

The time interval, in seconds, by which the LDAP and Vertica servers should synchronize. The interval is calculated based on the completion time of the previous synchronization operation, not its start time. This means that with an interval of 86400 seconds (one day), if the previous synchronization started at 9:00 and ended at 9:30, then the next synchronization would start at 9:30 the next day.

To use this scheduling method, LDAPLinkCron must not be set (default).

Default: 86400 (one day).

LDAPLinkFirstInterval

The first interval, in seconds, for LDAP/Vertica synchronization after the clerk node joins the cluster.

Default: 120

LDAPLinkRetryInterval

The time, in seconds, the system waits to retry a failed synchronization.

Default: 10

LDAPLinkRetryNumber

The number of retry attempts if synchronization failed.

Default: 10.

LDAPLinkSearchBase

The base dn from where to start replication.

Example:

=> SET PARAMETER LDAPLinkSearchBase='ou=vertica,dc=mycompany,dc=com';

Vertica recommends using a separate OU for database users.

LDAPLinkSearchTimeout

The timeout length, in seconds, for the LDAP search operation during an LDAP Link Service run.

Default: 10

LDAPLinkScope

Indicates what dn level to replicate.

Valid Values:

  • sub—Replicate entire subtree under baseDN

  • one—Replicate to one level under baseDN

  • base —Replicate only the baseDN level

If you decrease the scope (for example, sub to one), some users may not be recognized during the next synchronization.

Default: sub

LDAPLinkFilterUser

Determines how to filter users to be replicated.

Default: "(objectClass=inetOrgPerson)"

LDAPLinkFilterGroup

Determines how to filter groups to be replicated.

Default: "(objectClass=groupofnames)"

LDAPLinkGroupName

[Optional] The LDAP field to use when creating a role name in Vertica.

Default: cn

LDAPLinkGroupMembers

The LDAP group that identifies the members of an LDAP group. This attribute returns a Fully Qualified Domain Name (FQDN).

Default: member

LDAPLinkUserName

The LDAP field to use when creating a user name in Vertica.

Default: uid

LDAPLinkJoinAttr

Specifies the attribute on which you want to join to assign users to their roles.

Default: dn

Example:

POSIX groups associate users and groups with the uid attribute instead of dn.

=> SET PARAMETER LDAPLinkJoinAttr='uid';

LDAPLinkAddRolesAsDefault

Specifies whether the users synchronized through LDAP Link should have their groups set as default roles. If LDAPLinkAddRolesAsDefault is disabled (default), then the users are granted their groups as non-default roles, which must be manually enabled with SET ROLE.

Default: 0 (disabled)

Example:

To enable:

=> ALTER DATABASE DEFAULT SET LDAPLinkAddRolesAsDefault = 1;

To disable:

=> ALTER DATABASE DEFAULT SET LDAPLinkAddRolesAsDefault = 0;

Authentication parameters

Parameter Description
LDAPLinkBindDN

The LDAP Bind DN used for authentication.

Example:

=> SET PARAMETER LDAPLinkBindDN='CN=amir,OU=QA,DC=dc,DC=com';

LDAPLinkBindPswd

The valid password for the LDAP Bind DN to access the server. Only accessible by the dbadmin user.

Example:

=> SET PARAMETER LDAPLinkBindPswd='password';

Miscellaneous parameters

Parameter Description
LDAPLinkConflictPolicy

Determines how to resolve a user conflict.

Valid Values:

IGNORE—Ignores the incoming LDAP user and maintains the existing Vertica user.

MERGE—Converts the existing user to an LDAP user.

Default: MERGE

LDAPLinkStopIfZeroUsers

Enables or disables the shutdown of LDAPLink synchronization if no users are found in LDAP.

Valid values:

0 - Disables the LDAPLink synchronization shutdown if no users are found. This may lead to inadvertent dropping of Vertica users.

1 - Enables the LDAPLink synchronization shutdown if no users are found. This prevents inadvertent dropping of Vertica users.

LDAPLinkDryRun

[Optional] Tests the connection to the LDAP server and logs the response without doing a synchronization. Also tests if parameters are correctly set.

Note that this parameter is not the preferred dry run method. Instead, the LDAP_Link_Dryrun family of meta-functions provides more granular control over configurations and is the preferred way to perform LDAP Link dry runs.

Valid Values:

0 - Disables LDAPLinkDryRun

1 - Enables LDAPLinkDryRun

Default: 0

LDAPLinkConfigFile [Optional] If this parameter is set with the path to a .LDIF file, the LDAP Link service will use the file as the source tree instead of connecting to the LDAP server.

See Configuration parameter management for information on setting LDAP Link parameters.

4 - TLS for LDAP link

This page covers the LDAP Link service context.

Vertica establishes a connection to an LDAP server in two contexts, and each context has a corresponding TLS Configuration that controls if each connection should use TLS:

  1. LDAPLink: using the LDAPLink service or its dry run functions to synchronize users and groups between Vertica and the LDAP server.

  2. LDAPAuth: when a user with an ldap authentication method attempts to log into Vertica, Vertica attempts to bind the user to a matching user in the LDAP server. If the bind succeeds, Vertica allows the user to log in.

Query TLS_CONFIGURATIONS to view existing TLS Configurations:

=> SELECT * FROM tls_configurations WHERE name IN ('LDAPLink', 'LDAPAuth');
   name   |  owner  | certificate | ca_certificate | cipher_suites |  mode
----------+---------+-------------+----------------+---------------+----------
 LDAPLink | dbadmin | client_cert | ldap_ca        |               | VERIFY_CA
 LDAPAuth | dbadmin | client_cert | ldap_ca        |               | DISABLE
(2 rows)

This page covers the LDAP Link service context. For details on the LDAP authentication context, see TLS for LDAP authentication.

Vertica uses the LDAP Link service to retrieve users and groups from the LDAP server and to create corresponding users and roles in the database. To configure TLS for LDAP Link and its dry run functions, use the following procedure.

This procedure uses the predefined TLS Configuration LDAPLink. To create a custom TLS Configuration, see TLS configurations.

For details on key and certificate generation, see Generating TLS certificates and keys.

  1. If you want Vertica to verify the LDAP server's certificate before establishing the connection, generate or import a CA certificate and add it to the LDAPLink TLS Configuration.

    For example, to import the existing CA certificate LDAP_CA.crt:

    => \set ldap_ca '\''`cat ldap_ca.crt`'\''
    => CREATE CA CERTIFICATE ldap_ca AS :ldap_ca;
    CREATE CERTIFICATE
    

    Then, to add the ldap_ca CA certificate to LDAPLink:

    ALTER TLS CONFIGURATION LDAPLink ADD CA CERTIFICATES ldap_ca;
    
  2. If your LDAP server verifies client certificates, you must generate or import a client certificate and its key and add it to the LDAPLink TLS Configuration. Vertica presents this certificate to the LDAP server for verification by its CA.

    For example, to import the existing certificate client.crt (signed by the imported CA) and key client.key:

    => \set client_key '\''`cat client.key`'\''
    => CREATE KEY client_key TYPE 'RSA' AS :client_key;
    CREATE KEY
    
    => \set client_cert '\''`cat client.crt`'\''
    => CREATE CERTIFICATE client_cert AS :client_cert SIGNED BY ldap_ca KEY client_key;
    CREATE CERTIFICATE
    

    Then, to add client_cert to LDAPLink:

    => ALTER TLS CONFIGURATION LDAPLink CERTIFICATE client_cert;
    
  3. Enable TLS or LDAPS (the exact protocol used depends on the value of host in the AUTHENTICATION object) by setting the TLSMODE to one of the following. TRY_VERIFY or higher requires a CA certificate:

    • ENABLE: Enables TLS. Vertica does not check the LDAP server's certificate.

    • TRY_VERIFY: Establishes a TLS connection if one of the following is true:

      • The LDAP server presents a valid certificate.

      • The LDAP server doesn't present a certificate.

      If the LDAP server presents an invalid certificate, a plaintext connection is used.

    • VERIFY_CA: Connection succeeds if Vertica verifies that the LDAP server's certificate is from a trusted CA. Using this TLSMODE forces all connections without a certificate to use plaintext.

    • VERIFY_FULL: Connection succeeds if Vertica verifies that the LDAP server's certificate is from a trusted CA and the cn (Common Name) or subjectAltName attribute matches the hostname or IP address of the LDAP server.

      The cn is used for the username, so subjectAltName must match the hostname or IP address of the LDAP server.

    For example:

    => ALTER TLS CONFIGURATION LDAPLink TLSMODE 'verify_ca';
    ALTER TLS CONFIGURATION
    
  4. Verify that the LDAPLinkTLSConfig parameter is using the TLS Configuration:

    => SHOW CURRENT LDAPLinkTLSConfig;
      level  |       name        | setting
    ---------+-------------------+----------
     DEFAULT | LDAPLinkTLSConfig | LDAPLink
    (1 row)
    
  5. Set the LDAP Link Parameters according to your use case.

5 - Troubleshooting LDAP link issues

Various issues can arise with LDAP Link service, including:.

Various issues can arise with LDAP Link service, including:

  • Disconnected (Orphaned) users and roles

  • Lost objects

  • User conflicts

Disconnected (orphaned) users and roles

Vertica users and roles synchronized through LDAP Link can become disconnected, or orphaned, if an issue arises with the LDAP Link service. For example, users and roles become orphaned when you change the connection to the LDAP server as the following scenario describes:

  1. Create an LDAP connection as follows:

    => ALTER DATABASE MyDB1 SET PARAMETER LDAPLinkURL='ldap://ebuser',
    LDAPLinkSearchBase='dc=example,dc=com', LDAPLinkBindDN='mega',
    LDAPLinkBindPswd='$megapassword$';
    => ALTER DATABASE MyDB1 SET PARAMETER LDAPLinkOn = '1';
    
  2. Run an LDAP Link session to synchronize LDAP and Vertica users.

  3. Change one or more connection parameters from Step 1. You can change the connection only if you change one of the LDAPLinkURL or LDAPLinkSearchBase parameters. Users will not be orphaned if the new and old LDAPLinkURL and LDAPLinkSearchBase contain the same set of users.

  4. Run another LDAP Link session. The system attempts to re-synchronize LDAP and Vertica users. Because the connection has changed, the existing Vertica users cannot be synchronized with the LDAP users from the new connection. These Vertica users become orphaned.

As the dbadmin, you can identify orphaned users by checking the is_orphaned_from_ldap column in the USERS system table:

 => SELECT is_orphaned_from_ldap FROM users;

A field value of t indicates that the user is an orphaned user. Orphaned Vertica users cannot connect to the LDAP server and cannot login to Vertica using LDAP authentication (however, other authentication methods assigned to the user still work). In this case, you can delete the orphaned Vertica user and run the LDAP Link service to resynchronize users.

Re-parented objects

When you delete users or groups from the LDAP server, the LDAP Link service removes the same users and roles from Vertica, but does not delete objects owned by the deleted users and roles. To give these unowned objects a new owner, use the GlobalHeirUsername parameter, which specifies a user as the new parent for all objects originally owned by deleted users.

For example, to give ownership of unowned objects to user1, creating the user if it does not already exist:

=> ALTER DATABASE example_db SET PARAMETER GlobalHeirUsername=user1;

By default, this parameter is set to <auto> which re-parents objects to the dbadmin user.

If GlobalHeirUsername is empty, objects are not re-parented to another user.

For details, see Security Parameters.

User conflicts

Vertica users and roles synchronized using LDAP Link can become conflicted. Such conflicts can occur, for example, when you create a new user or group on the LDAP server and another user or role with the same name exists on the Vertica.

As the dbadmin, use one of the following parameters to resolve user conflicts:

  • LDAPLinkConflictPolicy

  • LDAPLinkStopIfZeroUsers

LDAPLinkConflictPolicy

LDAPLinkConflictPolicy controls how Vertica behaves when it encounters a conflict. Changes to this parameter take effect during the next synchronization.

  • LDAPLinkConflictPolicy=IGNORE ignores the incoming LDAP users and retains the existing Vertica user

  • LDAPLinkConflictPolicy=MERGE (default) merges the incoming LDAP user with the Vertica user and converts the database user to an LDAP user, retaining the database user's objects.

For example, to set the parameter:

=> ALTER DATABASE example_db SET PARAMETER LDAPLinkConflictPolcy='MERGE';

LDAPLinkStopIfZeroUsers

LDAPLinkStopIfZeroUsers controls how Vertica behaves when the LDAP server has zero users during synchronization.

  • LDAPLinkStopIfZeroUsers=0 does not stop the synchronization if no users are found in the LDAP server and all Vertica users with are dropped.

  • LDAPLinkStopIfZeroUsers=1 stops the synchronization if no users are found in the LDAP server and returns an error. No Vertica users are dropped.

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)

Use the ldap_link_events table to monitor LDAP Link synchronization:

=> SELECT transaction_id, event_type, entry_name, entry_oid FROM ldap_link_events;
   transaction_id |    event_type      | entry_name | entry_oid
------------------+--------------------+------------+-----------
45035996273705317 | SYNC_STARTED       |            |         0
45066962732553589 | SYNC_FINISHED      |            |         0
45066988112255317 | PROCESSING_STARTED |            |         0
23411234566789765 | USER_CREATED       | tuser      | 234548899
(4 rows)