Verify JWTs without changing role

[Javiervva]

Environment

• PostgreSQL version: Docker bitnami/postgresql:13.8.0
• PostgREST version: Docker postgrest/postgrest:latest (v11.2.1)
• Operating system: Windows 10 Enterprise with Docker Desktop 4.17.1 (using Docker's WSL 2 based engine, not a traditional Linux distribution like Ubuntu) without Kubernetes.

Description of issue

Hello,

I'm currently attempting to integrate PostgREST into a system that employs its own OAuth2 for securing the service. The challenge I'm facing is that the JWT payload we send to PostgREST doesn't contain the user's role. Due to security constraints, we retrieve role-related information using an alternative method. This creates an issue for us. From my understanding (please correct me if I'm mistaken), this role claim seems to be mandatory when using a third-party OAuth to identify the role that should be assigned in the database. However, in our scenario, there shouldn't be any role or permission distinctions. Only authorized users can log into our application, and being logged in implies they have full access. For this reason, we also don't see a need for an anonymous user, as we don't want anyone accessing without authentication. Our application is straightforward with limited functionalities. That's why we're looking to achieve this behavior with PostgREST.

I conducted several tests using the .sub (usercode) and created a role in the database that matches my usercode, granting it the necessary permissions. This approach worked. However, given the dynamic nature of various fields in our JWT payload that pertain to user-specific details, it becomes unfeasible to maintain individual roles based on this token information. This leads me to the core of my inquiry: Is there a way that if the PGRST_JWT_ROLE_CLAIM_KEY environment variable isn't specified, PostgREST would simply verify the JWT's signature against the JWKS specified in the PGRST_JWT_SECRET environment variable?

In essence, we're looking for PostgREST to handle just the JWT verification and use the default permissions of the main user. Furthermore, we'd like to explore solutions that don't involve implementing a custom PostgreSQL function in our database, if possible.

Docker-compose setup

version: '3'

services:
  postgrest:
    image: postgrest/postgrest:latest
    environment:
      PGRST_DB_URI: postgres://authenticator:password@psql/db
      # PGRST_SERVER_HOST: 0.0.0.0
      # PGRST_SERVER_PORT: 3000
      PGRST_DB_SCHEMA: <schema>
      PGRST_JWT_SECRET: "@/etc/postgrest/public_key.rsa.jwk.pub"
      PGRST_JWT_SECRET_IS_BASE64: false
      PGRST_JWT_AUD: <aud>
      # PGRST_ROLE_CLAIM_KEY: .sub
      # PGRST_JWT_CACHE_MAX_LIFETIME: 86400
      PGRST_DB_CONFIG: false
      PGRST_LOG_LEVEL: info
      # PGRST_SERVER_TRACE_HEADER: X-Request-Id
      # PGRST_DB_ANON_ROLE: anon
      PGRST_OPENAPI_MODE: 'ignore-privileges'
      PGRST_OPENAPI_SECURITY_ACTIVE: true
      # PGRST_OPENAPI_SERVER_PROXY_URI: http://nginx:80
      PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
    depends_on:
      - psql
    volumes:
      - ./public_key.rsa.jwk.pub:/etc/postgrest/public_key.rsa.jwk.pub
    ports:
      # - "127.0.0.1:3000:3000"
      - "3000:3000"
    networks:
      - localnet

  psql:
    image: bitnami/postgresql:13.8.0
    restart: always
    volumes:
      - 'postgresql_master_data:/bitnami/postgresql'
      # to load init tables
      - './init_db:/docker-entrypoint-initdb.d'
    environment:
      - POSTGRESQL_USERNAME=username
      - POSTGRESQL_PASSWORD=password
      - POSTGRESQL_DATABASE=db
      - POSTGRESQL_POSTGRES_PASSWORD=mysecretpostgrespass
    ports:
      - "8081:5432"
    networks:
      - localnet

  swagger:
    image: swaggerapi/swagger-ui:latest
    depends_on:
    - postgrest
    ports:
    - "8080:8080"
    environment:
      API_URL: http://localhost:3000/
    networks:
      - localnet

networks:
  localnet:

volumes:
  postgresql_master_data:

Setup database:

-- Create the 'authenticator' role with limited permissions. This role will be used by PostgREST 
-- to first connect to the database and then switch to the appropriate user role.
CREATE ROLE authenticator LOGIN NOINHERIT NOCREATEDB NOCREATEROLE NOSUPERUSER PASSWORD '<password>';

-- Create a user role named '<my_usercode>'. This represents the user code obtained from the JWT's '.sub' claim.
-- It's an example of how you might set up roles for individual users based on their JWT.
CREATE ROLE "<my_usercode>" LOGIN NOINHERIT NOCREATEDB NOCREATEROLE NOSUPERUSER PASSWORD '<password>';

-- Allow the 'authenticator' role to switch to the '<my_usercode>' role.
GRANT "<my_usercode>" TO authenticator;

-- Grant the schema usage permission to '<my_usercode>'.
GRANT USAGE ON SCHEMA <schema> TO "<my_usercode>";

-- Grant '<my_usercode>' the SELECT permission on the table in the specified schema.
GRANT SELECT ON TABLE <schema>.<table> TO "<my_usercode>";

With this setup, when I am the logged-in user and we send that JWT, PostgREST verifies the role against the database, assigns it, and with the necessary permissions set on it, the generated endpoints work correctly. However, when we disable the PGRST_JWT_ROLE_CLAIM_KEY environment variable, we receive a 401 Unauthorized response with the following body:

{
    "code": "PGRST301",
    "details": null,
    "hint": null,
    "message": "JWSError JWSInvalidSignature"
}

And the header reads:

WWW-Authenticate: Bearer error="invalid_token", error_description="JWSError JWSInvalidSignature"

For us, it would be ideal to know if there's a possibility for PostgREST to simply validate the JWT token against the JWKS for signature verification. If that's successful, and in the absence of a role claim and no anonymous role, we'd like to use the primary user for all operations. This would allow us to bypass the chameleon functionality, as it wouldn't be necessary in our case.

(Expected behavior vs actual behavior)

Aspect	Current Behavior	Expected Behavior
JWT Role Validation	Requires a role claim. Returns 401 Unauthorized with "JWSError JWSInvalidSignature" when not provided.	Validate the JWT token against the JWKS. If successful, use the primary user for all operations, bypassing chameleon.

(Steps to reproduce: Include a minimal SQL definition plus how you make the request to PostgREST and the response body)

1. Setup PostgREST Configuration:

Define the following environment variables:

PGRST_JWT_SECRET: "@/etc/postgrest/public_key.rsa.jwk.pub"
PGRST_JWT_SECRET_IS_BASE64: false
PGRST_JWT_AUD: <aud>

Omit Role Claim Key:

Ensure that the PGRST_JWT_ROLE_CLAIM_KEY environment variable is not set.

Disable Anonymous Role:

Comment out or omit the following line to deactivate the anonymous role:

# PGRST_DB_ANON_ROLE: anon

2. Database configuration

Execute the following SQL command to set up the required role in your database:

CREATE ROLE authenticator LOGIN NOINHERIT NOCREATEDB NOCREATEROLE NOSUPERUSER PASSWORD '<password>';

Note: Also add permissions to schemas/tables where necessary.

3. Make a Request:

Send a request to an exposed PostgREST endpoint, passing the JWT token in the 'Authorization' header.

4. Observe the Error:

The response body should be:

{
    "code": "PGRST301",
    "details": null,
     "hint": null,
     "message": "JWSError JWSInvalidSignature"
}

[wolfgangwalther]

Is there anything in the token payload that is constant, i.e. the same for every token? You could just extract that and add a role with this name to your database and then use that. Alternatively, if you could add such a constant payload to all the tokens, you could do something like { pgrst_role: 'my_role' } in the token and then have everyone use that my_role.

[Javiervva]

Hi @wolfgangwalther!

There are a variety of fields in our JWT payload that pertain to user-specific details. Due to the dynamic nature of these fields, they can differ and change frequently for individual users, making it a challenge to use them for consistent role determination.
Unfortunately, we're unable to modify the JWT payload due to security constraints beyond our control within the company.

Additionally, we face another challenge. For security reasons, the JWKS are updated at regular intervals. They cannot remain static, so we need to access the endpoint where the JWKS are exposed. Could you kindly confirm whether the PGRST_JWT_SECRET field has the capability to accept a URL as a parameter?

Thanks in advance.

[wolfgangwalther]

Additionally, we face another challenge. For security reasons, the JWKS are updated at regular intervals. They cannot remain static, so we need to access the endpoint where the JWKS are exposed. Could you kindly confirm whether the PGRST_JWT_SECRET field has the capability to accept a URL as a parameter?

You might be looking for what was discussed here as well: #2725 (comment)

[wolfgangwalther]

For us, it would be ideal to know if there's a possibility for PostgREST to simply validate the JWT token against the JWKS for signature verification. If that's successful, and in the absence of a role claim and no anonymous role, we'd like to use the primary user for all operations.

Something similar had been requested here: #2690

[steve-chavez]

Thinking more about this, I believe we should allow it.

Right now we could say that PostgREST includes a builtin pre-request function that does select set_config('role, 'val',..), set_config('search_path',..) but if the user wants to override that builtin it can be done as:

create function pre_request("request.role" name) returns void as $$
-- do set_config if you wish
$$ language sql;

alter role authenticator set pgrst.db_pre_request to 'pre_request';

Meaning the user is free to set the role obtained from the JWT using the role-claim-key. Once the pre-request has the request.role parameter we will not set the role.

@wolfgangwalther WDYT?

---

Maybe the parameter could be just "role" name or we could apply the same logic with a "request.jwt.claims" name.

---

Related to #1941

[wolfgangwalther]

If we want to disable setting the role via PostgREST, I think the easiest way to do so, would be to allow setting jwt-role-claim-key = Nothing. No role extracted, no role set.

The JWT would still be checked, of course. The user can then write a pre-request function if they want to - or not. If they need any claim from the JWT, they could use a jwt.role or jwt.other_claim argument on their pre-request function (if we implement the idea proposed in #1710 (comment) and discussed in #1941).

However, I would not tie "setting the role" to "a pre-request function with a specific argument exists". That's really awkward.

[steve-chavez]

#1710 (comment)

That sounds good. I like the idea of making our default set_configs an overridable builtin pre-request.

However, I would not tie "setting the role" to "a pre-request function with a specific argument exists". That's really awkward.

Right, maybe we need a config like db-tx-settings = true to leave all the set_configs to the pre-request. So then we can pass the info as arguments.

[steve-chavez]

As for this issue, then the conclusion would be to allow doing:

jwt-role-claim-key = ''

Currently jwt-role-claim-key always takes a default .role, so this wouldn't be a breaking change.

authRole would have to become a Maybe BS.ByteString.

postgrest/src/PostgREST/AppState.hs

Lines 79 to 82 in 6c3d7a9

	data AuthResult = AuthResult
	{ authClaims :: KM.KeyMap JSON.Value
	, authRole :: BS.ByteString
	}

Then setPgLocals can set the role depending on the existence of the value.

postgrest/src/PostgREST/Query.hs

Lines 249 to 251 in 6c3d7a9

	setPgLocals :: AppConfig -> KM.KeyMap JSON.Value -> BS.ByteString -> [(ByteString, ByteString)] ->
	ApiRequest -> Maybe Text -> DbHandler ()
	setPgLocals AppConfig{..} claims role roleSettings ApiRequest{..} tout = lift $

[wolfgangwalther]

Currently jwt-role-claim-key always takes a default .role, so this wouldn't be a breaking change.

Yes and no. How do you set this to Nothing via environment variable? How via in-database config? Where is the difference between not setting those, setting them to Nothing and to the default value?

I'm not sure this will actually work like that. So maybe we need a jwt-set-role = True | False :/

[wolfgangwalther]

Although.. every proper value for that config option needs to start with .. So we can basically use any string not starting with . as a special value to disable it. So jwt-role-claim-key = "null" or jwt-role-claim-key = "disabled" should work.

[steve-chavez]

So jwt-role-claim-key = "null" or jwt-role-claim-key = "disabled" should work.

Wouldn't be opposed to that, but since we're talking about the tx settings, wouldn't be better to come up with a general db-tx-settings that filters values that get to the db?

Related to #1941 (comment)

[wolfgangwalther]

Wouldn't be opposed to that, but since we're talking about the tx settings, wouldn't be better to come up with a general db-tx-settings that filters values that get to the db?

Oh.. I start to understand where you were coming from with your proposal in #3002 (comment). Basically you are saying SET LOCAL ROLE ... and SET LOCAL request.headers ... are not that different - so why not treat them the same?

I think we should really keep them separated. role is not just some other "setting". role is a core part of our authentication process - we should avoid any confusion by mixing this with other settings. It's just a coincidence (or bad design by PostgreSQL) that this uses the same command in PG.

[steve-chavez]

Oh.. I start to understand where you were coming from with your proposal in #3002 (comment). Basically you are saying SET LOCAL ROLE ... and SET LOCAL request.headers ... are not that different - so why not treat them the same?

Correct. That's why I think pre-request should be able to take full control of the tx settings, it makes a lot of sense to think of them as a builtin pre-request.

I think we should really keep them separated. role is not just some other "setting". role is a core part of our authentication process

Yeah, I've closed previous issues with the same argument before but users want more flexibility nowadays. Maybe this allows us to extend our authN system too. And postgrest-contrib could offer a convenience function so users don't forget to switch the role.

[wolfgangwalther]

Yeah, I've closed previous issues with the same argument before but users want more flexibility nowadays. Maybe this allows us to extend our authN system too. And postgrest-contrib could offer a convenience function so users don't forget to switch the role.

I am not saying we shouldn't make it configurable. I'm just saying "whether to SET ROLE" or not should be a different config setting than "whether to SET headers etc.".