Support access to JWT via SQL

[keeganmccallum]

The ability to access the claims of a JWT via SQL would provide a lot of power for handling authorization via views or eventually row-level security policies when supported. Postgrest exposes this model to handle authorization and it works quite well with custom claims IMHO, example: http://postgrest.org/en/v6.0/auth.html#web-users-sharing-role

[dain]

To clarify, you have a table containing a column JWT? If so, I think you can decode unencrypted and uncompressed JWTs today. This isn't super convenient but works. Generally, you

1. split the JTW on .
2. from_base64,
3. from_utf8
4. json_parse
5. Use the json casts to convert to a row or map to get values from the json.

Here is an example that decodes the a payload:

with t as (
  SELECT 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IlByZXN0byBSb2NrcyEiLCJpYXQiOjE1MTYyMzkwMjJ9.9Rrdbi0Lx26H7lSAlXDSBOlvAXxPxUntjTHq0E7P7TU' AS jwt
)
SELECT CAST(json_parse(from_utf8(from_base64(split_part(jwt, '.', 2)))) as ROW(sub VARCHAR, name VARCHAR, iat BIGINT))
FROM t

result:

                       payload                        
------------------------------------------------------
 {sub=1234567890, name=Presto Rocks!, iat=1516239022} 

[keeganmccallum]

@dain Thanks for the quick follow-up and detailed write-up. This is super useful and maybe handy with the initial question, but I'm actually talking about having some way to access the JWT used to identify the user internally in presto. I saw here https://github.com/prestosql/presto/issues/2014 that JWT's were used internally for auth, and I believe I saw it was possible to use your own JWT, from an IDP like auth0 for example. I was hoping for some way to get the JWT, specifically, it's claims so that I could use that information for authorization of the data.

For example, say a user had a claim account_id, and then there was a column account_id, I'd like to use the JWT in a query to only allow the user to see columns where account_id == jwt.account_id

[dain]

Accessing the login credentials would take a lot of extra thought. The authentication system is generic, so it would take a lot of careful design to expose information in a safe, secure, way. Then exposing this to queries would need to decide if the functions to support his are generic or authentication system specific. Exposing to access control would also require more design work.

BTW, the current JWT system in Presto is really designed for the Presto proxy, where there is a shared secret. Supporting a full IDP is a more complex problem.

[erikerlandson]

For example, say a user had a claim account_id, and then there was a column account_id, I'd like to use the JWT in a query to only allow the user to see columns where account_id == jwt.account_id

This would definitely be a useful feature.

maybe allow some kind of configuration about what payload fields to expose. So only payloads that are safe to expose are allowed.

[xsgao-github]

maybe allow some kind of configuration about what payload fields to expose. So only payloads that are safe to expose are allowed.

I have some experience with Cognos, here is how Cognos let administrators define what LDAP properties can be exposed and accessed (via session.PropertyName format). Maybe Trino can borrow this design from IBM Cognos.