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.
3. I hope others in the community can address these suggestions, as I am not a
C developer and cannot implement them myself.
YeXiu
1518981...@qq.com
原始邮件
发件人：Amit Kapila <amit.kapil...@gmail.com>
发件时间：2025年4月9日 18:44
收件人：YeXiu <1518981...@qq.com>
抄送：pgsql-hackers <pgsql-hackers@lists.postgresql.org>
主题：Re: Feature Recommendations for Logical Subscriptions
On Tue, Apr 8, 2025 at 2:48 PM YeXiu <1518981...@qq.com> wrote:
>
> Business Scenario:
> The BI department requires real-time data from the operational database.
In our current approach (on platform 14), we create a separate database within
our department's real-time backup instance, set up a logical replication
account, replicate required tables to this isolated database via logical
replication, and then create a dedicated account with column-level permissions
on specific tables for the BI department.
>
> Recommendations:
>
> 1、Column Filtering Functionality: During implementation, some tables may
contain sensitive data or long fields (e.g., text columns), while other fields
in these tables still need to be replicated to another database or instance.
Manually specifying individual columns becomes cumbersome, especially for
tables with many fields, and complicates future field additions. We recommend
adding a column filtering feature to logical replication to streamline this
process.
>
It would have been better if you could provide some examples. Let me
try to describe by example. We have a feature where users can specify
columns they want to replicate. For example: Create a publication that
publishes all changes for table users, but replicates only columns
user_id and firstname:
CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);
As per my understanding, you are expecting a feature where we can
specify columns that won't be replicated. For example say a table t1
has columns c1, c2, c3, ..., c10. Now, the user would like to
replicate all columns except c9 and c10, so he should be allowed to do
so by something like CREATE PUBLICATION users_filtered FOR TABLE t1
Except (c9, c10). Is that correct or you have something else in mind?
>
> 2、Logical Replication Account Permissions:
> Logical replication permissions should be decoupled from general database
access permissions.
> Proposed workflow:
> Create a dedicated account with logical replication privileges only.
> Create a logical replication slot and grant this account access only to
the authorized replication slot.
> This account would have no direct access to the database itself but could
subscribe to and consume data from the permitted replication slot.
> This approach allows securely providing the account to the BI department.
They can subscribe to the replication slot and perform downstream processing
independently, without risking exposure of unauthorized data.
>
We need to access catalog tables in the database while decoding
changes, so won't some interaction with database privileges still be
required?
BTW, are you planning to work on a patch on these proposals or you
expect someone else in the community to work on these proposals?
--
With Regards,
Amit Kapila.
