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 1uhUrs-000csd-DV for pgsql-general@arkaria.postgresql.org; Thu, 31 Jul 2025 15:06:52 +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 1uhUrr-001ezN-4G for pgsql-general@arkaria.postgresql.org; Thu, 31 Jul 2025 15:06:51 +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 1uhUrq-001eyn-Pn for pgsql-general@lists.postgresql.org; Thu, 31 Jul 2025 15:06:50 +0000 Received: from mail-oo1-xc2c.google.com ([2607:f8b0:4864:20::c2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uhUro-0002Xk-1a for pgsql-general@lists.postgresql.org; Thu, 31 Jul 2025 15:06:50 +0000 Received: by mail-oo1-xc2c.google.com with SMTP id 006d021491bc7-615950642d9so433971eaf.1 for ; Thu, 31 Jul 2025 08:06:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1753974406; x=1754579206; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=GDW1P9yo9iog2buqtDg7kQA6H6ABexj1f6JwGm7Rh5g=; b=nql2bm7TCDuf7p5++ER46K9weR9r1a0yxBmNDhXiRKBTkujcG9tgrW8CDdBZu01qvU lJjHrqIdvNL24q/otfMYefVc68xkWlVVJ2HJQSCuKTwoxObQwloFYxJMTh/Zlv2pQEKs WsLb/2hd6ZSr4ef4x7l4Ok/zGUDIAr55W7pvzDSFASwfxpbxXCunOi145vhfZrMNp+pq MrZ3FsFANgm7kNb17+PSYs+D0Sg6bff7m1TbbSkk7Alq/4PYi4FD7rKJ7pzILWNc4c36 IKrJFM2XyrgOS9ZWPO872Rw1VeXKS9ckb/odGVKwOTm7gCGnH/WmJLfHNkqJBSRDIY5J r7wQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1753974406; x=1754579206; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=GDW1P9yo9iog2buqtDg7kQA6H6ABexj1f6JwGm7Rh5g=; b=F6saCM3MhNRsrecdSryMlueR6fb8/4cPjd7lPdoGx6npbhtIJ3xOJD4lQhstx6zDLD 6/wfMEaivOhZZSbNKb5ZJA4XHKRdSNEtKUeGaQt1vBzxJ2wPHOf8uLyujkaFPVfjBgFP EWzqgqBkoM5Eblmop4E/vw7xx26smyTYKvj4jDyzkS6QzU360zLYhSvXn4RKgsO+b/b2 XLmTuoHpdR2pB31f7FnK8WAUhRw5nvZaguDl2IhVBAMxVHOmt5LYUGUdcjPCB1HE5STM TytBoY6mmygAfPqXZGhbbEIuyIjt4xbcZADZFLy/8fCD8s5vTBq7BAnB/aLIQ9cgrDnv CN4A== X-Forwarded-Encrypted: i=1; AJvYcCUNGPHIFTOJWpQUWUVsFInFsIqZyYwIS676isfkYGsiQQqWnJ/zj9h9VkqMiy26vr+G4xxIaOwnlv6Dzm9e@lists.postgresql.org X-Gm-Message-State: AOJu0YyVjTLsm5Cnwa5hub1u37TQKIOLZP8ifTmqEIP38wH9soJ5n1Dt WVimaChp1RNPmaTOtgx71RMAp164OFEcL+baZniYDMoXehOxT7V0Ijn5TycbLj25aF01hVCRx+d BgzKJaAiSkEHGeM180WYXPBImJpfQYyFOCoco X-Gm-Gg: ASbGncupCi0t2jzJcyJeiu4U4phTt+gy6Rp6ugjqiA2t872NOaOcha1/uxr8SHCi8Ie KE0SlNPGd/r/5mAuWlg5Ud2OE1TnoNWPQD//tRzyfRIRVOqQCzUsSqMKl4evM9Vb6wv4Bzr8H4n mepJmSaWMG6GHguyhpfu8+kWjsCWysk0qvlJ1PlZH68TzsT5jEmkSMQ/Hu4lYnZ2cpWZTJvH/Of uvvN87kNi3yzZg8ACNSiL9VPCcPeosgqcBBni+w X-Google-Smtp-Source: AGHT+IGP4OPunHLB8wfmuQd4Nux9tdueN1+X9K0AwsIgY8StKOiy4lOds1NJjBYDIwQ0hILCpnFi0JpPPQPaHArpu8w= X-Received: by 2002:a05:6820:2113:b0:615:a133:a0ed with SMTP id 006d021491bc7-6195d23bd83mr4863157eaf.2.1753974406413; Thu, 31 Jul 2025 08:06:46 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:6802:3708:b0:5da:a024:6db9 with HTTP; Thu, 31 Jul 2025 08:06:45 -0700 (PDT) In-Reply-To: References: <508f71c4-f1b1-4685-921d-bec8b361be10@aklaver.com> <662792ed-810d-46f1-a0c3-d4b55e5469fc@aklaver.com> <693d1252-89e4-498d-a5a6-5de6524bbb34@dalibo.com> From: "David G. Johnston" Date: Thu, 31 Jul 2025 08:06:45 -0700 X-Gm-Features: Ac12FXz1ocjO6a8Lo1KuYIa8vQFv22LJinGMGPVGdboB8-2fJ5L9cdO544G6x04 Message-ID: Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function To: Adrian Klaver Cc: Dominique Devienne , Guillaume Lelarge , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000708111063b3afd97" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000708111063b3afd97 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, July 31, 2025, Adrian Klaver wrote= : > On 7/31/25 04:37, Dominique Devienne wrote: > >> On Thu, Jul 31, 2025 at 11:35=E2=80=AFAM Guillaume Lelarge >> wrote: >> >>> On 31/07/2025 10:41, Dominique Devienne wrote: >>> >>>> On Wed, Jul 30, 2025 at 9:42=E2=80=AFPM Adrian Klaver < >>>> adrian.klaver@aklaver.com> wrote: >>>> how can has_table_privilege() "lie" like this? >>>> >>> >>> It doesn't lie. The role has DELETE privilege. I guess what it lacks is >>> the SELECT privilege. If you do a "DELETE FROM ... WHERE ...", you need >>> the SELECT privilege to perform the WHERE. Without "WHERE ...", it woul= d >>> work without the SELECT privilege. >>> >> >> Right on the money! Merci Guillaume!!! --DD >> >> PQ: NOTICE: can DELETE =3D t >> PQ: NOTICE: can SELECT =3D f >> > > So the below from the original post was not correct: > > "My setup ensures that the role I SET LOCAL ROLE to, has (indirectly) > been granted DMLs on that table." > > Not incorrect, just insufficient since select is not a DML action. David J. --000000000000708111063b3afd97 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, July 31, 2025, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 7/31/25 04:37, Dominique Devienne wrote:
On Thu, Jul 31, 2025 at 11:35=E2=80=AFAM Guillaume Lelarge
<guill= aume.lelarge@dalibo.com> wrote:
On 31/07/2025 10:41, Dominique Devienne wrote:
On Wed, Jul 30, 2025 at 9:42=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com= > wrote:
how can has_table_privilege() "lie" like this?

It doesn't lie. The role has DELETE privilege. I guess what it lacks is=
the SELECT privilege. If you do a "DELETE FROM ... WHERE ...", yo= u need
the SELECT privilege to perform the WHERE. Without "WHERE ...", i= t would
work without the SELECT privilege.

Right on the money! Merci Guillaume!!! --DD

PQ: NOTICE: can DELETE =3D t
PQ: NOTICE: can SELECT =3D f

So the below from the original post was not correct:

"My setup ensures that the role I SET LOCAL ROLE to, has (indirectly)<= br> been granted DMLs on that table."


Not incorrect, just insufficient since sel= ect is not a DML action.

David J.

--000000000000708111063b3afd97--