Deploy PostgREST on Clever-Cloud
https://postgrest.org/en/stable/
PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations.
In front of a PostgreSQL database, PostgREST is a tool that can generate APIs directly from the database schema, without any code, just a configuration.
With SQL skills, we are able to generate our backend platform with this tool. Of course, this tool is not a silver bullet. For a standard API that access data in a relational database, it is perfect. With functions, native support of JWT authentication, we are able to build a production grade backend.
Clever Cloud is a Platform as a Service, aiming at helping companies to ship way faster their web services in the cloud, with a pay-as-you-go pricing model. No infrastructure to manage, no system to update and no orchestrator to troubleshoot.
In other words : “git push” your app, and you’re done.
A SGBD (in our case PostgreSQL) has many features in addition to storage :
- multi users support
- views
- any kind of control on insert with triggers
- functions
- pagination
- …
Many of those features are not used by standard ORMs. Some controls or filters are not done directly on the database but after. Of course, ORMs supports native functionnalities but we know projects that uses ORMs just as a layer between data and API, all controls are done in the application server instead of using database native features.
For many use cases, we need to access data from a database through an API. This is exactly what PostgREST does without any difficulties, just rely on database native features.
For us, PostgREST provides :
- fast deployment
- new APIs on the fly just by creating views
- performance and scalability
- less code to maintain
If PostgREST is used safely (see below), it has all needed waranties for accessing a database through a REST API.
At please-open.it, we use PostgREST in several projects that are in production without any issue. We have now a set of recommendations and advices to get something :
- safe
- upgradable
- easy to maintain
The PostgREST documentation https://postgrest.org/en/stable/references/api/tables_views.html does not give any advice about architecture, only the tool capabilities for exposing tables, views and function as REST endpoints.
https://en.wikipedia.org/wiki/Database
https://www.cockroachlabs.com/blog/history-of-databases-distributed-sql/
Managing a large set of data in the 80’s meant giving access to that data for users. A database administrator was in charge of this part, the database management system already supported multi users. Each user can request data with a terminal :
On each table, access is granted through the “GRANT” command (and still exists).
https://en.wikipedia.org/wiki/View_(SQL)
a view is the result set of a stored query, which can be queried in the same manner as a persistent database collection object.
With modern architectures we do not create views (and sometimes we never write SQL queries, we trust in ORMs), but a view have 2 main features that could change the game :
- preset complex queries with a direct access just by a “SELECT”
- grant access to a user to this view
Remember the MVC pattern ?
- Model : our data model
- View …
- Controller : triggers in SQL
Our views will be the exposed APIs.
A view is an API, it should be “usable” out of the box. If we have a relational model and we expose tables directly, we have to make JOIN, FILTER, ORDER BY in the request. This is not how we do it.
We build our API only for our needs, by using specific views.
If we connect directly PostgREST to the “public” schema, we have to make the request so it will look like this :
GET /films?select=id,directors(first_name,last_name),title,year,rating,language
With view :
GET /films
With our model, by using a view, the API documentation is enough. If we plug directly to the tables, any API user needs to know the data model in order to make requests.
While inserting data with any kind of API, we have to control before insertion. Data types are not enough, for specific needs we use a native feature : triggers.
https://www.postgresql.org/docs/current/sql-createtrigger.html
https://www.postgresql.org/docs/15/sql-createview.html
PostgreSQL allow INSERT, UPDATE and DELETE on “simple views” (“Updatable Views” in the documentation).
According to the documentation :
- The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
- The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
- The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
- The view’s select list must not contain any aggregates, window functions or set-returning functions.
With all those 4 points, we know how to create “updatable views”, so as described in the PostgREST documentation we can insert, update or delete data through our views.
https://postgrest.org/en/stable/references/api/tables_views.html#insert
Postgrest is available on Github : https://github.com/PostgREST/postgrest
The repository does not have a branch per release but only a tag : https://github.com/PostgREST/postgrest/tags.
Clever-cloud can not deploy from a tag, only from branches. The easiest solution is :
- fork the postgrest project on your own github account
- create a branch from the tag
- push it to github
- deploy on Clever-cloud
First of all, clone the repository. Do not forget to uncheck “copy the main branch only”.
Now, you can clone it on your machine, then create the branch from the tag you want :
git checkout -b 11.0.1 tags/v11.1.0
Then :
git push --set-upstream origin 11.0.1
Link your Github account to your Clever-cloud account. After that, you will have access to all your github repositories.
Go to “create” then “an application”. Select “postgrest” in the list :
Select “Haskell” application type :
Then in the “scalability” section, edit the default param and select a “pico” instance which is widely enough for most use cases :
Select your zone, and now click on “Create”. The application will be built and launched. It results in a failure because we have to set it up.
In the “informations” tab, you can select the branch you want to deploy. In our case 11.1.0 :
https://www.clever-cloud.com/doc/deploy/addon/postgresql/postgresql/
Postgrest relies on a PostgreSQL database. Clever-Cloud has a PostgreSQL database as a service we will use.
From your administration console, create a new add-on then select “postgres”. For our use, we will not select a “DEV” instance.
Link it to your Postgrest application created previously :
In your add-on dashboard, open PG Studio :
Note : you can use any Postgresql client as you like.
As explained previously, we use a specific schema for APIs. You should not expose your model that contains your data. Retrieve the user of your database in the add-on dashboard.
CREATE SCHEMA api;
GRANT USAGE ON SCHEMA api to <you_user>;
-- GRANT ALL ON SCHEMA api to <you_user>;
-- We can give all access to our API, but for this example we will give access view by view.
PostgREST can be set up with a configuration file or directly with environment variables (like Keycloak) :
We need at least :
- PGRST_DB_ANON_ROLE : which role to use as anonymous role. In our case, it is the username
- PGRST_DB_SCHEMAS : schema to expose
- PGRST_DB_URI : connection to the database. Just copy/paste POSTGRESQL_ADDON_URI value
- PGRST_SERVER_PORT : by default Clever-Cloud expects an application to listen on port 8080.
- PGREST_OPENAPI_SERVER_PROXY_URI : our domain name
Restart the application. From the “domain names” tab, open your application and now you have a JSON File for OpenAPI.
You’re done !
In our case, we directly connect Postgrest to a Keycloak database. All tables are in a schema called “public”, to link an API to a table or a set of tables, we create some views :
CREATE VIEW api.client AS (select id, client_id from public.client);
GRANT SELECT ON api.client to <user>;
Our API is now created, check in the OpenAPI schema :
Only get on /client. Try this API and you will have only id and client_id returned.
Of course, a view can contains filters :
DROP VIEW api.client;
CREATE VIEW api.client AS (select id, client_id from public.client where public.client.enabled=false);
GRANT SELECT ON api.client to <user>;
The API will return only disabled clients.
Postgrest has some capabilities for a multi users application, with 2 differents ways :
- using local accounts, with a “users” table
- JWT token, in our case we will generate them with Keycloak
Postgrest has the ability to manage users with a table and a set of functions. A “login” API is created with a function, this function is designed to return a JWT token.
After that, every request has to contain a JWT token for authentication, PostgREST does not have a session management system.
https://postgrest.org/en/stable/how-tos/sql-user-management.html#sql-user-management
Because it uses a non official extension (pgjwt), we can not apply this method to our deployment on Clever-Cloud.
Use this documentation as an example to see how it is possible to execute custom functions from an API Call. Of course, we can make our own session management with a special table that contains all current sessions. But, this is a bad design for the DB performance.
JWT tokens are signed, by default, with the RSA256 algorithm. We need to retrieve the public key in order to bring it to postgres for signature verification. In the “realm settings” section, go to “Keys” tab and grab the public key :
This is a PEM format, PostgREST needs a JWK so we need a converter. Many exists, get the simplest : https://irrte.ch/jwt-js-decode/pem2jwk.html
We give the entire result to PostgREST in the variable PGRST_JWT_SECRET.
Due to limitations on PostgreSQL databases on Clever-Cloud, we can not use native roles.
With the parameter PGRST_JWT_ROLE_CLAIM_KEY we inform PostgREST where to find user roles in the JWT token. A mapping between PostgreSQL roles and given roles in the token is done.
We have to grant access to each view for concerned roles. So a user with needed roles in the token could request the view.
https://postgrest.org/en/stable/references/auth.html?highlight=role#overview-of-role-system
When you call an API with a JWT Token, all token informations are available as environment variables in the request.
Because we have views, we make our own SELECT request, we can make a kind of “row level filtering” https://www.postgresql.org/docs/current/ddl-rowsecurity.html :
CREATE VIEW api.me AS (
SELECT id, username FROM public.user
WHERE id = current_setting('request.jwt.sub', true)
);
Row Level Security policies are not available on views in PostgreSQL. It does not matter, we can write down our policy directly in the SQL request.
https://postgrest.org/en/stable/references/transactions.html#pre-request
If you need to check specific access (header, cookie …), a SQL function can be called before each request. With all variables set by PostgREST, a control can be done every time.
With the configuration “response.headers”, some advanced features about images could be done with functions for example :
Want a CSV instead of a JSON structure ? Just add Accept: text/csv header and that’s it !
https://postgrest.org/en/stable/references/api/tables_views.html?#bulk-insert
One request to create several elements ? Bulk insert is supported if you give a CSV list or an array in JSON.
https://postgrest.org/en/stable/references/api/openapi.html#openapi
COMMENT ON VIEW api.me IS
'Returns the current user';
COMMENT ON COLUMN api.me.id IS
'The userid is the subject field in JWT token';
The postgREST documentation explains how to technically use the tool, with tables, procedures, views … And a “getting started” documentation shows out of the box all capabilities of this great tool.
Using postgREST directly out of the box connected to tables is not great at many points :
- data security, you have to think about all rules on each table
- exposes data model
- needs to know data model for join
- no clear separation of what is exposed or not
Because this tool can use all the power of PostgreSQL, with some principles (separate schemas, views, filter direct in SQL requests), it shows all the power with less than a half of … 256Mb of RAM.
