Authentication

PostgREST is designed to keep the database at the center of API security. All authorization happens in the database . It is PostgREST’s job to authenticate requests – i.e. verify that a client is who they say they are – and then let the database authorize client actions.

Overview of role system

There are three types of roles used by PostgREST, the authenticator, anonymous and user roles. The database administrator creates these roles and configures PostgREST to use them.

../_images/security-roles.png

The authenticator role is used for connecting to the database and should be configured to have very limited access. It is a chameleon whose job is to “become” other users to service authenticated HTTP requests.

CREATE ROLE authenticator LOGIN NOINHERIT NOCREATEDB NOCREATEROLE NOSUPERUSER;
CREATE ROLE anonymous NOLOGIN;
CREATE ROLE webuser NOLOGIN;

Note

The names “authenticator” and “anon” names are configurable and not sacred, we simply choose them for clarity. See db-uri and db-anon-role.

User Impersonation

The picture below shows how the server handles authentication. If auth succeeds, it switches into the user role specified by the request, otherwise it switches into the anonymous role (if it’s set in db-anon-role).

../_images/security-anon-choice.png

This role switching mechanism is called user impersonation. In PostgreSQL it’s done with the SET ROLE statement.

Note

The impersonated roles will have their settings applied. See Impersonated Role Settings.

JWT-Based User Impersonation

We use JSON Web Tokens to authenticate API requests, this allows us to be stateless and not require database lookups for verification. As you’ll recall a JWT contains a list of cryptographically signed claims. All claims are allowed but PostgREST cares specifically about a claim called role.

{
  "role": "user123"
}

When a request contains a valid JWT with a role claim PostgREST will switch to the database role with that name for the duration of the HTTP request.

SET LOCAL ROLE user123;

Note that the database administrator must allow the authenticator role to switch into this user by previously executing

GRANT user123 TO authenticator;
-- similarly for the anonymous role
-- GRANT anonymous TO authenticator;

If the client included no JWT (or one without a role claim) then PostgREST switches into the anonymous role. The database administrator must set the anonymous role permissions correctly to prevent anonymous users from seeing or changing things they shouldn’t.

JWT Generation

You can create a valid JWT either from inside your database (see SQL User Management) or via an external service (see External JWT Generation).

Client Auth

To make an authenticated request the client must include an Authorization HTTP header with the value Bearer <jwt>. For instance:

GET /foo HTTP/1.1
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiamRvZSIsImV4cCI6MTQ3NTUxNjI1MH0.GYDZV3yM0gqvuEtJmfpplLBXSGYnke_Pvnl0tbKAjB4

The Bearer header value can be used with or without capitalization(bearer).

JWT Caching

PostgREST validates JWTs on every request. We can cache JWTs to avoid this performance overhead.

To enable JWT caching, the config jwt-cache-max-lifetime is to be set. It is the maximum number of seconds for which the cache stores the JWT validation results. The cache uses the exp claim to set the cache entry lifetime. If the JWT does not have an exp claim, it uses the config value. See jwt-cache-max-lifetime for more details.

Note

You can use the Server-Timing Header to see the effect of JWT caching.

Symmetric Keys

Each token is cryptographically signed with a secret key. In the case of symmetric cryptography the signer and verifier share the same secret passphrase, which can be configured with jwt-secret. If it is set to a simple string value like “reallyreallyreallyreallyverysafe” then PostgREST interprets it as an HMAC-SHA256 passphrase.

Asymmetric Keys

In asymmetric cryptography the signer uses the private key and the verifier the public key.

As described in the Configuration section, PostgREST accepts a jwt-secret config file parameter. However you can also specify a literal JSON Web Key (JWK) or set. For example, you can use an RSA-256 public key encoded as a JWK:

{
  "alg":"RS256",
  "e":"AQAB",
  "key_ops":["verify"],
  "kty":"RSA",
  "n":"9zKNYTaYGfGm1tBMpRT6FxOYrM720GhXdettc02uyakYSEHU2IJz90G_MLlEl4-WWWYoS_QKFupw3s7aPYlaAjamG22rAnvWu-rRkP5sSSkKvud_IgKL4iE6Y2WJx2Bkl1XUFkdZ8wlEUR6O1ft3TS4uA-qKifSZ43CahzAJyUezOH9shI--tirC028lNg767ldEki3WnVr3zokSujC9YJ_9XXjw2hFBfmJUrNb0-wldvxQbFU8RPXip-GQ_JPTrCTZhrzGFeWPvhA6Rqmc3b1PhM9jY7Dur1sjYWYVyXlFNCK3c-6feo5WlRfe1aCWmwZQh6O18eTmLeT4nWYkDzQ"
}

Note

This could also be a JSON Web Key Set (JWKS) if it was contained within an array assigned to a keys member, e.g. { keys: [jwk1, jwk2] }.

Just pass it in as a single line string, escaping the quotes:

jwt-secret = "{ \"alg\":\"RS256\", … }"

To generate such a public/private key pair use a utility like latchset/jose.

jose jwk gen -i '{"alg": "RS256"}' -o rsa.jwk
jose jwk pub -i rsa.jwk -o rsa.jwk.pub

# now rsa.jwk.pub contains the desired JSON object

You can specify the literal value as we saw earlier, or reference a filename to load the JWK from a file:

jwt-secret = "@rsa.jwk.pub"

JWT Claims Validation

PostgREST honors the exp claim for token expiration, rejecting expired tokens.

JWT Security

There are at least three types of common critiques against using JWT: 1) against the standard itself, 2) against using libraries with known security vulnerabilities, and 3) against using JWT for web sessions. We’ll briefly explain each critique, how PostgREST deals with it, and give recommendations for appropriate user action.

The critique against the JWT standard is voiced in detail elsewhere on the web. The most relevant part for PostgREST is the so-called alg=none issue. Some servers implementing JWT allow clients to choose the algorithm used to sign the JWT. In this case, an attacker could set the algorithm to none, remove the need for any signature at all and gain unauthorized access. The current implementation of PostgREST, however, does not allow clients to set the signature algorithm in the HTTP request, making this attack irrelevant. The critique against the standard is that it requires the implementation of the alg=none at all.

Critiques against JWT libraries are only relevant to PostgREST via the library it uses. As mentioned above, not allowing clients to choose the signature algorithm in HTTP requests removes the greatest risk. Another more subtle attack is possible where servers use asymmetric algorithms like RSA for signatures. Once again this is not relevant to PostgREST since it is not supported. Curious readers can find more information in this article. Recommendations about high quality libraries for usage in API clients can be found on jwt.io.

The last type of critique focuses on the misuse of JWT for maintaining web sessions. The basic recommendation is to stop using JWT for sessions because most, if not all, solutions to the problems that arise when you do, do not work. The linked articles discuss the problems in depth but the essence of the problem is that JWT is not designed to be secure and stateful units for client-side storage and therefore not suited to session management.

PostgREST uses JWT mainly for authentication and authorization purposes and encourages users to do the same. For web sessions, using cookies over HTTPS is good enough and well catered for by standard web frameworks.

Custom Validation

PostgREST does not enforce any extra constraints besides JWT validation. An example of an extra constraint would be to immediately revoke access for a certain user. Using db-pre-request you can specify a stored procedure to call immediately after User Impersonation and before the main query itself runs.

db-pre-request = "public.check_user"

In the function you can run arbitrary code to check the request and raise an exception(see RAISE errors with HTTP Status Codes) to block it if desired. Here you can take advantage of Request Headers, Cookies and JWT claims for doing custom logic based on the web user info.

CREATE OR REPLACE FUNCTION check_user() RETURNS void AS $$
DECLARE
  email text := current_setting('request.jwt.claims', true)::json->>'email';
BEGIN
  IF email = 'evil.user@malicious.com' THEN
    RAISE EXCEPTION 'No, you are evil'
      USING HINT = 'Stop being so evil and maybe you can log in';
  END IF;
END
$$ LANGUAGE plpgsql;