Re: Controlling changes in plpgsql variable resolution

2009/10/21 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Making this GUC suset would make it far less useful to users trying to
>> gradually upgrade their infrastructures, and make it more likely that
>> many/most of our users would just set it to backwards-compatible in
>> their postgresql.conf and not fix anything.  In fact, I'd go so far as
>> to say that if it's suset, we might as well make it sighup because
>> postgresql.conf is the *only* place anyone will touch it.
>
> No, they might reasonably also set it via ALTER DATABASE or ALTER USER.
>
>> In a secure database setup, none of the functions belong to the
>> superuser.  Yet an suset would mean that the function owner couldn't set
>> the parameter for their own functions, which is the most obvious place
>> to set it.
>
> That's what the #option alternative is for.  Yes, it's a bit ugly, but
> it's perfectly functional, and secure too.
>
>> Tom has proposed some kind of odd special "options" syntax to get around
>> this, but I think that's unnecessary.  So far on this thread, I haven't
>> seen anyone engineer an actual function exploit by using this setting;
>
> It would take a coincidence of names to make anything happen, so the
> details would depend a lot on the exact contents of the function you
> were hoping to break.  But since you insist:
>
>        create function foo(id int) ...
>        ...
>        select * into rec from tab where tab.id = id;
>        if found then do-something else do-something-else end if;
>
> Under old-style semantics this will do what the programmer thought.
> Under Oracle semantics it will return the first table row.  If
> do-something is security critical then this is enough to call it
> an exploit.  The reverse direction (code meant for Oracle behavior
> breaks under old-style) is not difficult to cons up either; I think
> you can find some examples in pgsql-bugs archives from people trying
> to port Oracle code to PG.
>
> Given that people are very prone to intentionally naming things as above
> (for a particularly egregious example try
> http://archives.postgresql.org/pgsql-bugs/2009-10/msg00054.php)
> I think it's entirely foolish to imagine this isn't a real hazard.
> If name collisions were improbable we'd not have so many complaints
> about the current behavior in our archives, and probably wouldn't be
> thinking about changing the behavior at all.
>
> As for the analogy to search_path, I think if we were doing that over
> knowing what we know today, we'd have locked it down a lot more from the
> beginning.  We might yet decide to lock it down more.  It's not a good
> example to cite when arguing that this setting doesn't need any
> protection.
>
>                        regards, tom lane
>

Although I am one who like to see Oracle behave in Pg I have to say,
so Oracle's behave is a significant risk. PL/pgSQL is more dynamic
then PL/SQL and it should be very strange, when somebody add new table
and for some functions will be broken. What I know, PL/SQL programmers
knows this problem and protects self.

In light of this I see more important default to raise error. Oracle
mode is good for stable environments, for migration from Oracle, but
for others is similar risk like current plpgsql, only with different
risks.

Personally I prefer #option. This option isn't possible to change
inside run of function, and this is exactly what we need. It's local
only plpgsql problem - it isn't related to postgres, it is related
only to plpgsql.

regards
Pavel Stehule