Feedback-ID: i76984098:Fastmail
Message-ID: <bc2b9760-3597-477a-b199-633ba3ac36e5@aklaver.com>
Date: Thu, 31 Jul 2025 07:13:16 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql)
 function
To: Dominique Devienne <ddevienne@gmail.com>,
 Guillaume Lelarge <guillaume.lelarge@dalibo.com>
Cc: pgsql-general@lists.postgresql.org
References: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com>
 <fa68fc72-e109-452e-8642-2b99c613f870@aklaver.com>
 <CAFCRh-9AzsOBd6cPFsgmbw=Mf3nN5tHj9YYQQHZG0XqxDSMK=Q@mail.gmail.com>
 <508f71c4-f1b1-4685-921d-bec8b361be10@aklaver.com>
 <662792ed-810d-46f1-a0c3-d4b55e5469fc@aklaver.com>
 <CAFCRh-8D+R=xufTFYN8SDDeEVwDNCb7kcspt9hsRW2T-QOMoKg@mail.gmail.com>
 <693d1252-89e4-498d-a5a6-5de6524bbb34@dalibo.com>
 <CAFCRh--tSWRRCMvtSovtRDX1wce5KCOutaDRBD5JKWb9atLC_w@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAFCRh--tSWRRCMvtSovtRDX1wce5KCOutaDRBD5JKWb9atLC_w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/bc2b9760-3597-477a-b199-633ba3ac36e5%40aklaver.com>
Precedence: bulk

On 7/31/25 04:37, Dominique Devienne wrote:
> On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
> <guillaume.lelarge@dalibo.com> wrote:
>> On 31/07/2025 10:41, Dominique Devienne wrote:
>>> On Wed, Jul 30, 2025 at 9:42 PM 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 would
>> work without the SELECT privilege.
> 
> Right on the money! Merci Guillaume!!! --DD
> 
> PQ: NOTICE: can DELETE = t
> PQ: NOTICE: can SELECT = 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."


-- 
Adrian Klaver
adrian.klaver@aklaver.com