On Mon, Jul 10, 2006 at 08:24:08PM +0200, Martijn van Oosterhout wrote: > On Mon, Jul 10, 2006 at 01:42:27PM -0400, Phil Frost wrote: > > I think that misses the point. One can easily find objects in a schema > > without usage by examining the system catalogs. The point is that there > > are ways to access objects without going through the schema usage check, > > and also that the check is made only once at the time a name is resolved > > to an oid, which may then be cached in a prepared statement, stored > > procedure, lastval, or the like. I would suggest something more like > > this: > > Can you SELECT/UPDATE/DELETE from a table knowing only its oid? I'd > like to see that trick. lastval() is an odd case, given the user > doesn't actually supply the oid.
I haven't found a way to do this yet, but I wouldn't be suprised if there is a clever way, especially considering C extensions that might come from contrib or other sources. It seems like there is a good deal of potential for non-malicious developers to open unknowingly serious security holes. I think lastval is a great example of this potential; fortunately sequence values are rarely compromising. Imagine the consequences of a function which returns the last inserted row in a similar manner. > > In applications where security is very important, it may be wise to > > assure that no users have undesired privileges on objects within a > > schema, and not to rely solely on the schema usage privilege. > > Indeed, never give priveledges unless you're sure you want people to > have them. The way I ran into this problem is moving a table that was previously in a public schema into a private schema. Since not having usage on the schema is enough to prevent access most of the time, the change passed testing. Later I noticed the odd grant on some objects in the private schema, and poking around I found ways to access them dispite not having usage on the schema. The "check just once at plan time, and only when referencing an object by name" semantics of the schema usage check which differ from the "check always" semantics of all the other ACL checks I found to be suprising. Since these fine details are not obvious, expected, or documented, there seems to be a good deal of potential for administrative error. Hopefully at least the docs will be updated so people can at least be aware of the issue. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
