Re: Proposal: Deferred Replica Filtering for PostgreSQL Logical Replication

On Sat, Mar 15, 2025 at 12:29 PM Dean <ds(dot)blue797(at)gmail(dot)com> wrote:

> Currently, PostgreSQL's logical replication filters apply
> deterministically. Deferred filtering, however, operates after the WAL has
> been decoded, giving it access to the complete row data and making
> filtering decisions based on mutable values. Additionally, record columns
> may be omitted by the filter.
>
> Deferred replica filtering allows for session-specific, per-row, and
> per-column filtering - features currently not supported by existing
> replication filters, enhancing security and data privacy.
>
>
We haven't even overcome this yet:

There are currently no privileges on publications. Any subscription (that
is able to connect) can access any publication. Thus, if you intend to hide
some information from particular subscribers, such as by using row filters
or column lists, or by not adding the whole table to the publication, be
aware that other publications in the same database could expose the same
information. Publication privileges might be added to PostgreSQL in the
future to allow for finer-grained access control.

You seem to be quite a few steps ahead.

That said:

The role used for the replication connection must have the REPLICATION
attribute (or be a superuser). If the role lacks SUPERUSER and BYPASSRLS,
publisher row security policies can execute. If the role does not trust all
table owners, include options=-crow_security=off in the connection string;
if a table owner then adds a row security policy, that setting will cause
replication to halt rather than execute the policy. Access for the role
must be configured in pg_hba.conf and it must have the LOGIN attribute.

So RLS is taken into consideration by the publication when sending changes
to a subscription. Though I wouldn't be surprised if there are edge cases
that could be worked on.

David J.