From: Vishal Arora <aroravishal22@hotmail.com>
To: Hyatt, Gordon <gordon.hyatt@joslin.harvard.edu>
To: pgsql-admin@postgresql.org
Subject: Re: permissions on tables
Date: Tue, 18 Mar 2008 22:44:35 +0530
Message-ID: <BLU110-W2139D7E8056C2C1F4D6847A9060@phx.gbl> (raw)
In-Reply-To: <8EA62D1B324F0A438A36E8B5F0848EB8F31F30@jdcmail1.joslin.harvard.edu>
References: <8EA62D1B324F0A438A36E8B5F0848EB8F31F27@jdcmail1.joslin.harvard.edu>
<BLU110-W20D2C65A381780816FEAC9A9060@phx.gbl>
<8EA62D1B324F0A438A36E8B5F0848EB8F31F30@jdcmail1.joslin.harvard.edu>
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.
I did not explicitly use NOINHERIT (in fact I used the PgAdmin v1.6.3 New Login Role and new Group Role wizard to create the user). I see that NOINHERIT is specified when I look at the SQL pane in PgAdmin for that login role.
I’ll remove and recreate that user allowing permissions to inherit from parent roles.
It seems to me that one would usually (not always) want user roles to inherit privileges from parent 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 AMTo: Hyatt, Gordon; pgsql-admin@postgresql.orgSubject: RE: [ADMIN] permissions on tables
Subject: [ADMIN] permissions on tablesDate: Mon, 17 Mar 2008 16:26:27 -0400From: Gordon.Hyatt@joslin.harvard.eduTo: pgsql-admin@postgresql.org
Forgive me if this is not the correct list for this type of question.
I thought I understood PostgreSQL’s privileges well enough, but I’m running into problems, so I must misunderstand something.
I have a website that I’m adding functionality to, and therefore need to expand the database. The database already contains around 30 populated tables with 1 group role (group_reader) and 1 user role (user_reader). To all existing tables, I’d assigned PUBLIC and group_reader SELECT privilege.
Everything is working fine.
Now, I created one more group role (called group_writer) and another user role (user_writer) and make sure that user_writer is a member of group_writer.
Did you use NOINHERIT while creating the user role? if yes, please create it without this parameter.
I then explicitly grant group_writer SELECT privilege on all tables. (I know this is technically not necessary as PUBLIC 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_writer SELECT, INSERT, UPDATE, and DELETE privileges to this table. Looking at the ACL list for this table confirms this.
When I attempt to access this table as user_writer, I’m denied access. I’m access this through Tomcat and verifying the connected user as user_writer.
I shouldn’t have to grant the PUBLIC group full access to this table as well, should I?
From what I understand of the manual, a user’s privileges are the SUM of the privileges of all groups of which that user is a member. Therefore, user_writer’s privileges should be {SELECT, INSERT, UPDATE, DELETE} from group_writer plus {} from PUBLIC, which should yield {SELECT, INSERT, UPDATE, DELETE}.
BTW, I’m running 8.2.6 on WinXP x64 SP2.
Thanks,
Gord
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=186=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: aroravishal22@hotmail.com, gordon.hyatt@joslin.harvard.edu
Subject: Re: permissions on tables
In-Reply-To: <BLU110-W2139D7E8056C2C1F4D6847A9060@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox