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 permission for functions</messages/by-id/4C2BBAEA.6040805@gmail.com&gt; xxx¡±£¬Is this right£¿

This is not a bug. Per the GRANT manual page [1]https://www.postgresql.org/docs/current/sql-grant.html:

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

在 2019年7月24日，上午10:09，yunlong <gaoyunlong@biss.com> 写道：

What’s PUBLIC mean ? public schema ?
My function is not in public schema, and I still can execute the function with the new db user after run revoke execute on function xxx from xxx

在 2019年7月24日，上午12:50，Tom Lane <tgl@sss.pgh.pa.us> 写道：

=?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 permission for functions</messages/by-id/4C2BBAEA.6040805@gmail.com&gt; 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

Make a copy for pgsql-bugs@lists.postgresql.org

On Wednesday, July 24, 2019, 高 云龙 <gaoyunlong@biss.com> wrote:

在 2019年7月24日，上午10:09，yunlong <gaoyunlong@biss.com> 写道：

What’s PUBLIC mean ? public schema ?
My function is not in public schema, and I still can execute the

function with the new db user after run revoke execute on function xxx from
xxx

PUBLIC here is referring to the system group that all roles are implicitly
and permanently members of.

You need to replace xxx with actual values and a self-contained script if
you want others to understand what is being questioned.

David J.

Thx for you , get it

在 2019年7月25日，上午11:08，David G. Johnston <david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>> 写道：

On Wednesday, July 24, 2019, 高 云龙 <gaoyunlong@biss.com<mailto:gaoyunlong@biss.com>> wrote:

在 2019年7月24日，上午10:09，yunlong <gaoyunlong@biss.com<mailto:gaoyunlong@biss.com>> 写道：

What’s PUBLIC mean ? public schema ?
My function is not in public schema, and I still can execute the function with the new db user after run revoke execute on function xxx from xxx

PUBLIC here is referring to the system group that all roles are implicitly and permanently members of.

You need to replace xxx with actual values and a self-contained script if you want others to understand what is being questioned.

David J.