Re: A function privilege problem

=?gb2312?B?uN8g1MbB+g==?= <gaoyunlong@biss.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
permissionfor 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