Thread: A function privilege problem
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 xxx”，Is this right？
How i find which db user have the privilege to a function? and the \dp+ or \df+ is not work.
=?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
> 在 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 executeon 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 "Executepermission 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 > 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> 写道：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.