MIME-Version: 1.0
References: <tencent_DCDF626FCD4A556C51BE270FDC3047540208@qq.com>
In-Reply-To: <tencent_DCDF626FCD4A556C51BE270FDC3047540208@qq.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Wed, 9 Apr 2025 16:14:51 +0530
Message-ID: <CAA4eK1+Z1=ZM5AJZz_GUWsjY982MfskEmYQS0QA6xGUZcJ9JGQ@mail.gmail.com>
Subject: Re: Feature Recommendations for Logical Subscriptions
To: YeXiu <1518981153@qq.com>
Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAA4eK1%2BZ1%3DZM5AJZz_GUWsjY982MfskEmYQS0QA6xGUZcJ9JGQ%40mail.gmail.com>
Precedence: bulk

On Tue, Apr 8, 2025 at 2:48=E2=80=AFPM YeXiu <1518981153@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 wit=
hin our department's real-time backup instance, set up a logical replicatio=
n account, replicate required tables to this isolated database via logical =
replication, and then create a dedicated account with column-level permissi=
ons on specific tables for the BI department.
>
> Recommendations:
>
> 1=E3=80=81Column Filtering Functionality=E2=80=8C: 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 cumbe=
rsome, especially for tables with many fields, and complicates future field=
 additions. We recommend adding a =E2=80=8Ccolumn filtering feature=E2=80=
=8C 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=E3=80=81Logical Replication Account Permissions=E2=80=8C:
> Logical replication permissions should be decoupled from general database=
 access permissions.
> Proposed workflow:
> Create a dedicated account with =E2=80=8Clogical replication privileges o=
nly=E2=80=8C.
> Create a logical replication slot and grant this account access =E2=80=8C=
only to the authorized replication slot=E2=80=8C.
> 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 processi=
ng 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?

--=20
With Regards,
Amit Kapila.