Overview of Role System


Authentication Sequence






  "role": "user123"




GRANT user123 TO authenticator;


Users and Groups


Roles for Each Web User

PostgREST can accommodate either viewpoint. If you treat a role as a single user then the the JWT-based role switching described above does most of what you need. When an authenticated user makes a request PostgREST will switch into the role for that user, which in addition to restricting queries, is available to SQL through the current_user variable.


  message_uuid    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  message_time    TIMESTAMP NOT NULL DEFAULT now(),
  message_from    NAME      NOT NULL DEFAULT current_user,
  message_to      NAME      NOT NULL,
  message_subject VARCHAR(64) NOT NULL,
  message_body    TEXT



CREATE POLICY chat_policy ON chat
  WITH CHECK (message_from = current_user)


Web Users Sharing Role


  "role": "webuser",
  "email": "[email protected]"


current_setting('request.jwt.claim.email', true)


Hybrid User-Group Roles

There is no performance penalty for having many database roles, although roles are namespaced per-cluster rather than per-database so may be prone to collision within the database. You are free to assign a new role for every user in a web application if desired. You can mix the group and individual role policies. For instance we could still have a webuser role and individual users which inherit from it:

-- grant this role access to certain tables etc

GRANT webuser TO user000;
-- now user000 can do whatever webuser can

GRANT user000 TO authenticator;
-- allow authenticator to switch into user000 role
-- (the role itself has nologin)

Custom Validation

PostgREST honors the exp claim for token expiration, rejecting expired tokens. However it does not enforce any extra constraints. An example of an extra constraint would be to immediately revoke access for a certain user. The configuration file paramter pre-request specifies a stored procedure to call immediately after the authenticator switches into a new role and before the main query itself runs.


pre-request = "public.check_user"


  LANGUAGE plpgsql
  AS $$
  IF current_user = 'evil_user' THEN
    RAISE EXCEPTION 'No, you are evil'

Client Auth


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

JWT Generation

You can create a valid JWT either from inside your database or via an external service. Each token is cryptographically signed with a secret passphrase – the signer and verifier share the secret. Hence any service that shares a passphrase with a PostgREST server can create valid JWT. (PostgREST currently supports only the HMAC-SHA256 signing algorithm.)

JWT from SQL

You can create JWT tokens in SQL using the pgjwt extension. It’s simple and requires only pgcrypto. If you’re on an environment like Amazon RDS which doesn’t support installing new extensions, you can still manually run the SQL inside pgjwt which creates the functions you will need.


CREATE TYPE jwt_token AS (
  token text

CREATE FUNCTION jwt_test() RETURNS public.jwt_token
    LANGUAGE sql
    AS $$
  SELECT sign(
    row_to_json(r), 'mysecret'
  ) AS token
  FROM (
      'my_role'::text as role,
  ) r;




-- run this once
ALTER DATABASE mydb SET "app.jwt_secret" TO '!!secret!!';

-- then all functions can refer to app.jwt_secret
SELECT sign(
  row_to_json(r), current_setting('app.jwt_secret')
) AS token
FROM ...

JWT from Auth0


To use Auth0, copy its client secret into your PostgREST configuration file as the jwt-secret. (Old-style Auth0 secrets are Base64 encoded. For these secrets set secret-is-base64 to true, or just refresh the Auth0 secret.) You can find the secret in the client settings of the Auth0 management console.


Make sure OIDC-conformant is toggled off.

A recent Auth0 change sets it on by default. Turn it off here:

Clients > Your App > Settings > Show Advanced Settings > OAuth > OIDC Conformant

Ensure also that your client application does not pass in any audience configuration.

Our code requires a database role in the JWT. To add it you need to save the database role in Auth0 app metadata. Then, you will need to write a rule that will extract the role from the user metadata and include a role claim in the payload of our user object. Afterwards, in your Auth0Lock code, include the role claim in your scope param.

// Example Auth0 rule
function (user, context, callback) {
  user.app_metadata = user.app_metadata || {};
  user.role = user.app_metadata.role;
  callback(null, user, context);
// Example using Auth0Lock with role claim in scope
  container: 'lock-container',
  auth: {
    params: { scope: 'openid role' },
    redirectUrl: FQDN + '/login', // Replace with your redirect url
    responseType: 'token'

JWT security







PostgREST aims to do one thing well: add an HTTP interface to a PostgreSQL database. To keep the code small and focused we do not implement SSL. Use a reverse proxy such as NGINX to add this, here’s how. Note that some Platforms as a Service like Heroku also add SSL automatically in their load balancer.

Schema Isolation

A PostgREST instance is configured to expose all the tables, views, and stored procedures of a single schema specified in a server configuration file. This means private data or implementation details can go inside a private schema and be invisible to HTTP clients. You can then expose views and stored procedures which insulate the internal details from the outside world. It keeps you code easier to refactor, and provides a natural way to do API versioning. For an example of wrapping a private table with a public view see the Public User Interface section below.

SQL User Management

Storing Users and Passwords



First we’ll need a table to keep track of our users:

-- We put things inside the basic_auth schema to hide
-- refer to helpers and tables inside.
create schema if not exists basic_auth;

create table if not exists
basic_auth.users (
  email    text primary key check ( email ~* '^[email protected]+\..+$' ),


create or replace function
basic_auth.check_role_exists() returns trigger
  language plpgsql
  as $$
    raise foreign_key_violation using message =
      'unknown database role: ' || new.role;
    return null;
  end if;
  return new;

drop trigger if exists ensure_user_role_exists on basic_auth.users;
create constraint trigger ensure_user_role_exists
  after insert or update on basic_auth.users
  for each row
  execute procedure basic_auth.check_role_exists();


create extension if not exists pgcrypto;

create or replace function
basic_auth.encrypt_pass() returns trigger
  language plpgsql
  as $$
  if tg_op = 'INSERT' or new.pass <> old.pass then
  end if;
  return new;

drop trigger if exists encrypt_pass on basic_auth.users;
create trigger encrypt_pass
  before insert or update on basic_auth.users
  for each row
  execute procedure basic_auth.encrypt_pass();


create or replace function
basic_auth.user_role(email text, pass text) returns name
  language plpgsql
  as $$
  return (
  select role from basic_auth.users
   where users.email = user_role.email
     and users.pass = crypt(user_role.pass, users.pass)

Public User Interface




create or replace function
login(email text, pass text) returns basic_auth.jwt_token
  language plpgsql
  as $$
  _role name;
  result basic_auth.jwt_token;
  -- check email and password
  select basic_auth.user_role(email, pass) into _role;
  if _role is null then
    raise invalid_password using message = 'invalid user or password';
  end if;

  select sign(
      row_to_json(r), 'mysecret'
    ) as token
    from (
      select _role as role, login.email as email,
    ) r
    into result;
  return result;

An API request to call this function would look like:

POST /rpc/login HTTP/1.1

{ "email": "[email protected]", "pass": "foobar" }

The response would look like the snippet below. Try decoding the token at jwt.io. (It was encoded with a secret of mysecret as specified in the SQL code above. You’ll want to change this secret in your app!)

  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJlbWFpbCI6ImZvb0BiYXIuY29tIiwicm9sZSI6ImF1dGhvciJ9.fpf3_ERi5qbWOE5NPzvauJgvulm0zkIG9xSm2w5zmdw"



-- sacred, we simply choose them for clarity
create role anon;
create role authenticator noinherit;
grant anon to authenticator;

grant usage on schema public, basic_auth to anon;
grant select on table pg_authid, basic_auth.users to anon;

You may be worried from the above that anonymous users can read everything from the basic_auth.users table. However this table is not available for direct queries because it lives in a separate schema. The anonymous role needs access because the public users view reads the underlying table with the permissions of the calling user. But we have made sure the view properly restricts access to sensitive information.