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

From: Dominique Devienne <[email-masked]>
Date: Wed, 30 Jul 2025 13:37:49 +0200

Hi. PostgreSQL v17 here.

I'm stumped by something, and would like pointers to sort something out.
Inside my function, I do:
------------
    EXECUTE format('SET LOCAL ROLE %I', schema_owner);
    raise notice 'CURRENT_USER = %, can DELETE = %', CURRENT_USER,
has_table_privilege('SchemaMapping', 'DELETE');

    DELETE FROM SchemaMapping
     WHERE "schema" = schema_name;
-----------

PQ: NOTICE: CURRENT_USER = Joe, can DELETE = t

ERROR: permission denied for table schemamapping CONTEXT: SQL
statement "DELETE FROM SchemaMapping WHERE "schema" = schema_name"
PL/pgSQL function foo(text,text) line 28 at SQL statement

The raise notice output is correct, and as expected. The DELETE error isn't.
How can has_table_privilege() return true, yet the DELETE fail? I don't get it.

My setup ensures that the role I SET LOCAL ROLE to, has (indirectly)
been granted DMLs on that table.

I also double-checked outside the routine, directly in code, for that privilege:

    c.ctx().setRole(schema_owner);
    auto rset = pq::exec(c, "select
has_table_privilege('SchemaMapping', 'DELETE')");
    bool has_delete_mapping = rset.scalar<bool>();
    BOOST_CHECK(has_delete_mapping);

Are there special consideration I'm unaware of, regarding SET ROLE
inside routines?

(beside the fact you can't SET ROLE in a SECURITY DEFINER routime! As
I discovered recently...).

I'd really appreciate some help here, as I don't understand what's going on.

Thanks, --DD

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 30 Jul 2025 08:23:37 -0700

> Inside my function, I do:

What is the ROLE that defined the function?

What does "My setup ensures that the role I SET LOCAL ROLE to, has 
(indirectly) been granted DMLs on that table." in terms of actual GRANTs?

-- 
Adrian Klaver
[email-masked]

---

From: Dominique Devienne <[email-masked]>
Date: Wed, 30 Jul 2025 17:47:14 +0200

> What is the ROLE that defined the function?

A 3rd role. But does it matter? Given that this is in SECURITY INVOKER function?

> (indirectly) been granted DMLs on that table." in terms of actual GRANTs?

There's a fixed ROLE (called :SOWNER) that has USAGE on the SCHEMA
that owns the table.
And an explicit GRANT INSERT, UPDATE, DELETE ON TABLE SchemaMapping to it.

And that fixed :SOWNER ROLE is granted to the per-schema role I SET
LOCAL ROLE to, WITH INHERIT TRUE.
Lets call that latter role :OWNER1.

So inside the function, when I `SET LOCAL ROLE :OWNER1`
I expect current_role to become :OWNER1 (the raise notice corroborates that),
which activates the :SOWNER fixed role, since it was granted WITH INHERIT TRUE,
which thus give USAGE on the table SCHEMA, and DELETE on its TABLE.

Thus the DELETE DML should work...

That's what I'm expecting to happen. But it doesn't. And I don't see why yet.

The function and the table belong to yet another role.
And when we enter the function, we're yet another one (obviously with
USAGE+EXECUTE, since could call it).
But once we SET LOCAL ROLE, the effective permissions used should be
for :OWNER1 and the inherited :SOWNER.

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 30 Jul 2025 09:21:08 -0700

> A 3rd role. But does it matter? Given that this is in SECURITY INVOKER function?

My mistake, a BC(Before Coffee) issue.

> for :OWNER1 and the inherited :SOWNER.

Could this be a search_path and/or naming issue, where the table 
SchemaMapping appears in more then one schema or different name case?

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 30 Jul 2025 12:42:39 -0700

> SchemaMapping appears in more then one schema or different name case?

If the above is not the issue, then a simple test case:

grant db_user to app_user with set true, inherit true;

-- As db_user

create table fnc_set_role_test(id integer, fld1 varchar);
insert into fnc_set_role_test values (1, 'test');

CREATE OR REPLACE FUNCTION public.role_set()
  RETURNS void
  LANGUAGE plpgsql
AS $function$

BEGIN
     EXECUTE format('SET LOCAL ROLE %I', 'db_user');
     raise notice 'CURRENT_USER = %, can DELETE = %', CURRENT_USER,
     has_table_privilege('fnc_set_role_test', 'DELETE');
     DELETE FROM fnc_set_role_test;
END;
$function$
;

-- As app_user
\c - app_user

select * from fnc_set_role_test ;

select role_set();
NOTICE:  CURRENT_USER = db_user, can DELETE = t
  role_set
----------

(1 row)

  select * from fnc_set_role_test ;
  id | fld1
----+------
(0 rows)

My suspicion is that there is a missing piece in your chain of roles.

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 31 Jul 2025 10:41:18 +0200

> 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?

---

From: Guillaume Lelarge <[email-masked]>
Date: Thu, 31 Jul 2025 11:35:05 +0200

> 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

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 31 Jul 2025 13:37:37 +0200

On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
<[email-masked]> wrote:
> > how can has_table_privilege() "lie" like this?
>

Right on the money! Merci Guillaume!!! --DD

PQ: NOTICE: can DELETE = t
PQ: NOTICE: can SELECT = f

---

From: Tom Lane <[email-masked]>
Date: Thu, 31 Jul 2025 10:11:53 -0400

> Right on the money! Merci Guillaume!!! --DD

So the real problem here is that the "permission denied" error message
doesn't tell you which permission you lack.  I think we've had prior
discussions about improving that, but it never got done.  In some
cases it's a bit complicated -- for example, here either whole-table
SELECT or SELECT on the specific column would do.  But even pointing
you in the direction of "you need SELECT" would likely have led to
the correct line of thought, so maybe giving 100% of the details
isn't necessary.

			regards, tom lane

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 31 Jul 2025 17:59:42 +0200

On Thu, Jul 31, 2025 at 4:11 PM Tom Lane <[email-masked]> wrote:
> discussions about improving that, but it never got done.

Indeed, a hint would have helped I'm sure.

But also, it's weird DELETE allows you to delete all rows.
Yet prevents you from deleting just one, i.e. a subset.
I get it, a WHERE needs to read, so needs SELECT.
Still, it obviously tripped me up here. And it's my bad.

---

From: Álvaro Herrera <[email-masked]>
Date: Thu, 31 Jul 2025 18:18:37 +0200

> 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 that
you aren't able to use DELETE RETURNING either, unless you have SELECT
privs.

> I get it, a WHERE needs to read, so needs SELECT.

Right.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"El destino baraja y nosotros jugamos" (A. Schopenhauer)