Re: A function privilege problem

=?gb2312?B?uN8g1MbB+g==?= <gaoyunlong(at)biss(dot)com> writes:
> Hi ,my postgresql is 11.1 and find a problem about the execute privilege
> Now I have a new db user and a UDF. The function¡¯s body is update a table

> When I run grant update on table xxx to the new user, find that i can execute the function and don¡¯t report "Execute permission for functions<https://www.postgresql.org/message-id/4C2BBAEA.6040805%40gmail.com> xxx¡±£¬Is this right£¿

This is not a bug. Per the GRANT manual page [1]:

PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
table columns, sequences, foreign data wrappers, foreign servers,
large objects, schemas, or tablespaces. For other types of objects,
the default privileges granted to PUBLIC are as follows: CONNECT and
TEMPORARY (create temporary tables) privileges for databases;
EXECUTE privilege for functions and procedures; and USAGE privilege
for languages and data types (including domains). The object owner
can, of course, REVOKE both default and expressly granted
privileges. (For maximum security, issue the REVOKE in the same
transaction that creates the object; then there is no window in which
another user can use the object.) Also, these initial default
privilege settings can be changed using the ALTER DEFAULT PRIVILEGES
command.

So the new user is making use of the default grant of EXECUTE to PUBLIC.
If you don't want that, revoke execute privilege on that function from
PUBLIC and then grant it to just the users who should be allowed to call
the function.

regards, tom lane

[1] https://www.postgresql.org/docs/current/sql-grant.html