Control Flow, Exceptions, Diagnostics, Dynamic SQL, and SECURITY DEFINER Safety

Learning outcomes

ServiceHub needs a small privileged routine that cancels a work order, records why it changed, and allows application callers to request only a constrained operation. This is where PL/pgSQL's control flow, diagnostics, dynamic SQL, and privilege model become one security problem. Dynamic commands must distinguish identifiers from values, and SECURITY DEFINER must not inherit a caller-controlled object lookup path.

1. Add a controlled cancellation/audit model

DROP TABLE IF EXISTS app.ch18_work_order_change CASCADE;SET ROLE servicehub_owner;CREATE TABLE app.ch18_work_order_change (  change_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  work_order_id bigint NOT NULL REFERENCES app.ch18_work_order(work_order_id),  changed_by name NOT NULL,  old_status text NOT NULL,  new_status text NOT NULL,  reason text NOT NULL,  changed_at timestamptz NOT NULL DEFAULT clock_timestamp());ALTER TABLE app.ch18_work_orderDROP CONSTRAINT IF EXISTS ch18_work_order_status_check;ALTER TABLE app.ch18_work_orderADD CONSTRAINT ch18_work_order_status_checkCHECK (status IN ('queued','assigned','completed','cancelled'));RESET ROLE;

The audit table is ordinary relational state. The routine will perform one explicit status transition and insert the audit row in the same transaction. If either statement fails, the caller's transaction fails unless the function catches and intentionally handles the error.

2. Control flow plus exact row-count diagnostics

CREATE OR REPLACE FUNCTION app.ch18_cancel_work_order(  p_id bigint,  p_reason text)RETURNS app.ch18_work_orderLANGUAGE plpgsqlVOLATILEPARALLEL UNSAFEAS $$DECLARE  v_before app.ch18_work_order;  v_after  app.ch18_work_order;  v_rows bigint;BEGIN  IF p_reason IS NULL OR length(btrim(p_reason)) < 5 THEN    RAISE EXCEPTION USING      ERRCODE = 'P1802',      MESSAGE = 'cancellation reason is too short',      HINT = 'Provide at least five non-space characters.';  END IF;  SELECT * INTO STRICT v_before  FROM app.ch18_work_order  WHERE work_order_id = p_id  FOR UPDATE;  IF v_before.status = 'completed' THEN    RAISE EXCEPTION USING      ERRCODE = 'P1803',      MESSAGE = format('completed work order %s cannot be cancelled', p_id);  ELSIF v_before.status = 'cancelled' THEN    RETURN v_before;  END IF;  UPDATE app.ch18_work_order  SET status = 'cancelled'  WHERE work_order_id = p_id  RETURNING * INTO v_after;  GET DIAGNOSTICS v_rows = ROW_COUNT;  ASSERT v_rows = 1, 'exactly one work order must be updated';  INSERT INTO app.ch18_work_order_change(    work_order_id, changed_by, old_status, new_status, reason  ) VALUES (    p_id, session_user, v_before.status, v_after.status, btrim(p_reason)  );  RETURN v_after;END$$;ALTER FUNCTION app.ch18_cancel_work_order(bigint,text)OWNER TO servicehub_owner;

SELECT ... INTO STRICT raises NO_DATA_FOUND or TOO_MANY_ROWS instead of silently assigning null/one arbitrary row. GET DIAGNOSTICS ROW_COUNT reports the immediately preceding SQL command. ASSERT is for programmer invariants and can be disabled with plpgsql.check_asserts; business errors belong in RAISE with stable SQLSTATE codes.

3. Loops are useful when each item genuinely needs procedural handling

CREATE OR REPLACE FUNCTION app.ch18_status_summary()RETURNS TABLE(status text, order_count bigint)LANGUAGE plpgsqlSTABLEAS $$DECLARE  v_status text;BEGIN  FOREACH v_status IN ARRAY ARRAY['queued','assigned','completed','cancelled']::text[]  LOOP    RETURN QUERY    SELECT v_status, count(*)    FROM app.ch18_work_order AS w    WHERE w.status = v_status;  END LOOP;END$$;SELECT * FROM app.ch18_status_summary();

This loop is deliberately small and bounded. In production, prefer one set-based GROUP BY when it expresses the operation directly; use procedural loops when each iteration has distinct control flow, diagnostics, or external routine calls that cannot be represented cleanly as one SQL statement.

4. Exception handlers should classify, not hide, failures

CREATE OR REPLACE FUNCTION app.ch18_cancel_with_diagnostics(  p_id bigint,  p_reason text)RETURNS TABLE(sqlstate text, message text, detail text, constraint_name text)LANGUAGE plpgsqlVOLATILEAS $$DECLARE  v_state text;  v_message text;  v_detail text;  v_constraint text;BEGIN  PERFORM app.ch18_cancel_work_order(p_id, p_reason);  RETURN QUERY SELECT '00000', 'success', NULL::text, NULL::text;EXCEPTION WHEN OTHERS THEN  GET STACKED DIAGNOSTICS    v_state = RETURNED_SQLSTATE,    v_message = MESSAGE_TEXT,    v_detail = PG_EXCEPTION_DETAIL,    v_constraint = CONSTRAINT_NAME;  RETURN QUERY SELECT v_state, v_message, v_detail, v_constraint;END$$;SELECT * FROM app.ch18_cancel_with_diagnostics(999999,'operator request');

This wrapper is pedagogical: production routines often should re-raise unexpected failures so transactions cannot continue under false success. The important point is that SQLSTATE, constraint name, detail, hint, and exception context are structured fields. Applications should branch on SQLSTATE/contract codes rather than English message text.

5. Dynamic SQL: identifiers are syntax; values are parameters

PL/pgSQL normally caches plans for static SQL. Dynamic SQL is appropriate when the table/column identifier itself changes. Values should still be bound through USING; identifiers should be quoted with format('%I', ...).

CREATE OR REPLACE FUNCTION app.ch18_count_orders_by(  p_column text,  p_value text)RETURNS bigintLANGUAGE plpgsqlSTABLEAS $$DECLARE  v_sql text;  v_count bigint;BEGIN  IF p_column NOT IN ('status') THEN    RAISE EXCEPTION USING      ERRCODE = 'P1804',      MESSAGE = 'unsupported filter column';  END IF;  v_sql := format(    'SELECT count(*) FROM app.ch18_work_order WHERE %I = $1',    p_column  );  EXECUTE v_sql INTO v_count USING p_value;  RETURN v_count;END$$;SELECT app.ch18_count_orders_by('status','completed');

USING keeps the value in its native parameter channel and avoids repeated text quoting/conversion. The whitelist controls which identifier shapes the routine is willing to expose. %I protects identifier syntax but does not decide authorization on its own.

6. Wrong dynamic SQL: concatenating values into executable text

CREATE OR REPLACE FUNCTION app.ch18_count_orders_unsafe(p_status text)RETURNS bigintLANGUAGE plpgsqlAS $$DECLARE  v_count bigint;BEGIN  EXECUTE 'SELECT count(*) FROM app.ch18_work_order WHERE status = ' || chr(39) || p_status || chr(39)  INTO v_count;  RETURN v_count;END$$;-- A value containing quote/comment syntax can alter the command text.-- Repair: EXECUTE '... status = $1' INTO v_count USING p_status;

Do not “repair” this with manual quote replacement. Parameter binding is the correct value channel. quote_literal/format('%L') are useful when a value genuinely must become literal text in generated DDL, but DML values should usually remain parameters.

7. SECURITY DEFINER is a privilege boundary

A normal function is SECURITY INVOKER: it uses the caller's privileges. A SECURITY DEFINER function uses the owner's privileges, so a caller can indirectly perform operations it could not perform directly. That is powerful enough to require threat modeling.

The course's app schema is owned by the non-login servicehub_owner role and application roles do not have CREATE there. We therefore treat app as a trusted schema for this lab and place pg_temp last in the function-specific search_path. Objects are still schema-qualified in the body.

BEGIN;CREATE OR REPLACE FUNCTION app.ch18_app_cancel_work_order(  p_id bigint,  p_reason text)RETURNS bigintLANGUAGE plpgsqlSECURITY DEFINERSET search_path = pg_catalog, app, pg_tempAS $$DECLARE  v_id bigint;BEGIN  PERFORM app.ch18_cancel_work_order(p_id, p_reason);  v_id := p_id;  RETURN v_id;END$$;ALTER FUNCTION app.ch18_app_cancel_work_order(bigint,text)OWNER TO servicehub_owner;REVOKE ALL ON FUNCTION app.ch18_app_cancel_work_order(bigint,text) FROM PUBLIC;GRANT EXECUTE ON FUNCTION app.ch18_app_cancel_work_order(bigint,text)TO servicehub_app;COMMIT;

New functions normally receive EXECUTE for PUBLIC. Revoking and granting in the same transaction avoids a window where every role could call a freshly created privileged routine. The owner should be the least-privileged role that still has the required table privileges—not a superuser by convenience.

8. Prove caller identity versus effective routine privileges

SELECT session_user, current_user;-- In a test session authenticated/SET ROLE as servicehub_app:SELECT app.ch18_app_cancel_work_order(18003,'customer withdrew request');SELECT change_id, work_order_id, changed_by, old_status, new_status, reasonFROM app.ch18_work_order_changeORDER BY change_id DESCLIMIT 5;

Inside a security-definer routine, object access executes with the function owner's effective privileges, while session_user remains useful for recording the original login identity. If your application uses role switching, decide whether session_user, current_user, or an explicit authenticated principal parameter represents the audit actor.

9. Security-definer review checklist

10. Checkpoint

Authoritative references

Routines, trigger timing, privileges, and planner promises are version-sensitive. These PostgreSQL 18 primary sources define the behavior used in this lesson.

• PostgreSQL 18 — PL/pgSQL Basic Statements
• PostgreSQL 18 — PL/pgSQL Control Structures and Error Diagnostics
• PostgreSQL 18 — PL/pgSQL Errors and Messages
• PostgreSQL 18 — CREATE FUNCTION / SECURITY DEFINER Safety
• PostgreSQL 18 — Function Security