Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 5F7282E40C8 for ; Thu, 13 Dec 2007 01:56:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 41112-01-3 for ; Thu, 13 Dec 2007 01:56:31 -0400 (AST) Received: from mail07.syd.optusnet.com.au (mail07.syd.optusnet.com.au [211.29.132.188]) by postgresql.org (Postfix) with ESMTP id 18FAB2E40D7 for ; Thu, 13 Dec 2007 01:56:23 -0400 (AST) Received: from [127.0.0.1] (online4-new.lnk.telstra.net [139.130.169.227]) (authenticated sender plengada@optusnet.com.au) by mail07.syd.optusnet.com.au (8.13.1/8.13.1) with ESMTP id lBD5uIF2032539 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 13 Dec 2007 16:56:21 +1100 Message-ID: <4760C969.1060801@reynolds.com.au> Date: Thu, 13 Dec 2007 14:55:53 +0900 From: Paul Lambert User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: pgsql admin Subject: Re: Schema security References: <4760905F.2020406@reynolds.com.au> <3212.1197524142@sss.pgh.pa.us> In-Reply-To: <3212.1197524142@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/83 X-Sequence-Number: 27600 Tom Lane wrote: > Paul Lambert writes: >> I would have thought giving the user all privileges on a schema would by >> default add them to all objects within it, > > Why would you think that? > > The analogy to think about is that usage privilege on a schema is > comparable to read access on a directory. That doesn't necessarily give > you access to any single file in the directory --- but lack of it does > ensure you cannot get to those files. > > regards, tom lane > > Point taken and yes, I would agree that default behavior should be to not give priviledges to anything other than the explicitly defined object - but would it not be a good idea to provide some sort of cascade/recurse option to granting/revoking privileges so that doing so on a container object results in the priviledges being propogated down the line for the cases where such is desired? Taking your example of file permissions - although it is not default behavior, it is possible to recursively apply a priviledge change to a directory onto files/subdirectories within it. Certainly it can be done on OpenVMS and Windows that I work with primarily and I'm 99% sure it can be done on *ix systems too. I.e. GRANT ALL ON SCHEMA TO CASCADE; NOTICE: GRANT ALL cascades to table "billings" NOTICE: GRANT ALL cascades to table "customers" NOTICE: GRANT ALL cascades to function "calculate_daily_balance()" etc... Much the same way that truncate or drop and so forth can have a cascade option to propogate down to dependant objects. -- Paul Lambert Database Administrator AutoLedgers