permissions on tables

From: "Hyatt, Gordon" <Gordon(dot)Hyatt(at)joslin(dot)harvard(dot)edu>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: permissions on tables
Date: 2008-03-17 20:26:27
Message-ID: 8EA62D1B324F0A438A36E8B5F0848EB8F31F27@jdcmail1.joslin.harvard.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Suresh Gupta VG 2008-03-18 05:43:39 Re: postgresql performance tuning tools
Previous Message Tom Lane 2008-03-17 20:11:04 Re: invalid memory alloc request