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

On 2025-Jul-31, Dominique Devienne wrote: > But also, it's weird DELETE allows you to delete all rows. > Yet prevents you from deleting just one, i.e. a subset. But you don't know what you deleted, so you cannot exfiltrate useful info by repeatedly deleting with varying WHERE values. I suspect t

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

On Thu, Jul 31, 2025 at 4:11 PM Tom Lane wrote: > Dominique Devienne writes: > > On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge > > wrote: > >> 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

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

On Thu, Jul 31, 2025 at 4:13 PM Adrian Klaver 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

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

On 7/31/25 08:06, David G. Johnston wrote: On Thursday, July 31, 2025, Adrian Klaver > 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

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

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 AM Guillaume Lelarge >> wrote: >> >>> On 31/07/2025 10:41, Dominique Devienne wrote: >>> On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver < adrian.kla...@aklav

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

On 7/31/25 04:37, Dominique Devienne wrote: On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge wrote: On 31/07/2025 10:41, Dominique Devienne wrote: On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver wrote: how can has_table_privilege() "lie" like this? It doesn't lie. The role has DELETE privil

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

Dominique Devienne writes: > On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge > wrote: >> 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 "WHE

Re: Yet more ROLE changes in v18 beta1???

On Thu, Jun 5, 2025 at 4:57 AM Tom Lane wrote: > Dominique Devienne writes: > > Unfortunately, digging into this is not something I can do right away. > > v18 is still a few months out, I do hope I can investigate before that. > > It'd be good if you could prioritize that a bit more. If there is

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

On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge wrote: > On 31/07/2025 10:41, Dominique Devienne wrote: > > On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver > > wrote: > > how can has_table_privilege() "lie" like this? > > It doesn't lie. The role has DELETE privilege. I guess what it lacks is >

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

On 31/07/2025 10:41, Dominique Devienne wrote: On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver wrote: My suspicion is that there is a missing piece in your chain of roles. But my point Adrian is that, in my case, has_table_privilege() returns true (t) yet the delete fails. Contrary to your exam

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

On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver wrote: > My suspicion is that there is a missing piece in your chain of roles. But my point Adrian is that, in my case, has_table_privilege() returns true (t) yet the delete fails. Contrary to your example above. I can easily accept that the problem l