public inbox for [email protected]  

help / color / mirror / Atom feed

From: Guillaume Lelarge <[email protected]>
To: [email protected]
Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
Date: Thu, 31 Jul 2025 11:35:05 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFCRh-8D+R=xufTFYN8SDDeEVwDNCb7kcspt9hsRW2T-QOMoKg@mail.gmail.com>
References: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com>
	<[email protected]>
	<CAFCRh-9AzsOBd6cPFsgmbw=Mf3nN5tHj9YYQQHZG0XqxDSMK=Q@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAFCRh-8D+R=xufTFYN8SDDeEVwDNCb7kcspt9hsRW2T-QOMoKg@mail.gmail.com>

On 31/07/2025 10:41, Dominique Devienne wrote:
> On Wed, Jul 30, 2025 at 9:42 PM Adrian Klaver <[email protected]> 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 lies on my side, but 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.

Quick test case:

postgres@rpm18 =# create role u1 login;
CREATE ROLE
postgres@rpm18 =# create table t1 (id integer);
CREATE TABLE
postgres@rpm18 =# grant delete on t1 to u1;
GRANT
postgres@rpm18 =# \c - u1
You are now connected to database "postgres" as user "u1".
postgres@rpm18 => delete from t1;
DELETE 0
postgres@rpm18 => delete from t1 where id=10;
ERROR:  permission denied for table t1
postgres@rpm18 => \c - postgres
You are now connected to database "postgres" as user "postgres".
🐘 on postgres@rpm18 =# grant select on t1 to u1;
GRANT
postgres@rpm18 =# \c - u1
You are now connected to database "postgres" as user "u1".
postgres@rpm18 => delete from t1 where id=10;
DELETE 0


-- 
Guillaume Lelarge
Consultant
https://dalibo.com

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox