Skip site navigation (1) Skip section navigation (2)

Re: permissions on tables

From: "Hyatt, Gordon" <Gordon(dot)Hyatt(at)joslin(dot)harvard(dot)edu>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: permissions on tables
Date: 2008-03-18 12:30:31
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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?  


Thanks, again.






From: Vishal Arora [mailto:aroravishal22(at)hotmail(dot)com] 
Sent: Tuesday, March 18, 2008 4:33 AM
To: Hyatt, Gordon; pgsql-admin(at)postgresql(dot)org
Subject: RE: [ADMIN] permissions on tables



Subject: [ADMIN] permissions on tables
Date: Mon, 17 Mar 2008 16:26:27 -0400
From: Gordon(dot)Hyatt(at)joslin(dot)harvard(dot)edu
To: pgsql-admin(at)postgresql(dot)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


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


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


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,



BTW, I'm running 8.2.6 on WinXP x64 SP2.









Detailed profiles 4 marriage! Only at Try it!

In response to


pgsql-admin by date

Next:From: Julius TuskenisDate: 2008-03-18 14:46:50
Subject: Locks with no database or relation
Previous:From: sathiya psqlDate: 2008-03-18 10:24:44
Subject: Re: postgresql performance tuning tools

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group