pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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 14:03:11 +0530
Message-ID: <BLU110-W20D2C65A381780816FEAC9A9060@phx.gbl> (raw)
In-Reply-To: <8EA62D1B324F0A438A36E8B5F0848EB8F31F27@jdcmail1.joslin.harvard.edu>
References: <8EA62D1B324F0A438A36E8B5F0848EB8F31F27@jdcmail1.joslin.harvard.edu>




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
 
_________________________________________________________________
Post free property ads on Yello Classifieds now! www.yello.in
http://ss1.richmedia.in/recurl.asp?pid=221=

view thread (5+ messages)  latest in thread

Message-ID: <BLU110-W20D2C65A381780816FEAC9A9060@phx.gbl>
Permalink:  ../BLU110-W20D2C65A381780816FEAC9A9060@phx.gbl/
Also on:    postgresql.org/message-id/BLU110-W20D2C65A381780816FEAC9A9060@phx.gbl

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: aroravishal22@hotmail.com, gordon.hyatt@joslin.harvard.edu
  Subject: Re: permissions on tables
  In-Reply-To: <BLU110-W20D2C65A381780816FEAC9A9060@phx.gbl>
  References: <8EA62D1B324F0A438A36E8B5F0848EB8F31F27@jdcmail1.joslin.harvard.edu> <BLU110-W20D2C65A381780816FEAC9A9060@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