Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id BA5AF2E4592 for ; Fri, 18 Jan 2008 09:46:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 18353-07 for ; Fri, 18 Jan 2008 09:46:23 -0400 (AST) Received: from web56404.mail.re3.yahoo.com (web56404.mail.re3.yahoo.com [216.252.111.83]) by postgresql.org (Postfix) with SMTP id 9DB0C2E44EF for ; Fri, 18 Jan 2008 09:46:34 -0400 (AST) Received: (qmail 47201 invoked by uid 60001); 18 Jan 2008 13:46:33 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=MckE8++DtYaOVE+LoOtfCmq2JDxjg7jJmUN2q6e7IzwiRz9hc22qhilfN0FtW95yY/4NDsgffab0MWOZwJivmj4SHgz6HjCSwyClYvR9q1eGIyBnGs2XphYyGpGuVcLmVyqAeXN1vPzhMgbq4UROtFJ3y2UE6KGEkKMJMHrixsI=; X-YMail-OSG: XrlTCQAVM1liuYJpuToU..Ph1Syzb4lqMMRV6QkroOvjra8aXRYjmf4D057dU2MCvSC6XeyEvDqM8_wljgJoP1DJb_j5gYdBXkhjvOvsuZyzcF7bi9VIhtrYCIlb5j8F.xmwfMyzHTtWQDrJfvYtrKUmLw-- Received: from [198.119.134.32] by web56404.mail.re3.yahoo.com via HTTP; Fri, 18 Jan 2008 05:46:33 PST Date: Fri, 18 Jan 2008 05:46:33 -0800 (PST) From: Jessica Richard Subject: Re: select only user To: "Milen A. Radev" , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-239525078-1200663993=:46379" Content-Transfer-Encoding: 8bit Message-ID: <58370.46379.qm@web56404.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/209 X-Sequence-Number: 27983 --0-239525078-1200663993=:46379 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Thanks!!! "ALTER USER username SET default_transaction_read_only to true" worked. One more question: where do I see the current setting of "default_transacton_read_only (true for false) for each user on the system? Is there a table that keeps this kind of info for all users? Thanks again, Jessica "Milen A. Radev" wrote: Milen A. Radev написа: > Jessica Richard написа: >> I created a user and granted only "select" to this user so that this >> user can only select the tables on a production system. But by >> default, this user can also CREATE TABLE successfully.... >> >> How can I revoke this "create table" privilege (and possible others) >> and limit this user to only the permissions granted by me? >> > > Revoke the "create" privilege on the "public" schema (or any other > schema you've created) from 'public' (that's special "role"). Also > revoke the "create" privilege on the database(s) in question from the > same special role 'public' to prevent creation of new schemas. > > More here - http://www.postgresql.org/docs/current/static/sql-grant.html. Almost forgot - there is another way if the role in question would be read-only: ALTER USER username SET default_transaction_read_only to true; (http://archives.postgresql.org/pgsql-admin/2007-10/msg00101.php) -- Milen A. Radev ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate --------------------------------- Looking for last minute shopping deals? Find them fast with Yahoo! Search. --0-239525078-1200663993=:46379 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Thanks!!!
"ALTER USER username SET default_transaction_read_only to true" worked.

One more question:

where do I see the current setting of "default_transacton_read_only (true for false) for each user on the system? Is there a table that keeps this kind of info for all users?

Thanks again,
Jessica

"Milen A. Radev" <milen@radev.net> wrote:
Milen A. Radev написа:
> Jessica Richard написа:
>> I created a user and granted only "select" to this user so that this
>> user can only select the tables on a production system. But by
>> default, this user can also CREATE TABLE successfully....
>>
>> How can I revoke this "create table" privilege (and possible others)
>> and limit this user to only the permissions granted by me?
>>
>
> Revoke the "create" privilege on the "public" schema (or any other
> schema you've created) from 'public' (that's special "role"). Also
> revoke the "create" privilege on the database(s) in question from the
> same special role 'public' to prevent creation of new schemas.
>
> More here - http://www.postgresql.org/docs/current/static/sql-grant.html.

Almost forgot - there is another way if the role in question would be
read-only:

ALTER USER username SET default_transaction_read_only to true;

(http://archives.postgresql.org/pgsql-admin/2007-10/msg00101.php)




--
Milen A. Radev


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Looking for last minute shopping deals? Find them fast with Yahoo! Search. --0-239525078-1200663993=:46379--