Query regarding functions of postgres
Hi PostgreSQL Community, Recently I was reading about functions Immutability and security definer but got confused Whether the below two functions can be marked immutable or not 1. If a function has constant Raise notice inside it. Eg. CREATE OR REPLACE FUNCTION text_equals(text, text) RETURNS boolean AS $$BEGIN RAISE NOTICE 'Comparing two texts'; RETURN $1 = $2;END; $$ LANGUAGE plpgsql; 2. If a function has Raise notice but extracting current user inside notice, although its output purely depends on its input arguments eg. CREATE OR REPLACE FUNCTION text_equals(text, text) RETURNS boolean AS $$BEGIN RAISE NOTICE 'Current user: %', current_user; RETURN $1 = $2;END; $$ LANGUAGE plpgsql; On security definer part I am confused with the below example set role postgres; CREATE OR REPLACE FUNCTION outer_function() RETURNS TEXT AS $$ DECLARE user_text TEXT; BEGIN SELECT 'OuterFunction() -> Current user is ' || current_user INTO user_text; user_text := user_text || ' | ' || inner_function(); RETURN user_text; END; $$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER; create role test; create role alex; grant create on schema public to test; set role test; CREATE OR REPLACE FUNCTION inner_function() RETURNS TEXT AS $$ DECLARE current_user_text TEXT; BEGIN current_user_text := 'InnerFunction() -> Current user is ' || current_user; RETURN current_user_text; END; $$ LANGUAGE plpgsql VOLATILE SECURITY INVOKER; set role alex; select outer_function(); outer_function --- OuterFunction() -> Current user is postgres | InnerFunction() -> Current user is postgres Shouldn't it be "InnerFunction() -> Current user is alex" instead of postgres as alex called the security invoker function I tried reading docs but couldn't get any satisfactory answers, it will be helpful if someone helped me out here Thanks, Ayush Vatsa SDE AWS
Re: pg_dumpall - restoration problem- resolved
Thanks Tom, Dumped in Ubuntu 22.04_1 and restore attempted using Ubuntu 22.04.3 Editing the dump file to C.UTF8 didn't solve the problem. The default for the database was en_AU.utf8 so I should have changed the collation to that, but it was one field in one table of superseded data, so I just erased the collation from that field. Still no idea on how it came to be there. Cheers Tony On Sunday 7 April 2024 10:35:44 AM ACST Tom Lane wrote: > Tony Bazeley writes: > > I've a problem with restoring a cluster created with pg_dump_all from 14.8 > > ( pg_dumpall >pgall.out and then psql -f pgall.out postgres). > > ... > > Attempting to restore to postgresql-16 results in errors > > > > 2024-04-05 22:17:15.418 ACDT [6565] postgres@tonbaz ERROR: collation > > "pg_catalog.C.UTF-8" for encoding "UTF8" does not exist at character 366 > > > > I don't understand the class text COLLATE pg_catalog."C.UTF-8" syntax, > > but > > select * from pg_collation shows a C.UTF8 but no C.UTF-8 > > I take it you are trying to restore onto a different OS platform with > different locale naming conventions. The easiest way to deal with it > probably is to edit the dump file and change "C.UTF-8" to "C.UTF8" > everywhere. (Manually editing an 8G dump file might be no fun, but > "sed" should make short work of it.) > > regards, tom lane
Re: Query regarding functions of postgres
On Sunday, April 7, 2024, Ayush Vatsa wrote: > > Whether the below two functions can be marked immutable or not > 1. If a function has constant Raise notice inside it. Eg. > Seems legit. > > 2. If a function has Raise notice but extracting current user inside notice, > although its output purely depends on its input arguments eg. > > No select outer_function(); > > outer_function > --- > OuterFunction() -> Current user is postgres | InnerFunction() -> Current > user is postgres > > Shouldn't it be "InnerFunction() -> Current user is alex" instead of postgres > as alex called the security invoker function > > As soon as the system entered the security defined function it changed current_user to the definer of that function and won’t change back until the function returns. Which it hasn’t when inner function is invoked. David J.
Re: Query regarding functions of postgres
Ayush Vatsa writes: > Recently I was reading about functions Immutability and security definer > but got confused > Whether the below two functions can be marked immutable or not > 1. If a function has constant Raise notice inside it. Eg. > CREATE OR REPLACE FUNCTION text_equals(text, text) RETURNS boolean AS $$BEGIN > RAISE NOTICE 'Comparing two texts'; > RETURN $1 = $2;END; > $$ LANGUAGE plpgsql; > 2. If a function has Raise notice but extracting current user inside > notice, although its output purely depends on its input arguments eg. > CREATE OR REPLACE FUNCTION text_equals(text, text) RETURNS boolean AS $$BEGIN > RAISE NOTICE 'Current user: %', current_user; > RETURN $1 = $2;END; > $$ LANGUAGE plpgsql; The definition of "immutable" includes a statement that immutable functions shouldn't have side-effects, so really this comes down to whether you consider the emission of a NOTICE to be an interesting side-effect. It has no further effect on the computation, so you could probably get away with considering it insignificant if you choose to. If you do, you are likely to observe the NOTICE coming out at unexpected times --- for instance, it might appear once during the planner's constant-folding phase, even though naive interpretation of the query suggests that it should appear many times or not at all. But beyond that possible POLA violation, neither of these functions will bother Postgres any. regards, tom lane
Clarification on View Privileges and Operator Execution in PostgreSQL
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
On Sun, Apr 7, 2024 at 9:10 AM Ayush Vatsa 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
> 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 wrote: > On Sun, Apr 7, 2024 at 9:10 AM Ayush Vatsa > 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
On Sun, Apr 7, 2024 at 9:32 AM Ayush Vatsa wrote: > > 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? > >> >> 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
On Sun, Apr 7, 2024 at 9:32 AM Ayush Vatsa 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
> 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 wrote: > On Sun, Apr 7, 2024 at 9:32 AM Ayush Vatsa > 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
On Sun, Apr 7, 2024 at 11:02 AM Ayush Vatsa 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 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? > >> >> See https://www.postgresql.org/docs/current/ddl-priv.html 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.
Re: Clarification on View Privileges and Operator Execution in PostgreSQL
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 wrote: > On Sun, Apr 7, 2024 at 11:02 AM Ayush Vatsa > 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 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? >> >>> >>> > See https://www.postgresql.org/docs/current/ddl-priv.html > 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. > >
