Horton
Menu

Re: Column Filtering in Logical Replication

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>, Rahila Syed <rahilasyed90@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-12-18T03:52:05Z
Lists: pgsql-hackers 
On Sat, Dec 18, 2021 at 7:04 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2021-Dec-17, Tomas Vondra wrote:
>
> > On 12/17/21 22:07, Alvaro Herrera wrote:
> > > So I've been thinking about this as a "security" item (you can see my
> > > comments to that effect sprinkled all over this thread), in the sense
> > > that if a publication "hides" some column, then the replica just won't
> > > get access to it.  But in reality that's mistaken: the filtering that
> > > this patch implements is done based on the queries that *the replica*
> > > executes at its own volition; if the replica decides to ignore the list
> > > of columns, it'll be able to get all columns.  All it takes is an
> > > uncooperative replica in order for the lot of data to be exposed anyway.
> >
> > Interesting, I haven't really looked at this as a security feature. And in
> > my experience if something is not carefully designed to be secure from the
> > get go, it's really hard to add that bit later ...
>
> I guess the way to really harden replication is to use the GRANT system
> at the publisher's side to restrict access for the replication user.
> This would provide actual security.  So you're right that I seem to be
> barking at the wrong tree ...  maybe I need to give a careful look at
> the documentation for logical replication to understand what is being
> offered, and to make sure that we explicitly indicate that limiting the
> column list does not provide any actual security.
>

IIRC, the use cases as mentioned by other databases (like Oracle) are
(a) this helps when the target table doesn't have the same set of
columns or (b) when the columns contain some sensitive information
like personal identification number, etc. I think there could be a
side benefit in this which comes from the fact that the lesser data
will flow across the network which could lead to faster replication
especially when the user filters large column data.

-- 
With Regards,
Amit Kapila.

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Explain about Column List feature.

  2. Doc: fix column list vs. replica identity rules.

  3. Prohibit combining publications with different column lists.

  4. Fix the check to limit sync workers.

  5. Wait for subscription to sync in t/031_column_list.sql

  6. Move prattrs to the pg_publication_rel section in docs

  7. Allow specifying column lists for logical replication

  8. Fix row filters with multiple publications

  9. Fix publish_as_relid with multiple publications

  10. Add some additional tests for row filters in logical replication.

  11. Add index on pg_publication_rel.prpubid

  12. Avoid using DefElemAction in AlterPublicationStmt

  13. Small cleanups related to PUBLICATION framework code

  14. Add PublicationTable and PublicationRelInfo structs

  15. Fix various concurrency issues in logical replication worker launching