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
