Menu

1 / 2

3 bugs readers found in my Postgres booking trigger (and how I fixed them)

DEV Community: postgres·Yassine Khattari·about 10 hours ago
#G4AWFS4O
#devtopostgresql#devto#dev#booking#trigger
Reading 0:00
15s threshold

A few weeks ago I wrote about preventing double bookings in Postgres with advisory locks, and recorded a short video where I test it live with two concurrent requests:

Readers reviewed the code and found real problems. This post lists three of them, what each one looked like, and the fix. If you build booking or checkout flows, you may have the same bugs.

Bug 1: the trigger ran with the wrong privileges

My first snippet was a plain trigger function. A trigger function runs with the privileges of the user doing the insert. With Row Level Security on, that user may only see their own rows, so count(*) could miss other people's bookings and the check would pass when it should not.

Fix: run the function as its owner, with a fixed search_path and qualified table names.

sql

CREATE OR REPLACE FUNCTION public.check_booking_availability()

RETURNS trigger

LANGUAGE plpgsql

SECURITY DEFINER

SET search_path = ''

AS $$

-- ...…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.