Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oY4la-0001OI-AR for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Sep 2022 12:11:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oY4lY-0004g5-SD for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Sep 2022 12:11:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oY4lX-0004fw-RD for pgsql-hackers@lists.postgresql.org; Tue, 13 Sep 2022 12:11:48 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oY4lU-000378-0m for pgsql-hackers@lists.postgresql.org; Tue, 13 Sep 2022 12:11:47 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id C96535C018A; Tue, 13 Sep 2022 08:11:41 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute5.internal (MEProxy); Tue, 13 Sep 2022 08:11:41 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm2; t=1663071101; x=1663157501; bh=H k5eaRZAxGPlU/GcwkKwCBRMpted2qh5HDrxRmcZoPQ=; b=tOkm8frTf6cRae6oW zkjAd3b8x88TuFkxS1exVkclCm676GarTGiOH274Sy/ebRRjDe9Gc7sjHtMdM1c5 yUJnX3UlAQD8PTIGu1uYkRhXjX7uUYW6D/EVPM7tnBZECgKUk2KptEQNWYcUq1BD qvskrA7Y6zuCmsCCF9/weIRWn+y8OXZHUgQ2EGv6skLZ2MDUwRjiOFae7TX77caT gxzrQmBlu34OVAe2t3+FFj0EW5KzlZXMDjtDAArQqjMIGTz296777eBby8xBI58d QmrYaPvaESGYUdBs0FCz20AF7HLcmvfbg1nYmSVq7bdPagC7DS1zVr84DvOfoflg hOfxA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvfedrfedugedggeelucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffhvfevuffkgggtugfgjgesmhekreertddtjeenucfhrhhomheptehlvhgr rhhoucfjvghrrhgvrhgruceorghlvhhhvghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh eqnecuggftrfgrthhtvghrnhepleefhefgieetffffudefuedvfeegffeujedtheevudeh jefgheehvedugffgfeeinecuffhomhgrihhnpehpohhsthhgrhdrvghspdgvnhhtvghrph hrihhsvggusgdrtghomhenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgr ihhlfhhrohhmpegrlhhvhhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhg X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 13 Sep 2022 08:11:40 -0400 (EDT) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id E867C38C; Tue, 13 Sep 2022 14:11:38 +0200 (CEST) Date: Tue, 13 Sep 2022 14:11:38 +0200 From: Alvaro Herrera To: Amit Kapila Cc: pgsql-hackers@lists.postgresql.org, Peter Smith Subject: Re: pgsql: Doc: Explain about Column List feature. Message-ID: <20220913121138.yn7ekkfysxzhkm2u@alvherre.pgsql> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="zn2crer6unn56bmv" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --zn2crer6unn56bmv Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit On 2022-Sep-07, Amit Kapila wrote: > Doc: Explain about Column List feature. > > Add a new logical replication section for "Column Lists" (analogous to the > Row Filters page). This explains how the feature can be used and the > caveats in it. > > Author: Peter Smith > Reviewed-by: Shi yu, Vignesh C, Erik Rijkers, Amit Kapila > Backpatch-through: 15, where it was introduced > Discussion: https://postgr.es/m/CAHut+PvOuc9=_4TbASc5=VUqh16UWtFO3GzcKQK_5m1hrW3vqg@mail.gmail.com Hi I just read these docs and noticed that it mentions that column lists can be used for security. As far as I remember, this is wrong: it is the subscriber that builds the query to read column data during initial sync, and the publisher doesn't forbid to read columns not in it, so it is entirely possible for a malicious subscriber to read columns other than those published. I'm pretty sure we discussed this at some point during development of the feature. So I suggest to mention this point explicitly in its own paragraph, to avoid giving a false sense of security. While going over this text I found some additional things that could --in my opinion-- stand some improvement: * It feels better to start the section saying that a list can be specified; subscriber must have all those columns; omitting list means to publish everything. That leads to shorter text (no need to say "you need to have them all, oh wait you might only have a few"). * there's no reason to explain the syntax in vague terms and refer the reader to the reference page. * The first few seem to give no useful structure, and instead cause the text to become disorganized. I propose to remove them, and instead mix the paragraphs in them so that we explain the rules and the behavior, and lastly the effect on specific commands. The attached patch effects those changes. One more thing. There's a sect2 about combining column list. Part of it seems pretty judgmental and I see no reason to have it in there; I propose to remove it (it's not in this patch). I think we should just say it doesn't work at present, here's how to work around it, and perhaps even say that we may lift the restriction in the future. The paragraph that starts with "Background:" is IMO out of place, and it repeats the mistake that column lists are for security. Lastly: In the create-publication reference page I think it would be better to reword the Parameters section a bit. It mentions FOR TABLE as a parameter, but the parameter is actually table_name; and the row-filter and column-list explanations are also put in there when they should be in their own expression and column_name varlistentries. I think splitting things that way would result in a clearer explanation. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ --zn2crer6unn56bmv Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="column-list-wording.patch" diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 0ab191e402..3ac6336be2 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1093,70 +1093,55 @@ test_sub=# SELECT * FROM child ORDER BY a; Column Lists - By default, all columns of a published table will be replicated to the - appropriate subscribers. The subscriber table must have at least all the - columns of the published table. However, if a - column list is specified then only the columns named - in the list will be replicated. This means the subscriber-side table only - needs to have those columns named by the column list. A user might choose to - use column lists for behavioral, security or performance reasons. + Each publication can optionally specify which columns of each table are + replicated to subscribers. The table on the subscriber side must have at + least all the columns that are published. If no column list is specified, + then all columns in the publisher are replicated. + See for details on the syntax. - - Column List Rules + + The choice of columns can be based on behavioral or performance reasons. + However, do not rely on this feature for security: a malicious subscriber + is able to obtain data from columns that are not specifically + published. If security is a consideration, protections can be applied + at the publisher side. + - - A column list is specified per table following the table name, and enclosed - by parentheses. See for details. - + + If no column list is specified, all columns of the table are replicated + through this publication, including any columns added later. This means + that having a column list which names all columns is not the same as having + no column list at all: if columns are added to the table afterwards, those + will not be replicated. + - - When specifying a column list, the order of columns is not important. If no - column list is specified, all columns of the table are replicated through - this publication, including any columns added later. This means a column - list which names all columns is not quite the same as having no column list - at all. For example, if additional columns are added to the table then only - those named columns mentioned in the column list will continue to be - replicated. - + + A column list can contain only simple column references. The order + of columns in the list is not preserved. + - - Column lists have no effect for TRUNCATE command. - + + For partitioned tables, the publication parameter + publish_via_partition_root determines which column list + is used. If publish_via_partition_root is + true, the root partitioned table's column list is used. + Otherwise, if publish_via_partition_root is + false (the default), each partition's column list is used. + - + + If a publication publishes UPDATE or + DELETE operations, any column list must include the + table's replica identity columns (see + ). + If a publication publishes only INSERT operations, then + the column list may omit replica identity columns. + - - Column List Restrictions - - - A column list can contain only simple column references. - - - - If a publication publishes UPDATE or - DELETE operations, any column list must include the - table's replica identity columns (see - ). - If a publication publishes only INSERT operations, then - the column list is arbitrary and may omit some replica identity columns. - - - - - - Partitioned Tables - - - For partitioned tables, the publication parameter - publish_via_partition_root determines which column list - is used. If publish_via_partition_root is - true, the root partitioned table's column list is used. - Otherwise, if publish_via_partition_root is - false (default), each partition's column list is used. - - - + + Column lists have no effect for the TRUNCATE command. + Initial Data Synchronization diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index f61641896a..0a68c4bf73 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -94,7 +94,7 @@ CREATE PUBLICATION name effect on TRUNCATE commands. See for details about column lists. - + Only persistent base tables and partitioned tables can be part of a --zn2crer6unn56bmv--