EXECUTE privilege v17

An SPL program (function, procedure, or package) can begin execution only if any of the following are true:

• The current user is a superuser.
• The current user was granted EXECUTE privilege on the SPL program.
• The current user inherits EXECUTE privilege on the SPL program by virtue of being a member of a group that has this privilege.
• EXECUTE privilege was granted to the PUBLIC group.

Whenever an SPL program is created in EDB Postgres Advanced Server, EXECUTE privilege is granted to the PUBLIC group by default. Therefore, any user can immediately execute the program.

You can remove this default privilege by using the REVOKE EXECUTE command. For example:

REVOKE EXECUTE ON PROCEDURE list_emp FROM PUBLIC;

You can then grant explicit EXECUTE privilege on the program to individual users or groups.

GRANT EXECUTE ON PROCEDURE list_emp TO john;

Now, user john can execute the list_emp program. Other users who don't meet any of the required conditions can't.

Once a program begins to execute, the next aspect of security is the privilege checks that occur if the program attempts to perform an action on any database object including:

• Reading or modifying table or view data
• Creating, modifying, or deleting a database object such as a table, view, index, or sequence
• Obtaining the current or next value from a sequence
• Calling another program (function, procedure, or package)

Each such action can be protected by privileges on the database object either allowed or disallowed for the user.

It's possible for a database to have more than one object of the same type with the same name, but each such object belongs to a different schema in the database. If this is the case, which object is being referenced by an SPL program? For more information, see Database object name resolution.