postgrest lessons learned

I’ve been spending some time recently getting to grips with postgrest by writing a small schema and figuring out how it all sits together with the help of a simple python client. The plan is to continue to develop it as a react/redux app once I have postgrest and the data figured out 🙂 The following are just some things I’ve learned that may have helped me from 10 days ago and may help someone else.

Roles

I’ve ended up with 3 roles.

authenticator

This role is used as the “base” role for the database. It has sufficient access to change roles and nothing else. I use this in the postgres connection url and so it needs a password to allow connections to postgres to be made.

anon

This role caused the most confusion initially as I failed to grasp that all connections from postgrest will use this role UNLESS a valid jwt_token is presented that contains a different role. This means it needs,as a minimum, access to everything involved in the login/signup process. For the database tere is nothing else that is visible to non-authenticated users so I have only the login/signup permissions.

user

The user role is set in the jwt_token. Once presented all further access will be as this user so the permissions need to reflect that. Additionally there are some aspects of the user management that need to be accessed by this role, e.g. when the user changes their details.

One schema to rule them all

When starting postgrest there is the option to pass a schema. If none is supplied then the default of “public” is used. Anything else isn’t accessible from postgrest. This gives a lot of flexibility but also caused me some confusion initially when things weren’t available. If you need to span schemas then you need to write a view – which must be a member of the schema you have chosen. So far I’ve chosen to just use the default schema of ‘public’. Call me lazy 🙂

Functions need /rpc/, views don’t

This one was pretty obvious from the documents, but taken together with the schema point above caused me a lot of confusion. Essentially if you have defined a function called ‘login’ and another called ‘signup’ then they are available as /rpc/login and /rpc/signup. Views are simply available, so if you create a view ‘users’ it’s simply available as ‘/users’.

Direct access or not?

Simply creating tables in the chosen schema is enough to make them available. So, if you have a table called ‘colours’ then it’s directly available via postgrest using ‘/colours’ (depending on the permissions obviously). If, however, you created it in the ‘constants’ schema then it would need a view to access it. Which is preferable depends on the design and how much extra SQL you want to write.
I’m still trying to decide which route to go down. Having the “raw” tables hidden and only accessible via views provides a level of indirection that could well be useful, though adds a lot of extra coding – the lack of which was part of the attraction of postgrest to start with!

jwt_claims

This type needs to be defined with the information required by the app. Once supplied by a request, the information is retrieved by current_setting(‘postgrest.claims.xxx’) (where xxx is the member name).

Commands are available

In writing the sql files, the commands that can be used in the psql command line app are available! This revelation helped me streamline my debugging 🙂 Additionally I have split the sql into separate files and used ‘\i‘ to include them in a main file. This allows me to run it all as one or just the parts I need to update.

I need to improve my postgresql knowledge

As a friend said a while ago, “postgresql is a real database”. My knowledge of writing views, functions and the other bits needed to glue everything together is getting better, but the more you know the better.

gitter

Buried in one of the various documentation page was a link to gitter.im/begriffs/postgrest. The room isn’t too busy but seems to have enough people who know about postgrest to offer valuable help. Some of the other rooms also seem to be helpful with low amounts of noise and their app is pretty good.

Update?

Updates are done using the PATCH http verb and normally return a 204 No Content response. The data can be sent as json and all usual filtering arguments can be used to select the records to be updated.

PATCH /fruits?id=eq.1 Content-Length: 23 Content-Type: application/json Accept: application/json {"name": "Green Apple"}

If you want the full record, you need to add the Prefer header. This should return a 200 OK status code with the record.

PATCH /fruits?id=eq.1 Content-Length: 23 Content-Type: application/json Accept: application/json Prefer: return=representation {"name": "Green Apple"}