From rjessil@yahoo.com Thu Jan 17 14:30:47 2008 Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 576DF2E004A for ; Thu, 17 Jan 2008 10:30:47 -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 95991-06 for ; Thu, 17 Jan 2008 10:30:33 -0400 (AST) Received: from web56407.mail.re3.yahoo.com (web56407.mail.re3.yahoo.com [216.252.111.86]) by postgresql.org (Postfix) with SMTP id B5D682E379A for ; Thu, 17 Jan 2008 10:30:38 -0400 (AST) Received: (qmail 88803 invoked by uid 60001); 17 Jan 2008 14:30:37 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=TJpfU7jQZ/aj0fHF22XcSyGNxY104ZO6mcAZNzdOwp9JAHqr3yeGw93QdufZfyrkyerWO86umHcVACjO+69l0YSy7n6WXmpGKwVfpUfKQEzkm8a9QkueveN2yAIJFBfyKZEiN3beMkaSo9PL7mRav+eZaT36xkEi4wktkOghDc8=; X-YMail-OSG: 5KiN89cVM1kS78UUaHRqPQ5sI6OmBwGQJ7ih1l_BF52eiNUQZNz5d734MzWN5TzKpBn.xqNSOwdgHBLIGJhodtsTIjiNGleobX.NmuABJ6.2PIQlF8iaeRFYWhhvSr9mYHDuk8QK4Bmn50Bp7Z6LQqiL Received: from [198.119.134.32] by web56407.mail.re3.yahoo.com via HTTP; Thu, 17 Jan 2008 06:30:37 PST Date: Thu, 17 Jan 2008 06:30:37 -0800 (PST) From: Jessica Richard Subject: select only user To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-315546726-1200580237=:88733" Content-Transfer-Encoding: 8bit Message-ID: <77335.88733.qm@web56407.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/201 X-Sequence-Number: 27975 --0-315546726-1200580237=:88733 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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? Thanks, Jessica --------------------------------- Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. --0-315546726-1200580237=:88733 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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?

Thanks,
Jessica


Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. --0-315546726-1200580237=:88733-- From milen@radev.net Thu Jan 17 15:15:44 2008 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 46E802E00E1 for ; Thu, 17 Jan 2008 11:15:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 74514-06 for ; Thu, 17 Jan 2008 11:15:30 -0400 (AST) Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id A261D2E00C1 for ; Thu, 17 Jan 2008 11:15:39 -0400 (AST) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1JFWSu-0007zb-EO for pgsql-admin@postgresql.org; Thu, 17 Jan 2008 15:15:36 +0000 Received: from pirus.securax.be ([213.16.46.130]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 17 Jan 2008 15:15:36 +0000 Received: from milen by pirus.securax.be with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 17 Jan 2008 15:15:36 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: "Milen A. Radev" Subject: Re: select only user Date: Thu, 17 Jan 2008 17:15:30 +0200 Lines: 15 Message-ID: References: <77335.88733.qm@web56407.mail.re3.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: pirus.securax.be User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) In-Reply-To: <77335.88733.qm@web56407.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/202 X-Sequence-Number: 27976 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. -- Milen A. Radev From milen@radev.net Thu Jan 17 15:24:31 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 11BE72E2EE6 for ; Thu, 17 Jan 2008 11:24:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 69503-01-2 for ; Thu, 17 Jan 2008 11:24:20 -0400 (AST) Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id E45632E2EFB for ; Thu, 17 Jan 2008 11:24:20 -0400 (AST) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1JFWbB-0008Rp-S7 for pgsql-admin@postgresql.org; Thu, 17 Jan 2008 15:24:10 +0000 Received: from pirus.securax.be ([213.16.46.130]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 17 Jan 2008 15:24:09 +0000 Received: from milen by pirus.securax.be with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 17 Jan 2008 15:24:09 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: "Milen A. Radev" Subject: Re: select only user Date: Thu, 17 Jan 2008 17:24:02 +0200 Lines: 29 Message-ID: References: <77335.88733.qm@web56407.mail.re3.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: pirus.securax.be User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/203 X-Sequence-Number: 27977 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 From rjessil@yahoo.com Fri Jan 18 13:46:41 2008 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-- From milen@radev.net Fri Jan 18 13:55:06 2008 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A52A12E034F for ; Fri, 18 Jan 2008 09:55:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 40015-10 for ; Fri, 18 Jan 2008 09:54:59 -0400 (AST) Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id D3CF92E2EF0 for ; Fri, 18 Jan 2008 09:54:59 -0400 (AST) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1JFrgM-0008Nz-2y for pgsql-admin@postgresql.org; Fri, 18 Jan 2008 13:54:54 +0000 Received: from pirus.securax.be ([213.16.46.130]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Fri, 18 Jan 2008 13:54:54 +0000 Received: from milen by pirus.securax.be with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Fri, 18 Jan 2008 13:54:54 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: "Milen A. Radev" Subject: Re: select only user Date: Fri, 18 Jan 2008 15:54:46 +0200 Lines: 34 Message-ID: References: <58370.46379.qm@web56404.mail.re3.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: pirus.securax.be User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) In-Reply-To: <58370.46379.qm@web56404.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/210 X-Sequence-Number: 27984 Jessica Richard написа: > 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? [...] SELECT * FROM pg_roles where rolname = ''; -[ RECORD 1 ]-+------------------------------------- rolname | rolsuper | f rolinherit | t rolcreaterole | f rolcreatedb | f rolcatupdate | f rolcanlogin | t rolconnlimit | -1 rolpassword | ******** rolvaliduntil | rolconfig | {default_transaction_read_only=true} oid | 53531114 (http://www.postgresql.org/docs/current/static/database-roles.html) -- Milen A. Radev