Message-ID: <693d1252-89e4-498d-a5a6-5de6524bbb34@dalibo.com>
Date: Thu, 31 Jul 2025 11:35:05 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql)
 function
To: pgsql-general@lists.postgresql.org
References: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com>
 <fa68fc72-e109-452e-8642-2b99c613f870@aklaver.com>
 <CAFCRh-9AzsOBd6cPFsgmbw=Mf3nN5tHj9YYQQHZG0XqxDSMK=Q@mail.gmail.com>
 <508f71c4-f1b1-4685-921d-bec8b361be10@aklaver.com>
 <662792ed-810d-46f1-a0c3-d4b55e5469fc@aklaver.com>
 <CAFCRh-8D+R=xufTFYN8SDDeEVwDNCb7kcspt9hsRW2T-QOMoKg@mail.gmail.com>
Content-Language: fr
From: Guillaume Lelarge <guillaume.lelarge@dalibo.com>
Organization: Dalibo
In-Reply-To: <CAFCRh-8D+R=xufTFYN8SDDeEVwDNCb7kcspt9hsRW2T-QOMoKg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/693d1252-89e4-498d-a5a6-5de6524bbb34%40dalibo.com>
Precedence: bulk

On 31/07/2025 10:41, Dominique Devienne wrote:
> On Wed, Jul 30, 2025 at 9:42=E2=80=AFPM Adrian Klaver <adrian.klaver@ak=
laver.com> wrote:
>> My suspicion is that there is a missing piece in your chain of roles.
>=20
> 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?
>=20
>=20

It doesn't lie. The role has DELETE privilege. I guess what it lacks is=20
the SELECT privilege. If you do a "DELETE FROM ... WHERE ...", you need=20
the SELECT privilege to perform the WHERE. Without "WHERE ...", it would=20
work without the SELECT privilege.

Quick test case:

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


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