"Tom Lane" <[EMAIL PROTECTED]> writes: > Josh Berkus <[EMAIL PROTECTED]> 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! ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly