Postgres Pro
Downloads
Home   >   mailing lists

Thread: Need help understanding has_function_privilege

Need help understanding has_function_privilege

From
Cosimo Simeone
Date:
Hi!
I'd need help understanding what i am doing wrong...

Where am I:
=# \c
psql (15.8 (Homebrew), server 14.15 (Debian 14.15-1.pgdg120+1))
You are now connected to database "postgres" as user "postgres".

Init:
=# create role my_user;
=# create schema my_schema;
=# create function my_schema.my_func(p1 text) returns integer as $$select 1::integer;$$ language sql;

It works...
=#  select my_schema.my_func('x');
 my_func
---------
       1

Now, the controversial part:
=# SELECT has_function_privilege('my_user', 'my_schema.my_func(text)', 'execute');
 has_function_privilege
------------------------
 t

true?
Well... Ok, "whatever"... I revoke it:
=# revoke execute on function my_schema.my_func(text) from my_user;
REVOKE

But still:
=# select has_function_privilege('my_user', 'my_schema.my_func(text)', 'execute');
 has_function_privilege
------------------------
 t

What am I doing wrong? :-)

Thanks for help!

Re: Need help understanding has_function_privilege

From
Joe Conway
Date:
On 3/19/25 04:24, Cosimo Simeone wrote:
> Hi!
> I'd need help understanding what i am doing wrong...
> 
> Where am I:
> =# \c
> psql (15.8 (Homebrew), server 14.15 (Debian 14.15-1.pgdg120+1))
> You are now connected to database "postgres" as user "postgres".
> 
> Init:
> =# create role my_user;
> =# create schema my_schema;
> =# create function my_schema.my_func(p1 text) returns integer as $ 
> $select 1::integer;$$ language sql;

See:


https://www.postgresql.org/docs/current/sql-createfunction.html#:~:text=execute%20privilege%20is%20granted%20to%20PUBLIC

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Need help understanding has_function_privilege

From
"David G. Johnston"
Date:
On Wednesday, March 19, 2025, Cosimo Simeone <cosimo.simeone@gmail.com> wrote:

true?
Well... Ok, "whatever"... I revoke it:
=# revoke execute on function my_schema.my_func(text) from my_user;
REVOKE

Roles can inherit privileges.  my_user is inheriting its execute privilege from PUBLIC.  You have to revoke a granted privilege.

David J.
 

Re: Need help understanding has_function_privilege

From
Cosimo Simeone
Date:
Hi, and thanks (both of you!)
Shouldn't the
 create role my_user NOINHERIT;
avoid this? And since not, why? :-)



On Thu, 20 Mar 2025 at 15:07, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, March 19, 2025, Cosimo Simeone <cosimo.simeone@gmail.com> wrote:

true?
Well... Ok, "whatever"... I revoke it:
=# revoke execute on function my_schema.my_func(text) from my_user;
REVOKE

Roles can inherit privileges.  my_user is inheriting its execute privilege from PUBLIC.  You have to revoke a granted privilege.

David J.
 

Re: Need help understanding has_function_privilege

From
"David G. Johnston"
Date:
On Friday, March 21, 2025, Cosimo Simeone <cosimo.simeone@gmail.com> wrote:
Hi, and thanks (both of you!)
Shouldn't the
 create role my_user NOINHERIT;
avoid this? And since not, why? :-)


We might need to improve documentation surrounding the public pseudo-role a bit.  Since it’s not a true group role I suspect inherit/noinherit doesn’t apply. (You also cannot SET to it, nor admin it - not tested.) Losing the execute privilege on every built-in function would be way too annoying.

David J.