Thread: BUG #1874: Non-Execute Privileges enforced on grant

The following bug has been logged online:

Bug reference:      1874
Logged by:          Mark Diener
Email address:      md@realmwireless.com
PostgreSQL version: 8.03
Operating system:   linux-i686
Description:        Non-Execute Privileges enforced on grant
Details:

It seems the EXECUTE privilege is not the only privilege that is being
checked during the execution of a PL/psql procedure language/function.

Only a superuser can execute non-trusted languages like python thus making
the python language unusable for average user.  Only for superusers.  What
happens when you want the python stored procedures to implement a layer of
security for standard users?

Then the pl/SQL language enforces SELECT/UPDATE/INSERT privileges on tables.
 It would appear intuitive that only the EXECUTE privilege should be
evaluated when a stored procedure executes.  By default, all superuser and
owner privileges should be allowed except for the EXECUTE privilege.

What happens when you want the pg/SQL stored procedures to implement a layer
of security for standard users and you don't want general users to have
select/update/insert privilege?  It is not an option to skip the select SQL
statement within stored procedures.

Mark Diener wrote:
> It seems the EXECUTE privilege is not the only privilege that is
> being checked during the execution of a PL/psql procedure
> language/function.

The EXECUTE privilege is checked before the function is executed.
During the execution of a function, you still need for each operation
the privileges that are ordinarily required for that operation.  If you
want to have the function executed with the effective privileges of its
owner, you can specify the clause SECURITY DEFINER when creating the
function.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/