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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
