CMS with the support of PostgREST

[thohlt]

Not sure if this is the right place to post this, but out of interest in how far you can go with PostgreSQL, I have spent the last few months building a content management system using only PostgREST for the API.

It can be fully self-hosted using the provided NixOS module and detailed information about the project can be found by following the links at https://archtika.com.

Some of the features used include row level security for authorization, image uploads using a BYTEA column and then validating the mimetype using SUBSTRING as well as the file size using OCTET_LENGTH, a storage quota system for the maximum number of bytes that can be uploaded to a particular website, exporting markdown articles using EXECUTE ... TO PROGRAM and PG_READ_BINARY_FILE and much more.

What is fascinating about this is that it is a really robust backend with a lot of functionality that has been achieved by just writing a few SQL statements, rather than having to deal with a lot of boilerplate code in addition to the database layout.

One thing that is currently missing is the addition of rate limiting using pre-request.

I thought this project could maybe be linked in https://docs.postgrest.org/en/v12/ecosystem.html#example-apps as a reference for other people, as some things might not be easy to figure out from the documentation alone.

Both my personal website (https://thilohohlt.com) and the documentation (https://docs.archtika.com) are now powered by archtika.

[wolfgangwalther]

It can be fully self-hosted using the provided NixOS module

You might be interested in the PostgREST NixOS module that I recently added: NixOS/nixpkgs#394041

Maybe you can re-use that?

[thohlt]

Interesting, thanks for the tip. I might have a look at it and see if it can simplify the module.

[wolfgangwalther]

I thought this project could maybe be linked in https://docs.postgrest.org/en/v12/ecosystem.html#example-apps as a reference for other people, as some things might not be easy to figure out from the documentation alone.

Sure, please put up a PR to add it.

[thohlt]

Done.

[steve-chavez]

@thiloho Very very interesting work. Where's the source for https://docs.archtika.com? I searched the markdown files on https://github.com/archtika/archtika, and didn't found them.

One thing that is currently missing is the addition of rate limiting using pre-request.

How are you planning to do it with pre-request? If the db is going to be hit for rate limit I don't think it would be effective. I'd suggest Nginx rate limiting instead.

[steve-chavez]

It is not a git-based CMS

Ah, got it. The markdown files are stored on the db tables.

[steve-chavez]

It might be possible to support a git-based workflow using a declarative approach for the SQL and couple it with this "trick":
PostgREST/plmustache#18 (comment)

\set tpl `cat tpl.md`
inserto into tbl (md) value (':tpl');

[thohlt]

There is already functionality to export all articles to a zip file at the click of a button (all done in Postgres here: https://github.com/archtika/archtika/blob/main/rest-api/db/migrations/20241029160539_export_articles.sql).

I suppose I could store a hashed GitHub access token in the website table. POST requests can be made using the http client with the markdown content stored in the main_content column as well as receiving from the webhook.

[thohlt]

Thanks a lot for the sponsorship @steve-chavez, I really appreciate it!

[steve-chavez]

@thiloho You welcome!

Btw, don't take any of my comments as feature requests. I'm already happy with having OSS projects building on top of PostgREST 😃

[wolfgangwalther]

image uploads using a BYTEA column and then validating the mimetype using SUBSTRING

You could consider using pg_byteamagic. It works nicely in my projects for that purpose.

One thing that I stumbled upon while browsing through the repo: The hardcoded jwt-secret in flake.nix. I assume this is only for local development, but it's hard to tell. For the random user, it would look better if a jwt-secret only used for local development is created in plain-text with a text like "only_used_for_local_development...." or so. This just "looks better", gives more trust.

[thohlt]

You could consider using pg_byteamagic. It works nicely in my projects for that purpose.

Yes, I first considered using it, but it is not packaged as a postgresqlPackage in nixpkgs, so I chose this method.

One thing that I stumbled upon while browsing through the repo: The hardcoded jwt-secret in flake.nix. I assume this is only for local development, but it's hard to tell.

Yes, this is only for local development, like most of the stuff in the flake.nix file. In production, a secure token is generated and rotated on server restart using /dev/urandom, as seen in the module.

I agree, though, that it can be a bit confusing.

[wolfgangwalther]

Yes, I first considered using it, but it is not packaged as a postgresqlPackage in nixpkgs, so I chose this method.

s/is/was

I packaged it a while ago in NixOS/nixpkgs#387925.

[thohlt]

Oh yes, a month ago. I added that functionality a while back, thanks for the tip.