SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function

On 7/30/25 04:37, Dominique Devienne wrote:

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.

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?

Thanks, --DD

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Jul 30, 2025 at 5:23 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 7/30/25 04:37, Dominique Devienne wrote:

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

What is the ROLE that defined the function?

A 3rd role. But does it matter? Given that this is in SECURITY INVOKER 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?

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.

On 7/30/25 08:47, Dominique Devienne wrote:

On Wed, Jul 30, 2025 at 5:23 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 7/30/25 04:37, Dominique Devienne wrote:

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

What is the ROLE that defined the function?

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

My mistake, a BC(Before Coffee) issue.

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.

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

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/30/25 09:21, Adrian Klaver wrote:

On 7/30/25 08:47, Dominique Devienne wrote:

On Wed, Jul 30, 2025 at 5:23 PM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 7/30/25 04:37, Dominique Devienne wrote:

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

What is the ROLE that defined the function?

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

My mistake, a BC(Before Coffee) issue.

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.

Could this be a search_path and/or naming issue, where the table
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.

--
Adrian Klaver
adrian.klaver@aklaver.com

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?

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

On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
<guillaume.lelarge@dalibo.com> wrote:

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:
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.

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

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

Dominique Devienne <ddevienne@gmail.com> writes:

On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
<guillaume.lelarge@dalibo.com> 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 "WHERE ...", it would
work without the SELECT privilege.

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

On 7/31/25 04:37, Dominique Devienne wrote:

On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
<guillaume.lelarge@dalibo.com> wrote:

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:
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.

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

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

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."

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thursday, July 31, 2025, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 7/31/25 04:37, Dominique Devienne wrote:

On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
<guillaume.lelarge@dalibo.com> wrote:

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:
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.

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

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

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 incorrect, just insufficient since select is not a DML action.

David J.

On 7/31/25 08:06, David G. Johnston wrote:

On Thursday, July 31, 2025, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> 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 incorrect, just insufficient since select is not a DML action.

1) Seems to be some difference on that:

https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

13 Data manipulation

13.5 <select statement: single row>

Function

Retrieve values from a specified row of a table.

2) What if you do SELECT some_data_mod_fnc()?

3) In the case at hand there was an implied SELECT as part of the DELETE.

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Jul 31, 2025 at 4:13 PM Adrian Klaver <adrian.klaver@aklaver.com> 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 Language.
I did grant INSERT, UPDATE, DELETE.

As opposed to DQL, Data Query Language.
And yes, I failed to grant SELECT.

Normally SELECT comes from yet another role.
But not in this specific case.

On Thu, Jul 31, 2025 at 4:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dominique Devienne <ddevienne@gmail.com> writes:

On Thu, Jul 31, 2025 at 11:35 AM Guillaume Lelarge
<guillaume.lelarge@dalibo.com> 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 "WHERE ...", it would
work without the SELECT privilege.

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.

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.

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