On Wed, Jun 26, 2024 at 12:11 PM <walther@xxxxxxxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxx> 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
