Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9A1D12E017F for ; Tue, 18 Mar 2008 14:14:46 -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 95559-04 for ; Tue, 18 Mar 2008 14:14:37 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from blu139-omc1-s8.blu139.hotmail.com (blu139-omc1-s8.blu139.hotmail.com [65.55.175.148]) by postgresql.org (Postfix) with ESMTP id 32FD22E017E for ; Tue, 18 Mar 2008 14:14:36 -0300 (ADT) Received: from BLU110-W21 ([65.55.162.181]) by blu139-omc1-s8.blu139.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Tue, 18 Mar 2008 10:14:35 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_b87c36c9-8912-4eaa-9c33-08c6927b35d1_" X-Originating-IP: [220.226.39.221] From: Vishal Arora To: "Hyatt, Gordon" , Subject: Re: permissions on tables Date: Tue, 18 Mar 2008 22:44:35 +0530 Importance: Normal In-Reply-To: <8EA62D1B324F0A438A36E8B5F0848EB8F31F30@jdcmail1.joslin.harvard.edu> References: <8EA62D1B324F0A438A36E8B5F0848EB8F31F27@jdcmail1.joslin.harvard.edu> <8EA62D1B324F0A438A36E8B5F0848EB8F31F30@jdcmail1.joslin.harvard.edu> MIME-Version: 1.0 X-OriginalArrivalTime: 18 Mar 2008 17:14:35.0206 (UTC) FILETIME=[89FA3E60:01C8891B] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.657 tagged_above=0 required=5 tests=AWL=0.846, HTML_MESSAGE=0.001, RCVD_IN_SBL=2.81 X-Spam-Level: *** X-Archive-Number: 200803/137 X-Sequence-Number: 28620 --_b87c36c9-8912-4eaa-9c33-08c6927b35d1_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Subject: Re: [ADMIN] permissions on tablesDate: Tue, 18 Mar 2008 08:30:31 -= 0400From: Gordon.Hyatt@joslin.harvard.eduTo: pgsql-admin@postgresql.org Thanks for your response. =20 I did not explicitly use NOINHERIT (in fact I used the PgAdmin v1.6.3 New L= ogin Role and new Group Role wizard to create the user). I see that NOINHE= RIT is specified when I look at the SQL pane in PgAdmin for that login role= . =20 I=92ll remove and recreate that user allowing permissions to inherit from p= arent roles. =20 =20 It seems to me that one would usually (not always) want user roles to inher= it privileges from parent roles (including group roles). Do you know why P= gAdmin defaults to NOINHERIT on user roles? =20 =20 Please use PgAdmin Support (pgadmin-support@postgresql.org) mailing list fo= r PgAdmin related queries. =20 =20 Thanks, again. =20 Gord =20 =20 From: Vishal Arora [mailto:aroravishal22@hotmail.com] Sent: Tuesday, March = 18, 2008 4:33 AMTo: Hyatt, Gordon; pgsql-admin@postgresql.orgSubject: RE: [= ADMIN] permissions on tables =20 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 =20 Detailed profiles 4 marriage! Only at Shaadi.com Try it! _________________________________________________________________ Post ads for free - to sell, rent or even buy.www.yello.in http://ss1.richmedia.in/recurl.asp?pid=3D186= --_b87c36c9-8912-4eaa-9c33-08c6927b35d1_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable




Subject: Re: [ADMIN] permissions on tables
Date: Tue, 18 Mar 2008 08:30:= 31 -0400
From: Gordon.Hyatt@joslin.harvard.edu
To: pgsql-admin@postgr= esql.org

Thanks for your resp= onse.

 =

I did not explicitly= use NOINHERIT (in fact I used the PgAdmin v1.6.3 New Login Role and new Gr= oup Role wizard to create the user).  I see that NOINHERIT is specifie= d when I look at the SQL pane in PgAdmin for that login role.=

 =

I=92ll remove and re= create that user allowing permissions to inherit from parent roles.  <= /SPAN>

 =

It seems to me that = one would usually (not always) want user roles to inherit privileges from p= arent roles (including group roles).  Do you know why PgAdmin defaults= to NOINHERIT on user roles? 

 =

Please use PgAdmin Support (pgadmin-support@postgresql.org) mailing = list for PgAdmin related queries.



 

 =

Thanks, again.

 =

Gord

 =

 =


From: Vishal Arora [mailto:aroravishal22@hotmail.com]
Sent: Tuesday, March 18, 2008 4:33 AM
= To: Hyatt, Gordon; pgsql-ad= min@postgresql.org
Subject:<= /B> RE: [ADMIN] permissions on tables

 




Subject: [ADMI= N] 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.<= SPAN style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">

 

Did you use NOINHERI= T while creating 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.<= /P>

 

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!



Post f= ree auto ads on Yello Classifieds now! Try it now! = --_b87c36c9-8912-4eaa-9c33-08c6927b35d1_--