On Thu, Apr 10, 2025 at 10:12 AM YeXiu <1518981...@qq.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=46usdwmfcnxmt3k...@mail.gmail.com -- With Regards, Amit Kapila.
