Re: lastval exposes information that currval does not

On Sun, Jul 09, 2006 at 02:32:24PM +0200, Martijn van Oosterhout wrote:
> On Sat, Jul 08, 2006 at 05:47:33PM -0400, Jim Nasby wrote:
> > On Jul 6, 2006, at 11:02 AM, Phil Frost wrote:
> > >I hope the above example is strong enough to elicit a comment from a
> > >qualified developer. If it is not, consider that stored procedures
> > >contain prepared statements, and many client applications cache
> > >prepared
> > >statements as well. Thus, revoking usage on a schema is about as
> > >good as
> > >nothing until all sessions have ended. It also means that any function
> > >which operates with OIDs can potentially bypass the schema usage
> > >check.
> >
> > The docs probably should elaborate that once something's been looked
> > up you no longer need permissions on the schema it resides in.
>
> I'm not sure this is really unexpected behaviour. On UNIX it is clearly
> defined that file permissions are checked only on open. Once you've
> opened it, changing permissions on the file won't affect you. If
> someone passes you a read/write descriptor to a file, you can
> read/write it even if you didn't have permissions to open the
> file/socket/whatever yourself.
>
> I'm not sure it makes sense to be able to revoke someone's permissions
> on an object they've already accessed. From a transactional point of
> view, the revoke should at the very least not affect transactions
> started prior to the revokation. Some things are shared across an
> entire session, and the rule extends to them. Is this a bug? Maybe, but
> it is debatable.

On UNIX it is also clearly defined that if one does not have execute
permissions on a directory, one can not open files within it by *any*
*means*. There are no procedures that bypass this by taking an inode
number directly.

It is generally understood in the UNIX commuinity that adding a function
in a new version that grants capabilities that were previously
unavailable is an obvious security bug.

If it doesn't make sense to be able to revoke permissions on objects
already accessed, why is this the behaviour of everything except the
schema usage check? Does your definition of "already accessed" include
"accessed in a 'security definer' procedure intended to prevent the
caller from accessing an object directly"?

Given that there are already several ways to bypass the check for usage
on a schema, and the developers seem to not be bothered at all by adding
more, of what security use is the schema usage privilege?

Is drawing a weak analogy to another system with a significantly
different security model a good way to validate security for PostgreSQL?

Is it a good idea to have a privilege with surprising semantics?

I'm sorry to keep arguing about this issue, but I am quite disturbed
with the lack of concern over security in the developer commuinity.
Perhaps the mindset here is that the SQL server will always be behind a
firewall and accessed through a web application. I'm here to say this is
not the case. Firewalls are comprimised, and not all applications are
web applications. I'd really not like to have to write a middleware
server just because the security in PostgreSQL is insufficient.

At a minimum, I'd like to see the documentation updated to document the
weakness of the usage privilege, and how to prevent these exploits. I'll
write the patch if there is agreement. Ideally, I'd like to see the
usage privilege changed to something more consistent and useful.