Configure Keycloak

The following procedure configures a Keycloak 18.0.0 server on 203.0.113.1.

The following procedure configures a Keycloak 18.0.0 server on 203.0.113.1 for integration with Vertica. For details, see Configuring OAuth authentication.

The goals of this procedure are to configure Keycloak and obtain the following information:

Client ID: The ID used to identify the Vertica database. This is configured by the user and set to vertica in the example procedure.
Client secret: A Keycloak-generated string used to refresh the OAuth token when it expires.
Discovery endpoint: The endpoint that serves information for all other endpoints as a JSON string. The endpoint for a Keycloak server on 203.0.113.1 is one of the following:
- https://203.0.113.1:8443/realms/myrealm/.well-known/openid-configuration (if TLS is configured)
- http://203.0.113.1:8443/realms/myrealm/.well-known/openid-configuration

Configure TLS (optional)

If you want to use TLS, you must obtain a certificate and key for Keycloak signed by a trusted CA. This example uses a self-signed CA for convenience. The following example creates a certificate and key in Vertica:

Generate the CA certificate:

=> CREATE KEY SSCA_key TYPE 'RSA' LENGTH 2048;
CREATE KEY

=> CREATE CA CERTIFICATE SSCA_cert
SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=Micro Focus/OU=Vertica/C N=Vertica Root CA'
VALID FOR 3650
EXTENSIONS 'nsComment' = 'Self-signed root CA cert'
KEY SSCA_key;
CREATE CERTIFICATE

Generate a server key and certificate, signed by your CA, setting the subjectAltName of the certificate to the DNS server and/or IP address of your Keycloak server:

=> CREATE KEY keycloak_key TYPE 'RSA' LENGTH 2048;
CREATE KEY

=> CREATE CERTIFICATE keycloak_cert
SUBJECT '/C=US/ST=Massachussets/L=Cambridge/O=Micro Focus/OU=Vertica/CN=Vertica Server'
SIGNED BY SSCA_cert
EXTENSIONS 'nsComment' = 'Keycloak CA', 'extendedKeyUsage' = 'serverAuth', 'subjectAltName' = 'DNS.1:dnsserver,IP:203.0.113.1'
KEY keycloak_key;
CREATE CERTIFICATE

Create the file keycloak_directory/conf/keyfile.pem with the content from the key column for the generated key:
```
=> SELECT key FROM cryptographic_keys WHERE name = 'keycloak_key';
```
Create the file keycloak_directory/conf/certfile.pem with the content from the certificate_text column for the generated certificate:
```
=> SELECT certificate_text FROM certificates WHERE name = 'keycloak_cert';
```
Append to your system's CA bundle the content from the certificate_text column for the generated CA certificate. The default CA bundle path and format varies between distributions; for details, see SystemCABundlePath:
```
=> SELECT certificate_text FROM certificates WHERE name = 'SSCA_cert';
```

Set the SystemCABundlePath configuration parameter:

=> ALTER DATABASE DEFAULT SET SystemCABundlePath = 'path/to/ca_bundle';

Start Keycloak

Enter the following commands for a minimal configuration to create the Keycloak admin and to start Keycloak in start-dev mode:

$ KEYCLOAK_ADMIN=kcadmin
$ export KEYCLOAK_ADMIN
$ KEYCLOAK_ADMIN_PASSWORD=password
$ export KEYCLOAK_ADMIN_PASSWORD
$ cd keycloak_directory/bin/
$ ./kc.sh start-dev --hostname 203.0.113.1 --https-certificate-file ../conf/certfile.pem --https-certificate-key-file=../conf/keyfile.pem

Open the Keycloak console with your browser (these examples use the default ports):
- For HTTP: http://203.0.113.1:8080
- For HTTPS: http://203.0.113.1:8443
Sign in as the admin.
Go to Client scopes > Client scope details > Mapper details, and toggle Full group path to Off. Vertica does not support subgroups.
(Optional) To make testing OAuth more convenient, go to Realm Settings > Tokens and increase Access Token Lifespan to a greater value (the default is 5 minutes).

Create the Vertica client

Go to Clients and select on Create. The Add Client page appears.
In Client ID, enter vertica.
Select Save. The client configuration page appears.
On the Settings tab, use the Access Type dropdown to select confidential.
On the Credentials tab, copy the Secret. This is the client secret used to refresh the token when it expires.

Create a Keycloak user

Keycloak users map to Vertica users with the same name. This example creates a the Keycloak user oauth_user.

On the Users tab, select Add user. The Add user page appears.
In Username, enter oauth_user.
On the Credentials tab, enter a password.