Thread: [MASSMAIL] Clarification on View Privileges and Operator Execution in PostgreSQL
[MASSMAIL] Clarification on View Privileges and Operator Execution in PostgreSQL
From
Ayush Vatsa
Date:
Hi PostgreSQL community,
I am recently studying about operators and views and I had doubts in two small things
1. I know if a view (security definer) is accessing a table then it is getting accessed by view owners privileges
but what about the view which contains inbuilt operators or inbuilt functions with whose privileges those will be executed. Eg.
SET ROLE postgres;
CREATE TABLE x(id INT);
CREATE VIEW v AS SELECT * FROM x WHERE id > 100;
CREATE ROLE alex;
GRANT SELECT ON v TO alex;
SET ROLE alex;
SELECT * FROM v;
Now table x will be accessed (SELECT * FROM x) with "postgres" privileges but who will execute the
underlying function inside the ( > ) operator ? Is it postgres or alex?
2. What if I used a user defined operator in the above example, then with whose privileges that operator will be executed?
Thanks
Ayush Vatsa
SDE AWS
Re: Clarification on View Privileges and Operator Execution in PostgreSQL
From
"David G. Johnston"
Date:
On Sun, Apr 7, 2024 at 9:10 AM Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:
but what about the view which contains inbuilt operators or inbuilt functions with whose privileges those will be executed. Eg.
From the create view documentation:
Functions called in the view are treated the same as if they had been called directly from the query using the view. Therefore, the user of a view must have permissions to call all functions used by the view. Functions in the view are executed with the privileges of the user executing the query or the function owner, depending on whether the functions are defined as SECURITY INVOKER or SECURITY DEFINER.
> Functions in the view are executed with the privileges of the user executing the query or the function owner
So does that imply to the function associated with the operators (both builtin and user defined) too.
Basically wanted to know about this -
> Now table x will be accessed (SELECT * FROM x) with "postgres" privileges but who will execute the
> underlying function inside the ( > ) operator ? Is it postgres or alex?
On Sun, 7 Apr 2024 at 21:56, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sun, Apr 7, 2024 at 9:10 AM Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:but what about the view which contains inbuilt operators or inbuilt functions with whose privileges those will be executed. Eg.From the create view documentation:Functions called in the view are treated the same as if they had been called directly from the query using the view. Therefore, the user of a view must have permissions to call all functions used by the view. Functions in the view are executed with the privileges of the user executing the query or the function owner, depending on whether the functions are defined as SECURITY INVOKER or SECURITY DEFINER.
Re: Clarification on View Privileges and Operator Execution in PostgreSQL
From
"David G. Johnston"
Date:
On Sun, Apr 7, 2024 at 9:32 AM Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:
> Functions in the view are executed with the privileges of the user executing the query or the function ownerSo does that imply to the function associated with the operators (both builtin and user defined) too.Basically wanted to know about this -> Now table x will be accessed (SELECT * FROM x) with "postgres" privileges but who will execute the> underlying function inside the ( > ) operator ? Is it postgres or alex?
An operator is a function invocation with special syntax. So I expect that sentence to apply.
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.
David J.
Re: Clarification on View Privileges and Operator Execution in PostgreSQL
From
"David G. Johnston"
Date:
On Sun, Apr 7, 2024 at 9:32 AM Ayush Vatsa <ayushvatsa1810@gmail.com> 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.
> 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
On Sun, 7 Apr 2024 at 22:31, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sun, Apr 7, 2024 at 9:32 AM Ayush Vatsa <ayushvatsa1810@gmail.com> 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.
Re: Clarification on View Privileges and Operator Execution in PostgreSQL
From
"David G. Johnston"
Date:
On Sun, Apr 7, 2024 at 11:02 AM Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:
> 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 operatorBut a small doubt arises here I have to revoke the execution of the function using the commandREVOKE ALL ON ALL FUNCTIONS IN SCHEMA public from public;but when I triedREVOKE EXECUTE ON FUNCTION text_equals(text,text) FROM alex;orREVOKE 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?
Especially the part regarding default privileges. The PUBLIC pseudo-role is granted execute on functions by default. You are probably trying to revoke a privilege from alex that was never granted to alex directly.
David J.
Understood.
Thanks David it was a nice conversation and clarification from you
Regards
Ayush Vatsa
On Sun, 7 Apr 2024 at 23:45, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sun, Apr 7, 2024 at 11:02 AM Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:> 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 operatorBut a small doubt arises here I have to revoke the execution of the function using the commandREVOKE ALL ON ALL FUNCTIONS IN SCHEMA public from public;but when I triedREVOKE EXECUTE ON FUNCTION text_equals(text,text) FROM alex;orREVOKE 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?Especially the part regarding default privileges. The PUBLIC pseudo-role is granted execute on functions by default. You are probably trying to revoke a privilege from alex that was never granted to alex directly.David J.