Security: any pgque_writer can ack another consumer/app's active batch by batch_id

[NikolayS]

Summary

Round-3 security review found that pgque.ack(batch_id) / finish_batch(batch_id) lets any pgque_writer finish any active batch if they know the batch_id.

Because pgque_writer inherits reader/table visibility, active batch ids are discoverable.

Impact

One app/user with pgque_writer can acknowledge/drop another app's active batch across queues/consumers.

This can cause message loss from the victim consumer's perspective.

Why it happens

ack(batch_id) is just:

return pgque.finish_batch(i_batch_id);

finish_batch() updates by sub_batch only:

update pgque.subscription
set sub_active = now(),
    sub_last_tick = sub_next_tick,
    sub_next_tick = null,
    sub_batch = null
where sub_batch = x_batch_id;

There is no queue, consumer, role, or ownership check.

Evidence

Local repro:

app_a received batch_id=1
app_b: select pgque.ack(1) -> 1
subscription advanced: sub_batch=NULL, sub_last_tick=2

Expected

A writer should not be able to finish another consumer's active batch unless PgQue intentionally treats pgque_writer as a fully trusted global operator.

If global trust is intended, docs should say so very explicitly. Otherwise API should enforce ownership/context.

Suggested fix options

1. Change modern wrapper API to require queue + consumer:

pgque.ack(queue text, consumer text, batch_id bigint)
pgque.nack(queue text, consumer text, batch_id bigint, msg_id bigint, ...)

and verify the active batch belongs to that queue/consumer.

2. Add per-queue/consumer ACLs or signed/opaque batch tokens if multiple apps share a database.

3. At minimum, document pgque_writer as a global trusted role that can affect all queues and consumers.

Regression test

• create two writer roles/app users
• app A receives a batch
• app B attempts ack(app_a_batch_id)
• expected: permission/error/no-op unless explicitly trusted global writer

Environment

Tested on main at 9b3f89f.