Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On Thursday, July 31, 2025, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> On 7/31/25 04:37, Dominique Devienne wrote:
>
>> On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
>> <guillaume(dot)lelarge(at)dalibo(dot)com> wrote:
>>
>>> On 31/07/2025 10:41, Dominique Devienne wrote:
>>>
>>>> On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver <
>>>> adrian(dot)klaver(at)aklaver(dot)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."
>
>
Not incorrect, just insufficient since select is not a DML action.

David J.