public inbox for [email protected]
help / color / mirror / Atom feedRe: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
4+ messages / 3 participants
[nested] [flat]
* Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
@ 2025-07-31 14:13 Adrian Klaver <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Adrian Klaver @ 2025-07-31 14:13 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; Guillaume Lelarge <[email protected]>; +Cc: [email protected]
On 7/31/25 04:37, Dominique Devienne wrote:
> On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
> <[email protected]> wrote:
>> On 31/07/2025 10:41, Dominique Devienne wrote:
>>> On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver <[email protected]> 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 protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
@ 2025-07-31 15:06 David G. Johnston <[email protected]>
parent: Adrian Klaver <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2025-07-31 15:06 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Guillaume Lelarge <[email protected]>; [email protected] <[email protected]>
On Thursday, July 31, 2025, Adrian Klaver <[email protected]> wrote:
> On 7/31/25 04:37, Dominique Devienne wrote:
>
>> On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
>> <[email protected]> wrote:
>>
>>> On 31/07/2025 10:41, Dominique Devienne wrote:
>>>
>>>> On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver <
>>>> [email protected]> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
@ 2025-07-31 15:45 Adrian Klaver <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2025-07-31 15:45 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Guillaume Lelarge <[email protected]>; [email protected] <[email protected]>
On 7/31/25 08:06, David G. Johnston wrote:
> On Thursday, July 31, 2025, Adrian Klaver <[email protected]
> <mailto:[email protected]>> wrote:
> 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.
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.
>
> David J.
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
@ 2025-07-31 15:54 Dominique Devienne <[email protected]>
parent: Adrian Klaver <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Dominique Devienne @ 2025-07-31 15:54 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Guillaume Lelarge <[email protected]>; [email protected]
On Thu, Jul 31, 2025 at 4:13 PM Adrian Klaver <[email protected]> wrote:
> On 7/31/25 04:37, Dominique Devienne wrote:
> 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 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-07-31 15:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-31 14:13 Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function Adrian Klaver <[email protected]>
2025-07-31 15:06 ` David G. Johnston <[email protected]>
2025-07-31 15:45 ` Adrian Klaver <[email protected]>
2025-07-31 15:54 ` Dominique Devienne <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox