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 3B11A9DCBDF for ; Tue, 29 Nov 2005 19:30:41 -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 37122-02 for ; Tue, 29 Nov 2005 19:30:42 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by postgresql.org (Postfix) with ESMTP id 68BD09DCBBF for ; Tue, 29 Nov 2005 19:30:38 -0400 (AST) Received: by wproxy.gmail.com with SMTP id 37so2542524wra for ; Tue, 29 Nov 2005 15:30:41 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=DEPyc8tpo3aLR3hGtcrzeARX57MKA+JDQi686cFbMf71pp/e9F7jSDy7wYuPoWSTi0x4BjLHAdtp5aQttKfvRXdyQD60mKPyiTJqU8lrB7WJYYZIyxt5YBiZanXM6AJlbGME/hD3ONLFY5c4Y+PAWdATKk8bCld9PtmIJU9bHWw= Received: by 10.54.159.11 with SMTP id h11mr9335863wre; Tue, 29 Nov 2005 15:30:40 -0800 (PST) Received: by 10.54.118.11 with HTTP; Tue, 29 Nov 2005 15:30:33 -0800 (PST) Message-ID: <65029d820511291530mc4a0f62nc1de5cf58d110765@mail.gmail.com> Date: Tue, 29 Nov 2005 15:30:33 -0800 From: Dan Tenenbaum To: pgsql-admin@postgresql.org Subject: the "users" group and restricting privileges MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4056_23733855.1133307033430" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.643 required=5 tests=[HTML_00_10=0.642, HTML_MESSAGE=0.001] X-Spam-Score: 0.643 X-Spam-Level: X-Archive-Number: 200511/338 X-Sequence-Number: 19711 ------=_Part_4056_23733855.1133307033430 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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? 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 th= e 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 t= o 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 i= s hard to disambiguate my search. If someone could point me towards documentation of the special properties o= f 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. Thanks ------=_Part_4056_23733855.1133307033430 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 wi= th 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?

Also, there is a group called "users" and the database owner is i= n 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&quo= t; 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&q= uot; 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 t= hing I most want help with is creating a user with restricted views as described above.

Thanks


------=_Part_4056_23733855.1133307033430--