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 <adrian.klaver@aklaver.com> 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