Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 36F812E00C8
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Thu, 19 Jun 2008 14:57:52 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 52997-03-2
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Thu, 19 Jun 2008 14:57:37 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by developer.postgresql.org (Postfix) with ESMTP id 55AF52E00CF
	for <pgsql-admin@postgresql.org>; Thu, 19 Jun 2008 14:57:02 -0300 (ADT)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Thu, 19 Jun 2008 12:57:00 -0500
Message-Id: <485A5758.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.3 
Date: Thu, 19 Jun 2008 12:56:51 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Rafael Domiciano" <rafael.domiciano@gmail.com>,
	"Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-admin@postgresql.org>,"Milen A. Radev" <milen@radev.net>
Subject: Re: Revoke for a new role
References: <3a0028490806130635v2a0890easf8c22b8362d06e89@mail.gmail.com>
	<g2tur0$rtq$1@ger.gmane.org> <11208.1213369902@sss.pgh.pa.us>
	<3a0028490806130906i553f9588g810b2d19d95a91a1@mail.gmail.com>
In-Reply-To: <3a0028490806130906i553f9588g810b2d19d95a91a1@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200806/159
X-Sequence-Number: 29497

>>> On Fri, Jun 13, 2008 at 11:06 AM, in message
<3a0028490806130906i553f9588g810b2d19d95a91a1@mail.gmail.com>, "Rafael
Domiciano" <rafael.domiciano@gmail.com> wrote: 
> So, there is no manner to define that the user can't do create or
drop
> objects, but can create temp tables?
 
What we normally do is something like this.
 
create user dbowner password 'ownerpasswd';
create user db password 'normalpasswd';
create user dbviewer password 'viewerpasswd';
create database db with owner dbowner;
\c db
revoke create on database db from public;
revoke create on schema public from public;
grant create on schema public to dbowner;
set role dbowner;
<create objects>
 
Then we limit access to the given database to the appropriate users in
the pg_hba.conf file.  The owner grants the desired rights to each
user.
 
I hope this helps.
 
-Kevin