Just-in-time user provisioning

To use this feature, you must use Keycloak as your identity provider.

Just-in-time (JIT) user provisioning is the act of automatically configuring an authenticated user and their roles based on information provided by the identity provider.

When a client uses an OAuth authentication record that enables JIT user provisioning, Vertica automatically performs the following actions:

Creates the user if they do not already exist in the database. The length of the username in the identity provider cannot be greater than 128 characters.
(Keycloak only) Grants to the user and sets as default the roles associated with the user (as specified by the identity provider), provided the roles already exist in Vertica.
Grants to the user the authentication record used to authenticate them if neither their user nor role has a grant on that record.

For example, if a client presents an OAuth token to authenticate as user Alice with role director, and Alice does not exist in Vertica, Vertica automatically creates the user Alice, grants to her the authentication record, and grants to her the director role as a default role.

To view users created by JIT user provisioning and which authentication record they use, query the USERS system table:

=> SELECT user_name, managed_by_oauth2_auth_id FROM users;
 user_name |  managed_by_oauth2_auth_id
-----------+-----------------------------
 dbadmin   |
 Bob       | 45035996273853300
 Margie    |
 Alice     | 45035996273866484
(4 rows)

For details on using JIT user provisioning with OAuth authentication, see Configuring OAuth authentication.

Enabling just-in-time user provisioning

JIT user provisioning is enabled at the authentication record level by setting the oauth2_jit_enabled parameter:

=> CREATE AUTHENTICATION v_oauth METHOD 'oauth' HOST '0.0.0.0/0';
=> ALTER AUTHENTICATION v_oauth SET oauth2_jit_enabled = 'yes';

=> SELECT auth_name, is_oauth2_jit_enabled FROM client_auth WHERE auth_name='v_oauth';
  auth_name  |  is_oauth_2_jit_enabled
-------------+--------------------------
 v_oauth     | True
(1 row)

Automatic role assignment

The roles automatically assigned to JIT-provisioned active users are based on the information provided by the identity provider (either from the endpoints introspect_url or userinfo_url) and are identified by the client/application specified by the OAuth2JITClient configuration parameter ( vertica by default):

=> ALTER DATABASE DEFAULT SET OAuth2JITClient = "vertica";

With OAuth2JITClient set to vertica, roles that both exist in Vertica and are listed in resource_access.vertica.roles are automatically granted to and set as default roles for JIT-provisioned users.

The identity provider shares user roles through the introspect_url or userinfo_url endpoints. Vertica first sends a request to the introspect_url. If no roles are found in the response, Vertica sends a request to userinfo_url.

For example, if a client attempts to authenticate as the user bob and no user with the same name exists in the database, Vertica sends the following request to the identity provider's introspect_url to retrieve the roles given to bob by the identity provider (this example is truncated):

{
    ...
    "resource_access": {
        "vertica": {
            "roles": [
                "customer-facing",
                "order-management",
                "idp-exclusive-role"
            ]
        },
        "account": {
            "roles": [
                "manage-account",
                "manage-account-links",
                "view-profile"
            ]
        }
    },
    "scope": "email profile roles",
    "sid": "dcdd14b1-fe47-491e-b62b-10d1e05c6ffe",
    "client_id": "vertica",
    "username": "bob",
    "active": true
}

Because the active field is true and bob has no corresponding user in the Vertica server, Vertica automatically creates the user bob and grants to him the roles that exist in Vertica and are listed in resource_access.vertica.roles: customer-facing and order-management. The role idp-exclusive-role does not exist in Vertica, so it is ignored.

Automatic user pruning

You can enable automatic user pruning to periodically drop users created by JIT user provisioning if they do not log in after a certain period of time. This cleanup service is managed by the following database-level configuration parameters:

EnableOAuthJITCleanup: Whether to enable cleanup (disabled by default).

=> ALTER DATABASE DEFAULT SET EnableOAuthJITCleanup = 1; --enables the pruning service
=> ALTER DATABASE DEFAULT SET EnableOAuthJITCleanup = 0; --disables the pruning service

OAuth2UserExpiredInterval: The number of days a user must be inactive before it is dropped (14 by default). This is calculated based on the current date and the LAST_LOGIN_TIME in the USERS system table.
Note

The LAST_LOGIN_TIME as recorded by the USERS system table is not persistent; if the database is restarted, the LAST_LOGIN_TIME for users created by just-in-time user provisioning is set to the database start time (this appears as an empty value in LAST_LOGIN_TIME).

You can view the database start time by querying the DATABASES system table:
```
=> SELECT database_name, start_time FROM databases;
 database_name |          start_time
---------------+-------------------------------
 VMart         | 2023-02-06 14:26:50.630054-05
(1 row)
```
```
=> ALTER DATABASE DEFAULT SET OAuth2UserExpiredInterval = 20;
```
GlobalHeirUsername: The user to reassign objects to if the owner is a JIT-provisioned (or LDAP) user that got dropped by the pruning service. If set to <auto>, objects are reassigned to the dbadmin.
```
=> ALTER DATABASE DEFAULT SET GlobalHeirUsername = <auto>
```

The cleanup service runs daily and there can be a delay of up to 24 hours for dropping an expired user.

Just-in-time user provisioning

Enabling just-in-time user provisioning

Automatic role assignment

Automatic user pruning

Note