Thread: Proposal: Deferred Replica Filtering for PostgreSQL Logical Replication

On Sun, Mar 16, 2025 at 12:59 AM Dean <ds.blue797@gmail.com> wrote:
>
> I'd like to propose an enhancement to PostgreSQL's logical replication system: Deferred Replica Filtering (DRF). The
goalof this feature is to provide more granular control over which rows are replicated by applying publication filters
afterthe WAL decoding process, before sending data to subscribers. 
>
> Currently, PostgreSQL's logical replication filters apply deterministically. Deferred filtering, however, operates
afterthe WAL has been decoded, giving it access to the complete row data and making filtering decisions based on
mutablevalues. Additionally, record columns may be omitted by the filter. 
>
> This opens up several possibilities for granular control. Consider the following examples:
> Alice and Bob subscribe to changes on a table with RLS enabled, allowing CRUD operations based on user's IDs.
> 1. Alice needs to know the timestamp at which Bob updated the table. With DRF, we can omit all columns except for the
timestamp.
> 2. Bob wants to track DELETEs on the table. Without DRF, Bob can see all columns on any deleted row, potentially
exposingcomplete records he shouldn't be authorized to view. DRF can filter these rows out. 
>
> Deferred replica filtering allows for session-specific, per-row, and per-column filtering - features currently not
supportedby existing replication filters, enhancing security and data privacy. 
>

We provide column lists [1] and row filters [2]. Doesn't that suffice
the need, if not, kindly let us know what exactly you need with some
examples.

[1] - https://www.postgresql.org/docs/devel/logical-replication-col-lists.html
[2] - https://www.postgresql.org/docs/devel/logical-replication-row-filter.html

--
With Regards,
Amit Kapila.

On 2025/03/19 10:00:00  </o=ExchangeLabs/ou=Exchange Administrative Group
(FYDIBOHF23SPDLT)/cn=Recipients/cn=3da34e28d9d240d4abadbbb549f9ff21-houzj.fnst>wrote:
 
On Wed, Mar 19, 2025 at 8:56 AM Dean wrote:
> Unfortunately, neither column lists nor row filters can provide the level of
> control I'm proposing. These revised examples might help illustrate the use
> case for DRF:
>
> Alice, Bob, and Eve subscribe to changes on a `friend_requests` table.
> Row-level security ensures CRUD access based on user IDs.
>
> 1. Per-subscriber column control: Bob makes a change to the table. Alice
>    should receive the entire record, while Eve should only receive the
>    timestamp - no other columns. Why DRF is needed: Column lists are static
>    and apply equally to all subscribers, meaning we can't distinguish Alice's
>    subscription from Eve's.

I would like to confirm the concept to ensure I understand it correctly. If
your goal is for the two subscriptions, Alice and Eve, to utilize different
column lists, you can achieve this by creating two separate publications:
Alice_pub and Eve_pub. You can specify the desired column list for the table in
Eve_pub. Consequently, the subscription Alice will subscribe to Alice_pub,
receiving the full record, while Eve will subscribe to Eve_pub, receiving a
limited set of columns. If this does not fully meet your requirements, could
you please provide additional details or clarify your specific needs ?

> 2. Bob DELETEs a row from the table. Alice should see the DELETE event, while
>    Eve should not even be aware of an event. Why DRF is needed: The
>    deterministic nature of row filters makes them unsuitable for
>    per-subscriber filtering based on session data.
>
>
> The goal of DRF is to allow per-subscriber variations in change broadcasts,
> enabling granular control over what data is sent to each subscriber based on
> their session context.

Same as above, it would be helpful to elaborate a bit more.

Best Regards,
Hou zj

Unfortunately, neither column lists nor row filters can provide the level of control I'm proposing. These revised examples might help illustrate the use case for DRF:

Alice, Bob, and Eve subscribe to changes on a `friend_requests` table. Row-level security ensures CRUD access based on user IDs.

1. Per-subscriber column control: Bob makes a change to the table. Alice should receive the entire record, while Eve should only receive the timestamp - no other columns. Why DRF is needed: Column lists are static and apply equally to all subscribers, meaning we can't distinguish Alice's subscription from Eve's.

2. Bob DELETEs a row from the table. Alice should see the DELETE event, while Eve should not even be aware of an event. Why DRF is needed: The deterministic nature of row filters makes them unsuitable for per-subscriber filtering based on session data.

The goal of DRF is to allow per-subscriber variations in change broadcasts, enabling granular control over what data is sent to each subscriber based on their session context.