Re: Controlling changes in plpgsql variable resolution

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