Skip to content
EngineerSoftware
Menu
Design
English

Engineer / Software / Portfolio

Designed a function‑first PostgreSQL data layer

Designed a PostgreSQL function‑first data layer — 1,275 stored functions across 34 schemas — so every read and write goes through a function the database can grant, rather than through a table.

Situation. Business logic has a way of leaking. A bit ends up in the API, a bit in some SQL a handler runs inline, and before long the same rule is written two or three slightly different ways and there’s nowhere you can point to and say “this is what the system does with its data.” That’s how bugs and security holes get in.

Task. The goal was one home for all of it: every read and write going through the database, the API a thin adapter that doesn’t know the business rules, and the whole thing lockable down tight.

Action. The data layer is function‑first. The schema is split by domain — identity, organization, review, message, notification and 29 more, 34 in all — and every operation the app can do is one of 1,275 PostgreSQL functions it calls; there’s no direct table access from Go at all. Then the database enforces it. The role the API logs in as, mariner, has EXECUTE on the app functions and USAGE on the schemas and nothing else — no SELECT, no INSERT, no way to touch a table directly — which comes to roughly 4,000 explicit grants rather than a blanket one. The functions run SECURITY DEFINER, owned by a separate non‑login function_owner role with a pinned search_path, and the superuser account stays reserved for migrations and cron, well away from the running app.

Result. The logic lives in one place you can actually audit, the API stays thin and boring in the good way, and the access boundary is enforced by Postgres itself rather than by everyone remembering the rules. If the API were somehow compromised, it still couldn’t do anything the functions don’t allow. At 1,275 functions the discipline costs something real — a new field is a migration and a function change, not a line in a query — and that friction is the price of the boundary holding.

Contact

Still working out what you need?

Tell us what is going wrong, or what you are trying to build. We will tell you what it would take, roughly what it would cost, and whether we are the right people for it.

From 1,400 DKK an hour, excluding VAT — and less per hour on a longer booking.

welcome@engineer.company +45 50 10 61 84 Copenhagen, Denmark