Request for comments on a PostgREST based backend template implementation.

[jenstroeger]

Following my initial questions in discussion #3952, I set out to build a backend template¹ that sports the following features:

• PostgREST as the web-server;
• The Python Alembic framework to manage database migrations;
• The Python Dramatiq framework to implement async workers using dramatiq-pg as the Postgres based message broker; and
• The Python pytest tools for backend testing.

I am now keen to get feedback on the initial database migration which sets up the database for PostgREST (direct link): the following endpoints are added

• /rpc/signup to create a new account;
• /rpc/login to log in;
• /profile to view and update the a user’s profile;
• /rpc/job to create and view async jobs.

There are a bunch of TODO comments in the code, and I welcome any thought, suggestion, recommendation, criticism, concern, yay, nay as PR comments or discussion here.

Thank you heaps 🤓

Footnotes

1. I’ve not yet added a frontend to the stack. ↩

[steve-chavez]

Cool pg + python stack! It does complete some pieces that postgREST needs to be a full-stack framework. Some thoughts:

The Python Alembic framework to manage database migrations;

A migration system is something many users have been asking over the years. We used to recommend sqitch, but nowadays I would want to document + recommend the extension-based approach Wolfgang mentioned here, which is looking like the most straightforward/integrated way. That coupled with pg_tle could even work on pg cloud vendors. Granted there are no docs about this yet.

The Python Dramatiq framework to implement async workers using dramatiq-pg as the Postgres based message broker; and

A must-have too, also related to your previous question #4049. At current work I'm trying to come up with a more integrated way with pg_net, although it's not 1.0/stable yet.

The Python pytest tools for backend testing.

We also use pytest for PostgREST io tests, I think right now it's the best option for integration testing.

[jenstroeger]

Thank you for the feedback and links! The reason why I opted for Alembic and Dramatiq and pytest is that they’re all Python frameworks, thereby trying to KISS the repo 🤓

At current work I'm trying to come up with a more integrated way with pg_net, although it's not 1.0/stable yet.

Hm that looks interesting indeed, thank you!

I’m also curious about your opinion on the actual data model itself that the first Alembic migration sets up (direct link).

[steve-chavez]

I’m also curious about your opinion on the actual data model itself that the first Alembic migration sets up (direct link).

• I really like the data schema. We should recommend that schema in our docs too.
• Good separation of roles, I like that dramatiq role. I've been wishing we had called the authenticator role just postgrest instead, it's easier to find postgrest related queries on pg_stat_activity then.
• On this line:

or email = current_setting('request.jwt.claims', true)::json->>'email'

I think you might want to change it to or email = (select current_setting('request.jwt.claims', true)::json->>'email') for
performance. We had a related discussion before PostgREST/postgrest-docs#609 (comment)

Same on this line.

• This is nice for function security:

alter default privileges revoke execute on functions from public

A warning there, if you have another role that creates functions (like functions_admin) then the above won't apply for it. The alter default privileges above really is alter default privileges for role postgres -- will only affect objects created by the postgres role. We should add this on the docs too!

• Nice using security_invoker = true, that should be the default in postgres!

[jenstroeger]

Thank you for the great feedback, @steve-chavez!

I've been wishing we had called the authenticator role just postgrest instead, it's easier to find postgrest related queries on pg_stat_activity then.

It’s too impactful to change that now, huh? I agree, though, it would make more sense to me as a user of PostgREST as well… (Then again, postgres and postgrest role names are different only in one letter!)

I think you might want to change it to or email = (select current_setting('request.jwt.claims', true)::json->>'email') for performance. We had a related discussion before PostgREST/postgrest-docs#609 (comment)

That’s an interesting discussion, thank you for the link! I changed the policies to using a sub-query but left the other uses alone.

A warning there, if you have another role that creates functions (like functions_admin) then the above won't apply for it. The alter default privileges above really is alter default privileges for role postgres -- will only affect objects created by the postgres role. We should add this on the docs too!

Thanks for the clarification, I extended the comment in the Alembic migration.

• Nice using security_invoker = true, that should be the default in postgres!

Yay 🤓

[jenstroeger]

Would you folks be open to adding a link to the fullstack-webapp-template repo to your templates section here?

[steve-chavez]

For sure, please go ahead