Re: Feature Recommendations for Logical Subscriptions

On Thu, Apr 10, 2025 at 10:12 AM YeXiu <1518981153(at)qq(dot)com> wrote:

>
> For example:
> Assume database db1 has a user table with columns c1, c2, c3, ..., c10,
> telphone, and content (where telphone is a sensitive data field, and
> content is of type text).
>
> 1.We need to synchronize the user table to the BI department, but they
> should not have access to the telphone column due to sensitivity. The
> content column is also unnecessary for BI as it is too long and lacks
> analytical value.
> During synchronization, we need to exclude both telphone and content
> columns. However, the user table may continue to add new columns (e.g.,
> c11, c12) in the future.
> The current approach is:
>
> CREATE PUBLICATION pub FOR TABLE public.user (c1, c2, c3, ..., c10);
>
> When new columns like c11 or c12 are added, we must manually update the
> publication:
>
> ALTER PUBLICATION pub SET TABLE public.user (c1, c2, c3, ..., c10, c11,
> c12);
>
> This repetitive work is inefficient. I suggest using the EXCEPT syntax as
> you mentioned earlier:
>
> CREATE PUBLICATION pub FOR TABLE public.user EXCEPT (telphone, content);
>
> This would automatically exclude sensitive or unnecessary columns, even as
> new columns are added. Additionally, we need a method to modify the
> exclusion list dynamically, such as:
>
> ALTER PUBLICATION pub SET TABLE public.user EXCEPT (telphone);
> -- or --
> ALTER PUBLICATION pub SET TABLE public.user EXCEPT (telphone, content,
> c11);
>
>
> 2. Second Issue:‌
> This extends the scenario above. I’m unsure how permissions are currently
> handled.
>
> You mentioned creating a dedicated account u1 with only logical
> replication privileges, setting up a replication slot, and granting access
> to this slot.
> My question: If the telphone column is excluded from the publication, does
> the subscriber still receive or parse data for telphone? Or is the column
> entirely absent from the replication slot?
> If the replication slot does NOT include telphone data, this is a
> non-issue and can be ignored.
>
>
We don't send the telephone data to the subscriber. So the subscriber would
never need to parse it.

> 3. I hope others in the community can address these suggestions, as I am
> not a C developer and cannot implement them myself.
>
>
Okay, we previously discussed this feature, but due to a lack of interest,
it has been dropped. You can join that thread [1] and help with testing and
feature specifications.

[1] -
https://www.postgresql.org/message-id/CALDaNm3dWZCYDih55qTNAYsjCvYXMFv=46UsDWmfCnXMt3kPCg@mail.gmail.com

--
With Regards,
Amit Kapila.