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

From: Adrian Klaver <[email-masked]>
Date: Thu, 31 Jul 2025 07:13:16 -0700

On 7/31/25 04:37, Dominique Devienne wrote:
> On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
> <[email-masked]> wrote:
>> On 31/07/2025 10:41, Dominique Devienne wrote:
>>> On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver <[email-masked]> 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
[email-masked]

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 31 Jul 2025 08:06:45 -0700

> been granted DMLs on that table."
Not incorrect, just insufficient since select is not a DML action.

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 31 Jul 2025 08:45:46 -0700

On 7/31/25 08:06, David G. Johnston wrote:
> On Thursday, July 31, 2025, Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>> wrote:

> Not incorrect, just insufficient since select is not a DML action.

1) Seems to be some difference on that:

https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

13  Data manipulation

13.5  <select statement: single row>

Function

          Retrieve values from a specified row of a table.

2) What if you do SELECT some_data_mod_fnc()?

3) In the case at hand there was an implied SELECT as part of the DELETE.

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 31 Jul 2025 17:54:27 +0200

> been granted DMLs on that table."

Not so. DML is Data Modification Language.
I did grant INSERT, UPDATE, DELETE.

As opposed to DQL, Data Query Language.
And yes, I failed to grant SELECT.

Normally SELECT comes from yet another role.
But not in this specific case.