Skip to Content
Meubles Morinière

Engineering choicesTechnical case studies4 min read

The storefront only has the rights it needs

Two applications on the same database, one of them open to the whole Internet. Here’s how we limit what a flaw in the storefront could do.

The problem

The ERP and the storefront share a Postgres database. The storefront is the exposed part: anonymous traffic, public forms, a checkout. The ERP handles the accounting, the stock and every customer’s data.

If the storefront connected with the same rights as the ERP, a flaw in the storefront (an injection, a logic error, a compromised dependency) would give access to everything. You could modify a paid order, read the internal notes or delete an invoice.

The constraints

  • A sale made online, in the store or from a quote is the same order, and we follow it through to the invoice. So both applications have to see the same data.
  • With one store and a small team, one more service to host and keep available isn’t worth it.
  • The catalogue is read on every page of the storefront. One more hop between the storefront and the database would add latency on every visit.
  • The storefront has good reasons to write to the database: orders awaiting payment, the customer account, withdrawals.
  • The guarantee can’t depend on the code it protects, because that code is exactly what may be at fault.

The alternatives

  • One database per application, with a sync between the two. That’s two schemas to maintain, and the sync becomes a source of errors itself. All that for a single store.
  • An ERP API in front of the database, called by the storefront. That’s one more service to host and keep available, and extra latency on every catalogue page.
  • A Postgres role reserved for the storefront, with its rights written in the code. Here, the database itself limits what the storefront can do, whatever its code does.

The decision

The storefront has its own Postgres role, moriniere_site. Its rights are written in the repository, and we compare them with the database on every migration applied to prod. It costs no extra service and no sync. And the guarantee doesn’t depend on the code it protects, since the database is the one that refuses.

The implementation

  1. The rights are written table by table and column by column (apps/site/scripts/lib/database-grants.ts). For each table, there are the privileges, the visible columns and the ones the storefront can modify. The columns reserved for the ERP (internal notes, the author of a write) stay invisible to the storefront.
  2. Triggers check the shape of what the storefront writes. With the storefront’s role, the database only accepts a web order awaiting payment, with no payment date and no internal note (Order_site_guard_insert). Order lines and quotes have equivalent guards.
  3. The role has time limits. A blocked query or an abandoned transaction doesn’t keep its connection or its locks for more than a few tens of seconds (SITE_SESSION_SETTINGS).
  4. At startup, the storefront refuses the wrong role. On a deployment, it checks that it’s connected as moriniere_site and that this role doesn’t bypass row-level security. Otherwise, it refuses to start (apps/site/shared/server/database-role-check.ts).
  5. A test collects every query the storefront makes and rejects any that doesn’t have its grant (apps/site/test/database-grants.regression.test.ts). And a suite on a real database tries these rights out (apps/site/test/db/site-database-role.db.test.ts).

Here’s the guard the database applies to an order written by the storefront, and the test that tries to mark an order as paid with the storefront’s role.

Order_site_guard_insertpackages/db/prisma/migrations/20261024020000_avis_retard_fabricant_et_rupture/migration.sql · lines 6–53
CREATE OR REPLACE FUNCTION "Order_site_guard_insert"() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF current_user = 'moriniere_site' AND (
    NEW."channel" <> 'WEB'
    OR NEW."paymentStatus" <> 'PENDING'
    OR NEW."fulfillmentStatus" <> 'UNFULFILLED'
    OR NEW."paidAt" IS NOT NULL
    OR NEW."confirmedAt" IS NOT NULL
    OR NEW."canceledAt" IS NOT NULL
    OR NEW."createdById" IS NOT NULL
    OR NEW."internalNote" IS NOT NULL
    OR NEW."customerId" IS NOT NULL
    OR NEW."depositCents" IS NOT NULL
    OR NEW."stripeSessionId" IS NOT NULL
    OR NEW."stripePaymentIntentId" IS NOT NULL
    OR NEW."shippedAt" IS NOT NULL
    OR NEW."deliveredAt" IS NOT NULL
    OR NEW."workshopDueAt" IS NOT NULL
    OR NEW."promisedWorkshopDueAt" IS NOT NULL
    OR NEW."workshopDueNoticeSentAt" IS NOT NULL
    OR NEW."supplierDelayNoticeSentAt" IS NOT NULL
    OR NEW."backorderNoticeSentAt" IS NOT NULL
    OR NEW."deliveryAppointmentStartsAt" IS NOT NULL
    OR NEW."deliveryAppointmentEndsAt" IS NOT NULL
    OR NEW."deliveryAppointmentNoticeSentAt" IS NOT NULL
    OR NEW."deliveryNoticeSentAt" IS NOT NULL
    OR NEW."deliveryEvidence" IS NOT NULL
    OR NEW."deliveryReserves" IS NOT NULL
    OR NEW."deliveryReservesNotedAt" IS NOT NULL
    OR NEW."carrierClaimedAt" IS NOT NULL
    OR NEW."confirmationSentAt" IS NOT NULL
    OR NEW."shipmentNoticeSentAt" IS NOT NULL
    OR NEW."pickupNoticeSentAt" IS NOT NULL
    OR NEW."withdrawalRequestedAt" IS NOT NULL
    OR NEW."withdrawalAcknowledgedAt" IS NOT NULL
    OR NEW."withdrawalClosedAt" IS NOT NULL
    OR NEW."withdrawalClosure" IS NOT NULL
    OR NEW."returnClosedAt" IS NOT NULL
    OR NEW."billingCorrectedAt" IS NOT NULL
    OR NEW."anonymizedAt" IS NOT NULL
    OR NEW."anonymizationReason" IS NOT NULL
  ) THEN
    RAISE EXCEPTION 'Order_site_guard_insert : le site ne crée qu''une commande web en attente de paiement';
  END IF;
  RETURN NEW;
END;
$$;

The results

At worst, a flaw in the storefront can create orders awaiting payment and write customer account data. It can’t mark an order as paid, touch an invoice or read what the ERP keeps to itself. It can still read what the storefront reads, though: the named columns of every order, contact details included. The actions that need those rights go through the ERP, over signed channels (Exchanges between the applications).

The limits

  • The list of rights follows the schema by hand. When the storefront has to read or write a table, we add it to the list, then run the script again on the database. The script that applies migrations before a deployment reports a misaligned role, and the regression test shows the omission before prod.
  • The storefront keeps normal writes (pending orders, customer account). So it still needs its own validations within that scope.
  • The role doesn’t protect secrets. If an attacker got the owner role’s password, they’d bypass everything. That role’s credentials are never given to the storefront’s project.

On this page