Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 8A67C2E3D9D for ; Thu, 13 Dec 2007 09:01:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 80124-01 for ; Thu, 13 Dec 2007 09:01:15 -0400 (AST) Received: from lists.commandprompt.com (host-159.commandprompt.net [207.173.203.159]) by postgresql.org (Postfix) with ESMTP id 0F61F2E3D91 for ; Thu, 13 Dec 2007 09:01:19 -0400 (AST) Received: from perhan.alvh.no-ip.org (200-126-76-117.bk5-dsl.surnet.cl [200.126.76.117]) (authenticated bits=0) by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id lBDD1F8A000636 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Thu, 13 Dec 2007 05:01:17 -0800 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 0B2CC47C87; Thu, 13 Dec 2007 10:01:15 -0300 (CLST) Date: Thu, 13 Dec 2007 10:01:15 -0300 From: Alvaro Herrera To: Paul Lambert Cc: pgsql admin Subject: Re: Schema security Message-ID: <20071213130114.GB6262@alvh.no-ip.org> References: <4760905F.2020406@reynolds.com.au> <3212.1197524142@sss.pgh.pa.us> <4760C969.1060801@reynolds.com.au> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <4760C969.1060801@reynolds.com.au> User-Agent: Mutt/1.5.17 (2007-11-01) X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (lists.commandprompt.com [207.173.203.159]); Thu, 13 Dec 2007 05:01:18 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/84 X-Sequence-Number: 27601 Paul Lambert wrote: > 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... Yes, it can be done at least with GNU chmod by using chmod -R. The problem is that it doesn't work too well for schemas -> tables, because the set of acceptable privileges is completely different. So the only case that would work is GRANT ALL. Perhaps what could work is to be able to specify wildcards in GRANT, for example GRANT SELECT, INSERT ON TABLE schema.* TO The problem with this idea is what happens if you create a new table in that schema? Is the role given access to that table? (The other problem is whether this new command conforms to the SQL standard, or is it in conflict with it.) -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "Estoy de acuerdo contigo en que la verdad absoluta no existe... El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)