Re: Add a hook for handling logical decoding messages on subscribers. - Mailing list pgsql-hackers

Hi,

On Thu, Sep 17, 2026 at 5:43 PM Masahiko Sawada <sawada.mshk@gmail.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():

Nice catch. Thanks for bringing it up.

> EXECUTE on that function is granted to PUBLIC, so any role that can
> connect to the publisher database can emit a logical decoding message.
> With this patch the apply worker hands the message to a handler that
> runs with the privileges of the subscription owner, and unlike
> insert/update/delete there is no table owner to switch to. One use
> case I have in mind is DDL replication. If an extension implements it
> on top of this hook and the subscription is owned by a superuser, any
> role on the publisher can choose what the handler is given and have it
> executed with superuser privileges on the subscriber. So extensions
> should carefully consider this case. I think the same applies to other
> extensions that might use this feature. The real problem is that there
> is no reliable way for the subscriber to tell whether a message came
> from the source it expects.

Right. This lets a less privileged user on the publisher get more
privileged things done on the subscriber.

> A practical solution is to revoke EXECUTE on pg_logical_emit_message()
> from PUBLIC on the publisher and grant it to a role created for that
> purpose.

That seems like a good approach. Since the message emitted by
pg_logical_emit_message() can carry anything the user wants and goes
into the WAL stream, careful production systems may already restrict
the function this way. But we can't assume that is always the case.

> I think that covers most cases I did consider having the
> server record the emitting role in the message so that the subscriber
> could check it, but I'm not sure this feature alone justifies it.

I lean towards this idea, with one change. Record what the emitter can
do, not who it is. Role names and OIDs of ordinary roles on the
publisher mean nothing on the subscriber. What the apply side wants to
know is simple. What privileges did the emitter have, with role
memberships expanded recursively, so they can be compared against what
the role applying the message has. So record the emitter's role
attributes and the predefined roles it had the privileges of, and hand
that to the hook as-is. With this, a hook implementer can say "the
emitter was less privileged than me, so I'll skip this logical
message", or apply a stricter rule per message type. It also answers
the real problem mentioned above, since the subscriber can now tell
whether the logical message came from a source it trusts. I can also
think of another place where this helps. The logical message today is
replayed as-is during crash recovery, on read replicas, and in setups
where replay is moved to storage away from compute.

I quickly played with this idea using Claude Code to illustrate it
better. I haven't reviewed it in depth. Attached for reference. Please
have a look.

That said, I don't want the hook addition to be blocked on this. It
can be discussed separately since there can be other valid use cases.

> So
> my current thought is to document these risks and add nothing special
> for these cases.

Documenting the risks is good enough to proceed for now IMHO. But hook
implementers can easily miss it.

I'm also just thinking out loud here. How about a predefined role for
pg_logical_emit_message()? The apply side could switch to that role
before calling the hook, which gives logical messages the same kind of
switch that apply gets with the table owner, and limits what any
logical message can do to whatever that role is granted on the
subscriber. Or the apply side could call the hook only when the
subscription owner has been granted that role, so the hook stays off
unless someone chose to give the subscription owner that role.

> I've added the documentation changes and rebased the patch. Any ideas
> and feedback is very welcome.

I reviewed the v5 patch and the diff from the v4 patch and it looks good to me.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com