"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'
services:
db:
image: postgres
ports:
- "5432:5432"
environment:
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.
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.
Roles
Go to "roles" tab in the client, we need only 2 roles : standard and admin. Create them, after we will
associate to users.
Users
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.
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
https://en.wikipedia.org/wiki/Representationalstatetransfer
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
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.
PGRSTDBANONROLE: webanon
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": [
"offline_access",
"uma_authorization"
]
},
"resource_access": {
"account": {
"roles": [
"manage-account",
"manage-account-links",
"view-profile"
]
},
"frontend": {
"roles": [
"todo_user"
]
}
},
"scope": "openid profile microprofile-jwt email",
"upn": "todo_user",
"email_verified": true,
"groups": [
"offline_access",
"uma_authorization"
],
"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'
services:
server:
image: postgrest/postgrest
ports:
- "3000:3000"
links:
- db:db
environment:
PGRST_DB_URI: postgres://postgres:password@db:5432/example
PGRST_DB_SCHEMA: api
PGRST_DB_ANON_ROLE: web_anon
PGRST_SERVER_PROXY_URI: "http://127.0.0.1:3000/"
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]'
depends_on:
- db
db:
image: postgres
ports:
- "5432:5432"
environment:
POSTGRES_PASSWORD: password
First example : access data with roles
docker exec -it postgrest_db_1 psql -U postgres
CREATE DATABASE example;
\c example;
CREATE SCHEMA api;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
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.
CREATE ROLE web_anon NOLOGIN;
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 :
CREATE ROLE standard NOLOGIN;
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 'http://127.0.0.1:3000/roles_example' \
--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 'http://127.0.0.1:3000/users_example' \
--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 :
ALTER TABLE api.users_example ENABLE ROW LEVEL SECURITY;
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 :
- users (populated on login with token informations)
- channels
- messages
A user can :
- list channels he has access to
- list messages from a channel
An admin can :
- delete messages
- grant access to a channel
Database model
CREATE SCHEMA api;
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
CREATE ROLE standard NOLOGIN;
GRANT USAGE ON SCHEMA api TO standard;
CREATE ROLE admin NOLOGIN;
GRANT USAGE ON SCHEMA api TO admin;
GRANT USAGE, SELECT ON ALL SEQUENCES IN 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;
Constraints
A row level constraint, a user can list only channels he has access to.
ALTER TABLE api.subscription ENABLE ROW LEVEL SECURITY;
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;
$$ LANGUAGE SQL STRICT;
And do not forget to grant execution for standard role. Be carreful, a function are executable for all roles by default.
REVOKE ALL PRIVILEGES ON FUNCTION api.add_user_if_not_exists FROM PUBLIC;
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 : http://127.0.0.1:8000/
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.
Next
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.