Thread: support for JSON Web Token

Hi,

I would like to advocate forJSON web token support in PostgreSQL.

JWT tokens are used in a lot of web applications and I think there are
some very nice use cases for passing the token down to PostgreSQL.

Some of the first thing that comes to mind is pairing it with row level
security to implement authorization policies and data access directly in DB.

I've did a bit of research and I found some prior work in [pgjwt] and
[postgres-jwt-prototype] .

It seems to be pretty easy to support since most of the work is done:
PostgreSQL has crypto support via pgcrypto and has native JSON handling
functions.

JWT is a widely used technology, especially in web applications.

This is why I think PostgreSQL should adopt a JWT extension in contrib.

I believe PostgreSQL will benefit, especially in the web apps use.


What do you think?

What would it take for a JWT extension to be added to postgresql contrib
or the official postgresql extensions ?


Thanks,

Eugen

[pgjwt] https://github.com/michelp/pgjwt

[postgres-jwt-prototype] https://github.com/larsar/postgres-jwt-prototype

Hi Michel,

Thanks for the reply. I will pitch in my view. My background comes from
developing (mainly Java based) applications:

La 03.03.2019 22:18, Michel Pelletier a scris:
> On Sat, Mar 2, 2019 at 4:09 PM Eugen Stan <eu@ieugen.ro
> <mailto:eu@ieugen.ro>> wrote:
>
>     Hi,
>
>     I would like to advocate forJSON web token support in PostgreSQL.
>
>     JWT tokens are used in a lot of web applications and I think there are
>     some very nice use cases for passing the token down to PostgreSQL.
>
>
> pgjwt author here.  While I do agree with you that a direct
> integration would simplify a few things, the idea doesn't really bring
> enough benefit to overcome some downsides.
>  
>
>     Some of the first thing that comes to mind is pairing it with row
>     level
>     security to implement authorization policies and data access
>     directly in DB.
>
>
> It's possible to do this now, tools like PostgREST and Postgraphile
> use a pattern where they log in as an Anonymous user but when they get
> a valid JWT, they SET ROLE to the correct user.  It's also possible to
> inspect jwt claims in RLS policy checks via variables that are set on
> each query.  It's worth noting that neither of these projects use
> pgjwt but rather use the libraries that come with their runtimes.
Thanks for the info. I heard about the projects but I am not familiar
with their inner workings. It's nice to know there are people working on
this.
>
> But more abstractly how would this integration work?  Postgres does
> not speak HTTP, you'd have to extend the client protocol.  That's
> gonna be a huge hurdle since its a large change to a lot of code
> outside of postgres itself.

I know PostgreSQL does not speek HTTP and I don't intend to do something
wild as extending the client protocol. Though, that could be a good
research project.

My idea is not that far from what PostgREST and Postgraphile are doing.
I am probably going to check their solution out in more detail.
The idea is to SET a JWT _*access_token*_ (not the role) for each query.
JWT has a simple, well defined format and I believe it is quite stable.

The JWT contains the user identity and claims about the user: what roles
the user belongs to, what permissions he has, etc.

The RLS policy can use those claims to make access decisions.

JWT support is not actually needed if the JWT parsing is done by the app
and the claims are SET on the query directly. 


>  
>
>     I've did a bit of research and I found some prior work in [pgjwt] and
>     [postgres-jwt-prototype] .
>
>     It seems to be pretty easy to support since most of the work is done:
>     PostgreSQL has crypto support via pgcrypto and has native JSON
>     handling
>     functions.
>
>
> pgcrypto only supports secret key signing with hmac, but it doesn't
> support public key signing.  pgjwt was always just an expedient tool,
> for me at least, to use as an early adopter of PostgREST.  It is nice
> to be able to generate valid jwts from the db with a secret.  But for
> any more serious use it's going to need not only work to pgcrypto or a
> gratuitous self-plug for pgsodium
> (https://github.com/michelp/pgsodium) to support public key signing
> and it's going to need some eyeballs from real security people.  I
> wrote them and I personally wouldn't use pgjwt (other than trival key
> generation) or pgsodium for production use without some more buy in
> from serious security people.  That's huge hurdle #2.

Since the app can do mostly anything in the DB I don't see a need to
validate things on Postgres side. Nice job on pgsodium.

>  
>
>     JWT is a widely used technology, especially in web applications.
>
>     This is why I think PostgreSQL should adopt a JWT extension in
>     contrib.
>
>     I believe PostgreSQL will benefit, especially in the web apps use.
>
>
>     What do you think?
>
>     What would it take for a JWT extension to be added to postgresql
>     contrib
>     or the official postgresql extensions ?
>
>
> I like your enthusiasm for the idea, but it has some serious hurdles
> noted.  In the mean time, if you're idea is to get working quickly
> with postgres and JWT, I would look at PostgREST and Postgraphile, and
> I hear Hasura is quite cool too although I don't have any firsthand
> experience with it.

Thanks. I will check out both PostgREST and Postgraphile . I've worked a
bit with join-monster to put GraphQL API on PostgreSQL but it did not
cover all the things that we needed.

>
> -Michel
>  
>
>
>
>     Thanks,
>
>     Eugen
>
>     [pgjwt] https://github.com/michelp/pgjwt
>
>     [postgres-jwt-prototype]
>     https://github.com/larsar/postgres-jwt-prototype
>
>
>