"CodeLess" backend using postgres, postgrest and oauth2 authentication with keycloak

Most of the time, a backend is just a layer between APIs and a database. Using the Postgrest project and some SQL we will build a backend without any Java, Golang, C# etc…

For this Demo, we will build a chat application with channels, messages, users and user profiles.

March 02,2020

First, a database

Database, an essential tool for every project on the web. Relationnal databases are not new… almost 50 years ! https://en.wikipedia.org/wiki/Relational_database

With stored procedures, views, user management, roles and a query langage we all know we can do whatever we want on our data. With frameworks, ORM and other tools that makes great integration between Object langages and Relational Databases we spend most of time using them to access data in a simple way, so simple that we can do directly with SQL.

https://www.postgresql.org/ is a very popular open source database system, so let's go with it.

Now our docker-compose file starts with :

version: '3'
            image: postgres
              - "5432:5432"
              POSTGRES_PASSWORD: password

An authentication server : keycloak with please-open.it

Go to https://console.please-open.it and subscribe to a free keycloak realm. It is the easiest way to start.

This realm will provide JWT tokens we need to authenticate users on our DB. JWT tokens are signed with a private key. We need to retreive a public key in order to verify token signature. Go to "realm settings", "keys", and get the public key for RSA RS256 algorithm.


This key is in PEM format. For postgrest, we need it in JWK. A simple tool online can do it for you : https://8gwifi.org/jwkconvertfunctions.jsp

Add "-----BEGIN PUBLIC KEY-----" and "-----END PUBLIC KEY-----" delimiters, paste your key and transform it to JWK.

pem to jwk

Create a client

A client for a web frontend is needed, to get an accesstoken after login. Go to "clients" and create a new one. This client in this example is named "frontend". Choose access type "public", and define the redirecturi to "https://playground.please-open.it/" for now.

new client


Go to "roles" tab in the client, we need only 2 roles : standard and admin. Create them, after we will associate to users. roles


2 users "standard" and 1 "admin".

After creating a user, remove all "required user actions", go to "credentials" and attribute a new password. In "Role Mappings", select your client "frontend" and add a "standard", or "admin" role.

add roles

Web application

The easiest way to obtain a token, is your (playground)[https://playground.please-open.it]. An opensource web application, oauth2 client for keycloak and token introspection. In "parameters", put your realm id and client id. Now we are ready to retreive a new token. Click on "login", enter a user credentials and the redirect will do the job.


An access_token is retreived and decoded. The base64 encoded will be used later, in each api call.

REST APIs : postgrest


Postgrest is a standalone app with a web server, turning a database directly into a RESTful API. Sometimes the only way we are using ORM for.


Authentication is ensured by JWT tokens. Postgrest verifies the signature with the given key (using "PGRSTJWTSECRET" variable), then get a role in the token's payload.

In order to verify the signature, a public key is needed. Retreive the JWK you have generated previously and paste is ENTIERLY in the "PGRSTJWTSECRET" variable.

Anonymous role

An anonymous role gives access to data without any authentication. We gives to postgrest a name of an anonymous role. By using this, all resources granted to this role will be accessible without a token.


Roles claim key

JWT tokens from keycloak looks like this :

            "jti": "de1eac06-1e81-47bb-96fd-fecf5dff2507",
            "exp": 1582648742,
            "nbf": 0,
            "iat": 1582648442,
            "iss": "https://app.please-open.it/auth/realms/122aa842-0cf0-48e6-a5bc-cca00254a9bb",
            "aud": "account",
            "sub": "bd9c53ff-d7f9-4131-b608-c7b8971059ad",
            "typ": "Bearer",
            "azp": "frontend",
            "nonce": "d251bdb0-5233-4c65-8a4e-f5ba78bc1a8e",
            "auth_time": 1582646909,
            "session_state": "fe5bbce5-1c6e-4715-986b-517e3250187b",
            "acr": "0",
            "realm_access": {
                "roles": [
            "resource_access": {
                "account": {
                    "roles": [
                "frontend": {
                    "roles": [
            "scope": "openid profile microprofile-jwt email",
            "upn": "todo_user",
            "email_verified": true,
            "groups": [
            "preferred_username": "todo_user"

"sub" is the user id, we need it later to link data to user. "email" is also interesting.

Now, let's check the role of "frontend" client. The path is : resource_access.frontend.role at index 0.

So, we have the representation in the "PGRSTROLECLAIMKEY" : PGRSTROLECLAIMKEY: '.resource_access.frontend.roles[0]'

Enrich the docker-compose with this component :

version: '3'
            image: postgrest/postgrest
              - "3000:3000"
              - db:db
              PGRST_DB_URI: postgres://postgres:password@db:5432/example
              PGRST_DB_SCHEMA: api
              PGRST_DB_ANON_ROLE: web_anon
              PGRST_SERVER_PROXY_URI: ""
              PGRST_JWT_SECRET: '{"kty":"RSA","e":"AQAB","kid":"442d692e-cec3-4917-8164-e97ce25a8e7a","n":"wQ8mDGjqlM03MGKm6Tp9gGFmxcE5Cf4Z6UMa1-i_H-UpilX9PVGLG54TP0NaMMgL4u6wpjI7CPVnowmMO0VvNlZLi9WJBGDj7zY_olMcYwubvb6rWBQ1DDnlnHa4g79_C4CVftUXrAHOhi4TrbjtiSX0f39f6WazfB__L8DtwDc2g5lCLazgXZl2oXn4PV1TmxsV9bKI391T_EbQpE2G-M9nM8mqH5Qj1F2GK5xX2S-_8zY11de2mekqRLfGzU3advbIJrj1ZiCWPC7WKI4MTEpcrki9m929ZbHRoKvDv2BQrq1DM3Ryj6YLgETtwxqmdTgyOOHG_8qmK0Nc9zDmLQ"}'
              PGRST_ROLE_CLAIM_KEY: '.resource_access.frontend.roles[0]'
              - db
            image: postgres
              - "5432:5432"
              POSTGRES_PASSWORD: password

First example : access data with roles

docker exec -it postgrest_db_1 psql -U postgres
        \c example;
        CREATE SCHEMA api;

Ok, now we have an empty database with a schema to expose as REST api.

Create a table for an example :

CREATE TABLE api.roles_example (
          uuid    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
          message_time    TIMESTAMP NOT NULL DEFAULT now(),
          message_body    TEXT

This table is exposed as a REST api. Create a role web_anon, defined as an anonymous role. Giving only access to schema permit API description in swagger.

        GRANT USAGE ON SCHEMA api TO web_anon;
        grant select on api.roles_example to web_anon;

Go to http://localhost:3000 and voilà ! This is a description for swagger.

Go to https://swagger.io/tools/swagger-ui/ and open "Live Demo". Enter your API URI http://localhost:3000 in the field and click on "explore".

GET request is authorized without an accesstoken, due to "webanon" role.


Only select is permitted on web_anon.

Now we register a new role "standard" which corresponds to the role we created previously in keycloak. With the configuration, postgrest will check in JWT token at resourceaccess.frontend.roles[0] value is "standard". This role will gives all access on api.rolesexample table :

        GRANT USAGE ON SCHEMA api TO standard;
        GRANT ALL ON api.roles_example TO standard;

Retreive an access_token from playground, and use it in "Authorization" header.

Now, we can create a new entry in db :

curl --location --request POST '' \
        --header 'Authorization: Bearer e...6Q' \
        --header 'Content-Type: application/json' \
        --data-raw '{"message_body": "learn how to auth"}'

The entry is created :

example=# select * from api.roles_example;
                         uuid                 |       message_time        |   message_body
         b425a0fa-3a7c-41fe-b38e-d83690c48772 | 2020-02-25 21:22:37.27535 | learn how to auth
        (1 row)

Retreive user data

Now we want to retreive a user context. A JWT token from keycloak contains a user id and also an email.

postgrest has a keywork "current_user" which is only the current ROLE ! Beware !!!

Postgrest sets variables with the JWT token content, decoded !

With current_setting function from postgres https://www.postgresql.org/docs/9.6/functions-admin.html those variables are accessible.

Create a new table as :

CREATE TABLE api.users_example (
          uuid    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
          message_time    TIMESTAMP NOT NULL DEFAULT now(),
          user_from       NAME      NOT NULL DEFAULT current_setting('request.jwt.claim.sub', true),
          role            NAME      NOT NULL DEFAULT current_user,
          message_body    TEXT

grant privileges :

GRANT ALL ON api.users_example TO standard;

From previous curl request, change only uri :

curl --location --request POST '' \
        --header 'Authorization: Bearer ey...g' \
        --header 'Content-Type: application/json' \
        --data-raw '{"message_body": "learn how to auth"}'
example=# select * from api.users_example;
                         uuid                 |        message_time        |              user_from               |   role   |   message_body
         72836148-ff67-4e94-bea7-cf44586ef491 | 2020-02-25 21:55:45.716668 | ab3196db-08f9-4651-b33b-1277fa39677c | standard | learn how to auth
        (1 row)

Wonderful !

Set a new value with the second user, for next step.

Filter data for user

Postgres from version 9.5 allows policies with row level security. https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html

First, enable it on table :

CREATE POLICY users_example_policy ON api.users_example
          USING (user_from = current_setting('request.jwt.claim.sub', true))
          WITH CHECK (user_from = current_setting('request.jwt.claim.sub', true));

I'm not kidding… with only this, not select is filtered on user id, retreived from JWT token.

Build an app entirely

Now we are going to build a chat app, from scratch. Keycloak will be our oauth2 provider. Database is of course Postgres, APIs served by Postgrest and a simple web application with Bootstrap.

We want :

A user can :

An admin can :

Database model

CREATE TABLE api.user (
          id              NAME      PRIMARY KEY DEFAULT current_setting('request.jwt.claim.sub', true),
          email           NAME      NOT NULL DEFAULT current_setting('request.jwt.claim.email', true),
          username        NAME      NOT NULL DEFAULT current_setting('request.jwt.claim.prefered_username', true)
CREATE TABLE api.channel (
          id              SERIAL           PRIMARY KEY,
          title           VARCHAR(50)      NOT NULL,
          description     TEXT             
CREATE TABLE api.message (
          id              SERIAL       PRIMARY KEY,
          channel_id      INTEGER      REFERENCES   api.channel(id),
          user_id         NAME         NOT NULL DEFAULT current_setting('request.jwt.claim.sub', true),
          message_time    TIMESTAMP    NOT NULL DEFAULT now(),
          body            TEXT             
CREATE TABLE api.subscription (
          id              SERIAL       PRIMARY KEY,
          channel_id      INTEGER,
          FOREIGN KEY (channel_id) REFERENCES api.channel(id),
          user_id         NAME         REFERENCES   api.user,
          subscriber      NAME         NOT NULL DEFAULT current_setting('request.jwt.claim.sub', true)

Roles and Privileges

        GRANT USAGE ON SCHEMA api TO standard;
        GRANT USAGE ON SCHEMA api TO admin;
GRANT ALL ON api.subscription TO admin;
GRANT SELECT ON api.subscription TO standard;
GRANT ALL ON api.message TO standard;
GRANT ALL ON api.message TO admin;
GRANT ALL ON api.channel TO admin;
GRANT SELECT ON api.user TO admin;
        GRANT INSERT ON api.user TO admin;
        GRANT INSERT ON api.user TO standard;
GRANT SELECT ON api.user TO standard;


A row level constraint, a user can list only channels he has access to.


So an associated policy for a standard user, that can only see it's subscriptions :

CREATE POLICY subscription_policy ON api.subscription FOR SELECT TO standard
          USING (user_id = current_setting('request.jwt.claim.sub', true));

And an admin policy that can see everything :

CREATE POLICY subscription_policy_admin ON api.subscription TO admin
          USING (true);

Users table

We need to populate users tables only if a user does not exists.

CREATE FUNCTION api.add_user_if_not_exists() RETURNS VOID AS 
             INSERT INTO api.user VALUES (current_setting('request.jwt.claim.sub', true), current_setting('request.jwt.claim.email', true), current_setting('request.jwt.claim.preferred_username', true) ) ON CONFLICT DO NOTHING;

And do not forget to grant execution for standard role. Be carreful, a function are executable for all roles by default.

        GRANT EXECUTE ON FUNCTION api.add_user_if_not_exists TO standard;
        GRANT EXECUTE ON FUNCTION api.add_user_if_not_exists TO admin;

A stored procedure is available under "/rpc" URI. A GET request on /rpc/adduserifnotexists will execute the function in "read" mode, a POST in write.

So, a POST request with a Bearer token will populate user table :

example=# select * from api.user;
         b32157b4-6b65-4bb1-9c2e-7e39b3fa5659 | admin@example.com    | admin
         b81f4b7c-da3a-40e3-9a0a-ffc31379775f | user1@please-open.it | user1

Create first data

Register a new channel. A token with "admin" role is needed.

curl --location --request POST 'localhost:3000/channel' \
        --header 'Authorization: Bearer ey...g' \
        --header 'Content-Type: application/json' \
        --data-raw '{
            "title": "General"

Get on the same URI to retreive created resource :

                "id": 1,
                "title": "General",
                "description": null

And create a subscription for user :

curl --location --request POST 'localhost:3000/subscription' \
        --header 'Authorization: Bearer ey...A' \
        --header 'Content-Type: application/json' \
        --data-raw '{
            "channel_id": 1,
            "user_id": "b81f4b7c-da3a-40e3-9a0a-ffc31379775f"

A view to get all chans

Any view in the schema is also exposed as an API. Combined with the policy "subscriptionpolicy" previously created, this simple view shows directly chans that a user can have access under "/usersubscription" GET request :

CREATE VIEW api.user_subscription AS SELECT sub.channel_id, sub.user_id, chan.title, chan.description FROM api.subscription sub JOIN api.channel chan ON sub.channel_id=chan.id;
GRANT SELECT on api.user_subscription TO standard;
                "channel_id": 1,
                "user_id": "b81f4b7c-da3a-40e3-9a0a-ffc31379775f",
                "title": "General",
                "description": null

A view to get messages only from chans user has access to

CREATE VIEW api.user_messages AS SELECT mes.message_time, mes.body, sub.channel_id, sub.user_id, chan.title, chan.description FROM api.message mes JOIN api.channel chan ON mes.channel_id=chan.id JOIN api.subscription sub ON sub.channel_id=chan.id order by mes.message_time DESC;
GRANT SELECT on api.user_messages TO standard;

A GET request on /user_messages with a JWT token in "Authorization" header will show only messages user has access to.

Authentication layer : openresty and openidc

By default, postgrest only checks a signature in given token.

Using https://openresty.org/en/ and https://github.com/zmartzone/lua-resty-openidc we can have a nginx proxy with authentication on an openid connect server.

Your client on your keycloak realm need to have "confidential" access type.

A new redirect URI is also needed. In my case :

Using our openresty-oidc docker image : https://github.com/please-openit/nginx-openresty-oidc

Now because the authentication is driven by a backend, we need a "confidential" access type. A "credentials" tab is now available for client_secret. Do not forget to put the right redirect URI.


With a stateless layer, higly scalable there is no risk compared to a Java (or Go, haskell, python…) backend made by yourself. All stuff is done by Postgres which is a reliable product with high performance.

Keep in mind this product while designing a backend, this is a great time saver.