Thread: current_role of caller of a DEFINER function
Hi. I was led to believe (by an hallucination...) that I could know the current_role of the caller of a DEFINER function, but after actual experimentation, turns out it shows the OWNER of the function, and not the current_role of the caller. I foolishly thought curent_role != current_user inside the DEFINER function, but reading back the doc, it's clear current_role = current_user = user, thus that was wishful thinking. Only session_user is representative of the caller, and reliable (modulo SUPERUSER and SET AUTHORIZATION, but that's a different story and kinda normal) So I have two questions: 1) Is there any way to know the current_role of the caller of a DEFINER function. I fear the answer is no, but better be sure from experts here. 2) Why isn't there a way to obtain the above? What harm would it be? Obviously for #2, distinguishing current_role from current_user inside DEFINER is a no-go, for backward compatibility. But could a new variable be invented for that? What obvious technical reason I'm missing would make that harmful or difficult? As to the use-case, now that I have a queue mechanism for tasks to be delegated to services, I need to make sure the poster of the task has the right privileges, so I wanted to capture session_user and current_role of the "poster", and the service would use that info, reliably captured (inside the DEFINER function to post a task), using ROLEs and GRANTs and other app-specific permission data. LOGIN users have different persona in the system, so the current_role matters, to determine whether the connection posting the task to be processed asynchronously by a service is allowed or not. I'd think I'm not the only one that would need something like this, no? Thanks, --DD
On Wed, 2024-06-26 at 10:35 +0200, Dominique Devienne wrote: > So I have two questions: > 1) Is there any way to know the current_role of the caller of a > DEFINER function. I fear the answer is no, but better be sure from > experts here. > 2) Why isn't there a way to obtain the above? What harm would it be? Just te be certain, let me ask a question back: If a SECURITY DEFINER function calls another SECURITY DEFINER function, which role would you like to get: 1. the invoker that called the first function 2. the owner of the first function (which is the user that called the second function) Yours, Laurenz Albe
On 2024-06-26 10:35 +0200, Dominique Devienne wrote: > Hi. I was led to believe (by an hallucination...) that I could know > the current_role of the caller of a DEFINER function, but after actual > experimentation, turns out it shows the OWNER of the function, and not > the current_role of the caller. > > I foolishly thought curent_role != current_user inside the DEFINER > function, but reading back the doc, it's clear current_role = > current_user = user, thus that was wishful thinking. Only session_user > is representative of the caller, and reliable (modulo SUPERUSER and > SET AUTHORIZATION, but that's a different story and kinda normal) > > So I have two questions: > 1) Is there any way to know the current_role of the caller of a > DEFINER function. I fear the answer is no, but better be sure from > experts here. Have you tried capturing current_user with a function parameter and default value? https://www.postgresql.org/message-id/f82f70fd-665f-6384-5e8a-987ab9e640d3%40technowledgy.de -- Erik
Dominique Devienne: > 1) Is there any way to know the current_role of the caller of a > DEFINER function. I fear the answer is no, but better be sure from > experts here. You can do something like this: CREATE DOMAIN current_user_only AS text CONSTRAINT current_user_only CHECK (VALUE = CURRENT_USER); CREATE FUNCTION func( calling_user current_user_only DEFAULT CURRENT_USER ) ... SECURITY DEFINER; The default value will be evaluated in the context of the calling user, the constraint forces it to never be set explicitly to something else. Thus you can use calling_user inside your function. Best, Wolfgang
On Wed, Jun 26, 2024 at 12:11 PM <walther@technowledgy.de> wrote: > Dominique Devienne: > > 1) Is there any way to know the current_role of the caller of a > > DEFINER function. I fear the answer is no, but better be sure from > > experts here. > > You can do something like this: > > CREATE DOMAIN current_user_only AS text > CONSTRAINT current_user_only CHECK (VALUE = CURRENT_USER); > > CREATE FUNCTION func( > calling_user current_user_only DEFAULT CURRENT_USER > ) ... SECURITY DEFINER; > > The default value will be evaluated in the context of the calling user, > the constraint forces it to never be set explicitly to something else. Fantastic Wolfgang. Thanks! (again...) I just tested it in my unit test (unlike last time, see below...), and it works perfectly. I tried w/o param, to benefit from the default. OK. I tried w/ an explicit param, that satisfies the constraint. OK. I tired w/ an explicit param, that does NOT satisfy the constraint (i.e. simulating a hack), and it is KO as expected: ERROR: value for domain captured_current_role violates check constraint "equals_current_role" On Wed, Jun 26, 2024 at 12:06 PM Erik Wienhold <ewie@ewie.name> wrote: > Have you tried capturing current_user with a function parameter and default value? > https://www.postgresql.org/message-id/f82f70fd-665f-6384-5e8a-987ab9e640d3%40technowledgy.de I'm embarrassed to admit that you posted a link to a response to one of my own threads/questions... Which Wolfgang in fact had already answered then even. I had completely forgotten about it, sorry. On Wed, Jun 26, 2024 at 11:08 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > On Wed, 2024-06-26 at 10:35 +0200, Dominique Devienne wrote: > > So I have two questions: > > 1) Is there any way to know the current_role of the caller of a > > DEFINER function. I fear the answer is no, but better be sure from > > experts here. > > Just to be certain, let me ask a question back: > > If a SECURITY DEFINER function calls another SECURITY DEFINER function, > which role would you like to get: > 1. the invoker that called the 1st function > 2. the owner of the 1st function (which is the user that called the 2nd function) Honestly Laurenz, I didn't think about it, and it does not matter too much in my case. Because what matters to me is the initial entry-point, from caller to DEFINER function, to accurately capture the role, and then I can pass it on explicitly myself if needed. This is for more knowledgeable people to decide on. I still think such a new variable would be useful, and simpler than Wolfgang's DOMAIN solution, but I do have a solution that works right now, and not in a future version, assuming such a change ever occurs, so this is already great for me. Thank you all, --DD
On Wednesday, June 26, 2024, Dominique Devienne <ddevienne@gmail.com> wrote:
Only session_user
is representative of the caller, and reliable (modulo SUPERUSER and
SET AUTHORIZATION, but that's a different story and kinda normal)
Why can you not use session_user then?
David J.
On Wed, 26 Jun 2024 at 08:42, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, June 26, 2024, Dominique Devienne <ddevienne@gmail.com> wrote:Only session_user
is representative of the caller, and reliable (modulo SUPERUSER and
SET AUTHORIZATION, but that's a different story and kinda normal)Why can you not use session_user then?
Speaking only for myself, if I am writing a security definer and I go to check the calling role, I want to know the role which was used in the privilege check as to whether the function would even be permitted to be called. What I would be looking for is to behave differently depending on who called me. The original role which connected to the database is totally irrelevant, and could even be a security problem: if superuser does a set role, I shouldn't then be doing security checks which report back that the current role is superuser.
Imagine code like this:
select objects from table where owner = [calling role] …
I think this ties into the related discussions on questions like what search_path should be in effect during trigger execution and during REFRESH MATERIALIZED VIEW and other maintenance commands. It also relates into the question of what role executes triggers and performs calculations during REFRESH MATERIALIZED VIEW and other maintenance commands.
Essentially the current behaviour is quirky and built up over time by a series of individual decisions, and does not appear to have any systematic theory of operation which would answer all these questions all at once.
On Wed, Jun 26, 2024 at 2:42 PM David G. Johnston <david.g.johnston@gmail.com> wrote: > On Wednesday, June 26, 2024, Dominique Devienne <ddevienne@gmail.com> wrote: >> Only session_user >> is representative of the caller, and reliable (modulo SUPERUSER and >> SET AUTHORIZATION, but that's a different story and kinda normal) > > Why can you not use session_user then? Hi. As I already wrote above, the current_role matters in our security model. The LOGIN user (i.e. session_user) is used only for authentication to the DB and to connect. All other security concerns are on other app-maintained (NOLOGIN) roles, used for authorization. --DD
On Wed, Jun 26, 2024 at 2:25 PM Dominique Devienne <ddevienne@gmail.com> wrote: > On Wed, Jun 26, 2024 at 11:08 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > On Wed, 2024-06-26 at 10:35 +0200, Dominique Devienne wrote: > > > So I have two questions: > > > 1) Is there any way to know the current_role of the caller of a > > > DEFINER function. I fear the answer is no, but better be sure from > > > experts here. > > > > Just to be certain, let me ask a question back: > > > > If a SECURITY DEFINER function calls another SECURITY DEFINER function, > > which role would you like to get: > > 1. the invoker that called the 1st function > > 2. the owner of the 1st function (which is the user that called the 2nd function) > > Honestly Laurenz, I didn't think about it, and it does not matter too > much in my case. > Because what matters to me is the initial entry-point, from caller to > DEFINER function, > to accurately capture the role, and then I can pass it on explicitly > myself if needed. > This is for more knowledgeable people to decide on. Hi. Resurrecting this thread, 1 year later, to follow up on Laurenz question, and ask another of my own. It just so happens that I now have two layers of SECURITY DEFINER functions. The 1st (inner) layer is to encapsulate sensitive DDLs (and audit them), is one "DBA" schema that belong to a "DBA" role, that knows (almost) nothing of the application and is only concerned with pure-PostgreSQL stuff, and the 2nd (outer) layer more application specific, in another "App Admin" schema (that belongs to different ROLE yet). I'm using the DOMAIN type discussed here, on the outer layer, to capture the CURRENT_ROLE, i.e. the caller of that outer SECURITY DEFINER FUNCTION. This works great. But I also want to pass it down to the lower/inner layer, for auditing/logging purposes only. Initially I tried using the same DOMAIN type/value, but then I'd violate the CHECK constraints. The initial value was captured before entering the outer SECURITY DEFINER function, and when copying the value to pass it to the inner SECURITY DEFINER function, that value no longer matches the "new" CURRENT_ROLE from within the SECURITY DEFINER context. The work-around is to take it as text (or name) instead of the DOMAIN type, but that feels unsatisfactory, since then the caller could pass an arbitrary value, not something that comes from my DOMAIN type, which enforces the fact its value is the CURRENT_ROLE. So my question is whether my inner-procs can take another type, that can only be created from my DOMAIN type? I.e. I'd want to enforce the value I'm getting comes from my DOMAIN type, and only that type. Is that possible? Thanks, --DD