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. :)
