A few questions after my first explorations of PostgREST

[jenstroeger]

Hello,

I’ve been working with (Python) backends for many years and it has always felt somewhat bulky and clumsy and verbose to connect an app layer (Python) via an ORM layer (SQLAlchemy) to a database layer (PostgreSQL, previously also MySQL). I must admit: after exploring PostgREST and playing around with it I’m very smitten by its approach!

I do, however, have a few questions and requests for comments as to how to proceed in order to implement this or that requirement. I hope it’s ok to share a bit of code here…

Question 1

Setting up the JWT secret

-- Configure the JWT secret for the db. This should be set via environment variable.
alter database test_db set "app.jwt_secret" to '8R...J8';

is discussed here #3765 and I assume still the recommended way of propagating the secret from the environment to both, the PostgreSQL container and PostgREST container? Ideally, I think a single vaulted sources of truth would be best.

Question 2

The section SQL User Management was very helpful but I think a function illustrating user signup would help to complete the picture. I currently use this:

create function api.signup(email text, passwd text) returns record language plpgsql security definer as $$
    declare
        ret record;
    begin
        with
            p as (insert into api.profile(email) values (signup.email)),  -- `profile` table is a public view of the user's profile data
            u as (insert into auth.user(email, passwd) values (signup.email, signup.passwd))
        select signup.email into ret;
        return ret;
    end;
$$;
grant execute on function api.signup to anonymous;

This returns a 200 Ok but how can I have PostgREST return a 201 Created instead?

Is there a general recommendation of function vs. view? For example, I’d allow a POST to api.profile for anonymous web users and consider that a sign-up; then an insert trigger would insert a row into the private auth.user table. And following the mention here I guess it’s ok to create a new role in the system for every new user sign-up, to implement more fine-grained role-based access control (RBAC).

Question 3

As recommended in the OpenAPI section I used COMMENT to improve the generated spec. However, I noticed that for the public functions (e.g. api.signup() above) the spec contains two entries:

How can I prevent the GET from appearing there and, more generally, how do I control the HTTP methods that are allowed on a function?

Question 4

I’ve used pgTAP in the past to test PostgreSQL data modeling and it worked really well. With PostgREST, however, I suppose testing at the HTTP REST API level is more meaningful — what tools do you recommend here? Projects like RESTler Fuzzer seem useful for automation whereas Mocha or pytest require (tedious) manual setup.

What are your recommendations?

Question 5

In other Python projects we always use Alembic to handle db versioning and up/down migrations as the software evolves. There’s an interesting discussion What migration/versioning tool do you use?, discussion #2999, and, in addition, I came across pg_roll. How does PostgREST suggest to evolve the db as the project unfolds?

Question 6

I need to trigger async jobs (Python). There are PostgreSQL extensions that send messages to a queue (e.g. AMQP Bridge) and there’s PG’s own LISTEN and NOTIFY. The naive approach, I guess, would be to set up a Python container that opens a connection & session to the db and then just LISTENs endlessly, refreshes the connection periodically, and spawns async workers whenever a notification arrives from the db. (See also dramatiq-pg.)

What extensions or suggestions does the community have here?

[wolfgangwalther]

is discussed here #3765 and I assume still the recommended way of propagating the secret from the environment to both, the PostgreSQL container and PostgREST container? Ideally, I think a single vaulted sources of truth would be best.

Ideally, one should do the following:

• Use asymmetric encryption with a JWK / JWKS. (Bonus points for JWKS + rotating keys...)
• Only give the public key to PostgREST for verification.
• Store the private key securely e.g. via pgsodium or vault.
• Only allow access to the key via a SECURITY DEFINER function, which creates the JWT.

Note, that the latter is rather vague on purpose - because I have not implemented it myself like this, yet.

This returns a 200 Ok but how can I have PostgREST return a 201 Created instead?

You need set_config('response.status', ...), see https://docs.postgrest.org/en/v12/references/transactions.html#response-status-code.

Is there a general recommendation of function vs. view? For example, I’d allow a POST to api.profile for anonymous web users and consider that a sign-up; then an insert trigger would insert a row into the private auth.user table.

Personally, I would not use a view for signup. Here's why:

• Are you ruling out an "invitation based workflow" forever? If not, then POST /profile could indeed be something else: The act of an admin creating a profile in advance, thus inviting a user. The act of "signing up" would then be the acceptance of the invitation. I find that semantically better, especially because PATCH /profile and DELETE /profile correspond well to those admin actions.
• I personally made it a rule for me to always put stuff that requires DML for an anonymous user behind SECURITY DEFINER functions - and thus not as part of VIEWs. It's not that easy to write those RLS policies in a way that they really work well for anonymous users, but still allowing the necessary DML to create those users.

And following the mention here I guess it’s ok to create a new role in the system for every new user sign-up, to implement more fine-grained role-based access control (RBAC).

Since I wrote the linked comment an additional "Yes", won't help much. I have triggers on my data.profile table, which then create those roles automatically when the rows are inserted.

How can I prevent the GET from appearing there and, more generally, how do I control the HTTP methods that are allowed on a function?

We have plenty of open tickets about OpenAPI, which we are not actively working on in the haskell code base anymore*. The goal is to do #1698, so OpenAPI is currently a bit "on hold".

That being said, #1870 is tangentially related, because we always show all methods for VIEWs, too - so that would be the same for RPCs as well, I guess.

I’ve used pgTAP in the past to test PostgreSQL data modeling and it worked really well. With PostgREST, however, I suppose testing at the HTTP REST API level is more meaningful — what tools do you recommend here? Projects like RESTler Fuzzer seem useful for automation whereas Mocha or pytest require (tedious) manual setup.

I have experimented with newman in the past, but found it tedious to set up and work with. In my current long-going project, we are using a self-written snapshot tool. We basically just save tons of requests as yaml files, run those requests and save the responses in git. Whenever we change our API, we get snapshot changes - so we can immediately tell in which ways our API changes. The cool thing is, that we get the same feedback when upgrading PostgREST, which is quite helpful.

So yeah, I have a few lessons learned in that area, but nothing open sourced right now. The takeaway for me: I didn't find anything that satisfied my requirements when I started with that a couple of years ago, so rolled my own. I'm curious what the options are nowadays.. or whether it might make sense to build something in this area from scratch.

In other Python projects we always use Alembic to handle db versioning and up/down migrations as the software evolves. There’s an interesting discussion What migration/versioning tool do you use?, discussion #2999, and, in addition, I came across pg_roll. How does PostgREST suggest to evolve the db as the project unfolds?

That's the burning question, all the time, I think. One that, I haven't found the ultimate answer to, yet. My journey through that space was roughly like this:

• All the classic migration tools ("write up and down migrations, the tool will run them") are so... tedious to work with! Of course, we want the code to be checked into the git repo, right? But who can keep track of tables / structure of a bigger project when all you have is a big number of migrations? I want CREATE TABLE statements - and only those - in my repo, to be able to "see" the structure. Also.. with 100s of functions, views etc. writing migrations for all of those is really annoying.
• Next up is a number of tools (and I tried to roll my own, too), which will write the migrations for you, if you only give them the state before and after. This is much better to track history in a git repo - e.g. to add a column you will just add the column to the CREATE TABLE statement. Nice clean diff, not much to do. Unfortunately... it becomes really complicated when you're dealing with edge cases. What if I want to rename a table? Would that be an ALTER TABLE... or ... would the tool see this as DROP TABLE ..; CREATE TABLE ...;, because it can't make the connection? Feels too dangerous and magic.
• I settled for a combination of "fewest migrations as possible" + "dump all CREATE TABLE in the repo automatically" and a few other simplifications in the already mentioned Schema Migration Tool Preference #2999 (reply in thread).
• This still doesn't solve the biggest challenge, though: How to make sure that those migrations are really not causing downtime or block anything? It's hard to do that and it becomes even harder, when you do a lot of stuff in the database (which PostgREST leads you to do).. and want to keep the "refactor heavily" mentality to consistently improve the code and database structure.
• Enter pgroll, which I was excited about when I read about it... for about 10 minutes. Until I realized that, like with all the migration tools mentioned before, this works quite well when your database is mostly a dumb datastore and all your business logic is outside the database. You spin up multiple instances of your business layer, and each accesses the different versions of the API provided. Fine. Once you have your business logic in the database, you'll need to deploy that twice - inside the database. But now you have 100s of TRIGGERs on your tables - you can't deploy them twice or chaos will happen for sure.

I have been thinking about a different approach lately. Basically an extension of what I did in #2999 (reply in thread) already, where I recreate most of my database objects (except tables) from scratch on every deployment. What if.. I could recreate the whole database from scratch on every deployment? No migrations anymore, ever. At least not "change the existing table in this and that way". Instead, I'd deploy a new database, build the new schema from scratch and then sync the database via logical replication. OK - now the schema between the two instances differs, so we still need some kind of "migration". We would "only" need a rule to transform data from the old schema into the new schema. This could possibly be expressed as simple SELECT statements. You rename a column from a to b? SELECT a AS b, c, d FROM tbl would be the "transformation rule". Unfortunately, logical replication doesn't support that, yet, so this is really just an idea at this stage. But the consequences would be huge: Major version upgrades? Built-in, just a regular deployment. Changing underlying database settings like the default collation or even changing the collation provider? Same, just a regular deployment. Basically everything that is really hard right now, would become simple.

A lot of text, but no clear conclusion, sorry :)

I need to trigger async jobs (Python). There are PostgreSQL extensions that send messages to a queue (e.g. AMQP Bridge) and there’s PG’s own LISTEN and NOTIFY. The naive approach, I guess, would be to set up a Python container that opens a connection & session to the db and then just LISTENs endlessly, refreshes the connection periodically, and spawns async workers whenever a notification arrives from the db. (See also dramatiq-pg.)

What extensions or suggestions does the community have here?

Can't help that much here, because I don't trigger async jobs outside the database. I do everything inside. Yes, I do have a bit of python code as well - but I can run that via plpython3u functions. Yes, some of that needs to be async, too - I trigger my queues via pg_cron. Works great, so far.

Hopefully some of that is helpful.

[jenstroeger]

@wolfgangwalther thank you so much, your comments do help a lot and shed some light on various areas. Shall I mark this as an answer?

One aspect I’ve not addressed above is logging out: the example JWT approach doesn’t mention a server-side session & session ID which could be used to implement a “logging out”. Is that something you’ve considered before?

I need to trigger async jobs (Python). […]

In that context I came across pgmq which is also an interesting approach, somewhat different than using PG’s NOTIFY and LISTEN.

[wolfgangwalther]

Shall I mark this as an answer?

You can either mark it as an answer or keep it open for a bit - maybe somebody else comes up with their own input, too? I don't know whether that'd be the case, though.

Your call!

One aspect I’ve not addressed above is logging out: the example JWT approach doesn’t mention a server-side session & session ID which could be used to implement a “logging out”. Is that something you’ve considered before?

For me, logging out is a purely client-side activity: Just "forget" about the JWT - done.

If you're after "revoking JWT from the server-side", have a look at https://docs.postgrest.org/en/v12/references/auth.html#custom-validation.

[jenstroeger]

For me, logging out is a purely client-side activity: Just "forget" about the JWT - done.

I agree with the sentiment, but I have a feeling that the frontend folks would like to ensure that using a JWT after ”logging out” fails to auth. There’s also the problem of multiple browser tabs/client-side sessions and managing those — this needs more discussion, methinks.

If you're after "revoking JWT from the server-side", have a look at https://docs.postgrest.org/en/v12/references/auth.html#custom-validation.

Yup, thank you, that’s what I was thinking as well 👍🏼

[wolfgangwalther]

I agree with the sentiment, but I have a feeling that the frontend folks would like to ensure that using a JWT after ”logging out” fails to auth. There’s also the problem of multiple browser tabs/client-side sessions and managing those — this needs more discussion, methinks.

That's not really specific to PostgREST, but rather to the concept of stateless JWT in general - there is no such concept as "logging out".

Revoking a JWT should really be the exception, not something you do regularly. There are a few resources out there, which write up about stuff like that, I suggest you read up on the topic in general.

If you want to make sure the JWT don't work anymore, eventually - then you'll need to set a shorter expiry time.