Re: pl/pgsql enabled by default

Mike Mascari wrote:

> Neil Conway wrote:
>
>> Mike Mascari wrote:
>>
>>> People who use views to achieve row security, which is a rather
>>> common paradigm, cannot allow users to create functions with side
>>> effects.
>>
>>
>>
>> Can you elaborate? I'm not sure I follow you.
>>
>> (I'll note anyway that (1) SQL functions can have side effects:
>> CREATE FUNCTION foo() RETURNS VOID AS 'DELETE FROM ...', for example
>
>
> Wow. That's a problem IMHO. Many people use views to allow userA to
> query his salary without seeing userB's. If userA can just:
>
> 1. Create a SQL function which inserts into another table its arguments
>
> 2. Issue a query like:
>
> SELECT *
> FROM view_of_salaries_based_on_current_user
> WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary,
> employee);
>
> and that function is writable with just 'SQL', then many, many people
> have a serious security risk on their hands. Perhaps this is why
> Oracle's standard UDFs cannot perform inserts, updates, or deletes.
>
>

but the side effect function will only run (unless you set it with
security definer) with the privileges of the caller - it won't grant
visibility to things that user can't otherwise see.

In any case, you should define your security setup with the
capabilities / limitations of the db engine in mind. If there is any
security problem in your scenario, it is that you appear to have made
unwarranted assumptions about how postgres works, rather than that
postgres has a problem.

Either way, this does not illustrate how enabling plpgsql by default is
a security risk.

cheers

andrew