Security: pgque roles are bypassed by default PUBLIC EXECUTE on functions

[NikolayS]

Summary

Round-2 raw SQL security testing found that PgQue's role model is porous because most functions keep PostgreSQL's default PUBLIC EXECUTE privilege.

Although docs describe pgque_reader, pgque_writer, and pgque_admin as distinct access levels, a read-only / ungranted role can execute writer/admin-ish SECURITY DEFINER functions such as create_queue, drop_queue, and send.

Impact

A role intended to be read-only can mutate PgQue state:

• create queues
• drop queues
• send events
• possibly manage consumers / config / lifecycle functions depending on schema usage and function grants

This weakens the intended pgque_reader / pgque_writer / pgque_admin separation.

Repro

\i sql/pgque.sql

create role r_reader login;
grant pgque_reader to r_reader;

set role r_reader;

select
  has_schema_privilege(current_user, 'pgque', 'USAGE') as schema_usage,
  has_function_privilege(current_user, 'pgque.create_queue(text)', 'EXECUTE') as can_create,
  has_function_privilege(current_user, 'pgque.drop_queue(text)', 'EXECUTE') as can_drop,
  has_function_privilege(current_user, 'pgque.send(text,text,text)', 'EXECUTE') as can_send;

select pgque.create_queue('r_reader_created');
reset role;

Observed:

schema_usage | can_create | can_drop | can_send
-------------+------------+----------+---------
t            | t          | t        | t

create_queue
------------
1

Also verified with an ungranted PUBLIC role in a local probe:

PASS: ungranted PUBLIC role can create queue
PASS: ungranted PUBLIC role can drop queue it did not own

Expected

• pgque_reader should not be able to execute writer/admin mutating APIs.
• Ungranted PUBLIC roles should not be able to create/drop queues or mutate PgQue state.

Suggested fix

Use deny-by-default function privileges:

revoke execute on all functions in schema pgque from public;

Then explicitly grant the intended functions to each role:

• reader: introspection/read-only functions only
• writer: send/receive/ack/nack/subscribe APIs and necessary PgQ primitives
• admin: queue lifecycle/config/maintenance functions
• uninstall: keep superuser/schema-owner only

Need to account for functions created after blanket revokes too, especially colocated API/DLQ functions.

Tests to add

Add a role matrix test using actual SET ROLE, not only has_function_privilege:

• reader cannot execute: create_queue, drop_queue, send, receive, ack, nack, dlq_replay, set_queue_config
• writer can execute intended producer/consumer APIs but cannot purge/drop/admin config if policy says so
• admin can execute admin functions
• PUBLIC cannot mutate PgQue

Evidence

Tested on main at 9b3f89f.
Existing tests/run_all.sql passed before this probe.

[NikolayS]

Fix landed in PR #118

Option: Deny-by-default via blanket revoke + per-function revokes for API layer.

Red test output (commit e6927e3):

ERROR:  PUBLIC EXECUTE not revoked from 29 function(s): pgque.create_queue(text),
pgque.drop_queue(text), pgque.drop_queue(text, boolean), pgque.set_queue_config(text, text, text),
pgque.insert_event(text, text, text), pgque.insert_event(text, text, text, text, text, text, text),
pgque.send(text, text), pgque.send(text, jsonb), pgque.send(text, text, text),
pgque.send(text, text, jsonb), pgque.send_batch(text, text, text[]),
pgque.send_batch(text, text, jsonb[]), pgque.receive(text, text, integer),
pgque.ack(bigint), pgque.nack(bigint, pgque.message, interval, text),
pgque.subscribe(text, text), pgque.unsubscribe(text, text),
pgque.register_consumer(text, text), pgque.unregister_consumer(text, text),
pgque.next_batch(text, text), pgque.finish_batch(bigint), pgque.dlq_replay(bigint),
pgque.dlq_replay_all(text), pgque.dlq_purge(text, interval), pgque.start(),
pgque.stop(), pgque.maint(), pgque.ticker(), pgque.force_tick(text)

Fix commit: ed3e6a1

Fix approach:

• sql/pgque-additions/roles.sql: added revoke execute on all functions in schema pgque from public; (blanket deny — covers all core + additions functions created before roles.sql runs)
• sql/pgque-additions/dlq.sql: per-function revokes for DLQ functions (created after roles.sql)
• sql/pgque-api/maint.sql, receive.sql, send.sql: per-function revokes colocated with function definitions (API layer runs after roles.sql)

Green: PASS: security_public_execute - PUBLIC EXECUTE revoked from all mutating functions

PR: #118

[NikolayS]

Fix landed: commit 8e05192 on PR #118

What changed:

The pgque.maint() ownership check previously used JOIN pg_authid to resolve proowner to a role name. pg_authid is not readable by non-superusers on managed PostgreSQL services (RDS, Aurora, Cloud SQL, AlloyDB, Supabase, Neon, Crunchy Bridge), causing maint() to fail on every managed-DB install.

Both occurrences replaced with pg_catalog.pg_get_userbyid(p.proowner) — a built-in function readable by all roles, no pg_authid access required.

Tests added (tests/test_security_extra_maint.sql):

• Test A: attacker-owned queue_extra_maint function is still blocked (existing red→green)
• Test B: legit same-owner hook DOES execute (new green path)
• Test C: maint() called as a non-superuser (who cannot SELECT from pg_authid) succeeds — this is the direct proof-of-fix for this issue

gh pr checks 118 — no CI configured yet; all three tests pass locally.

[NikolayS]

Scrubbed the migration note for existing installs from docs/reference.md on branch fix/security-roles-and-definer: 72f193a. No v0.x users exist yet, so the migration prose was noise.