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.
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.
