In this post, I walk through how to configure CockroachDB to allow users to login to the SQL prompt using their SSO login.
Instructions on how to configure SSO access for the DBConsole are available here.
The users will request a JSON Web Token (JWT) from their identity provider (IdP) and use that token as the temporary password.
There are many IdPs, and in this example I am using Okta. The workflow is similar for all IdPs, so while the details for how to configure the IdP varies from IdP to IdP, from the point of view of CockroachDB this is entirely transparent.
Prerequisite: IdP (Okta) setup
To generate a JWT, we first need to setup an IdP. Okta offers a developer account, so I signed up and quickly created an App integration.
Here is a quick walk-through:
Create a few users
Navigate to Directory > People and click Add Person.
Here's my list: I set password Mazinga123 for all these users.
Later, we'll use the password to request a JWT.
Create a Group
Go to Directory > Groups, click Add Group and call it "Bankers".
Then, select that group and assign users to it:
Create an App Integration
Navigate to Applications > Applications and click Create App Integration.
Select the "OIDC" as the protocol, and "Native application" as the type.
I call the integration "BankApp". Ensure "Resource Owner Password" is selected.
Under "Assignments", add the "Bankers" group we previously created.
Click Save.
Now, create a Client Secret.
Make sure you jot down your credentials. In my case:
client_id=0oab1arbbieTSsFVJ5d7
client_secret=WbAWHW9UeURzXez99PMGWvuVp4KiGDXTNQrDajvszN2iQ3YyMjGyx0sWts1sM36J
Configure Token fields
Next, we need to edit the Okta JWT by adding the user.login field.
This is the "Username" field in the Person profile.
Go to Security > API, select the "default" Authorization Server, and select the "Claims" tab.
From here, add a claim:
You can actually preview what the token will look like.
Check the bottom right hand corner, in the Payload section, the "login" field is now present.
Finally, jot down the Issuer URI, we will need this URL later to find the .well-known details. Ours is https://dev-85651931.okta.com/oauth2/default
We are now ready to configure CockroachDB against our Okta BankApp integration.
CockroachDB setup
Start a CockroachDB demo cluster, as it automatically loads a temporary license.
$ cockroach demo --no-example-database
#
# Welcome to the CockroachDB demo database!
#
# You are connected to a temporary, in-memory CockroachDB cluster of 1 node.
#
# This demo session will send telemetry to Cockroach Labs in the background.
# To disable this behavior, set the environment variable
# COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING=true.
#
# Reminder: your changes to data stored in the demo session will not be saved!
#
# If you wish to access this demo cluster using another tool, you will need
# the following details:
#
# - Connection parameters:
# (webui) http://127.0.0.1:8080/demologin?password=demo9642&username=demo
# (cli) cockroach sql --certs-dir=/Users/fabio/.cockroach-demo -u demo -d defaultdb
# (sql) postgresql://demo:demo9642@127.0.0.1:26257/defaultdb?sslmode=require&sslrootcert=%2FUsers%2Ffabio%2F.cockroach-demo%2Fca.crt
#
# - Username: "demo", password: "demo9642"
# - Directory with certificate files (for certain SQL drivers/tools): /Users/fabio/.cockroach-demo
#
# You can enter \info to print these details again.
#
# Server version: CockroachDB CCL v23.1.4 (x86_64-apple-darwin19, built 2023/06/16 20:54:39, go1.19.4) (same version as client)
# Cluster ID: f72ea38c-c5b3-437e-bd03-2391e6b5d150
# Organization: Cockroach Demo
#
# Enter \? for a brief introduction.
#
demo@127.0.0.1:26257/defaultdb>
The relevant documentation to configure CockroachDB with the IdP for SSO login is here.
First, open the .well-known page for our IdP.
In our case, this is URL https://dev-85651931.okta.com/oauth2/default/.well-known/openid-configuration.
Here, we can take the server.jwt_authentication.issuers setting, and by navigating to the URL indicated by the jwkt_uri field, also the server.jwt_authentication.jwks.
-- trivial
SET CLUSTER SETTING server.jwt_authentication.enabled = true;
-- taken from field "issuer" in the .well-known page
SET CLUSTER SETTING server.jwt_authentication.issuers = 'https://dev-85651931.okta.com/oauth2/default';
-- taken by navigating to the URL in field "jwks_uri" in the .well-known page
SET CLUSTER SETTING server.jwt_authentication.jwks = '{"keys":[{"kty":"RSA","alg":"RS256","kid":"6jnGo_xZFr13SVCKgH5Tl8RN9cXqybYBQEo2Vf7Wagw","use":"sig","e":"AQAB","n":"mF3tIoT8h_2lpvYSCE_YopPW9Yp9fx4ddDNYFhFhmcqeMKMLl_JIXjnfs2EMB9zlwBm0hHkphGGWPfsy8wLZob2bVwVj8-3yPK3qRIt7ouW8OhNGn8tmQHB_fSDugPp-A_MuedNAkgeFB4zEcEJbVHjykC6cEYbyyPmhD0VJf0q3ifkg2Fxm8075QcV0iIcl46RHxTToTDeW44gPyMqzLrVq2UxOw-yn_I-o_M065hiONMthdiIR6KvjhO-fqtBbD37BH6XehSe2rtAxUpuLvGnoa25Gl7GkhD7_f6qIa3tmoYMPVvbxQSU8Ly2_AGJ7BCqgOMMyE0knsGLUi-dlBw"}]}';
-- this is the client_id
SET CLUSTER SETTING server.jwt_authentication.audience = '["0oab1arbbieTSsFVJ5d7"]';
-- this is the name of the custom field we added to our token
SET CLUSTER SETTING server.jwt_authentication.claim = 'login';
-- this strips the @bank.com
SET CLUSTER SETTING server.identity_map.configuration = 'https://dev-85651931.okta.com/oauth2/default /^(\S+)(?:@) \1';
Finally, let's create some users, with the name matching the name part of the email address we saved in Okta.
-- 'mrossi@bank.com' is mapped to user 'mrossi', that is,
-- we strip the '@bank.com' part
CREATE USER mrossi;
CREATE USER akumar;
CREATE USER yfofana;
Demo
We are now ready to request Okta for a JWT.
We use curl, following the example in the Okta doc.
curl -s -X POST \
-H "Content-type:application/x-www-form-urlencoded" \
-d "client_id=0oab1arbbieTSsFVJ5d7&client_secret=WbAWHW9UeURzXez99PMGWvuVp4KiGDXTNQrDajvszN2iQ3YyMjGyx0sWts1sM36J&grant_type=password&username=mrossi@bank.com&password=Mazinga123&scope=openid" \
"https://dev-85651931.okta.com/oauth2/default/v1/token" | jq
Here's the formatted response. We are only interested in the id_token, so copy that string.
{
"token_type": "Bearer",
"expires_in": 3600,
"access_token": "eyJraWQiOiI2am5Hb194WkZyMTNTVkNLZ0g1VGw4Uk45Y1hxeWJZQlFFbzJWZjdXYWd3IiwiYWxnIjoiUlMyNTYifQ.eyJ2ZXIiOjEsImp0aSI6IkFULlMtT0wzdmo2cl9Kem82RFMyOGcyNDRhUnVOSWFKUDBqRXVHVUlLZkFvajQiLCJpc3MiOiJodHRwczovL2Rldi04NTY1MTkzMS5va3RhLmNvbS9vYXV0aDIvZGVmYXVsdCIsImF1ZCI6ImFwaTovL2RlZmF1bHQiLCJpYXQiOjE2OTM0MTI2MTAsImV4cCI6MTY5MzQxNjIxMCwiY2lkIjoiMG9hYjFhcmJiaWVUU3NGVko1ZDciLCJ1aWQiOiIwMHViMWRuNXFjN0pBaFpHOTVkNyIsInNjcCI6WyJvcGVuaWQiXSwiYXV0aF90aW1lIjoxNjkzNDEyNjEwLCJzdWIiOiJtcm9zc2lAYmFuay5jb20ifQ.LqiPT1lwzTVWJ1yCFUfL7toFANAIcx4v9c6fYl5HTApbq3wmrpRCJQ9Jy-UgH-SHp4DxYV-tt-i1I-l3409_nVPTK751CxqNCySBsUqXiUHvsV7ZfNxkzgw_0BxCatirk32T08oJc0wuARyP9a1Pif_BwzawHP46vdhrikGwRXrrdtjV9yDLvzGwiHlS8IyTz0lRcCwVR0tq02EfEpBxFg992HJl-VZ4NMzFPj-D6LgAdo4vpJnY-fgHP-IZo26ijfSD9mvyB2V5lu4GAmcD9bV7LfSH_CilPrGFh481y2-YrBknMxUT_4tmN3LY6TZeBo3nDt4gqRjxPxhdDgcTHg",
"scope": "openid",
"id_token": "eyJraWQiOiI2am5Hb194WkZyMTNTVkNLZ0g1VGw4Uk45Y1hxeWJZQlFFbzJWZjdXYWd3IiwiYWxnIjoiUlMyNTYifQ.eyJzdWIiOiIwMHViMWRuNXFjN0pBaFpHOTVkNyIsInZlciI6MSwiaXNzIjoiaHR0cHM6Ly9kZXYtODU2NTE5MzEub2t0YS5jb20vb2F1dGgyL2RlZmF1bHQiLCJhdWQiOiIwb2FiMWFyYmJpZVRTc0ZWSjVkNyIsImlhdCI6MTY5MzQxMjYxMCwiZXhwIjoxNjkzNDE2MjEwLCJqdGkiOiJJRC41M3FaQ3pRVzZNa3dLZXh0d2FFcG1HWDFyX1k1bGFpNEp3TUJlWGl1c2J3IiwiYW1yIjpbInB3ZCJdLCJpZHAiOiIwMG9iMHUzOTI0QXJ1Q3RLZjVkNyIsImF1dGhfdGltZSI6MTY5MzQxMjYxMCwiYXRfaGFzaCI6IllqTV8xUGFxRHpGU3V6bkJPQ3Y4QVEiLCJsb2dpbiI6Im1yb3NzaUBiYW5rLmNvbSJ9.abhdpxyTW7WnQApWBJmiuZo_ziz7UEoKkHnf4nEaHsnDPzen32KwLWV4fNM4lvGl7YSND6lFErcZ_xLhBuqHutiH888UEyLzbuOSSFch0ie63VS0ElKlF6M1sXq_U8vRKikWpxC0dF3z1VTnKbRcfxN7QB6sxYn7iAUWt2xbEkaRTFO5lL4t5lFx8xUUBqtFFgy6im4w4CUyEvOww0DGwTC9gVifPiTa-fxGfN-OLFDyUZPM8F2JMmaYhpVWy-dplQjrmOv96zJ07GbsutPP5BrSb2JRCrEBZY0nTK8LklcVV4uZUbZJtTRDpHgNlmgzqzwlRWrt4dDvybnRZRq3Xg"
}
For the sake of learning, we can use a tool such as token.dev to decode the JWT.
Note our "login" field in the Payload.
Armed with our JWT, we can now proceed to login.
$ cockroach sql --url "postgresql://mrossi:eyJraWQiOiI2am5Hb194WkZyMTNTVkNLZ0g1VGw4Uk45Y1hxeWJZQlFFbzJWZjdXYWd3IiwiYWxnIjoiUlMyNTYifQ.eyJzdWIiOiIwMHViMWRuNXFjN0pBaFpHOTVkNyIsInZlciI6MSwiaXNzIjoiaHR0cHM6Ly9kZXYtODU2NTE5MzEub2t0YS5jb20vb2F1dGgyL2RlZmF1bHQiLCJhdWQiOiIwb2FiMWFyYmJpZVRTc0ZWSjVkNyIsImlhdCI6MTY5MzQxMjYxMCwiZXhwIjoxNjkzNDE2MjEwLCJqdGkiOiJJRC41M3FaQ3pRVzZNa3dLZXh0d2FFcG1HWDFyX1k1bGFpNEp3TUJlWGl1c2J3IiwiYW1yIjpbInB3ZCJdLCJpZHAiOiIwMG9iMHUzOTI0QXJ1Q3RLZjVkNyIsImF1dGhfdGltZSI6MTY5MzQxMjYxMCwiYXRfaGFzaCI6IllqTV8xUGFxRHpGU3V6bkJPQ3Y4QVEiLCJsb2dpbiI6Im1yb3NzaUBiYW5rLmNvbSJ9.abhdpxyTW7WnQApWBJmiuZo_ziz7UEoKkHnf4nEaHsnDPzen32KwLWV4fNM4lvGl7YSND6lFErcZ_xLhBuqHutiH888UEyLzbuOSSFch0ie63VS0ElKlF6M1sXq_U8vRKikWpxC0dF3z1VTnKbRcfxN7QB6sxYn7iAUWt2xbEkaRTFO5lL4t5lFx8xUUBqtFFgy6im4w4CUyEvOww0DGwTC9gVifPiTa-fxGfN-OLFDyUZPM8F2JMmaYhpVWy-dplQjrmOv96zJ07GbsutPP5BrSb2JRCrEBZY0nTK8LklcVV4uZUbZJtTRDpHgNlmgzqzwlRWrt4dDvybnRZRq3Xg@localhost:26257/defaultdb?options=--crdb:jwt_auth_enabled=true&sslmode=require"
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v23.1.4 (x86_64-apple-darwin19, built 2023/06/16 20:54:39, go1.19.4) (same version as client)
# Cluster ID: f72ea38c-c5b3-437e-bd03-2391e6b5d150
# Organization: Cockroach Demo
#
# Enter \? for a brief introduction.
#
mrossi@localhost:26257/defaultdb>
Success! Let's try to use that token with another user, akumar:
$ cockroach sql --url "postgresql://akumar:eyJraWQiOiI2am5Hb194WkZyMTNTVkNLZ0g1VGw4Uk45Y1hxeWJZQlFFbzJWZjdXYWd3IiwiYWxnIjoiUlMyNTYifQ.eyJzdWIiOiIwMHViMWRuNXFjN0pBaFpHOTVkNyIsInZlciI6MSwiaXNzIjoiaHR0cHM6Ly9kZXYtODU2NTE5MzEub2t0YS5jb20v
b2F1dGgyL2RlZmF1bHQiLCJhdWQiOiIwb2FiMWFyYmJpZVRTc0ZWSjVkNyIsImlhdCI6MTY5MzQxNjM4NCwiZXhwIjoxNjkzNDE5OTg0LCJqdGkiOiJJRC5feXhFOEd4MG9oZVZvQ1RYRXkwZFNwc0JrWlFZeHFoam9sY0JZZVBLTXFJIiwiYW1yIjpbInB3ZCJdLCJpZHAiOiIwMG9iMHUzOTI0QXJ1Q3RLZjVkNyIsImF1dGhfdGltZSI6M
TY5MzQxNjM4NCwiYXRfaGFzaCI6IlljNF9SUF9UWUxNM3B1RjdjSjlCYmciLCJsb2dpbiI6Im1yb3NzaUBiYW5rLmNvbSJ9.GrviZKNF4HezfhnmpvpZFZgKrYmivMknsZGFjD1eXSx2IoiBaG-t-q8CB9oVL48e3ZQXeXqXIl9PvL84a4ed8VCR1wJWH53xKTsAMGmx3dAJpxdO7PhNK1P0Eg2eY4p76mJ2qHbUGb201As_oC-OQAGkuGEZq
lBHV634Mv3zcdKjFFuUYaFkadMXqvIZomSoWUL5jDM8fqKtOUNoONHilthnOgZ3RxgfzGyeijdFS1_ODscbjbIzamKw6C7u698KG7eohVJMP2dIJr4jo33jpcwztRRQfU5ElcuINqLBw8Gv-_A1MlM4WrPHzsYN_OWgaRM_wd8UzOiyuMLFKirHFA@localhost:26257/defaultdb?options=--crdb:jwt_auth_enabled=true&sslm
ode=require"
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: JWT authentication: invalid principal
SQLSTATE: 28000
DETAIL: token issued for [mrossi@bank.com] and login was for akumar
Failed running "sql"
Failed as expected!
Your IdP administrator will be responsible for instructing on how to request a JWT, and what claim to use, so do not worry if all this is overwhelming.
From the CockroachDB side, what matters is that our SQL username matches with what the IdP has stored for a particular user: in this example, we used the email address, but it can be any unique ID that your company has adopted.
Top comments (0)