Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8C67E2E02EE for ; Tue, 18 Mar 2008 05:33:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 04801-01 for ; Tue, 18 Mar 2008 05:33:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from blu139-omc1-s25.blu139.hotmail.com (blu139-omc1-s25.blu139.hotmail.com [65.55.175.165]) by postgresql.org (Postfix) with ESMTP id 97CF82E0276 for ; Tue, 18 Mar 2008 05:33:13 -0300 (ADT) Received: from BLU110-W20 ([65.55.162.183]) by blu139-omc1-s25.blu139.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Tue, 18 Mar 2008 01:33:11 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_92a80b62-4152-48eb-8989-4585802df9a7_" X-Originating-IP: [59.160.193.34] From: Vishal Arora To: "Hyatt, Gordon" , Subject: Re: permissions on tables Date: Tue, 18 Mar 2008 14:03:11 +0530 Importance: Normal In-Reply-To: <8EA62D1B324F0A438A36E8B5F0848EB8F31F27@jdcmail1.joslin.harvard.edu> References: <8EA62D1B324F0A438A36E8B5F0848EB8F31F27@jdcmail1.joslin.harvard.edu> MIME-Version: 1.0 X-OriginalArrivalTime: 18 Mar 2008 08:33:11.0490 (UTC) FILETIME=[B36E0220:01C888D2] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.102 tagged_above=0 required=5 tests=AWL=0.101, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/133 X-Sequence-Number: 28616 --_92a80b62-4152-48eb-8989-4585802df9a7_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Subject: [ADMIN] permissions on tablesDate: Mon, 17 Mar 2008 16:26:27 -0400= From: Gordon.Hyatt@joslin.harvard.eduTo: pgsql-admin@postgresql.org Forgive me if this is not the correct list for this type of question. =20 I thought I understood PostgreSQL=92s privileges well enough, but I=92m run= ning into problems, so I must misunderstand something. =20 =20 I have a website that I=92m adding functionality to, and therefore need to = expand the database. The database already contains around 30 populated tab= les with 1 group role (group_reader) and 1 user role (user_reader). To all= existing tables, I=92d assigned PUBLIC and group_reader SELECT privilege. = =20 =20 Everything is working fine. =20 Now, I created one more group role (called group_writer) and another user r= ole (user_writer) and make sure that user_writer is a member of group_write= r. =20 Did you use NOINHERIT while creating the user role? if yes, please create i= t without this parameter. =20 I then explicitly grant group_writer SELECT privilege on all tables. (I kn= ow this is technically not necessary as PUBLIC has already been assigned SE= LECT privilege.) =20 I created (tbl_batch) and deliberately decided to not grant PUBLIC access t= o this table. Instead, I granted group_writer SELECT, INSERT, UPDATE, and = DELETE privileges to this table. Looking at the ACL list for this table co= nfirms this. =20 When I attempt to access this table as user_writer, I=92m denied access. I= =92m access this through Tomcat and verifying the connected user as user_wr= iter. =20 I shouldn=92t have to grant the PUBLIC group full access to this table as = well, should I? =20 From what I understand of the manual, a user=92s privileges are the SUM of = the privileges of all groups of which that user is a member. Therefore, us= er_writer=92s privileges should be {SELECT, INSERT, UPDATE, DELETE} from gr= oup_writer plus {} from PUBLIC, which should yield {SELECT, INSERT, UPDATE,= DELETE}. =20 =20 BTW, I=92m running 8.2.6 on WinXP x64 SP2. =20 =20 Thanks, =20 Gord =20 _________________________________________________________________ Post free property ads on Yello Classifieds now! www.yello.in http://ss1.richmedia.in/recurl.asp?pid=3D221= --_92a80b62-4152-48eb-8989-4585802df9a7_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable




Subject: [ADMIN] permissions on tables
Date: Mon, 17 Mar 2008 16:26:27 -= 0400
From: Gordon.Hyatt@joslin.harvard.edu
To: pgsql-admin@postgresql= .org

Forgive me if this is not the correct list for= this type of question.

 

I thought I understood PostgreSQL=92s privileg= es well enough, but I=92m running into problems, so I must misunderstand so= mething. 

 

I have a website that I=92m adding functionali= ty to, and therefore need to expand the database.  The database alread= y contains around 30 populated tables with 1 group role (group_reader) and = 1 user role (user_reader).  To all existing tables, I=92d assigned PUB= LIC and group_reader SELECT privilege. 

 

Everything is working fine.

 

Now, I created one more group role (called gro= up_writer) and another user role (user_writer) and make sure that user_writ= er is a member of group_writer.

 

Did you use NOINHERIT while cr= eating the user role? if yes, please create it without this parameter.=

 

I then explicitly grant group_writer SELECT pr= ivilege on all tables.  (I know this is technically not necessary as P= UBLIC has already been assigned SELECT privilege.)

 

I created (tbl_batch) and deliberately decided= to not grant PUBLIC access to this table.  Instead, I granted group_w= riter SELECT, INSERT, UPDATE, and DELETE privileges to this table.  Lo= oking at the ACL list for this table confirms this.

 

When I attempt to access this table as user_wr= iter, I=92m denied access.  I=92m access this through Tomcat and verif= ying the connected user as user_writer.

 

I shouldn=92t have to grant the PUBLIC  g= roup full access to this table as well, should I?

 

From what I understand of the manual, a user= =92s privileges are the SUM of the privileges of all groups of which that u= ser is a member.  Therefore, user_writer=92s privileges should be {SEL= ECT, INSERT, UPDATE, DELETE} from group_writer plus {} from PUBLIC, which s= hould yield {SELECT, INSERT, UPDATE, DELETE}.

 

 

BTW, I=92m running 8.2.6 on WinXP x64 SP2.

 

 

Thanks,

 

Gord

 


Detailed profiles 4 marriage! Only at Shaadi.com Try it! = --_92a80b62-4152-48eb-8989-4585802df9a7_--