Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rxWbH-009QYu-NL for pgsql-admin@arkaria.postgresql.org; Thu, 18 Apr 2024 18:35:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rxWbG-008Iry-1U for pgsql-admin@arkaria.postgresql.org; Thu, 18 Apr 2024 18:35:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rxWbF-008Irq-J1 for pgsql-admin@lists.postgresql.org; Thu, 18 Apr 2024 18:35:09 +0000 Received: from mout.kundenserver.de ([212.227.17.24]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rxWbC-001fd1-Aa for pgsql-admin@lists.postgresql.org; Thu, 18 Apr 2024 18:35:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=msym.fr; s=s1-ionos; t=1713465304; x=1714070104; i=msalais@msym.fr; bh=+URyLte5NOuPXeNNlGZpT7jcSqvJAApsdp+MwD09gQM=; h=X-UI-Sender-Class:From:To:Subject:Date:Message-ID:MIME-Version: Content-Type:cc:content-transfer-encoding:content-type:date:from: message-id:mime-version:reply-to:subject:to; b=so9tpM8qL+Ph6+T5wQYgTU/Yomo6RPp70bn6rQG5UOowTS/aJmNc2jpGCzDBFBxb p8jgw8Sw0f+AVPZXkLpFYhF8So42xwPNPzxvEnNmQ7BBkVkr4puHc5NQJr+KQyysK s4Z7HXRaay/DUjNy77Ap4luB2uBmuwZTZOOc1lV9sq3xVi334fOCJv77OQA2ER/sq WAbVl+2gRKf9FCDn6nNETceud1ltki79w2H11xXxhy6tj8k35MJfSMQJyMBJ56lb1 5F5WA0EXOYcIDrZw3sC5Ymv6Fypmdjy6r6OHLsIvH7QnafYj9lur9lGWEOFdCQbUB AO2mQ8p24GTSM32S5g== X-UI-Sender-Class: 55c96926-9e95-11ee-ae09-1f7a4046a0f6 Received: from msim1 ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue106 [212.227.15.184]) with ESMTPSA (Nemesis) id 1MXH7g-1sEUQI1VKo-00YlFj for ; Thu, 18 Apr 2024 20:35:04 +0200 From: To: Subject: A flaw in treating WITH CHECK OPTION views Date: Thu, 18 Apr 2024 20:35:02 +0200 Message-ID: <000501da91bf$20e2b380$62a81a80$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01DA91CF.E46BF8B0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdqRXW9Y9SEMwVNaTLCOiM0a8fvL7A== Content-Language: fr X-Provags-ID: V03:K1:9XcwbK+l65AzZeVi0Ol5eyfJ3sDgAQODpXO3OAkYN9yr5DHd3Th TzDOOXTURVV60ITYvRVCtLIHb2LEJbpnUdH/4y7DiqHqLDjw6m+gTIwiGL2IkUv/+xgjZhd o7FJjZuza/zPYaq2zaJ0WqlJ29qqI+uVXC/RVj/iyp7jjJ/pD4tW9CROY37F9Anm+6au8ZI maVIWx2KrtHY9p45YtAQw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:Kv/w1asHCrU=;3KxShtMs+BrXQt/5Gf60aUnhi+i De2quax2eO+F9e2XwBZg4qgXx7fNksoMblMljiVO755n/31qLC83ReFExF8fO7lQVopraZBIK 7J3JPM3JyHHxcL6eXY37DWCunsatAhnmYqCvkQxM9n0b3tRypMlcgoPzhTv7Hw2riPbgCBCy7 WOi8+j9xS3UZ04ZFaZleYj8zbxMbCxoJugnw62tCxgrzZRLAqUwplHCkc76BwLV9TGhZxfXly 8YBpX7XASI2Oac/wYBLrpAZD1xIwkdgsmMzQQ9JZU2pA/uc0Ovr8horXOjPmlZbpM3o9RiWUy whXQrnf1CBn+wRIovQ1RzMb0snXbcktSTzj75k8U0KUJqgMNbh5li2VSzdP22IAmd02Bv/ACX 9maiwVHTn8A30qsryOSmKovjkQQEohvn+I72eRwXb03M43lWL9WHxZY4fl6l2+kd2c6mlXz1x +ENOEtNZq0h+UXk0ib5gvNegIvB9rnDcT5cio+TZgB0GAwldM9z8EW1Z2Smh+N1B9Sl7acMJO n5quv/v8QT6er9g2QmjZRX+ELuQHjiRqwrD8I87NggZtfGcOtBHvGHDwknkuCLfKRnmPwi24F 68W56MUdD8IEXx3Jds7yTU2ETkHLXEYf7NlyDrutHSNzLZE6jGVIu6xqxEsYEowrTpv8waS8C //sDLuOaxPYNX60RSU2BXDfwSRP12VamZCWOUqr5FE8W5+96C/SmZ1ON1T7VcH19X/yHrhDmK +n02XujEHTsQ/hEEKCy2eZuxTyPpPRlrFmsfsWjHRhPrtACrCbg+bQ= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0006_01DA91CF.E46BF8B0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I will demonstrate what I want with a little example msym=3D> create table t (c1 int, c2 int, c3 int); CREATE TABLE msym=3D> insert into t values (1, 1, 10),(2, 1, 10), (3, 2, 20), (4, 2, 20= ); INSERT 0 4 msym=3D> create view v as select c1, c2 from t where c2 =3D 2 with check o= ption; CREATE VIEW msym=3D> select * from v; c1 | c2 =2D---+---- 3 | 2 4 | 2 (2 lignes) msym=3D> update v set c2 =3D 3 where c1 =3D 3; ERROR: new row violates check option for view "v" DETAIL : Failing row contains (3, 3, 20). Suppose that view use is exactly to hide value of c3! This example shows a security issue. Best regards Michel SALAIS ------=_NextPart_000_0006_01DA91CF.E46BF8B0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

I will demonstrate what I want with a little = example

msym=3D> create table t (c1 int, c2 = int, c3 int);

CREATE = TABLE

msym=3D> insert into t values (1, 1, = 10),(2, 1, 10), (3, 2, 20), (4, 2, 20);

INSERT 0 4

msym=3D> create view v as select c1, = c2 from t where c2 =3D 2 with check option;

CREATE VIEW

msym=3D> select * from = v;

c1 | c2

----+----

  3 |  = 2

  4 |  = 2

(2 lignes)

 

msym=3D> update v set c2 =3D 3 where = c1 =3D 3;

ERROR:  new row violates check = option for view "v"

DETAIL : Failing = row contains (3, 3, 20).

 

 

Suppose that view use is exactly to hide value of c3! This = example shows a security issue.

 

Best = regards

 

Michel SALAIS

------=_NextPart_000_0006_01DA91CF.E46BF8B0--