Re: permissions on tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 








Subject: permissions on tables
Date: Mon, 17 Mar 2008 16:26:27 -0400
From: Gordon.Hyatt@xxxxxxxxxxxxxxxxxx
To: pgsql-admin@xxxxxxxxxxxxxx

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!

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux