Re: support for JSON Web Token

On Sat, Mar 2, 2019 at 4:09 PM Eugen Stan <eu(at)ieugen(dot)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.

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'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.

> 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.

-Michel

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