X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 93CEB9DD60D for ; Tue, 29 Nov 2005 20:41:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01538-03-4 for ; Tue, 29 Nov 2005 20:42:00 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 19DBB9DCD84 for ; Tue, 29 Nov 2005 20:13:01 -0400 (AST) Received: by flake.decibel.org (Postfix, from userid 1001) id ED7311529D; Tue, 29 Nov 2005 18:13:04 -0600 (CST) Date: Tue, 29 Nov 2005 18:13:04 -0600 From: "Jim C. Nasby" To: Dan Tenenbaum Cc: pgsql-admin@postgresql.org Subject: Re: the "users" group and restricting privileges Message-ID: <20051130001304.GV78939@pervasive.com> References: <65029d820511291530mc4a0f62nc1de5cf58d110765@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <65029d820511291530mc4a0f62nc1de5cf58d110765@mail.gmail.com> X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.008 required=5 tests=[AWL=0.008] X-Spam-Score: 0.008 X-Spam-Level: X-Archive-Number: 200511/340 X-Sequence-Number: 19713 On Tue, Nov 29, 2005 at 03:30:33PM -0800, Dan Tenenbaum wrote: > I want to create a postgresql user with restricted permissions--all it > should be able to do is select on a few views that I specify. > > So I did the following, as the database owner: > create user viewer password 'xxxx'; > grant select on myview to viewer; > > Then, when I start psql as the viewer user, specifying the same database > with the -d switch, I try this: > select * from myview; > and get this: > ERROR: permission denied for schema myschema > What do I need to do to get the correct permissions? GRANT USAGE ON SCHEMA myschema TO viewer; > Also, there is a group called "users" and the database owner is in that > group. But I have not granted any explicit privileges to that group. And the > database is owned by a particular user, not a group. However, I notice that > when I added the "viewer" user to the "users" group, that the user seemed to > be able to do everything that the database owner could do. That is not what > I want. But the above (not being able to select a view that I thought I had > select permission for) is not what I want either. > > The above paragraph would seem to suggest that a group called "users" has > some special properties. I could not find any documentation for that in the > postgres docs (I am using version 7.4). Perhaps I couldn't find it because > almost every page in the docs seems to have the word "users" in it, so it is > hard to disambiguate my search. > If someone could point me towards documentation of the special properties of > the "users" group that would be helpful...but the thing I most want help > with is creating a user with restricted views as described above. There is no default users group, only PUBLIC (which isn't really a group in the system afaict). If you install newsysviews (http://pgfoundry.org/projects/newsysviews/) you can query pg_user_grants to help diagnose where the permissions are comming from. Or you could use a big, hairy query to do it... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461