"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> Josh Berkus <josh@agliodbs.com> writes:
>> Security Definer has ramifications in PostgreSQL which I don't think it
>> does in Oracle. Particularly, see:
>> http://www.postgresql.org/docs/techdocs.77
>
> BTW, that article needs to be updated to show the (much easier) way to
> do it as of 8.3.
>
> I concur that "make all your functions security definer by default" is
> unlikely to make a system more secure overall --- it'll just move the
> problems around. Especially if it's applied blindly by someone who
> stopped reading at that point.
I think the reason Oracle DBAs are accustomed to using security definer for
everything is that it has some further effects aside from selecting the
privileges to use.
Remember that in Oracle the current "role" also controls what we call the
"search_path". So selecting security definer is effectively selecting lexical
scoping over dynamic scoping. It nails down all the references in the package
or function at compile time.
That does have more robust security implications. It's also supposed to
perform better. And experience shows lexical scoping makes it easier to build
large complex systems without getting bogged down in lots of
action-at-a-distance.
In Postgres the performance consequence is reversed. We have a performance
*hit* for security definer. And the pl interpreters don't behave any
differently as far as when they do their lookups.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!