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 (IdP).

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 exist in the database. The username must conform to conventions described in Identifiers.
If you configure automatic role assignment, Vertica identifies roles that exist in both the user or group identity and the database and grants each role to the Vertica database user as a default role.
Grants to the user the authentication record that is associated with the IdP if the user or role does not already have a grant on that authentication record.

You can view all JIT-provisioned users and their authentication record with the USERS system table. If the user was JIT-provisioned, then the managed_by_oauth2_auth_id column lists the JIT-enabled authentication record:

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

Enable JIT provisioning

When Vertica authenticates a JIT-provisioned user, it loops through all existing OAuth authentication records for a record that can authenticate the OAuth token in the client request. If Vertica locates the authentication record, it assigns the record to the JIT-provisioned user.

After you create an OAuth authentication record, you must alter its authentication record to enable JIT. To enable JIT, set the oauth2_jit_enabled OAuth authentication parameter to yes:

=> ALTER AUTHENTICATION v_oauth SET oauth2_jit_enabled = 'yes';
ALTER AUTHENTICATION

To confirm that JIT authentication is enabled on the authentication record, query the CLIENT_AUTH_PARAMS table:

=> SELECT * FROM CLIENT_AUTH_PARAMS WHERE auth_name = 'v_oauth' AND auth_parameter_name = 'oauth2_jit_enabled';
     auth_oid      | auth_name | auth_parameter_name | auth_parameter_value
-------------------+-----------+---------------------+----------------------
 45035996273859012 | v_oauth   | oauth2_jit_enabled  | yes
(1 row)

Automatic role assignment

Vertica can automatically assign a role to a JIT-provisioned user if the OAuth token contains the following:

Client identity role that exactly matches a Vertica database role
Group identity role that exactly matches a Vertica database role
Group identity name that exactly matches a Vertica database role

Vertica updates the role assignment each time a JIT-provisioned user authenticates with the OAuth token. For example, if Vertica automatically assigned a role using the group identity name and then that group identity is removed from the IdP, Vertica revokes that role the next time that JIT-provisioned user authenticates to Vertica. In addition, you can add a role to a blocklist that prevents Vertica from automatically granting that role during JIT provisioning. These features ensure that Vertica reflects up-to-date role assignments so that you can manage your database user identities from a single, secure location.

Prerequisites

Create an OAuth authentication record.
Enable JIT provisioning.
Create the Vertica database roles that you want to automatically assign to JIT-provisioned users.

Configure role assignment

An IdP manages Vertica as a client identity. An IdP client can have zero or more roles, where each role has specific privileges in the client environment. IdPs also manage group identities, where each group identity is a collection of user identities that share attributes and roles.

During automatic role assignment, Vertica treats each IdP client role, group role, and group name the same. If the JIT-provisioned user has a client role, group role, or group name that maps to a Vertica role, Vertica automatically assigns that role to the JIT-provisioned user. A single OAuth token can contain both client and group identities.

To associate a database with an IdP client or group identity, set the corresponding security parameter:

OAuth2JITRolesClaimName identifies the Vertica client identity in an IdP that contains the role mappings:
```
=> ALTER DATABASE DEFAULT SET OAuth2JITRolesClaimName = 'resource_access.IdPClientName.roles';
```
OAuth2JITGroupsClaimName identifies a group identity that contains Vertica client role mappings:
```
=> ALTER DATABASE DEFAULT SET OAuth2JITGroupsClaimName = 'IdPGroupClaimName';
```

Vertica automatically assigns a role only if the database security parameter setting exactly matches an IdP client or group claim name. If there is a match, then Vertica looks at the match in the IdP. If there is a client role, group name, or group role that exactly matches an existing Vertica database role, then Vertica grants the database role to the JIT-provisioned user. Vertica ignores any roles or groups that do not have exact matches in Vertica.

Restrict role assignment

Vertica can assign any role to a JIT-provisioned user, including DBADMIN and PSEUDOSUPERUSER. Because granting unrestricted privileges might raise security concerns, you can restrict Vertica role assignments with the OAuth2JITForbiddenRoles security parameter:

=> ALTER DATABASE DEFAULT SET OAuth2JITForbiddenRoles = 'dbadmin,pseudosuperuser'

When an IdP user has a role or group mapping that matches a role in OAuth2JITForbiddenRoles, Vertica does not grant the JIT-provisioned user that role.

Verify role assignments

To verify which roles were automatically assigned to JIT-provisioned users, you can query the vs_users table. The num_jit_roles column lists the number of roles that were assigned with JIT provisioning, and oauth2_jit_roles list the names of each role:

=> \x
Expanded display is on.
=> SELECT num_default_roles, default_roles, num_jit_roles, oauth2_jit_roles, managed_by_oauth2_auth_id
   FROM vs_users 
   WHERE name = 'username';
-[ RECORD 1 ]-------------+----------------------------------------------------------
num_default_roles         | 2
default_roles             | vertica-admin, vertica-user
num_jit_roles             | 2
oauth2_jit_roles          | vertica-admin, vertica-user
managed_by_oauth2_auth_id | 45035996273851314

Example

In this example scenario, an organization uses the Keycloak IdP to manage user identities across their organization. The Keycloak implementation has a client identity named vertica and a group identity named groups. The following table lists the client roles, IdP groups, and group roles:

vertica client roles groups name groups roles

`vertica` client roles	`groups` name	`groups` roles
dbadmin orders_user view_realm	realm_admin	user_admin

dbadmin

orders_user

view_realm

realm_admin

user_admin

Vertica configuration

Vertica has a database named orders with the following security parameter settings that define the roles claim, group claim, and restricted roles:

=> SELECT parameter_name, current_value FROM configuration_parameters
   WHERE parameter_name = 'OAuth2JITRolesClaimName'
   OR parameter_name = 'OAuth2JITGroupsClaimName'
   OR parameter_name = 'OAuth2JITForbiddenRoles';
      parameter_name      |            current_value
--------------------------+-------------------------------------
 OAuth2JITForbiddenRoles  | dbadmin,pseudosuperuser
 OAuth2JITGroupsClaimName | groups
 OAuth2JITRolesClaimName  | resource_access.vertica.roles
(3 rows)

Vertica has the following users:

=> SELECT USER_NAME FROM USERS;
 USER_NAME
-----------
 dbadmin
(1 row)

Vertica has the following assigned roles:

=> SELECT NAME, ASSIGNED_ROLES FROM ROLES;
      NAME       | ASSIGNED_ROLES
-----------------+----------------
 public          |
 dbduser         |
 dbadmin         | dbduser*
 pseudosuperuser | dbadmin*
 ...
 orders_user     |
 user_admin      |

Role assignment process

A client authenticates to the orders database as a user named Alice, but the Alice user does not exist in Vertica. The authentication record associated with the orders database uses IDP validation and has oauth2_jit_enabled set to yes, so Vertica can JIT provision the Alice user and then assign roles that exist in both the IdP and Vertica.

When Alice authenticates to the orders database, Vertica performs the following steps:

Provisions the Alice user.
Sends a request to the IdP to retrieve roles using the OAuth2JITRolesClaimName and OAuth2JITGroupsClaimName security parameter settings. In this case, Vertica requests roles for a client identity named vertica and a group identity named groups.
Compares all client and group roles in the IdP response to the roles in Vertica and performs the following:
- Grants the orders_user role to the JIT-provisioned Alice user, and sets it as a default role.
- Grants the user_admin role to the JIT-provisioned Alice user, and sets it as a default role.
- Does not grant the dbadmin role to Alice because it is specified by the OAuth2JITForbiddenRoles security parameter.
- Ignores the realm_admin group name and view_admin group role because there is no corresponding role in Vertica.

After the automatic role assignment process completes, Alice has default roles that reflect the IdP configuration:

=> SELECT user_name, default_roles FROM users 
   WHERE user_name = 'Alice';
 user_name |      default_roles
-----------+-------------------------
 Alice     | orders_user, user_admin
(1 row)

The automatic role assignment workflow occurs each time a client authenticates as the Alice user. This ensures that Vertica role assignments reflect the current role assignments in the IdP.

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.