BUG #2516: group privs do not seem to be honored - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2516
Logged by:          Sam Howard
Email address:      sam.howard@gmxtechnologies.com
PostgreSQL version: 8.1.4 -Deb Etch
Operating system:   Linux - Debian Etch
Description:        group privs do not seem to be honored
Details:

Trying to implement user level access and security, and am finding
unexpected behavior with respect to group roles.

This sort of user/group structure seems like it should be commonplace, so I
have a hard time believing it is a bug, but I cannot figure out why it is
not working as I expect.  Apologies in advance if it is user error.  :)

Platform: Debian Etch (testing)
DB: PostgreSQL 8.1.4 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.4
20060507 (prerelease) (Debian 4.0.3-3), pkg version 8.1.4-2

Simple scenerio:

Create a role to own the db objects:

CREATE ROLE dbowner LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

Create a group role for the user roles to belong to:

CREATE ROLE db_group
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

Create the application role and add it to the group:

CREATE ROLE appuser LOGIN
  ENCRYPTED PASSWORD 'mdblahblahblah'
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
GRANT db_group TO appuser;

Create table foo with a serial and varchar column, and matching sequence for
the serial:

CREATE TABLE foo
(
  id serial NOT NULL,
  data1 varchar(32),
  CONSTRAINT foo_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE foo OWNER TO dbowner;
GRANT ALL ON TABLE foo TO dbowner;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE foo TO db_group;

Now, the default privs on the sequence do not include the db_group, so let's
add them now:

GRANT SELECT, UPDATE ON foo_id_seq TO db_group;

With a sample table and associated sequence set up, and group privs to
insert (or update in the case of the sequence), let's try an insert as the
user "appuser":

=> insert into foo (data1) values ('some stuff');
ERROR:  permission denied for relation foo

=> \z foo;
             Access privileges for database "db"
 Schema | Name | Type  |          Access privileges
--------+------+-------+--------------------------------------
 public | foo  | table | {dbowner=arwdRxt/dbowner,db_group=arwd/dbowner}
(1 row)

=> \z foo_id_seq;
                Access privileges for database "db"
 Schema |    Name    |   Type   |         Access privileges
--------+------------+----------+------------------------------------
 public | foo_id_seq | sequence |
{dbowner=arwdRxt/dbowner,db_group=rw/dbowner}
(1 row)

=> \dg;
                                 List of roles
  Role name   | Superuser | Create role | Create DB | Connections |  Member
of
--------------+-----------+-------------+-----------+-------------+---------
----
 dbowner      | no        | no          | no        | no limit    |
{db_group}
 appuser      | no        | no          | no        | no limit    |
{db_group}

Based on appuser being in the group role db_group, and db_group having
select, insert, update on the table foo, and select, update on its sequence,
foo_seq_id, I would expect the insert to succeed.

If I specifically grant select, insert, update for the user appuser to the
table foo like:

GRANT SELECT, INSERT, UPDATE ON foo TO appuser;

Then try my INSERT:

=> insert into foo (data1) values ('some stuff');
ERROR:  permission denied for sequence foo_id_seq

This continues to make me wonder if the membership of appuser in the group
role db_group is having any effect.

Adding grants to the sequence like:

GRANT SELECT, UPDATE ON foo_id_seq TO appuser;

And then trying the INSERT again:

=> insert into foo (data1) values ('some stuff');
INSERT 0 1

SUCCESS!

Please feel free to direct me to some additional documentation that
addresses this issue, if it is not a bug.  I've read over the GRANT manpage
a few times, and I *think* I'm doing it right.

The fact that if I add the specific user rights, it works, makes me hope
it's broken and not me.  :)