I just made something similar for a colleague. Essentially I made a hidden table that contained one row for each (user,role) pair with suitable constraints. The user table was then a view of the hidden table with all the roles assembled into an array. I then made it possible to insert into the view by adding an appropriate rule. Here is how:
trailer=# create table harvester (id int unique, label text);
CREATE TABLE
trailer=# insert into harvester values (1,'grain'), (2,'cricket');
INSERT 0 2
trailer=# create table donkey (id int, others int references
harvester(id));
CREATE TABLE
trailer=# create unique index donkey_ears on donkey (id, others);
CREATE INDEX
trailer=# create view combine as select id, array_agg(others) as others
from donkey group by id;
CREATE VIEW
trailer=# create rule combine_insert as on insert to combine do instead
(delete from donkey where donkey.id=new.id;insert into donkey select
new.id,unnest(new.others) );
CREATE RULE
trailer=# insert into combine values (1,'{1,2}');INSERT 0 2
trailer=# select * from combine ;
id | others
----+--------
1 | {1,2}
(1 row)
trailer=# insert into combine values (1,'{1,2}');
INSERT 0 2
trailer=# select * from combine ;
id | others
----+--------
1 | {1,2}
(1 row)
trailer=# insert into combine values (2,'{1,2,3}');
ERROR: insert or update on table "donkey" violates foreign key
constraint "donkey_others_fkey"
DETAIL: Key (others)=(3) is not present in table "harvester".
trailer=#
I hope that helps. You can make it a bit more efficient and add more rules depending on your requirements.