Keep JWT tokens in the table instead of database settings (an idea)

[spinus]

I was struggling with current_setting and alter database <x> set <key> = <value> (I needed to restart postgrest in order to work) and went to #PostgreSQL irc channel to ask about that.

After I asked the question ningu and Zr40 pointed me out that database settings can be read by anyone. It's ok if postgrest is the only client, but you never know :-)

Zr40 suggested that probably better solution is to keep JWT tokens in separate table which can be nicely secured (and postgrest is using this feature quite a lot, together with schemas).

What do you think about refactoring examples to use this kind of approach rather than database setting which can be read by anyone?

[spinus]

This also kind of relates to #505, we could add an option to postgrest to use tokens from specific table for signature checking.

[eric-brechemier]

@spinus This makes sense. I suggest the following structure for the table:

postgrest.jwt_private_keys(
  valid_from TIMESTAMPTZ,
  valid_until TIMESTAMPTZ,
  jwt_private_key BYTEA
)

• the postgrest user should be granted read-only access to the table
• only the latest key (most recent valid_from) would be used for signature of JWT
• other valid keys may only be used to request a new token; requests which do not return a JWT would fail with HTTP code 401 Unauthorized
• PostgREST would process keys from newest to oldest (ORDER BY valid_from DESC)

[ppKrauss]

Hi @eric-brechemier , good suggestion. To discuss, as we talking about SQL table, let's express it in standard SQL... I also imagine the (optional) RefreshToken as in my issue#505 comment, so something like

CREATE TABLE jwt_private_keys (
  user_role_id bigint NOT NULL PRIMARY KEY, --- need REFERENCES to some user-role table
  token_valid_from TIMESTAMPTZ NOT NULL DEFAULT now(),
  token_valid_until TIMESTAMPTZ NOT NULL DEFAULT now()+interval '3 weeks',
  rfshtoken_valid_until TIMESTAMPTZ  DEFAULT now()+interval '1 year',
  jwt_private_key BYTEA,
  CHECK(rfshtoken_valid_until>token_valid_until),
  CHECK(token_valid_until>token_valid_from) 
)

... make sense for you?

In the "standard RefreshToken approach" login creates the pair token-refreshToken. In this illustration, after 1 year the user need a new login... Each "RefreshToken event" updates the token_valid_from and token_valid_until fields. Only a new login updates rfshtoken_valid_until.

PS: remember the refresh token objective.

[eric-brechemier]

@ppKrauss There is a confusion in your suggestion between tokens (1 per user) and keys/secrets (1 per server, currently).

There is no need to store refresh tokens if they are JWT as well. Only the secret is needed to check that the refresh token is legit.

PS: remember the refresh token objective.

I am all in favor of using a common vocabulary, but let's not replace the problem to match the solution. The original issue was JWT secrets getting compromised, not JWT tokens.

[ppKrauss]

Hi @eric-brechemier, sorry, let's repair... Is not evident for me how the ACL is implement in PostgREST... Yes, ideal is "1 per user", but the simplest is to do separated login for each application (each id in a user-role table)...

Show the modifications that I change the SQL table declaration of my post.

[eric-brechemier]

@ppKrauss let's take an example:

In 2016, Alice Corp launches the service alice.example.com using PostgREST. The service generates 1000 JWT for 1000 users bob1, bob2, ..., bob1000. There is only 1 secret at this point, used to sign and verify all the JWT tokens:

Valid From	Valid Until	Secret
2016	2020	7cb189d8-410b-4073-8929-04cc884cb967

In 2017, Alice Corp is bought by Charlie Corp. They decide to migrate to a new secret and plan to upgrade all users to the new secret by 2018:

Valid From	Valid Until	Secret
2016	2018	7cb189d8-410b-4073-8929-04cc884cb967
2017	2020	1724cdb1-17c1-4606-88e6-4da675e60d20

When the user bob42 connects to alice.example.com (now redirected to charlie.example.com) for the first time after the change, the new secret (2017–2020) is used first to verify bob42's JWT. It fails. The older secret (2016–2018) is then used in turn and the verification succeeds. The user bob42 is now allowed to call any function that returns a new JWT, which will be signed using the new secret. But access to all other resources would be forbidden.

[ppKrauss]

Sorry all, English is not my first language... Before to continue, a jargon question: Rule (as RFC7519 or Auth0) or Role (as PostgREST Guide)? ... Or always "JWT rule == Database role" in the PostgREST discussions?

[eric-brechemier]

@ppKrauss the database role is stored in a JWT claim.

Claim
A piece of information asserted about a subject. A claim is represented as a name/value pair consisting of a Claim Name and a Claim Value.
— https://tools.ietf.org/html/rfc7519#section-2

[begriffs]

To recap, we want to keep the JWT server secret out of a GUC and move it into a table with proper permissions. That makes sense. This is just a change to the way people write their stored procs and other SQL, right, no anticipated changes necessary for the postgrest binary?

[spinus]

@begriffs, depends. If the feature that postgrest is using secret from a table is desired (for example you dynamically change secret, you want to sign tokens with new key only, but you still want to support tokens signed by old one), I think postgrest would need to support reading secrets from table.

[ppKrauss]

I agreee @begriffs, and think that the best is to use a table and the pgcrypto extension.

[begriffs]

I made a docs issue for this approach, so closing the issue in this repo.

[spinus]

good stuff, thanks

[BCreativeS]

pgCRON - funtion to keep tract of expiring jwt
pgCrypto for the authorizing