Subject: Re: Clarification on View Privileges and Operator Execution in PostgreSQL

From: "David G. Johnston" <[email-masked]>
Date: Sun, 7 Apr 2024 10:01:18 -0700

On Sun, Apr 7, 2024 at 9:32 AM Ayush Vatsa <[email-masked]> wrote:

>  but who will execute the
> > underlying function inside the ( > ) operator ? Is it postgres or alex?
>
>>
I'm reasonably confident that all the built-in functions are security
invoker.  Not that a pure function like greater-than really cares.

David J.

---

From: Ayush Vatsa <[email-masked]>
Date: Sun, 7 Apr 2024 23:32:36 +0530

> If you want to confirm what the documentation says create a custom
operator/function that alex is not permitted to execute and have them query
a view defined by postgres that uses that function.
Thanks for the suggestion, it helped and I found out alex could not execute
the view as it didn't have privileges for the function associated with
operator

But a small doubt arises here I have to revoke the execution of the
function using the command
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public from public;
but when I tried
REVOKE EXECUTE ON FUNCTION text_equals(text,text) FROM alex;
or
REVOKE ALL ON FUNCTION text_equals(text,text) FROM alex;
It didn't work i.e alex can still execute text_equals function. Why is it
so?

Thanks
Ayush Vatsa
SDE AWS