Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 115999FBB53 for ; Thu, 11 Oct 2007 17:12:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 11460-01 for ; Thu, 11 Oct 2007 17:11:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id A18549FBB42 for ; Thu, 11 Oct 2007 17:11:57 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1Ig4LM-00072Q-Ma for pgsql-admin@postgresql.org; Thu, 11 Oct 2007 20:09:16 +0000 Received: from 77.70.61.39 ([77.70.61.39]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 11 Oct 2007 20:09:16 +0000 Received: from milen by 77.70.61.39 with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 11 Oct 2007 20:09:16 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: "Milen A. Radev" Subject: Re: how to create an user with ONLY select permission and nothing else? Date: Thu, 11 Oct 2007 22:35:43 +0300 Lines: 27 Message-ID: References: <320881.18517.qm@web56413.mail.re3.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1251 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: 77.70.61.39 User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) In-Reply-To: <320881.18517.qm@web56413.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/103 X-Sequence-Number: 27017 Jessica Richard написа: [...] > Two questions: > > 1. How do I create a user with ONLY select permission but nothing else (not even creating any new tables)? You could not create a read-only role by just using CREATE ROLE - you could control the privileges that role has on particular DB objects. When you create DB objects (schemas, tables, functions etc.) revoke all privileges from PUBLIC, because every authenticated user is (part of) PUBLIC. Then give some privileges to those roles that need them - like "SELECT" for the role in question. > > 2. How do I check to see what permissions a particular user currently has? > > I know that \z tablename can show who has what permissions on a table, but it does not tell if a user has "create table" permission or not. Tables are created in schemas so you need to check if the role has "CREATE" privilege for the particular schema. Check also the "CREATE" privilege for that role on the DB. -- Milen A. Radev