Re: Add a hook for handling logical decoding messages on subscribers.

On Fri, Sep 18, 2026 at 6:13 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> Thanks for reviewing the patch!
>
> While I also think the patch is in good shape, I'd like to raise a
> security risk this feature might introduce, particularly around
> pg_logical_emit_message():
>

Review comments:
===============
1. Typo (/logcal/logical): <function>pg_logcal_emit_message()</function></link>.

2.
+ * The handler always runs with the privileges of the subscription owner.
+ * The payload is chosen by whoever called pg_logical_emit_message() on the
+ * publisher, which by default any role may do, so a handler that acts on the
+ * payload gives every role on the publisher the subscription owner's
+ * privileges. A handler that needs less than that can lower them itself
+ * with SwitchToUntrustedUser().

One thing to consider about this is advice is that unlike DML there's
no target role to derive, and SwitchToUntrustedUser() errors outright
if the subscription owner can't SET ROLE to whatever the handler
picks. So, it doesn't sound straight-forward to follow this advice.

3.
Restrict who can emit messages
+ on the publisher. Revoke the default privilege and grant it to a
role created
+ for this purpose:
+<programlisting>
+REVOKE EXECUTE ON FUNCTION pg_logical_emit_message(boolean, text,
text, boolean) FROM PUBLIC;
+REVOKE EXECUTE ON FUNCTION pg_logical_emit_message(boolean, text,
bytea, boolean) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_logical_emit_message(boolean, text,
text, boolean) TO message_emitter;
+GRANT EXECUTE ON FUNCTION pg_logical_emit_message(boolean, text,
bytea, boolean) TO message_emitter;
+</programlisting>

That above security recipe sounds reasonable, but it asks the
subscriber's security to be enforced by an action in a cluster the
subscriber admin frequently does not control. The Security section
should say something on the lines of: if you don't administer both the
subscriber and publisher, don't install a message handler that does
anything you wouldn't let every publisher role do. Also, I think the
above REVOKE has to be performed in all databases separately.

4. Based on above two points, I think we can fill the hole with something like:

CREATE SUBSCRIPTION ... WITH (messages = true, message_handler_role =
'msg_handler')

and then run the hook under SwitchToUntrustedUser(that_role). That
gives the subscriber admin a local, explicit privilege mechanism that
does not depend on the publisher's ACLs, doesn't touch WAL or the
protocol, and reuses machinery already in usercontext.c. If the option
is unset, either don't call the hook or keep today's behavior with the
documented warning.

--
With Regards,
Amit Kapila.