The following bug has been logged online:

Bug reference: 2516
Logged by: Sam Howard
Email address: sam.howard (AT) gmxtechnologies (DOT) 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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Sam Howard" <sam.howard (AT) gmxtechnologies (DOT) com> writes:
I believe the problem is here:

The behavior you're expecting requires appuser to be marked INHERIT.
As the CREATE ROLE man page explains:

A role with the INHERIT attribute can automatically use whatever
database privileges have been granted to all roles it is
directly or indirectly a member of. Without INHERIT, membership
in another role only grants the ability to SET ROLE to that
other role; the privileges of the other role are only available
after having done so. If not specified, INHERIT is the default.

regards, tom lane

PS: Sorry for belated response, but I and most of the other developers
have been off at a conference ...

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match