public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: [email protected]
Subject: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
Date: Wed, 30 Jul 2025 13:37:49 +0200
Message-ID: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com> (raw)

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

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: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com>

* 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